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

Wrong result (extra rows) with FROM subquery inside ALL subquery, LEFT JOIN, derived_merge

    XMLWordPrintable

Details

    Description

      The following test case

      SET optimizer_switch = 'derived_merge=on,in_to_exists=on';
       
      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (4),(8);
       
      CREATE TABLE t2 (b INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (7),(0);
       
      CREATE TABLE t3 (c INT, d INT NOT NULL) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (0,4),(8,6);
       
      SELECT * FROM t1
      WHERE a >= ALL ( 
      SELECT d FROM t2 LEFT JOIN ( SELECT * FROM t3 ) AS alias ON ( c = b ) 
      WHERE b >= a 
      );

      returns 2 rows:

      a
      ---
      4
      8

      The same query without the inner subquery returns only one row:

      SELECT * FROM t1
      WHERE a >= ALL ( SELECT d FROM t2 LEFT JOIN t3 ON ( c = b ) WHERE b >= a );
      a
      ---
      8

      A query with a view instead of the inner subquery also returns one row:

      CREATE ALGORITHM=MERGE VIEW v AS SELECT * FROM t3;
      SELECT * FROM t1 WHERE a >= ALL ( 
      SELECT d FROM t2 LEFT JOIN v ON ( c = b ) 
      WHERE b >= a 
      );
      a
      ---
      8
      CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v AS SELECT * FROM t3;
      SELECT * FROM t1 WHERE a >= ALL (
      SELECT d FROM t2 LEFT JOIN v ON ( c = b )
      WHERE b >= a
      );
      a
      ---
      8

      1 row is the correct result.

      branch: maria/5.3
      revision-id: sergii@pisem.net-20121123121131-p4nfv8j2cbh68dbg
      date: 2012-11-23 13:11:31 +0100
      revno: 3605

      On maria/5.3 the problem appeared with revno 3592 (Merge MariaDB 5.1.66 -> 5.2 -> 5.3).

      Also reproducible on maria/5.5, maria/10.0.
      MySQL 5.6 returns the correct result.

      Minimal optimizer_switch: derived_merge=on,in_to_exists=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,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

      EXPLAIN (with the default optimizer_switch):

      EXPLAIN EXTENDED
      SELECT * FROM t1 
      WHERE a >= ALL ( 
      SELECT d FROM t2 LEFT JOIN ( SELECT * FROM t3 ) AS alias ON ( c = b ) 
      WHERE b >= a 
      );
      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	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2100.00	Using where
      2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2100.00	Using where; Using join buffer (flat, BNL join)
      Warnings:
      Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
      Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t3`.`d` from `test`.`t2` left join (`test`.`t3`) on((`test`.`t3`.`c` = `test`.`t2`.`b`)) where ((`test`.`t2`.`b` >= `test`.`t1`.`a`) and trigcond((<cache>(`test`.`t1`.`a`) < `test`.`t3`.`d`)))))))

      Setting it to Minor for now as it can be considered an edge case.

      Attachments

        Activity

          People

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