[MDEV-5112] InnoDB index intersection returns less results than expected - Jira

This bug affects MariaDB 5.5.3x perhaps older versions too.

This bug concerns also all MySQL 5.6 versions (>= 5.6.8), it will be fixed on 5.6.14 : http://bugs.mysql.com/bug.php?id=69581

The test case below comes from the above link :

USE test;

DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table1` (

  `col1` bigint(20) unsigned NOT NULL ,

  `col2` bigint(20) unsigned NOT NULL ,

  `col3` datetime NOT NULL ,

  PRIMARY KEY (`col3`),

  KEY (`col1`),

  KEY (`col2`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

 PARTITION BY RANGE (TO_DAYS(col3))

 PARTITION p_20130310 VALUES LESS THAN (735303) ENGINE = InnoDB,

 PARTITION p_20130311 VALUES LESS THAN (735304) ENGINE = InnoDB,

 PARTITION p_20130312 VALUES LESS THAN (735305) ENGINE = InnoDB

);

INSERT INTO `table1` VALUES (2,96,'2013-03-08 16:28:05');

INSERT INTO `table1` VALUES (1,2,'2013-03-08 16:47:39');

INSERT INTO `table1` VALUES (1,2,'2013-03-08 16:50:27');

INSERT INTO `table1` VALUES (1,2,'2013-03-11 16:33:04');

INSERT INTO `table1` VALUES (1,2,'2013-03-11 16:33:24');

INSERT INTO `table1` VALUES (2,2,'2013-03-12 10:11:48');

SET optimizer_switch='index_merge=on';

SELECT @@optimizer_switch;

SELECT * FROM table1 WHERE col1 = 1 AND col2 = 2

    AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00'

GROUP BY 1, 2, 3;

EXPLAIN SELECT * FROM table1 WHERE col1 = 1 AND col2 = 2

    AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00'

GROUP BY 1, 2, 3;

SET optimizer_switch='index_merge=off';

SELECT @@optimizer_switch;

SELECT * FROM table1 WHERE col1 = 1 AND col2 = 2

    AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00'

GROUP BY 1, 2, 3;

EXPLAIN SELECT * FROM table1 WHERE col1 = 1 AND col2 = 2

    AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00'

GROUP BY 1, 2, 3;

With index_merge=on you obtain :

+------+------+---------------------+

| col1 | col2 | col3                |

+------+------+---------------------+

|    1 |    2 | 2013-03-08 16:47:39 |

|    1 |    2 | 2013-03-08 16:50:27 |

+------+------+---------------------+

2 rows in set (0.00 sec)

but you should obtain what you have when you disable index_merge :

+------+------+---------------------+

| col1 | col2 | col3                |

+------+------+---------------------+

|    1 |    2 | 2013-03-08 16:47:39 |

|    1 |    2 | 2013-03-08 16:50:27 |

|    1 |    2 | 2013-03-11 16:33:04 |

|    1 |    2 | 2013-03-11 16:33:24 |

+------+------+---------------------+

4 rows in set (0.00 sec)

is duplicated by

MDEV-5102 MySQL Bug 69581

relates to

MDEV-6151 Wrong result (missing row) with LEFT JOIN, HAVING and ORDER BY

links to

Transition	Time In Source Status	Execution Times

Sergei Petrunia made transition - 2013-10-22 23:53

Open

In Progress

14d 8h 59m

Sergei Petrunia made transition - 2013-10-24 14:33

In Progress

Closed

1d 14h 40m

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server