Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.3, 5.5.31, 5.3.12
-
Component/s: None
-
Labels:
-
Environment: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 | +------+------+
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Pushed the fix
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')))