Percona Server moved to https://jira.percona.com/projects/PS

  1. Series 5.7
  2. Bug #1405677
  3. Comment #2

Comment 2 for bug 1405677

Revision history for this message
Nilnandan Joshi (nilnandan-joshi) wrote :

Able to reproduce the same with above table structure and PS 5.6.21.

mysql> show create table a_datakind \G
*************************** 1. row ***************************
       Table: a_datakind
Create Table: CREATE TABLE `a_datakind` (
  `DataKindID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DataName` varchar(100) NOT NULL DEFAULT '',
  `DataDesc` text NOT NULL,
  `DataType` tinyint(3) unsigned NOT NULL,
  `ParentStageType` int(10) unsigned NOT NULL COMMENT 'Parent stage data refers to.',
  `ParentDataKind` int(10) unsigned DEFAULT NULL,
  `Sort` int(10) NOT NULL DEFAULT '0',
  `AllowMany` varchar(60) NOT NULL DEFAULT '' COMMENT 'Several data can be associated with move',
  `Readonly` varchar(100) NOT NULL DEFAULT '0' COMMENT 'Data are calculated at move / stage start.',
  `Required` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Data is required.',
  `onChange` varchar(100) NOT NULL DEFAULT '' COMMENT 'Javascript code',
  `onSave` varchar(100) NOT NULL DEFAULT '' COMMENT 'PHP code.',
  `Param` text NOT NULL,
  `Param2` varchar(100) NOT NULL DEFAULT '',
  `Param3` varchar(200) NOT NULL DEFAULT '',
  `DefValue` text NOT NULL COMMENT 'SQL code.',
  `Transform` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`DataKindID`),
  KEY `FK_a_datakind_type` (`DataType`),
  KEY `FK_a_datakind_datakind` (`ParentDataKind`),
  KEY `FK_a_datakind_stage` (`ParentStageType`),
  KEY `Transform` (`Transform`),
  CONSTRAINT `FK_a_datakind_datakind` FOREIGN KEY (`ParentDataKind`) REFERENCES `a_datakind` (`DataKindID`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql>
mysql> show create table data \G
*************************** 1. row ***************************
       Table: data
Create Table: CREATE TABLE `data` (
  `DataID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DataKind` int(10) unsigned NOT NULL DEFAULT '0',
  `ParentMove` int(10) unsigned NOT NULL DEFAULT '0',
  `ParentData` int(10) unsigned DEFAULT NULL,
  `Data` longblob NOT NULL,
  PRIMARY KEY (`DataID`),
  KEY `Index_data` (`DataKind`,`Data`(32)),
  KEY `FK_data_parentdata` (`ParentData`),
  KEY `FK_data_move` (`ParentMove`),
  CONSTRAINT `FK_data_datakind` FOREIGN KEY (`DataKind`) REFERENCES `a_datakind` (`DataKindID`),
  CONSTRAINT `FK_data_parentdata` FOREIGN KEY (`ParentData`) REFERENCES `data` (`DataID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql> insert into a_datakind (DataName,DataDesc,DefValue,Transform) values ('abcded','asdkjasd','asdasd',1);
Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> insert into a_datakind (DataName,DataDesc,DefValue,Transform) values ('abcded','asdkjasd','asdasd',10);
Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> insert into a_datakind (DataName,DataDesc,DefValue,Transform) values ('abcded','asdkjasd','asdasd',15);
Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> insert into a_datakind (DataName,DataDesc,DefValue,Transform) values ('abcded','asdkjasd','asdasd',11);
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> select * from a_datakind;
+------------+----------+----------+----------+-----------------+----------------+------+-----------+----------+----------+----------+--------+-------+--------+--------+----------+-----------+
| DataKindID | DataName | DataDesc | DataType | ParentStageType | ParentDataKind | Sort | AllowMany | Readonly | Required | onChange | onSave | Param | Param2 | Param3 | DefValue | Transform |
 +------------+----------+----------+----------+-----------------+----------------+------+-----------+----------+----------+----------+--------+-------+--------+--------+----------+-----------+
| 3 | abcded | asdkjasd | 0 | 0 | NULL | 0 | | 0 | 0 | | | | | | asdasd | 1 |
| 4 | abcded | asdkjasd | 0 | 0 | NULL | 0 | | 0 | 0 | | | | | | asdasd | 10 |
| 5 | abcded | asdkjasd | 0 | 0 | NULL | 0 | | 0 | 0 | | | | | | asdasd | 15 |
| 6 | abcded | asdkjasd | 0 | 0 | NULL | 0 | | 0 | 0 | | | | | | asdasd | 11 |
 +------------+----------+----------+----------+-----------------+----------------+------+-----------+----------+----------+----------+--------+-------+--------+--------+----------+-----------+
4 rows in set (0.00 sec)

mysql> insert into data (DataKind,ParentMove,Data) values (3,100,'/home/nilnandan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into data (DataKind,ParentMove,Data) values (3,200,'/home/nilnandan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into data (DataKind,ParentMove,Data) values (3,300,'/home/nilnandan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into data (DataKind,ParentMove,Data) values (3,400,'/home/nilnandan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into data (DataKind,ParentMove,Data) values (5,400,'/home/nilnandan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into data (DataKind,ParentMove,Data) values (6,400,'/home/nilnandan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into data (DataKind,ParentMove,Data) values (6,400,'/home/nilnandan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into data (DataKind,ParentMove,Data) values (6,500,'/home/nilnandan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into data (DataKind,ParentMove,Data) values (6,700,'/home/nilnandan');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 );
+--------+----------+------------+------------+-----------------+
| DataID | DataKind | ParentMove | ParentData | Data |
 +--------+----------+------------+------------+-----------------+
| 6 | 6 | 400 | NULL | /home/nilnandan |
| 7 | 6 | 400 | NULL | /home/nilnandan |
| 8 | 6 | 500 | NULL | /home/nilnandan |
| 9 | 6 | 700 | NULL | /home/nilnandan |
 +--------+----------+------------+------------+-----------------+
4 rows in set (0.00 sec)

mysql> EXPLAIN SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 );
+----+-------------+-------+------+-------------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 +----+-------------+-------+------+-------------------+-----------+---------+-------+------+-------------+
| 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 9 | Using where |
| 2 | SUBQUERY | dk | ref | PRIMARY,Transform | Transform | 5 | const | 1 | Using index |
 +----+-------------+-------+------+-------------------+-----------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 );
+----+-------------+-------+------+-------------------+------------+---------+--------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 +----+-------------+-------+------+-------------------+------------+---------+--------------------+------+-------------+
| 1 | SIMPLE | dk | ref | PRIMARY,Transform | Transform | 5 | const | 1 | Using index |
| 1 | SIMPLE | d | ref | Index_data | Index_data | 4 | test.dk.DataKindID | 1 | Using where |
 +----+-------------+-------+------+-------------------+------------+---------+--------------------+------+-------------+
2 rows in set (0.00 sec)

mysql>