| Bug #13484 | Wrong result on SELECT of table with multiple keys | ||
|---|---|---|---|
| Submitted: | 26 Sep 2005 15:36 | Modified: | 19 Oct 2005 16:48 |
| Reporter: | Berto van de Kraats | ||
| Status: | Closed | ||
| Category: | Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0.14-rc-nightly-20050922 | OS: | Linux (Linux Suse SLES 9 SP1) |
| Assigned to: | Bugs System | Target Version: | |
[26 Sep 2005 15:38]
Berto van de Kraats
mysql script to reproduce the problem
Attachment: bug13484 (text/plain), 44.50 KiB.
[26 Sep 2005 18:27]
Alexander Keremidarski
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.
[1 Oct 2005 9:53]
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/internals/30599
[7 Oct 2005 10:06]
Sergey Petrunya
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.
[19 Oct 2005 16:48]
Jon Stephens
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.
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