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

SQ pushdown: Wrong result (missing rows) with materialization+semijoin, IN and ALL subqueries, UNION

    XMLWordPrintable

Details

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

    Description

      Test case:

      SET optimizer_switch = 'materialization=on,semijoin=on';
       
      CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1),(2);
       
      CREATE TABLE t2 (i2 INT, c2 CHAR(1), KEY(c2,i2)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (8,'c'),(6,'c');
       
      CREATE TABLE t3 (i3 INT, c3 CHAR(1)) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (1,'c'),(4,'r');
       
      SELECT * FROM t1, t2  
      WHERE i1 IN ( SELECT i3 FROM t3, t2 WHERE c2 = c3 AND i3 < ALL ( SELECT 4 UNION SELECT 8 ) );

      Expected result:

      i1	i2	c2
      1	8	c
      1	6	c

      Actual result: empty set

      Reproducible starting from revno: 3546 on 10.0-mdev83 (timour@askmonty.org-20130401103605-2cbjiqkw8j1hd4sk).

      Minimal optimizer_switch: materialization=on,semijoin=on
      Full optimizer_switch (default):

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

      EXPLAIN:

      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	
      1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
      1	PRIMARY	t2	index	NULL	c2	7	NULL	2	100.00	Using where; Subqueries: 3; Using index; Using join buffer (flat, BNL join)
      2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Subqueries: 3
      2	MATERIALIZED	t2	ref	c2	c2	2	test.t3.c3	1	100.00	Using index
      3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
      4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
      NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
      Warnings:
      Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t2`) join `test`.`t2` where ((`test`.`t2`.`c2` = `test`.`t3`.`c3`) and <not>(<in_optimizer>(`test`.`t3`.`i3`,(<min>(select 4 union select 8) <= <cache>(`test`.`t3`.`i3`)))))

      Attachments

        Issue Links

          Activity

            People

              timour Timour Katchaounov (Inactive)
              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.