We're updating the issue view to help you get more done. 

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

Description

The following test case

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 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:

1 2 3 4 a --- 4 8

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

1 2 3 4 5 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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 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.

1 2 3 4 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):

1 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):

1 2 3 4 5 6 7 8 9 10 11 12 13 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.

Environment

None

Status

Assignee

Oleksandr Byelkin

Reporter

Elena Stepanova

Labels

Fix versions

Affects versions

10.0.0
5.5.28
5.3.10

Priority

Major