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

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • None
    • 10.0.1
    • None
    • 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

              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.