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

Exists2In: Wrong result (extra rows) with STRAIGHT_JOIN, EXISTS subquery, NOT NULL column

    XMLWordPrintable

    Details

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

      Description

      The following query

      SELECT STRAIGHT_JOIN * FROM t1 AS alias1, t1 AS alias2 
      WHERE EXISTS ( SELECT 1 FROM t1 WHERE b < alias2.b AND a = alias1.a )

      on the test data produces 4 rows if it's run with exists_to_in=on, and 1 row otherwise. 1 row is the correct result.

      bzr version-info

      revision-id: sanja@askmonty.org-20120718105902-imt07qp0v913sqa4
      date: 2012-07-18 13:59:02 +0300
      build-date: 2012-07-19 21:33:12 +0400
      revno: 3466

      Reproducible with MyISAM, Aria, InnoDB.

      Minimal optimizer_switch:
      exists_to_in=on,in_to_exists=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

      EXPLAIN with the minimal optimizer_switch (exists_to_in=on,in_to_exists=on, everything else OFF):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
      2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      Warnings:
      Note	1276	Field or reference 'test.alias2.b' of SELECT #2 was resolved in SELECT #1
      Note	1276	Field or reference 'test.alias1.a' of SELECT #2 was resolved in SELECT #1
      Note	1003	select straight_join `test`.`alias1`.`a` AS `a`,`test`.`alias1`.`b` AS `b`,`test`.`alias2`.`a` AS `a`,`test`.`alias2`.`b` AS `b` from `test`.`t1` `alias1` join `test`.`t1` `alias2` where <in_optimizer>(`test`.`alias1`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` where ((`test`.`t1`.`b` < `test`.`alias2`.`b`) and (<cache>(`test`.`alias1`.`a`) = `test`.`t1`.`a`))))

      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	alias1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
      2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      Warnings:
      Note	1276	Field or reference 'test.alias2.b' of SELECT #2 was resolved in SELECT #1
      Note	1276	Field or reference 'test.alias1.a' of SELECT #2 was resolved in SELECT #1
      Note	1003	select straight_join `test`.`alias1`.`a` AS `a`,`test`.`alias1`.`b` AS `b`,`test`.`alias2`.`a` AS `a`,`test`.`alias2`.`b` AS `b` from `test`.`t1` `alias1` join `test`.`t1` `alias2` where <expr_cache><`test`.`alias1`.`a`,`test`.`alias2`.`b`>(<in_optimizer>(`test`.`alias1`.`a`,<exists>(select `test`.`t1`.`a` from `test`.`t1` where ((`test`.`t1`.`b` < `test`.`alias2`.`b`) and (<cache>(`test`.`alias1`.`a`) = `test`.`t1`.`a`)))))

      Test case:

      SET optimizer_switch = 'exists_to_in=on,in_to_exists=on';
       
      CREATE TABLE t1 (a INT, b VARCHAR(1) NOT NULL);
      INSERT INTO t1 VALUES (1,'s'),(2,'e');
       
      SELECT STRAIGHT_JOIN * FROM t1 AS alias1, t1 AS alias2 
      WHERE EXISTS ( SELECT 1 FROM t1 WHERE b < alias2.b AND a = alias1.a );

      Expected result:

      a	b	a	b
      2	e	1	s

      Actual result:

      a	b	a	b
      1	s	1	s
      2	e	1	s
      1	s	2	e
      2	e	2	e

      Please note that removing 'NOT NULL' option from the column definition makes the same query return an empty result, which is also incorrect.

        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: