For the following query
(select a from t1 limit 2) order by a desc
|
EXPLAIN shows
MariaDB [test]> explain (select a from t1 limit 2) order by a desc;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 3 | |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|
though the execution of the query in debugger clearly indicates that first a temporary table is created for (select a from t1 limit 2) then the rows from this temporary are sorted.
So the expected output from EXPLAIN would be
MariaDB [test]> explain (select a from t1 limit 2) order by a desc;
|
+------+--------------+----------+------+---------------+------+---------+------+------+----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+----------+------+---------------+------+---------+------+------+----------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 3 | |
|
| NULL | UNION RESULT | <union1> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort |
|
+------+--------------+----------+------+---------------+------+---------+------+------+----------------+
|
The full test case is:
create table t1 (a int);
|
insert into t1 values (3), (7), (1);
|
explain (select a from t1 limit 2) order by a desc;
|
drop table t1;
|
Bug
Major
