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-3879

Exists2In: Wrong result (extra row) and unexpected warning with exists_to_in=on and a NOT EXISTS subquery

    XMLWordPrintable

Details

    • Technical task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 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 (3,'y'),(6,'w');
       
      CREATE TABLE t2 (a2 INT, b2 CHAR(1)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (3,'y'),(6,'d');
       
      SELECT * FROM t1
      WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b2 = b1 AND a2 = a1 );

      returns 2 rows and throws a warning:

      a1	b1
      ------------
      3	y
      6	w
      Warnings:
      Warning	1292	Truncated incorrect DOUBLE value: 'y'

      Without exists_to_in switch there is only one row and no warning, which is the expected result:

      a1	b1
      ------------
      6	w

      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	Using where
      2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1276	Field or reference 'test.t1.b1' 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` from `test`.`t1` where (not((<expr_cache><`test`.`t1`.`b1`,`test`.`t1`.`a1`>(<in_optimizer>((`test`.`t1`.`b1`,`test`.`t1`.`a1`),(`test`.`t1`.`b1`,`test`.`t1`.`a1`) in ( <materialize> (select `test`.`t2`.`b2`,`test`.`t2`.`a2` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`b1` in <temporary table> on distinct_key where ((`test`.`t1`.`b1` = `<subquery2>`.`b2`) and (`test`.`t1`.`a1` = `<subquery2>`.`a2`)))))) and `test`.`t1`.`a1` and `test`.`t1`.`b1`)))
       

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.