diff options
| -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; |