[MDEV-4778] Incorrect results from Aria/MyISAM SELECT using index with prefix length on TEXT column Created: 2013-07-11  Updated: 2013-07-16  Resolved: 2013-07-16

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.3, 5.5.31, 5.3.12
Fix Version/s: 10.0.5, 5.5.32

Type: Bug Priority: Major
Reporter: James Peacock Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: optimizer
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    |
 
+------+------+



 Comments   
Comment by Elena Stepanova [ 2013-07-11 ]

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')))

Comment by Sergei Petrunia [ 2013-07-16 ]

Pushed the fix

Generated at Thu Feb 08 06:59:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.