Bug #101517 EXPLAIN in tree format is inconsistent with classical format when LIMIT 1 used
Submitted: 9 Nov 2020 6:32 Modified: 9 Nov 2020 7:39
Reporter: Hope Lee (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.20, 8.0.22 OS:CentOS
Assigned to: CPU Architecture:Any

[9 Nov 2020 6:32] Hope Lee
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.
[9 Nov 2020 7:39] MySQL Verification Team
Hello Lee,

Thank you for the report and test case.

regards,
Umesh