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

Exists2In: Wrong result (extra rows) with exists_to_in=ON, materialization=OFF, NOT EXISTS subquery

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.0.0
    • Component/s: None
    • Labels:
      None

      Description

      The following query

      SELECT * FROM t1 WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b = a )

      on the test data returns 3 rows if executed with exists_to_in=on, materialization=off, and 2 rows otherwise. The correct result is 2 rows.

      bzr version-info

      revision-id: sanja@montyprogram.com-20120430093355-4yrpta86g97g6rhb
      date: 2012-04-30 12:33:55 +0300
      build-date: 2012-05-02 22:58:36 +0400
      revno: 3390

      optimizer_switch (default + exists_to_in=on + materialization=off):

      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=off,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

      EXPLAIN with optimizer_switch = default + exists_to_in=on + materialization=off (wrong result):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
      2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
      Warnings:
      Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
      Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(((`test`.`t1`.`a` is not null) and <exists>(select `test`.`t2`.`b` from `test`.`t2` where ((`test`.`t2`.`b` is not null) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`b`))))))

      EXPLAIN with optimizer_switch = default + exists_to_in=off + materialization=off (correct result):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
      2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
      Warnings:
      Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
      Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`a`>(exists(select 1 from `test`.`t2` where (`test`.`t2`.`b` = `test`.`t1`.`a`)))))

      Test case:

      SET optimizer_switch='materialization=off';
       
      CREATE TABLE t1 ( a INT ) ;                                     
      INSERT INTO t1 VALUES (0),(8),(1);
       
      CREATE TABLE t2 ( b INT ) ;                    
      INSERT INTO t2 VALUES (1),(2),(3);
       
      SELECT * FROM t1 WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b = a );

      Expected result:

      # a
      # ---
      # 0
      # 8

      Actual result:

      # a
      # ---
      # 0
      # 8
      # 1

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: