revision-id: 8149e4d0a139b901c8902b5b9fae371cef47275f (mariadb-10.3.26-174-g8149e4d) parent(s): b1b4d67bcda32472f5b9c46465bff9db86904a00 author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-07 15:08:18 -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@mariadb.com> --- mysql-test/main/order_by.result | 25 +++++++++++++++++++++++++ mysql-test/main/order_by.test | 16 ++++++++++++++++ sql/item_subselect.cc | 2 ++ sql/sql_select.cc | 2 +- 4 files changed, 44 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index f4e88d6..c8f63f8 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -3460,6 +3460,31 @@ 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 UNIT RESULT <unit1> 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 UNIT RESULT <unit1> 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 # # MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test index 7488414..08d5982 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -2293,6 +2293,22 @@ 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 --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 9f43561..352d80da9 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -274,6 +274,8 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref) res= TRUE; goto end; } + if (sl == unit->first_select() && !sl->next_select()) + unit->fake_select_lex= 0; } } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6c090ea..7f4c6d2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -26370,7 +26370,7 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result) sl->options|= SELECT_DESCRIBE; } - if (unit->is_unit_op()) + if (unit->is_unit_op() || unit->fake_select_lex) { if (unit->union_needs_tmp_table() && unit->fake_select_lex) {