diff options
author | Sergei Petrunia <psergey@askmonty.org> | 2021-12-15 15:12:06 +0300 |
---|---|---|
committer | Sergei Petrunia <psergey@askmonty.org> | 2021-12-15 22:43:24 +0300 |
commit | 136bcfdf7504092e7e49eba95e8be010da44c594 (patch) | |
tree | f09a88d3a9e53ab039a840a14cbee0ed57c9e944 | |
parent | f1ca949f2bcb3a0bd32fbb2c0eed7ba7dd1e099f (diff) | |
download | mariadb-git-bb-10.2-mdev2720.tar.gz |
MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMITbb-10.2-mdev2720
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.
-rw-r--r-- | mysql-test/r/order_by_innodb.result | 23 | ||||
-rw-r--r-- | mysql-test/t/order_by_innodb.test | 22 | ||||
-rw-r--r-- | sql/sql_select.cc | 8 |
3 files changed, 53 insertions, 0 deletions
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 47422116e38..3079145c1b9 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -21986,7 +21986,15 @@ check_reverse_order: } } 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; |