Bug #98675 LIMIT clause in derived table garbles EXPLAIN Note
Submitted: 19 Feb 20:57 Modified: 24 Feb 13:41
Reporter: Øystein Grøvlen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[19 Feb 20:57] Øystein Grøvlen
Description:
If a derived table contains a LIMIT clause, the derived table is missing from the EXPLAIN Note:

mysql> explain select i from (select i from t1 join t1 t2 using(i) order by t2.j limit 1) dt;
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------+------+----------+----------------+
| 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           |
|  2 | DERIVED     | t2         | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    2 |   100.00 | Using filesort |
|  2 | DERIVED     | t1         | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.i |    1 |   100.00 | Using index    |
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------+------+----------+----------------+
3 rows in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select '1' AS `i` from dual

Without LIMIT clause, this does not happen:

mysql> explain select i from (select i from t1 join t1 t2 using(i) order by t2.j) dt;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using index; Using temporary; Using filesort       |
|  1 | SIMPLE      | t2    | NULL       | ALL   | PRIMARY       | NULL    | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` join `test`.`t1` `t2` where (`test`.`t2`.`i` = `test`.`t1`.`i`) order by `test`.`t2`.`j`

How to repeat:
create table t1(i int primary key, j int);
insert into t1 values (1,1), (2,1);
explain select i from (select i from t1 join t1 t2 using(i) order by t2.j limit 1) dt;
show warnings;
[19 Feb 21:01] Øystein Grøvlen
It seems the bug is related to using two instances of the same table.  Using two different tables works as expected:

mysql> explain select i from (select t1.i from t1 join t2 using(i) order by t2.i limit 1) dt;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+--------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra                          |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+--------------------------------+
|  1 | PRIMARY     | NULL  | NULL       | NULL   | NULL          | NULL    | NULL    | NULL      | NULL |     NULL | no matching row in const table |
|  2 | DERIVED     | t2    | NULL       | index  | PRIMARY       | PRIMARY | 4       | NULL      |    1 |   100.00 | Using index                    |
|  2 | DERIVED     | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.i |    1 |   100.00 | Using index                    |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+--------------------------------+
3 rows in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select NULL AS `i` from (/* select#2 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`i` = `test`.`t2`.`i`) order by `test`.`t2`.`i` limit 1) `dt`
[20 Feb 10:11] Steinar Gunderson
Hi Øystein,

Due to the LIMIT 1, the derived table becomes a const table and is executed during optimization. So what you're seeing (SELECT 1 AS i FROM DUAL) is what is actually executed. Without the LIMIT 1, there's no const table and the table stays (and thus, you can see it in the note). Of course, you could argue this is still a shortcoming.
[20 Feb 13:21] MySQL Verification Team
Hi Øystein, Steinar,

First of all, thank you Øystein for the bug report. Also thanks a lot, Steinar, for your response.

Our Reference Manual states it clearly:

"
When some tables are of const or system type, expressions involving columns from these tables are evaluated early by the optimizer and are not part of the displayed statement. 
"

Hence, we have two options. One is to document it with more detail and the other is to include it obligatory in the EXPLAIN statement's output, regardless of the EXPLAIN format.

In first case it would make a documentation bug and in the second case it is a feature request.

What do you think ???
[20 Feb 20:56] Øystein Grøvlen
Sorry,

I did not realize that "LIMIT 1" would actually trigger execution during optimization.  
I guess this is an intended behavior, and in some ways it makes sense.  owbut a bit confusing behavior, and it does not seem to be consistent:

mysql> explain select * from t1 where t1.j = (select t1.i from t1 join t1 t2 using(i) order by t2.j desc limit 1);
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+----------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra          |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+----------------+
|  1 | PRIMARY     | t1    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL      |    2 |    50.00 | Using where    |
|  2 | SUBQUERY    | t2    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    2 |   100.00 | Using filesort |
|  2 | SUBQUERY    | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.i |    1 |   100.00 | Using index    |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+----------------+
3 rows in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `test`.`t1`.`i` AS `i`,`test`.`t1`.`j` AS `j` from `test`.`t1` where (`test`.`t1`.`j` = (/* select#2 */ select `test`.`t1`.`i` from `test`.`t1` join `test`.`t1` `t2` where (`test`.`t1`.`i` = `test`.`t2`.`i`) order by `test`.`t2`.`j` desc limit 1))

For consistent behavior, should not the subquery be replaced by the its result in the above Note?
[21 Feb 12:58] MySQL Verification Team
Hi Øystein,

Regarding changing notes from EXPLAIN, we can make it a feature request.

Do you concur ????
[21 Feb 22:30] Øystein Grøvlen
Hi Sinisa!

Sounds good to me.
[24 Feb 13:41] MySQL Verification Team
Hi Øystein Grøvlen,

First of all, do note that I still write your 'Ø' properly.

Second, I am verifying it as a feature request for the changes in the output of the Notes from the EXPLAIN as described in this report.