Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-83 Cost-based choice for the pushdown of subqueries to joined tables
  3. MDEV-4407

SQ pushdown: Wrong result (extra rows) with IN and EXISTS subqueries, optimizer_use_condition_selectivity>1

    XMLWordPrintable

    Details

    • Type: Technical task
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.0.3
    • Component/s: None
    • Labels:
      None

      Description

      SET optimizer_switch='expensive_pred_static_pushdown=on';
      SET optimizer_use_condition_selectivity=3;
      SET use_stat_tables=PREFERABLY;
       
      CREATE TABLE t1 (a INT, b INT, KEY(b)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(0,0);
      ANALYZE TABLE t1;
      FLUSH TABLES;
       
      SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a );

      Expected result:

      a	b
      2	2

      Actual result:

      a	b
      2	2
      0	0

      (the second row is wrong because it doesn't match the EXISTS condition: there is no row in t1 where b would be less than 0)

      Also reproducible with optimizer_use_condition_selectivity=2, without table statistics.

      EXPLAIN with expensive_pred_static_pushdown=on:

      EXPLAIN EXTENDED SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a );
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	outer_t1	ALL	b	NULL	NULL	NULL	4	100.00	
      1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	Using where
      2	MATERIALIZED	t1	ALL	b	NULL	NULL	NULL	4	75.00	Using where
      3	DEPENDENT SUBQUERY	t1	index	b	b	5	NULL	4	100.00	Using where; Using index
      Warnings:
      Note	1276	Field or reference 'test.outer_t1.a' of SELECT #3 was resolved in SELECT #1
      Note	1003	select `test`.`outer_t1`.`a` AS `a`,`test`.`outer_t1`.`b` AS `b` from `test`.`t1` `outer_t1` semi join (`test`.`t1`) where (<expr_cache><`test`.`outer_t1`.`a`>(exists(select 1 from `test`.`t1` where (`test`.`t1`.`b` < `test`.`outer_t1`.`a`))) and (`test`.`t1`.`b` <> 1))

      EXPLAIN with expensive_pred_static_pushdown=off:

      EXPLAIN EXTENDED SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a );
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	100.00	
      1	PRIMARY	outer_t1	ALL	b	NULL	NULL	NULL	4	75.00	Using where; Subqueries: 3; Using join buffer (flat, BNL join)
      2	MATERIALIZED	t1	ALL	b	NULL	NULL	NULL	4	75.00	Using where
      3	DEPENDENT SUBQUERY	t1	index	b	b	5	NULL	4	100.00	Using where; Using index
      Warnings:
      Note	1276	Field or reference 'test.outer_t1.a' of SELECT #3 was resolved in SELECT #1
      Note	1003	select `test`.`outer_t1`.`a` AS `a`,`test`.`outer_t1`.`b` AS `b` from `test`.`t1` `outer_t1` semi join (`test`.`t1`) where ((`test`.`outer_t1`.`b` = `test`.`t1`.`a`) and <expr_cache><`test`.`outer_t1`.`a`>(exists(select 1 from `test`.`t1` where (`test`.`t1`.`b` < `test`.`outer_t1`.`a`))) and (`test`.`t1`.`b` <> 1))

      revision-id: timour@askmonty.org-20130417090331-mdqmyx1dwkciogqn
      revno: 3611
      branch-nick: 10.0-mdev83

        Attachments

          Activity

            People

            Assignee:
            timour Timour Katchaounov (Inactive)
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: