[MDEV-25682] Explain shows an execution plan different from actually executed Created: 2021-05-14  Updated: 2021-06-08  Resolved: 2021-06-08

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2, 10.3
Fix Version/s: 10.2.39, 10.3.30

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Igor Babaev
Resolution: Fixed Votes: 0
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;



 Comments   
Comment by Igor Babaev [ 2021-05-14 ]

This is another test case that produces a wrong explain:

create table t2 (a int, b int);
 
insert into t2 values (3,70), (7,10), (1,40), (4,30);
 
explain (select b,a from t2 order by a limit 3) order by b desc;


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

Comment by Igor Babaev [ 2021-05-14 ]

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.

Comment by Marko Mäkelä [ 2021-05-18 ]

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.

Comment by Igor Babaev [ 2021-06-08 ]

A fix for this bug was pushed into 10.2. An adjusted fix was pushed into 10.3

Generated at Thu Feb 08 09:39:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.