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; |
Attachments
Activity
Roman, please click 'Review done' and assign the task back to me.
Just in case you can look at the patch in bb-10.2-igor (https://buildbot.mariadb.org/#/grid?branch=bb-10.2-igor).
Thanks.
This was already pushed to 10.2, but a merge to 10.3 is not trivial (my attempts would break the table value constructor tests) and hence this must be fixed in 10.3 separately.
A fix for this bug was pushed into 10.2. An adjusted fix was pushed into 10.3
This is another test case that produces a wrong explain:
MariaDB [test]> explain (select b,a from t2 order by a limit 3) order by b desc;+------+-------------+-------+------+---------------+------+---------+------+------+----------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+-------+------+---------------+------+---------+------+------+----------------+| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 4 | Using filesort |+------+-------------+-------+------+---------------+------+---------+------+------+----------------+