summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--mysql-test/r/order_by_innodb.result23
-rw-r--r--mysql-test/t/order_by_innodb.test22
-rw-r--r--sql/sql_select.cc8
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;