Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-38 NOT EXISTS to IN (part of exists2in transformation for 10.0)
  3. MDEV-3880

Exists2In: Wrong result (missing rows) with exists_to_in=on, LEFT JOIN and NOT EXISTS subquery

    XMLWordPrintable

Details

    • Technical task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • None
    • 10.0.1
    • None
    • None

    Description

      The following test case

      SET optimizer_switch = 'exists_to_in=on';
       
      CREATE TABLE t1 (a1 INT, b1 CHAR(1)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (4,'b'),(5,'y');
       
      CREATE TABLE t2 (b2 CHAR(1)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES ('z'),('b');
       
      CREATE TABLE t3 (a3 INT, b3 CHAR(1)) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (4,'j'),(6,'v');
       
      SELECT * FROM t1 LEFT JOIN t2 ON ( b2 = b1 ) 
      WHERE NOT EXISTS ( SELECT * FROM t3 WHERE b3 = b2 AND a3 = a1 ) ;
       

      returns one row:

      a1	b1	b2
      ------------------
      4	b	b

      while it should return two:

      a1	b1	b2
      ------------------
      4	b	b
      5	y	NULL

      and it does so without exists_to_in.

      branch: 10.0-exists2in
      revision-id: psergey@askmonty.org-20121123063639-1jqbkjuxevqe1vb6
      date: 2012-11-23 10:36:39 +0400
      revno: 3482

      Minimal optimizer_switch: exists_to_in=on,in_to_exists=on or exists_to_in=on,materialization=on
      Full optimizer_switch (default + exists_to_in=on):

      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,exists_to_in=on

      Reproducible with MyISAM, Aria, InnoDB.

      EXPLAIN (with the default optimizer_switch + exists_to_in=on):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
      1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
      2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1276	Field or reference 'test.t2.b2' of SELECT #2 was resolved in SELECT #1
      Note	1276	Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
      Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`b2` = `test`.`t1`.`b1`)) where (not((<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>((`test`.`t2`.`b2`,`test`.`t1`.`a1`),(`test`.`t2`.`b2`,`test`.`t1`.`a1`) in ( <materialize> (select `test`.`t3`.`b3`,`test`.`t3`.`a3` from `test`.`t3` where 1 ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery2>`.`b3`) and (`test`.`t1`.`a1` = `<subquery2>`.`a3`)))))) and `test`.`t1`.`a1` and `test`.`t2`.`b2`)))
      SELECT * FROM t1 LEFT JOIN t2 ON ( b2 = b1 ) 
       

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            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.