Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4778

Incorrect results from Aria/MyISAM SELECT using index with prefix length on TEXT column

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.3, 5.5.31, 5.3.12
    • Fix Version/s: 10.0.5, 5.5.32
    • 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    |
      +------+------+

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              jamesp James Peacock
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: