SUBQUERY changes to DEPENDENT SUBQUERY when using a trivial derived table
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
In Progress
|
Undecided
|
Unassigned |
Bug 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:
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES
(4),(6)
(1),(8)
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES
(1),(7)
(1),(3)
explain extended SELECT * FROM t1, 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 | 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) |
+------
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) |
+------
The problem is repeatable on 5.3 and 5.5 (earlier versions are not applicable because they don't support derived_merge)