Details
-
Type:
Bug
-
Status: Closed (View Workflow)
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.2, 10.3
-
Component/s: Optimizer
-
Labels: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; |