Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL)
-
None
Description
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; |