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

LP:695442 - Wrong result in maria-5.3-wl21 with ORDER BY + LIMIT

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      The test case below returns 1 row with maria-5.3-wl21 and no rows with other trees and other plans. In particular, maria-5.3 produces exactly the same plan and the correct result. Maria-5.2 produces a slightly different plan with a correct result.

      Test case:

      --source include/have_innodb.inc
      CREATE TABLE t2 ( f7 varchar(64), KEY (f7)) ENGINE=InnoDB;
      INSERT IGNORE INTO t2 VALUES ('d'),('UALLN'),('d'),('z'),('r'),('YVAKV'),('d'),('TNGZK'),('e'),('xucupaxdyythsgiw'),('why'),('ttugkxucupaxdyyt'),('l'),('LHTKN'),('d'),('o'),('v'),('KGLCJ'),('your');
      CREATE TABLE t1 ( f10 int(11), f4 varchar(10), KEY (f10), KEY (f4)) ;
      INSERT IGNORE INTO t1 VALUES ('4','e'),('891879424','l'),('-243400704','ectlyqupbk'),('1851981824','of'),('-1495203840','you'),('4','no'),('-1436942336','c'),('891420672','DQQYO'),('608698368','qergldqmec'),('1','x');

      SET SESSION optimizer_switch='index_merge_sort_intersection=off';
      SELECT t1.f4 AS field1 FROM t2 JOIN t1 ON t2.f7 = t1.f4 WHERE t1.f10 IN ( 9 , 0 , 100 ) ORDER BY field1 LIMIT 1;

      Explain:

      id select_type table type possible_keys key key_len ref rows Extra
      1 SIMPLE t1 range f10,f4 f4 13 NULL 10 Using where
      1 SIMPLE t2 ref f7 f7 67 test.t1.f4 1 Using where; Using index

      Attachments

        Activity

          People

            igor Igor Babaev (Inactive)
            philipstoev Philip Stoev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.