| Bug #69581 | InnoDB index intersection returns less results than expected | ||
|---|---|---|---|
| Submitted: | 26 Jun 2013 10:04 | Modified: | 7 Oct 2013 10:56 |
| Reporter: | Putinas Piliponis | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.6.12-log Source distribution | OS: | Any |
| Assigned to: | ADITYA ANANTHAPADMANABHA | CPU Architecture: | Any |
| Tags: | INDEX, innodb, intersect | ||
[26 Jun 2013 10:04]
Putinas Piliponis
[26 Jun 2013 14:49]
MySQL Verification Team
Thank you for the bug report. Please provide the create table statement. Thanks.
[27 Jun 2013 10:28]
Putinas Piliponis
Provided. In private post though - Slightly sensitive information.
[10 Jul 2013 15:02]
Vitaly Veksler
I have the same bug on mysql 5.6.12 table size can be less than 1GB to reproduce the bug.
[10 Jul 2013 15:05]
Vitaly Veksler
CentOS 6 x64
[31 Jul 2013 14:22]
MySQL Verification Team
Could you please provide the dump file with insert data too? . Thanks.
[9 Aug 2013 8:38]
Putinas Piliponis
Sorry, I wish I could, but the data contains private date which I cannot share.
[28 Aug 2013 5:42]
朱 先生
I have the same problem
Version:5.6.13
table use
/*!50100 PARTITION BY HASH ( city)
PARTITIONS 30 */
1 SIMPLE biz_merchant_2 index_merge manauser,merchant_category manauser,merchant_category 75,75 \N 1 Using intersect(manauser,merchant_category); Using where
---
only return city=1's records
[28 Aug 2013 7:14]
Putinas Piliponis
朱 先生, can you please upload whole table as developers weren't able to recreate the issue?
[24 Sep 2013 2:57]
MySQL Verification Team
Verified with a small test case in 5.6.11, 5.6.13.
[24 Sep 2013 2:59]
MySQL Verification Team
test case
Attachment: testcase-indexmerge.sql (application/octet-stream, text), 1.50 KiB.
[24 Sep 2013 3:00]
MySQL Verification Team
test case result
Attachment: testcase-indexmerge.sql.txt (text/plain), 1.21 KiB.
[4 Oct 2013 10:10]
Valeriy Kravchuk
It seems 5.5.32 is NOT affected (see below), so why don't we have "regression" tag here?
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.5.32 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS `table1`;
Query OK, 0 rows affected, 1 warning (0.27 sec)
mysql> 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
-> );
Query OK, 0 rows affected (2.31 sec)
mysql> INSERT INTO `table1` VALUES (2,96,'2013-03-08 16:28:05');
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO `table1` VALUES (1,2,'2013-03-08 16:47:39');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO `table1` VALUES (1,2,'2013-03-08 16:50:27');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO `table1` VALUES (1,2,'2013-03-11 16:33:04');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO `table1` VALUES (1,2,'2013-03-11 16:33:24');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO `table1` VALUES (2,2,'2013-03-12 10:11:48');
Query OK, 1 row affected (0.03 sec)
mysql>
mysql> SET optimizer_switch='index_merge=on';
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT @@optimizer_switch;
+-------------------------------------------------------------------------------
-----------------------------------------+
| @@optimizer_switch
|
+-------------------------------------------------------------------------------
-----------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_inte
rsection=on,engine_condition_pushdown=on |
+-------------------------------------------------------------------------------
-----------------------------------------+
1 row in set (0.00 sec)
mysql> 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;
+------+------+---------------------+
| 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 (1.06 sec)
mysql> 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;
+----+-------------+--------+-------------+-------------------+-----------+-----
----+------+------+-------------------------------------------------------------
---------+
| id | select_type | table | type | possible_keys | key | key_
len | ref | rows | Extra
|
+----+-------------+--------+-------------+-------------------+-----------+-----
----+------+------+-------------------------------------------------------------
---------+
| 1 | SIMPLE | table1 | index_merge | PRIMARY,col1,col2 | col1,col2 | 8,8
| NULL | 2 | Using intersect(col1,col2); Using where; Using index; Using
filesort |
+----+-------------+--------+-------------+-------------------+-----------+-----
----+------+------+-------------------------------------------------------------
---------+
1 row in set (0.00 sec)
mysql>
mysql> SET optimizer_switch='index_merge=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@optimizer_switch;
+-------------------------------------------------------------------------------
------------------------------------------+
| @@optimizer_switch
|
+-------------------------------------------------------------------------------
------------------------------------------+
| index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_int
ersection=on,engine_condition_pushdown=on |
+-------------------------------------------------------------------------------
------------------------------------------+
1 row in set (0.00 sec)
mysql> 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;
+------+------+---------------------+
| 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)
mysql> 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;
+----+-------------+--------+------+-------------------+------+---------+-------
+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+--------+------+-------------------+------+---------+-------
+------+-----------------------------+
| 1 | SIMPLE | table1 | ref | PRIMARY,col1,col2 | col1 | 8 | const
| 4 | Using where; Using filesort |
+----+-------------+--------+------+-------------------+------+---------+-------
+------+-----------------------------+
1 row in set (0.00 sec)
[7 Oct 2013 10:54]
Jørgen Løland
Posted by developer: This bug has already been in fixed in 5.6.14 by duplicate internal bug and documented with the following changelog entry. Noted in 5.6.14, 5.7.3 changelogs. For partitioned tables, queries could return different results depending on whether Index Merge was used.
[23 Oct 2013 14:39]
Sergey Petrunya
The fix is incomplete. See Bug #70703.
