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

Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions

    XMLWordPrintable

Details

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

    Description

      The following test case

      --source include/have_innodb.inc
       
      SET  optimizer_switch='index_merge=on,index_merge_intersection=on';
       
      CREATE TABLE t1 (
       a INT PRIMARY KEY,
       b INT,
       c VARCHAR(1024) CHARACTER SET utf8,
       d INT,
       KEY (b)
      ) ENGINE=InnoDB;
       
      INSERT INTO t1 VALUES 
      (1, 9, 'one', 11), (2, 6, 'two', 12), (3, 2, 'three', 13), (4, 5, 'four', 14);
       
      CREATE TABLE t2 (e INT, g INT) ENGINE=InnoDB;
      INSERT INTO t2 VALUES (1,9), (2,6) ;
       
      SELECT * FROM t1, t2 WHERE g = b AND ( a < 7 OR a > e );
       
      DROP TABLE t1, t2;
       

      produces a wrong result. It returns the right 2 rows, but values in the 2nd row are strange:

      a       b       c       d       e       g
      1       9       one     11      1       9
      2       6               0       2       6

      There are no c='' and d=0 in the dataset, they should be 'two' and 12 instead.

      bzr version-info

      revision-id: timour@askmonty.org-20121022095529-87ykx0dubnj62c9y
      date: 2012-10-22 12:55:29 +0300
      revno: 3452
       
      branch: ~maria-captains/maria/10.0-serg

      Not reproducible on MariaDB 5.2 revno 3163, 5.3 revno 3587, 5.5 revno 3562, MySQL 5.6 revno 4229.
      Not reproducible with t1 being a MyISAM or Aria table.

      Minimal optimizer_switch: index_merge=on,index_merge_intersection=on
      Full optimizer_switch (default):

      index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

      EXPLAIN (with the minimal optimizer_switch):

      id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered   Extra
      1       SIMPLE  t2      ALL     NULL    NULL    NULL    NULL    2       100.00
      1       SIMPLE  t1      ALL     PRIMARY,b       NULL    NULL    NULL    4       75.00   Range checked for each record (index map: 0x3)
      Warnings:
      Note    1003    select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`e` AS `e`,`test`.`t2`.`g` AS `g` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`b` = `test`.`t2`.`g`) and ((`test`.`t1`.`a` < 7) or (`test`.`t1`.`a` > `test`.`t2`.`e`)))

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.