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

Exists2In: Wrong result (extra or missing rows) with exists_to_in + materialization, EXISTS subquery

    XMLWordPrintable

Details

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

    Description

      The following query

      SELECT * FROM t1 AS alias
      WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b );

      on the test data returns 1 row with exists_to_in=off, 2 rows with exists_to_in=on + materialization=on + semijoin=on, and 0 rows with exists_in=on + materialization=on + semijon=off. The correct result is 1 row.

      bzr version-info
      revision-id: sanja@montyprogram.com-20120430093355-4yrpta86g97g6rhb
      date: 2012-04-30 12:33:55 +0300
      build-date: 2012-05-02 17:50:48 +0400
      revno: 3390

      Default optimizer_switch + 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

      EXPLAINs:

      With exists_to_in=ON, materialization=ON, everything else OFF (empty result set instead of 1 row):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	alias	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      Warnings:
      Note	1276	Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1
      Note	1276	Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1
      Note	1003	select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` where <in_optimizer>(`test`.`alias`.`b`,`test`.`alias`.`b` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` > `test`.`alias`.`a`) ), <primary_index_lookup>(`test`.`alias`.`b` in <temporary table> on distinct_key where ((`test`.`alias`.`b` = `<subquery2>`.`a`)))))

      With exists_to_in=ON, materialization=ON, semijoin=ON, everything else OFF (2 rows instead of 1 row):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	alias	ALL	NULL	NULL	NULL	NULL	2	100.00	
      1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	Using where
      2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1276	Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1
      Note	1276	Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1
      Note	1003	select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` semi join (`test`.`t1`) where ((`test`.`t1`.`a` > `test`.`alias`.`a`))

      With default optimizer_switch + exists_to_in=ON (2 rows instead of 1 row):

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	alias	ALL	NULL	NULL	NULL	NULL	2	100.00	
      1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	Using where
      2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1276	Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1
      Note	1276	Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1
      Note	1003	select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` semi join (`test`.`t1`) where ((`test`.`t1`.`a` > `test`.`alias`.`a`))

      Test case:

      SET optimizer_switch='exists_to_in=on';
       
      CREATE TABLE t1 ( a VARCHAR(1), b VARCHAR(1) );
      INSERT INTO t1 VALUES ('v','v'),('s','v');
       
      SELECT * FROM t1 AS alias
      WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b );

      Expected result:

      # a   b
      # -----
      # s   v

      Actual result differs depending on the optimizer_switch (see above).

      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.