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

Wrong result (missing rows) with index_merge+index_merge_intersection, join, AND/OR conditions, InnoDB

    XMLWordPrintable

Details

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

    Description

      The following query

      SELECT ta.* FROM t1 AS ta, t1 AS tb
      WHERE ( tb.b != ta.b OR tb.a = ta.a )
        AND ( tb.b = ta.c OR tb.b = ta.b );

      on test data returns 2 rows with index_merge=ON and index_merge_intersection=ON, and 3 rows otherwise. 3 rows is the correct result.

      bzr version-info

      revision-id: monty@askmonty.org-20120627141312-z65pj80390f0f5pp
      date: 2012-06-27 17:13:12 +0300
      build-date: 2012-07-02 05:43:00 +0400
      revno: 3460

      Could not reproduce on MariaDB 5.3 or MySQL trunk.

      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	ta	ALL	a,b	NULL	NULL	NULL	3	100.00	
      1	SIMPLE	tb	ALL	a,b	NULL	NULL	NULL	3	100.00	Range checked for each record (index map: 0x3)
      Warnings:
      Note	1003	select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b`,`test`.`ta`.`c` AS `c` from `test`.`t1` `ta` join `test`.`t1` `tb` where (((`test`.`tb`.`b` <> `test`.`ta`.`b`) or (`test`.`tb`.`a` = `test`.`ta`.`a`)) and ((`test`.`tb`.`b` = `test`.`ta`.`c`) or (`test`.`tb`.`b` = `test`.`ta`.`b`)))

      Test case:

       
      --source include/have_innodb.inc
       
      CREATE TABLE t1 (
        a INT, b CHAR(1), c CHAR(1), KEY(a), KEY(b)
      ) ENGINE=InnoDB;
       
      INSERT INTO t1 VALUES (8,'v','v'),(8,'m','m'),(9,'d','d');
       
      SET optimizer_switch = 'index_merge=on,index_merge_intersection=on';
       
      SELECT ta.* FROM t1 AS ta, t1 AS tb
      WHERE ( tb.b != ta.b OR tb.a = ta.a )
        AND ( tb.b = ta.c OR tb.b = ta.b );
       

      Expected result:

      a	b	c
      -----------------
      8	v	v
      8	m	m
      9	d	d

      Actual result:

      a	b	c
      -----------------
      8	v	v
      8	m	m

      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.