revision-id: 3c9b73935632aaacc941b1600c5046d57dec0ff6 (mariadb-10.2.40-139-g3c9b7393563) parent(s): 026984c360ce27c62072ed6ce798ec855952c974 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-12-15 15:12:06 +0300 message: MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT Followup to fix for MDEV-25858: When test_if_skip_sort_order() decides to use an index to satisfy ORDER BY ... LIMIT clause, it should disable "Range Checked for Each Record" optimization. Do this in all cases. --- mysql-test/r/order_by_innodb.result | 23 +++++++++++++++++++++++ mysql-test/t/order_by_innodb.test | 22 ++++++++++++++++++++++ sql/sql_select.cc | 8 ++++++++ 3 files changed, 53 insertions(+) diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result index 14b9b861a14..28922ef65f2 100644 --- a/mysql-test/r/order_by_innodb.result +++ b/mysql-test/r/order_by_innodb.result @@ -198,5 +198,28 @@ id id 1 NULL 2 1 3 3 +# +# MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT +# +# This must NOT have "Range checked for each record" without any +# provisions to produce rows in the required ordering: +explain +select +t1.id,t2.id +from +t1 left join +t2 on t2.id2 = t1.id and +t2.id = (select dd.id +from t2 dd +where +dd.id2 = t1.id and +d1 > '2019-02-06 00:00:00' + order by +dd.d1, dd.d2, dd.id limit 1 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL PRIMARY 4 NULL # Using index +1 PRIMARY t2 eq_ref PRIMARY,id2 PRIMARY 4 func # Using where +2 DEPENDENT SUBQUERY dd range id2,for_latest_sort for_latest_sort 6 NULL # Using where drop table t1,t2; # End of 10.2 tests diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test index 97c043b8dbc..af12644c073 100644 --- a/mysql-test/t/order_by_innodb.test +++ b/mysql-test/t/order_by_innodb.test @@ -184,6 +184,28 @@ from order by dd.d1 desc, dd.d2 desc, dd.id desc limit 1 ); + +--echo # +--echo # MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT +--echo # + +--echo # This must NOT have "Range checked for each record" without any +--echo # provisions to produce rows in the required ordering: +--replace_column 9 # +explain +select + t1.id,t2.id +from + t1 left join + t2 on t2.id2 = t1.id and + t2.id = (select dd.id + from t2 dd + where + dd.id2 = t1.id and + d1 > '2019-02-06 00:00:00' + order by + dd.d1, dd.d2, dd.id limit 1 + ); drop table t1,t2; --echo # End of 10.2 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 54a2facfe9f..1ab0c295e73 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -21980,7 +21980,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, } } else if (select && select->quick) + { + /* Cancel "Range checked for each record" */ + if (tab->use_quick == 2) + { + tab->use_quick= 1; + tab->read_first_record= join_init_read_record; + } select->quick->need_sorted_output(); + } tab->read_record.unlock_row= (tab->type == JT_EQ_REF) ? join_read_key_unlock_row : rr_unlock_row;