| Bug #56862 | Execution of a query that uses index merge returns a wrong result | ||
|---|---|---|---|
| Submitted: | 19 Sep 2010 21:02 | Modified: | 14 Dec 2010 0:07 |
| Reporter: | Igor Babaev | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.92-bzr, 5.1.51-bzr, 5.5.7 | OS: | Any |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
[20 Sep 2010 3:27]
Valeriy Kravchuk
Verified just as described with current mysql-5.1 tree from bzr on Mac OS X:
mysql> SELECT COUNT(*) FROM
-> (SELECT * FROM t1
-> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
+----------+
| COUNT(*) |
+----------+
| 6144 |
+----------+
1 row in set (0.26 sec)
mysql> SELECT COUNT(*) FROM
-> (SELECT * FROM t1 IGNORE INDEX(idx)
-> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
+----------+
| COUNT(*) |
+----------+
| 6145 |
+----------+
1 row in set (0.32 sec)
[20 Sep 2010 3:37]
Valeriy Kravchuk
Current mysql-5.0 is also affected:
macbook-pro:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.92-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop table t1;
Query OK, 0 rows affected (0.37 sec)
mysql> CREATE TABLE t1 (
-> pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> a int,
-> b int,
-> INDEX idx(a))
-> ENGINE=INNODB;
INSERT INTO t1(a,b) VALUES
(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1 VALUES (1000000, 0, 0);
SET SESSION sort_buffer_size = 1024*36;
EXPLAIN
SELECT COUNT(*) FROM
(SELECT * FROM t1
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> INSERT INTO t1(a,b) VALUES
-> (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
-> (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
-> (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
-> (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
Query OK, 20 rows affected (0.00 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
Query OK, 20 rows affected (0.01 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
Query OK, 40 rows affected (0.00 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
Query OK, 80 rows affected (0.01 sec)
Records: 80 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1(a,b) SELECT a,b FROM t1;
Query OK, 160 rows affected (0.02 sec)
Records: 160 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1(a,b) SELECT a,b FROM t1;
Query OK, 320 rows affected (0.06 sec)
Records: 320 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1(a,b) SELECT a,b FROM t1;
Query OK, 640 rows affected (0.07 sec)
Records: 640 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1(a,b) SELECT a,b FROM t1;
Query OK, 1280 rows affected (0.10 sec)
Records: 1280 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1(a,b) SELECT a,b FROM t1;
Query OK, 2560 rows affected (0.16 sec)
Records: 2560 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1(a,b) SELECT a,b FROM t1;
Query OK, 5120 rows affected (0.29 sec)
Records: 5120 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1(a,b) SELECT a,b FROM t1;
Query OK, 10240 rows affected (0.58 sec)
Records: 10240 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1(a,b) SELECT a,b FROM t1;
Query OK, 20480 rows affected (1.01 sec)
Records: 20480 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1(a,b) SELECT a,b FROM t1;
Query OK, 40960 rows affected (2.19 sec)
Records: 40960 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1(a,b) SELECT a,b FROM t1;
Query OK, 81920 rows affected (4.18 sec)
Records: 81920 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 VALUES (1000000, 0, 0);
Query OK, 1 row affected (0.01 sec)
mysql> SET SESSION sort_buffer_size = 1024*36;
Query OK, 0 rows affected (0.00 sec)
mysql> EXPLAIN
-> SELECT COUNT(*) FROM
-> (SELECT * FROM t1
-> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
+----+-------------+-------+-------------+---------------+-------------+---------+------+-------+--------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+---------------+-------------+---------+------+-------+--------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DERIVED | t1 | index_merge | PRIMARY,idx | idx,PRIMARY | 5,4 | NULL | 11419 | Using sort_union(idx,PRIMARY); Using where |
+----+-------------+-------+-------------+---------------+-------------+---------+------+-------+--------------------------------------------+
2 rows in set (0.34 sec)
mysql> SELECT COUNT(*) FROM
-> (SELECT * FROM t1
-> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
+----------+
| COUNT(*) |
+----------+
| 6144 |
+----------+
1 row in set (0.25 sec)
mysql> EXPLAIN
-> SELECT COUNT(*) FROM
-> (SELECT * FROM t1 IGNORE INDEX(idx)
-> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
+----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DERIVED | t1 | ALL | PRIMARY | NULL | NULL | NULL | 164257 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+
2 rows in set (0.29 sec)
mysql> SELECT COUNT(*) FROM
-> (SELECT * FROM t1 IGNORE INDEX(idx)
-> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
+----------+
| COUNT(*) |
+----------+
| 6145 |
+----------+
1 row in set (0.30 sec)
But as setting bigger sort_buffer_size solves the problem:
mysql> SET SESSION sort_buffer_size = 1024*36*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(*) FROM (SELECT * FROM t1 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
+----------+
| COUNT(*) |
+----------+
| 6145 |
+----------+
1 row in set (0.32 sec)
I'd say we have acceptable workaround here.
[20 Sep 2010 7:28]
Philip Stoev
Increasing the sort_buffer_size only makes it so that a bigger table is required to reproduce the bug. With a 32K sort_buffer_size, the bug has been observed with 1000 rows. With 256K - with 10000 rows and so on. So, actually there is no acceptable workaround, unless you have a unlimited memory and a bounded dataset.
[7 Oct 2010 12:37]
MySQL Verification Team
another index merge + innodb bug recently fixed: bug #50402
[18 Oct 2010 16:56]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/121009 3533 Sergey Glukhov 2010-10-18 Bug#56862 Execution of a query that uses index merge returns a wrong result In case of low memory sort buffer QUICK_INDEX_MERGE_SELECT creates temporary file where is stores row ids which meet QUICK_SELECT ranges except of clustered pk range, clustered range is processed separately. In init_read_record we check if temporary file is used and choose appropriate record access method. It does not take into account that temporary file contains partial result in case of QUICK_INDEX_MERGE_SELECT with clustered pk range. The fix is always to use rr_quick if QUICK_INDEX_MERGE_SELECT with clustered pk range is used. @ mysql-test/suite/innodb/r/innodb_mysql.result test case @ mysql-test/suite/innodb/t/innodb_mysql.test test case @ mysql-test/suite/innodb_plugin/r/innodb_mysql.result test case @ mysql-test/suite/innodb_plugin/t/innodb_mysql.test test case @ sql/opt_range.h added new method @ sql/records.cc The fix is always to use rr_quick if QUICK_INDEX_MERGE_SELECT with clustered pk range is used.
[20 Oct 2010 9:54]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/121275 3533 Sergey Glukhov 2010-10-20 Bug#56862 Execution of a query that uses index merge returns a wrong result In case of low memory sort buffer QUICK_INDEX_MERGE_SELECT creates temporary file where is stores row ids which meet QUICK_SELECT ranges except of clustered pk range, clustered range is processed separately. In init_read_record we check if temporary file is used and choose appropriate record access method. It does not take into account that temporary file contains partial result in case of QUICK_INDEX_MERGE_SELECT with clustered pk range. The fix is always to use rr_quick if QUICK_INDEX_MERGE_SELECT with clustered pk range is used. @ mysql-test/suite/innodb/r/innodb_mysql.result test case @ mysql-test/suite/innodb/t/innodb_mysql.test test case @ mysql-test/suite/innodb_plugin/r/innodb_mysql.result test case @ mysql-test/suite/innodb_plugin/t/innodb_mysql.test test case @ sql/opt_range.h added new method @ sql/records.cc The fix is always to use rr_quick if QUICK_INDEX_MERGE_SELECT with clustered pk range is used.
[26 Oct 2010 18:43]
Konstantin Osipov
If you really believe the risk is low, check with Gleb who set the initial values, update the risk field, and then send to the triage.
[2 Nov 2010 15:15]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/122559 3551 Sergey Glukhov 2010-11-02 Bug#56862 Execution of a query that uses index merge returns a wrong result In case of low memory sort buffer QUICK_INDEX_MERGE_SELECT creates temporary file where is stores row ids which meet QUICK_SELECT ranges except of clustered pk range, clustered range is processed separately. In init_read_record we check if temporary file is used and choose appropriate record access method. It does not take into account that temporary file contains partial result in case of QUICK_INDEX_MERGE_SELECT with clustered pk range. The fix is always to use rr_quick if QUICK_INDEX_MERGE_SELECT with clustered pk range is used. @ mysql-test/suite/innodb/r/innodb_mysql.result test case @ mysql-test/suite/innodb/t/innodb_mysql.test test case @ mysql-test/suite/innodb_plugin/r/innodb_mysql.result test case @ mysql-test/suite/innodb_plugin/t/innodb_mysql.test test case @ sql/opt_range.h added new method @ sql/records.cc The fix is always to use rr_quick if QUICK_INDEX_MERGE_SELECT with clustered pk range is used.
[5 Nov 2010 16:25]
Kazuhiko Shiozaki
Hello. I got yet another wrong result caused by index merge with MySQL-5.1.52. $ cat test.sql /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; DROP TABLE IF EXISTS `table`; CREATE TABLE `table` ( `uid` int(10) unsigned DEFAULT NULL, `str` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, KEY `uid` (`uid`), KEY `str` (`str`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; INSERT INTO `table` VALUES (11,'Assignee'); INSERT INTO `table` VALUES (11,'Assignor'); INSERT INTO `table` VALUES (11,'user:foo'); INSERT INTO `table` VALUES (11,'user:foo:Author'); EXPLAIN SELECT str FROM `table` WHERE str like "user:foo" and uid=11; SELECT str FROM `table` WHERE str like "user:foo" and uid=11; EXPLAIN SELECT str FROM `table` force index (uid,str) WHERE str like "user:foo" and uid=11; SELECT str FROM `table` force index (uid,str) WHERE str like "user:foo" and uid=11; EXPLAIN SELECT str FROM `table` force index (uid) WHERE str like "user:foo" and uid=11; SELECT str FROM `table` force index (uid) WHERE str like "user:foo" and uid=11; EXPLAIN SELECT str FROM `table` force index (str) WHERE str like "user:foo" and uid=11; SELECT str FROM `table` force index (str) WHERE str like "user:foo" and uid=11; [result] $ mysql test < test.sql id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE table index_merge uid,str uid,str 5,768 NULL 1 Using intersect(uid,str); Using where; Using index id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE table index_merge uid,str uid,str 5,768 NULL 1 Using intersect(uid,str); Using where; Using index id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE table ref uid uid 5 const 2 Using where str user:foo id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE table range str str 768 NULL 1 Using where str user:foo Here, 1st and 2nd results are bad (no result) where index_merge is used. 3rd and 4th results are good where index_merge is not used.
[5 Nov 2010 16:53]
Kazuhiko Shiozaki
I forgot to say that http://lists.mysql.com/commits/122559 did not solve my case.
[8 Nov 2010 9:31]
Sergei Glukhov
Hi Kazuhiko, the problem you mentioned is fixed in Bug#56423.
[23 Nov 2010 10:29]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/124718 3516 Sergey Glukhov 2010-11-23 Bug#56862 Execution of a query that uses index merge returns a wrong result In case of low memory sort buffer QUICK_INDEX_MERGE_SELECT creates temporary file where is stores row ids which meet QUICK_SELECT ranges except of clustered pk range, clustered range is processed separately. In init_read_record we check if temporary file is used and choose appropriate record access method. It does not take into account that temporary file contains partial result in case of QUICK_INDEX_MERGE_SELECT with clustered pk range. The fix is always to use rr_quick if QUICK_INDEX_MERGE_SELECT with clustered pk range is used. @ mysql-test/suite/innodb/r/innodb_mysql.result test case @ mysql-test/suite/innodb/t/innodb_mysql.test test case @ mysql-test/suite/innodb_plugin/r/innodb_mysql.result test case @ mysql-test/suite/innodb_plugin/t/innodb_mysql.test test case @ sql/opt_range.h added new method @ sql/records.cc The fix is always to use rr_quick if QUICK_INDEX_MERGE_SELECT with clustered pk range is used.
[5 Dec 2010 12:43]
Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)
[14 Dec 2010 0:07]
Paul DuBois
Noted in 5.1.54, 5.5.8 changelogs. Queries executed using the Index Merge access method and a temporary file could return incorrect results.
[15 Dec 2010 5:52]
Bugs System
Pushed into mysql-5.1 5.1.55 (revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (version source revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (merge vers: 5.1.55) (pib:23)
[16 Dec 2010 22:34]
Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)
Description: The same query over an InnoDB table executed with and without certain hints returns different results: mysql> EXPLAIN -> SELECT COUNT(*) FROM -> (SELECT * FROM t1 -> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +----+-------------+-------+-------------+---------------+-------------+---------+------+-------+--------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+---------------+-------------+---------+------+-------+--------------------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | | 2 | DERIVED | t1 | index_merge | PRIMARY,idx | idx,PRIMARY | 5,4 | NULL | 11419 | Using sort_union(idx,PRIMARY); Using where | +----+-------------+-------+-------------+---------------+-------------+---------+------+-------+--------------------------------------------+ 2 rows in set (0.42 sec) mysql> SELECT COUNT(*) FROM -> (SELECT * FROM t1 -> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +----------+ | COUNT(*) | +----------+ | 6144 | +----------+ 1 row in set (0.35 sec) mysql> mysql> EXPLAIN -> SELECT COUNT(*) FROM -> (SELECT * FROM t1 IGNORE INDEX(idx) -> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | | 2 | DERIVED | t1 | ALL | PRIMARY | NULL | NULL | NULL | 164430 | Using where | +----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+ 2 rows in set (0.44 sec) mysql> SELECT COUNT(*) FROM -> (SELECT * FROM t1 IGNORE INDEX(idx) -> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +----------+ | COUNT(*) | +----------+ | 6145 | +----------+ 1 row in set (0.44 sec) The second result (when the query is executed with a hint) is correct, while the first result is incorrect. This bug can be watched only for InnoDB tables. Most probably that the bug can be reproduced for 5.0 as well. How to repeat: The bug can be reproduced with the following test case: CREATE TABLE t1 ( pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, a int, b int, INDEX idx(a)) ENGINE=INNODB; INSERT INTO t1(a,b) VALUES (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700), (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000); INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1; INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1; INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1 VALUES (1000000, 0, 0); SET SESSION sort_buffer_size = 1024*36; EXPLAIN SELECT COUNT(*) FROM (SELECT * FROM t1 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; SELECT COUNT(*) FROM (SELECT * FROM t1 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; EXPLAIN SELECT COUNT(*) FROM (SELECT * FROM t1 IGNORE INDEX(idx) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; SELECT COUNT(*) FROM (SELECT * FROM t1 IGNORE INDEX(idx) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; DROP TABLE t1;