[Commits] 677f1ef: MDEV-25682 Explain shows an execution plan different from actually executed

IgorBabaev igor at mariadb.com
Sat May 15 02:43:36 EEST 2021


revision-id: 677f1ef6f00793b3ad2a42b4e6f0fcbb7cd0e39d (mariadb-10.2.31-950-g677f1ef)
parent(s): e607f3398c69147299884d3814cf063d2e7516ce
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-05-14 16:43:36 -0700
message:

MDEV-25682 Explain shows an execution plan different from actually executed

If a select query contained an ORDER BY clause that followed a LIMIT clause
or an ORDER BY clause or ORDER BY with LIMIT the EXPLAIN output for the
query showed an execution plan different from that was actually executed.

Approved by Roman Nozdrin <roman.nozdrin at mariadb.com>

---
 mysql-test/r/order_by.result | 25 +++++++++++++++++++++++++
 mysql-test/t/order_by.test   | 16 ++++++++++++++++
 sql/sql_select.cc            |  2 +-
 3 files changed, 42 insertions(+), 1 deletion(-)

diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index b144101..39b4e25 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -3460,4 +3460,29 @@ SET max_length_for_sort_data=@save_max_length_for_sort_data;
 SET max_sort_length= @save_max_sort_length;
 SET sql_select_limit= @save_sql_select_limit;
 DROP TABLE t1;
+#
+# MDEV-25682: EXPLAIN for SELECT with ORDER BY after [ORDER BY] LIMIT
+#
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+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
+(select a from t1 limit 2) order by a desc;
+a
+7
+3
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	Using filesort
+NULL	UNION RESULT	<union1>	ALL	NULL	NULL	NULL	NULL	NULL	Using filesort
+(select b,a from t2 order by a limit 3) order by b desc;
+b	a
+70	3
+40	1
+30	4
+drop table t1,t2;
 # End of 10.2 tests
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 36c25ed..4e50fc5 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -2293,4 +2293,20 @@ SET max_sort_length= @save_max_sort_length;
 SET sql_select_limit= @save_sql_select_limit;
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-25682: EXPLAIN for SELECT with ORDER BY after [ORDER BY] LIMIT
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+explain (select a from t1 limit 2) order by a desc;
+(select a from t1 limit 2) order by a desc;
+
+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;
+(select b,a from t2 order by a limit 3) order by b desc;
+
+drop table t1,t2;
+
 --echo # End of 10.2 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index b85bd31..ce70620 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -25332,7 +25332,7 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
     sl->options|= SELECT_DESCRIBE;
   }
 
-  if (unit->is_union())
+  if (unit->is_union() || unit->fake_select_lex)
   {
     if (unit->union_needs_tmp_table() && unit->fake_select_lex)
     {


More information about the commits mailing list