| Bug #29740 | index_merge query returns incorrect results for HEAP table | ||
|---|---|---|---|
| Submitted: | 12 Jul 2007 1:09 | Modified: | 27 Jul 2007 4:30 |
| Reporter: | Sergey Petrunya | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0-bk, 5.2-bk | OS: | Any |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
| Tags: | heap, Memory | ||
[12 Jul 2007 1:21]
MySQL Verification Team
Thank you for the bug report. Verified as described.
[12 Jul 2007 4:35]
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/30754 ChangeSet@1.2533, 2007-07-12 08:33:07+04:00, sergefp@mysql.com +4 -0 BUG#29740: Wrong query results for index_merge/union over HEAP table. - make HEAP table engine return HA_KEY_SCAN_NOT_ROR flag for both BTREE and HEAP indexes.
[13 Jul 2007 15:18]
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/30883 ChangeSet@1.2533, 2007-07-13 19:13:40+04:00, sergefp@mysql.com +4 -0 BUG#29740: Wrong query results for index_merge/union over HEAP table. - return HA_KEY_SCAN_NOT_ROR flag for HASH indexes; - Fix ha_heap::cmp_ref() to work with BTREE index scans.
[14 Jul 2007 18:06]
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/30936 ChangeSet@1.2524, 2007-07-14 22:02:10+04:00, sergefp@mysql.com +2 -0 BUG#29740: Make the test result deterministic
[20 Jul 2007 23:46]
Bugs System
Pushed into 5.1.21-beta
[20 Jul 2007 23:49]
Bugs System
Pushed into 5.0.48
[27 Jul 2007 4:30]
Paul DuBois
Noted in 5.0.48, 5.1.21 changelogs. For MEMORY tables, the index_merge union access method could return incorrect results.
Description: index_merge query returns incorrect results for HEAP table How to repeat: Run this: --disable_warnings drop table if exists t1; --enable_warnings CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `filler` char(200) DEFAULT NULL, `b` int(11) DEFAULT NULL, KEY `a` (`a`), KEY `b` (`b`) ) ENGINE=MEMORY DEFAULT CHARSET=latin1; insert into t1 values (0, 'filller', 0 ), (1, 'filller', 1 ), (2, 'filller', 2 ), (3, 'filller', 3 ), (4, 'filller', 4 ), (5, 'filller', 5 ), (6, 'filller', 6 ), (7, 'filller', 7 ), (8, 'filller', 8 ), (9, 'filller', 9 ), (0, 'filller', 0 ), (1, 'filller', 1 ), (2, 'filller', 2 ), (3, 'filller', 3 ), (4, 'filller', 4 ), (5, 'filller', 5 ), (6, 'filller', 6 ), (7, 'filller', 7 ), (8, 'filller', 8 ), (9, 'filller', 9 ), (10, 'filller', 10 ), (11, 'filller', 11 ), (12, 'filller', 12 ), (13, 'filller', 13 ), (14, 'filller', 14 ), (15, 'filller', 15 ), (16, 'filller', 16 ), (17, 'filller', 17 ), (18, 'filller', 18 ), (19, 'filller', 19 ), (4, '5 ', 0 ), (5, '4 ', 0 ), (4, '4 ', 0 ), (4, 'qq ', 5 ), (5, 'qq ', 4 ), (4, 'zz ', 4 ); explain select * from t1 where a=4 or b=4; select * from t1 where a=4 or b=4; select * from t1 ignore index(a,b) where a=4 or b=4; drop table t1; For SELECTs you'll get: mysql> explain select * from t1 where a=4 or b=4; +----+-------------+-------+-------------+---------------+------+---------+---- -+------+-------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+---------------+------+---------+---- -+------+-------------------------------+ | 1 | SIMPLE | t1 | index_merge | a,b | a,b | 5,5 | NUL | 4 | Using union(a,b); Using where | +----+-------------+-------+-------------+---------------+------+---------+---- -+------+-------------------------------+ mysql> select * from t1 where a=4 or b=4; +------+---------+------+ | a | filler | b | +------+---------+------+ | 4 | zz | 4 | | 4 | qq | 5 | | 4 | 4 | 0 | | 4 | 5 | 0 | | 4 | filller | 4 | | 4 | filller | 4 | | 4 | zz | 4 | | 5 | qq | 4 | | 4 | filller | 4 | | 4 | filller | 4 | +------+---------+------+ 10 rows in set (1.76 sec) mysql> mysql> select * from t1 ignore index(a,b) where a=4 or b=4; +------+---------+------+ | a | filler | b | +------+---------+------+ | 4 | filller | 4 | | 4 | filller | 4 | | 4 | 5 | 0 | | 4 | 4 | 0 | | 4 | qq | 5 | | 5 | qq | 4 | | 4 | zz | 4 | +------+---------+------+ 7 rows in set (0.00 sec) Suggested fix: Analysis -------- The problem is caused by the use of "Using union" over HASH indexes. HASH indexes do not have the Rowid Ordered Retrieval property. More analysis ------------- Comments in opt_range.cc show this: Scans on HASH indexes are not ROR scans .... ... Check (3) is made check_quick_select() But the check seems to be no longer there.