MySQL Bugs: #98675: LIMIT clause in derived table garbles EXPLAIN Note

Bug #98675	LIMIT clause in derived table garbles EXPLAIN Note
Submitted:	19 Feb 2020 20:57	Modified:	24 Feb 2020 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

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;

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`

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.

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 ???

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?

Hi Øystein,

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

Do you concur ????

Hi Sinisa!

Sounds good to me.

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.