MySQL Bugs: #71184: Wrong result (missing row) on select with index

Bug #71184	Wrong result (missing row) on select with index_merge from a partitioned table
Submitted:	19 Dec 2013 21:23	Modified:	20 Dec 2013 11:57
Reporter:	Elena Stepanova	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Filing on behalf of our user, initially reported as https://mariadb.atlassian.net/browse/MDEV-5392

The test case provided in "How to repeat" section produces the following results:

SELECT DISTINCT maintab.BArt FROM t1 AS maintab 
WHERE maintab.HubLst = 16000 
AND maintab.TrbwGr = '4m' 
AND maintab.BArt IN ('D', 'DA', 'DA4', 'DB', 'DQA', 'Z', 'ZA', 'ZB');
BArt

SELECT DISTINCT maintab.BArt FROM t1 AS maintab 
WHERE maintab.HubLst = 16000 
AND maintab.TrbwGr = '4m' 
AND maintab.BArt IN ('D', 'DA', 'DA4', 'DB', 'DQA', 'Z', 'ZA', 'ZB') 
AND maintab.BArt IN ('ZB','Z');
BArt
Z

Obviously, the first result is incorrect. 

How to repeat:
--source include/have_partition.inc 

CREATE TABLE `t1` ( 
  `Id` int(11) NOT NULL, 
  `BArt` char(3) NOT NULL, 
  `HubLst` mediumint(8) unsigned NOT NULL, 
  `TrbwGr` char(3) NOT NULL, 
  `GltVonDat` date NOT NULL, 
  `GltBisDat` date NOT NULL, 
  `Freq` tinyint(3) unsigned NOT NULL, 
  `Ident` char(150) DEFAULT NULL, 
  KEY `Ind_HubLst` (`HubLst`), 
  KEY `Ind_TrbwGr` (`TrbwGr`) 
) ENGINE=MyISAM 
PARTITION BY RANGE COLUMNS(BArt,GltBisDat) 
( 
 PARTITION p22 VALUES LESS THAN ('E','2011-06-30') ENGINE = MyISAM, 
 PARTITION p46 VALUES LESS THAN ('ZB','2011-06-30') ENGINE = MyISAM, 
 PARTITION p47 VALUES LESS THAN ('ZB',MAXVALUE) ENGINE = MyISAM 
); 
INSERT INTO `t1` VALUES (16011353,'E',2000,'4m','2003-06-01','2003-07-09',50,480); 
INSERT INTO `t1` VALUES (20005451,'Z',20000,'2m','2007-07-01','2008-06-18',50,400); 
INSERT INTO `t1` VALUES (20005445,'Z',20000,'2m','2007-07-01','2008-06-18',50,400); 
INSERT INTO `t1` VALUES (20005444,'Z',20000,'2m','2007-06-15','2007-06-30',50,400); 
INSERT INTO `t1` VALUES (20005443,'Z',20000,'2m','2007-06-15','2007-06-30',50,400); 
INSERT INTO `t1` VALUES (20005442,'Z',20000,'2m','2007-06-15','2007-06-30',50,400); 
INSERT INTO `t1` VALUES (20005441,'Z',20000,'2m','2007-06-15','2007-06-30',50,400); 
INSERT INTO `t1` VALUES (20005440,'Z',20000,'2m','2007-06-15','2007-06-30',50,400); 
INSERT INTO `t1` VALUES (20005439,'Z',20000,'2m','2007-06-01','2007-06-14',50,400); 
INSERT INTO `t1` VALUES (20003668,'Z',16000,'4m','2005-01-01','2006-12-31',50,230); 
INSERT INTO `t1` VALUES (20003667,'Z',16000,'4m','2005-01-01','2006-12-31',50,230); 
INSERT INTO `t1` VALUES (20003501,'Z',16000,'4m','2005-01-01','2007-05-31',50,480); 
INSERT INTO `t1` VALUES (20002352,'Z',25000,'2m','2007-01-01','2007-05-31',50,400); 
INSERT INTO `t1` VALUES (20002351,'Z',25000,'2m','2005-01-01','2006-12-31',50,400); 
INSERT INTO `t1` VALUES (20002350,'Z',25000,'2m','2005-01-01','2006-12-31',50,400); 
INSERT INTO `t1` VALUES (20002349,'Z',25000,'3m','2007-01-01','2007-05-31',50,230); 
INSERT INTO `t1` VALUES (20002348,'Z',25000,'3m','2007-01-01','2007-05-31',50,230); 
INSERT INTO `t1` VALUES (20002347,'Z',25000,'3m','2005-01-01','2006-12-31',50,230); 
INSERT INTO `t1` VALUES (20002346,'Z',25000,'3m','2005-01-01','2006-12-31',50,230); 
INSERT INTO `t1` VALUES (20002345,'Z',25000,'3m','2007-01-01','2007-05-31',50,230); 
INSERT INTO `t1` VALUES (20002344,'Z',25000,'3m','2007-01-01','2007-05-31',50,230); 
INSERT INTO `t1` VALUES (20002331,'Z',25000,'2m','2005-01-01','2006-12-31',50,230); 
INSERT INTO `t1` VALUES (20002330,'Z',25000,'2m','2005-01-01','2006-12-31',50,230); 
INSERT INTO `t1` VALUES (20002296,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400); 
INSERT INTO `t1` VALUES (20002294,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400); 
INSERT INTO `t1` VALUES (20002292,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400); 
INSERT INTO `t1` VALUES (20002290,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400); 
INSERT INTO `t1` VALUES (20002289,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400); 
INSERT INTO `t1` VALUES (20002288,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400); 
INSERT INTO `t1` VALUES (20002287,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400); 
INSERT INTO `t1` VALUES (20002286,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400); 
INSERT INTO `t1` VALUES (20002285,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400); 
INSERT INTO `t1` VALUES (20002284,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400); 
INSERT INTO `t1` VALUES (20002283,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400); 
INSERT INTO `t1` VALUES (20002282,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400); 
INSERT INTO `t1` VALUES (20002280,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400); 
INSERT INTO `t1` VALUES (20002278,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400); 
INSERT INTO `t1` VALUES (20002276,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400); 
INSERT INTO `t1` VALUES (20002274,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400); 
INSERT INTO `t1` VALUES (20002273,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400); 
INSERT INTO `t1` VALUES (20002272,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400); 
INSERT INTO `t1` VALUES (20002271,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400); 
INSERT INTO `t1` VALUES (20002270,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400); 
INSERT INTO `t1` VALUES (20002269,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400); 
INSERT INTO `t1` VALUES (20002268,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400); 
INSERT INTO `t1` VALUES (20002267,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400); 
INSERT INTO `t1` VALUES (20002266,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400); 
INSERT INTO `t1` VALUES (22000018,'ZB',25000,'4m','2008-06-06','2050-12-31',60,460); 

SELECT DISTINCT maintab.BArt FROM t1 AS maintab 
WHERE maintab.HubLst = 16000 
AND maintab.TrbwGr = '4m' 
AND maintab.BArt IN ('D', 'DA', 'DA4', 'DB', 'DQA', 'Z', 'ZA', 'ZB'); 

SELECT DISTINCT maintab.BArt FROM t1 AS maintab 
WHERE maintab.HubLst = 16000 
AND maintab.TrbwGr = '4m' 
AND maintab.BArt IN ('D', 'DA', 'DA4', 'DB', 'DQA', 'Z', 'ZA', 'ZB') 
AND maintab.BArt IN ('ZB','Z'); 

DROP TABLE t1;

Thank you for the bug report. I couldn't repeat with source server:

mysql> INSERT INTO `t1` VALUES (22000018,'ZB',25000,'4m','2008-06-06','2050-12-3
1',60,460);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT DISTINCT maintab.BArt FROM t1 AS maintab
    -> WHERE maintab.HubLst = 16000
    -> AND maintab.TrbwGr = '4m'
    -> AND maintab.BArt IN ('D', 'DA', 'DA4', 'DB', 'DQA', 'Z', 'ZA', 'ZB');
Empty set (0.05 sec)

mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.15                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.15                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set (0.01 sec)

mysql 5.6 > INSERT INTO `t1` VALUES (22000018,'ZB',25000,'4m','2008-06-06','2050-12-31',60,460);
Query OK, 1 row affected (0.00 sec)

mysql 5.6 >
mysql 5.6 > SELECT DISTINCT maintab.BArt FROM t1 AS maintab
    -> WHERE maintab.HubLst = 16000
    -> AND maintab.TrbwGr = '4m'
    -> AND maintab.BArt IN ('D', 'DA', 'DA4', 'DB', 'DQA', 'Z', 'ZA', 'ZB');
+------+
| BArt |
+------+
| Z    |
+------+
1 row in set (0.05 sec)

mysql 5.6 > show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.6.16              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.6.16-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)

i wonder if it was fixed by http://bugs.mysql.com/bug.php?id=71095 ?

Thank you, Shane. Probably it was, I won't even bother trying, if the verification team is still serious about switching a report into 'Can't repeat' (thus making it totally useless as a public record for further search and reference, btw), without as much as checking what caused the change in behavior, whether it's a fix or a luck, while they can reproduce the bug easily on the last released version.

I actually hit the bug and can reproduce it, please kindly raise the severity of the bug since it result data missing from the result.
test query
mysql> SELECT action,id,data_timestamp
    -> FROM mytable 
    -> where action = 'process_combine' 
    -> and data_timestamp = '2015-03-18 01:00:00' 
    -> limit 1;
+-----------------+----------+---------------------+
| action          | id       | data_timestamp      |
+-----------------+----------+---------------------+
| process_combine | 17672161 | 2015-03-18 01:00:00 |
+-----------------+----------+---------------------+
1 row in set (0.00 sec)

# enable index_merge
mysql> set session optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on";
Query OK, 0 rows affected (0.00 sec)

# run the same query return 0 row
mysql> SELECT action,id,data_timestamp FROM mytable  where action = 'process_combine'  and data_timestamp = '2015-03-18 01:00:00'  limit 1;
Empty set (0.42 sec)

# query explain indicates index_merge has been used
mysql> explain SELECT action,id,data_timestamp FROM mytable  where action = 'process_combine'  and data_timestamp = '2015-03-18 01:00:00'  limit 1;
+----+-------------+--------+-------------+-----------------------+-----------------------+---------+------+------+------------------------------------------------------------------+
| id | select_type | table  | type        | possible_keys         | key                   | key_len | ref  | rows | Extra                                                            |
+----+-------------+--------+-------------+-----------------------+-----------------------+---------+------+------+------------------------------------------------------------------+
|  1 | SIMPLE      | mytable | index_merge | data_timestamp,action | data_timestamp,action | 6,48    | NULL |    1 | Using intersect(data_timestamp,action); Using where; Using index |
+----+-------------+--------+-------------+-----------------------+-----------------------+---------+------+------+------------------------------------------------------------------+

# disable index_merge
mysql> set session optimizer_switch="index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off";
Query OK, 0 rows affected (0.00 sec)

# run same query return data again
mysql> SELECT action,id,data_timestamp FROM mytable  where action = 'process_combine'  and data_timestamp = '2015-03-18 01:00:00'  limit 1;
+-----------------+----------+---------------------+
| action          | id       | data_timestamp      |
+-----------------+----------+---------------------+
| process_combine | 17672161 | 2015-03-18 01:00:00 |
+-----------------+----------+---------------------+

mysql> explain SELECT action,id,data_timestamp FROM mytable  where action = 'process_combine'  and data_timestamp = '2015-03-18 01:00:00'  limit 1;
+----+-------------+--------+------+-----------------------+----------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys         | key            | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+-----------------------+----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | mytable | ref  | data_timestamp,action | data_timestamp | 6       | const |   23 | Using where |
+----+-------------+--------+------+-----------------------+----------------+---------+-------+------+-------------+

# table schema, it has been modified a little bit w/o exposing our internal knowledge
CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `process_time` int(11) DEFAULT NULL,
  `data_timestamp` datetime DEFAULT NULL,
  `action` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`,`timestamp`),
  KEY `timestamp` (`timestamp`),
  KEY `data_timestamp` (`data_timestamp`),
  KEY `action` (`action`),
) ENGINE=InnoDB AUTO_INCREMENT=18161656 DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(`timeStamp`)
( PARTITION part20150317150000 VALUES LESS THAN ('2015-03-17 15:00:00') ENGINE = InnoDB,
 PARTITION part20150317180000 VALUES LESS THAN ('2015-03-17 18:00:00') ENGINE = InnoDB,
 PARTITION part20150317210000 VALUES LESS THAN ('2015-03-17 21:00:00') ENGINE = InnoDB,
 PARTITION part20150318000000 VALUES LESS THAN ('2015-03-18 00:00:00') ENGINE = InnoDB,
 PARTITION part20150318030000 VALUES LESS THAN ('2015-03-18 03:00:00') ENGINE = InnoDB,
 PARTITION part20150318060000 VALUES LESS THAN ('2015-03-18 06:00:00') ENGINE = InnoDB,
 PARTITION part20150318090000 VALUES LESS THAN ('2015-03-18 09:00:00') ENGINE = InnoDB,
 PARTITION part20150318120000 VALUES LESS THAN ('2015-03-18 12:00:00') ENGINE = InnoDB,
 PARTITION part20150318150000 VALUES LESS THAN ('2015-03-18 15:00:00') ENGINE = InnoDB,
 PARTITION part20150318180000 VALUES LESS THAN ('2015-03-18 18:00:00') ENGINE = InnoDB,
 PARTITION part20150318210000 VALUES LESS THAN ('2015-03-18 21:00:00') ENGINE = InnoDB,
 PARTITION part20150319000000 VALUES LESS THAN ('2015-03-19 00:00:00') ENGINE = InnoDB,
 PARTITION part20150319030000 VALUES LESS THAN ('2015-03-19 03:00:00') ENGINE = InnoDB,
 PARTITION part20150319060000 VALUES LESS THAN ('2015-03-19 06:00:00') ENGINE = InnoDB,
 PARTITION part20150319090000 VALUES LESS THAN ('2015-03-19 09:00:00') ENGINE = InnoDB,
 PARTITION part20150319120000 VALUES LESS THAN ('2015-03-19 12:00:00') ENGINE = InnoDB,
 PARTITION part20150319150000 VALUES LESS THAN ('2015-03-19 15:00:00') ENGINE = InnoDB,
 PARTITION part20150319180000 VALUES LESS THAN ('2015-03-19 18:00:00') ENGINE = InnoDB,
 PARTITION part20150319210000 VALUES LESS THAN ('2015-03-19 21:00:00') ENGINE = InnoDB,
 PARTITION future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)

Benjamin,

I cannot repeat this with current version 5.6.25. If you still can create full test case and open separate bug.