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:
None 
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:09] Sergey Petrunya
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.
[12 Jul 2007 1:21] Miguel Solorzano
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.