LP:1013343 - SUBQUERY changes to DEPENDENT SUBQUERY when using a trivial derived table

Description

There are two queries that are different only in that one of them accesses a table directly, while the other reads it from a "trivial" derived table, ie. through a "(SELECT * FROM t2) as alias" construct. EXPLAINs should be the same, but they are different:

1 2 3 4 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (4),(6),(3),(5),(3),(246),(2),(9),(3),(8), (1),(8),(8),(5),(7),(5),(1),(6),(2),(9);
1 2 3 4 5 CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1),(7),(4),(7),(0),(2),(9),(4),(0),(9), (1),(3),(8),(8),(18),(84),(6),(3),(6),(6); explain extended SELECT * FROM t1, t2 AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
1 2 3 4 5 6 7 8 +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where | | 1 | PRIMARY | alias | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using join buffer (flat, BNL join) | | 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | | | 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
1 2 3 4 5 6 7 8 9 explain extended SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b ); +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where | | 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using join buffer (flat, BNL join) | | 3 | DEPENDENT SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | | | 3 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (flat, BNL join) | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+

Environment

None

Status

Assignee

Sergei Petrunia

Reporter

Sergei Petrunia

Labels

External issue ID

None

External issue ID

None

Fix versions

Priority

Major
Configure