Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.3, 5.5.31, 5.3.12
-
None
-
Linux dev1 3.7.8-gentoo #3 SMP Tue Feb 26 19:11:07 UTC 2013 x86_64 AMD Opteron(tm) Processor 6174 AuthenticAMD GNU/Linux
gcc-4.7.2
Built from release tarballs using gcc-4.7.2
Description
The second SELECT in the following example does not return all matching rows. Incorrect results are returned whenever idx1 is used, with or without FORCE/IGNORE INDEX.
The bug appears to manifest with the MyISAM or Aria engines, but not XtraDB
The bug does not manifest if:
- The TEXT column is made VARCHAR
- The ENGINE is INNODB
- 'y' is changed to 'x'
- The WHERE clause is written as (c1,c2) IN (('aa','x'),('a','y'))
|
DROP TABLE IF EXISTS example; |
|
|
CREATE TABLE example ( |
c1 TEXT ,
|
c2 VARCHAR(2) , |
INDEX idx1 (c2,c1(2)), |
INDEX idx2 (c2,c1(1)) |
) ENGINE=MyISAM;
|
|
|
INSERT INTO example (c1,c2) |
VALUES ('aa','x'), |
('a' ,'y'); |
|
SELECT * FROM example |
IGNORE INDEX(idx1,idx2) |
WHERE (c1='aa' AND c2='x') |
OR (c1='a' AND c2='y'); |
|
|
+------+------+ |
| c1 | c2 |
|
+------+------+ |
| aa | x |
|
| a | y |
|
+------+------+ |
|
SELECT * FROM example |
FORCE INDEX(idx1) |
WHERE (c1='aa' AND c2='x') |
OR (c1='a' AND c2='y'); |
|
|
+------+------+ |
| c1 | c2 |
|
+------+------+ |
| aa | x |
|
+------+------+ |
|
SELECT * FROM example |
FORCE INDEX(idx2) |
WHERE (c1='aa' AND c2='x') |
OR (c1='a' AND c2='y'); |
|
|
+------+------+ |
| c1 | c2 |
|
+------+------+ |
| aa | x |
|
| a | y |
|
+------+------+ |
Minimal optimizer_switch: index_condition_pushdown=on
EXPLAIN with the minimal optimizer_switch:
id 1
select_type SIMPLE
table example
type range
possible_keys idx1
key idx1
key_len 10
ref NULL
rows 2
filtered 100.00
Extra Using index condition; Using where
Warnings:
Level Note
Code 1003
Message select `test`.`example`.`c1` AS `c1`,`test`.`example`.`c2` AS `c2` from `test`.`example` FORCE INDEX (`idx1`) where (((`test`.`example`.`c1` = 'aa') and (`test`.`example`.`c2` = 'x')) or ((`test`.`example`.`c1` = 'a') and (`test`.`example`.`c2` = 'y')))