Description:
When the syntax "LIMIT 1" is used in the derived table, the EXPLAIN output in tree format is inconsistent with the classical format.
How to repeat:
CREATE TABLE t1(a INT);
CREATE TABLE t2(b INT);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (3), (4);
ANALYZE TABLE t1, t2;
mysql> EXPLAIN FORMAT = TREE SELECT * FROM t1, (SELECT * FROM t2 ORDER BY b LIMIT 1) as derived_table;
+------------------------------------------+
| EXPLAIN |
+------------------------------------------+
| -> Table scan on t1 (cost=0.45 rows=2)
|
+------------------------------------------+
1 row in set (0.00 sec)
The execution result is like the above, while the EXPLAIN output in classical format is like the below:
mysql> EXPLAIN SELECT * FROM t1, (SELECT * FROM t2 ORDER BY b LIMIT 1) as derived_table;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
| 2 | DERIVED | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using filesort |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
3 rows in set, 1 warning (0.00 sec)
The classical format result tells us that the whole query composes of two query blocks, and the second query block needs to do the filesort on t2.
But the output in tree format tells us we only need to do a table scan on t1, which is confusing.
