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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.3, 5.5.31, 5.3.12
    • 10.0.5, 5.5.32
    • 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    |
      +------+------+

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            jamesp James Peacock
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.