MySQL Bugs: #13484: Wrong result on SELECT of table with multiple keys

Bug #13484	Wrong result on SELECT of table with multiple keys
Submitted:	26 Sep 2005 13:36	Modified:	19 Oct 2005 14:48
Reporter:	Berto van de Kraats	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.0.14-rc-nightly-20050922	OS:	Linux (Linux Suse SLES 9 SP1)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
A select on a table such that the optimizer has the choice to use two keys (one of them being non-unique), sometimes gives the incorrect result. In the case where a wrong result is given an index_merge is used.
The table X contains 5 indexes:

  PRIMARY KEY  (`t_cpac`,`t_vers`,`t_rele`,`t_cust`), 
  UNIQUE KEY `IX_2` (`t_cpac`,`t_vedf`,`t_redf`,`t_cudf`,`t_vers`,`t_rele`,`t_cust`),    
  UNIQUE KEY `IX_3` (`t_pacc`,`t_cpac`,`t_vers`,`t_rele`,`t_cust`), 
  UNIQUE KEY `IX_4` (`t_cust`,`t_cpac`,`t_vers`,`t_rele`),   
  KEY `IX_5` (`t_vers`,`t_rele`,`t_cust`)

The query is:
select t_vers,t_rele,t_cust,t_tver_1 from X where t_vers = '7.6' and t_rele='a' and t_cust = ' '

How to repeat:
Run the attached mysql script.  In the case where the result is as expected, the script gives the following output:

t_vers  t_rele  t_cust  t_tver_1
7.6     a               7.6 
7.6     a               7.6 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  X       ref     IX_4,IX_5       IX_5    16      const,const,const       2       Using where

If the wrong result is given, the following output results:

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  X       index_merge     IX_4,IX_5       IX_5,IX_4       16,6    NULL    1       Using intersect(IX_5,IX_4); Using where

mysql script to reproduce the problem

Attachment: bug13484 (text/plain), 44.50 KiB.

Verified on Fedora Core4 with 5.0 from BK tree:
ChangeSet@1.1989.1.12, 2005-09-25 15:44:05+02:00, serg@serg.mylan

To make it more obvious here is simpler WHERE clause:

mysql> select t_vers,t_rele,t_cust,t_tver_1 from X where t_vers = '7.6'; +--------+--------+--------+----------+
| t_vers | t_rele | t_cust | t_tver_1 |
+--------+--------+--------+----------+
| 7.6    | a      |        | 7.6      |
| 7.6    | a      |        | 7.6      |
+--------+--------+--------+----------+
2 rows in set (0.01 sec)

mysql> select t_vers,t_rele,t_cust,t_tver_1 from X where t_vers = '7.6' and t_rele='a' and t_cust = ' ';
Empty set (0.00 sec)

Empty set is obviously wrong result.

mysql> EXPLAIN select t_vers,t_rele,t_cust,t_tver_1 from X where t_vers = '7.6' and t_rele='a' and t_cust = ' '\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: X
         type: index_merge
possible_keys: IX_4,IX_5
          key: IX_5,IX_4
      key_len: 16,6
          ref: NULL
         rows: 1
        Extra: Using intersect(IX_5,IX_4); Using where

Now let's ignore IX_4 and IX_5

mysql> select t_vers,t_rele,t_cust,t_tver_1 from X IGNORE KEY(IX_4) where t_vers = '7.6' and t_rele='a' and t_cust = ' ';
+--------+--------+--------+----------+
| t_vers | t_rele | t_cust | t_tver_1 |
+--------+--------+--------+----------+
| 7.6    | a      |        | 7.6      |
| 7.6    | a      |        | 7.6      |
+--------+--------+--------+----------+

mysql> select t_vers,t_rele,t_cust,t_tver_1 from X IGNORE KEY(IX_5) where t_vers = '7.6' and t_rele='a' and t_cust = ' ';
+--------+--------+--------+----------+
| t_vers | t_rele | t_cust | t_tver_1 |
+--------+--------+--------+----------+
| 7.6    | a      |        | 7.6      |
| 7.6    | a      |        | 7.6      |
+--------+--------+--------+----------+
2 rows in set (0.00 sec)

If only one of the above indexes is FORCEd the result is correct so the problem occurs only when Index_merge is used.

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/internals/30599

Fix pushed into 5.0.15 tree.
Bug description:
Queries that are executed using index_merge union or intersection could produce wrong results if underlying table was an InnoDB table with primary key containing varchar members.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented fix in 5.0.15 changelog. Closed.