revision-id: 3e7f8502d33816678eac58e216842e1ee97b29ca (mariadb-10.2.31-1001-g3e7f8502d33) parent(s): 152c83d49ca821c54aa49f6b43e33cba63e4d19f author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-10 23:54:14 +0300 message: MDEV-25858: Query results are incorrect when indexes are added If test_if_skip_sort_order() decides to use an index to produce required ordering, it should disable "Range Checked for each record" (RCfER) optimization. This is because RCfER may decide to use an index (or index_merge) which will not produce the required ordering. --- mysql-test/r/order_by_innodb.result | 52 +++++++++++++++++++++++++++++++++++++ mysql-test/t/order_by_innodb.test | 51 ++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 6 +++++ 3 files changed, 109 insertions(+) diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result index 9cdf9800cee..14b9b861a14 100644 --- a/mysql-test/r/order_by_innodb.result +++ b/mysql-test/r/order_by_innodb.result @@ -147,4 +147,56 @@ i n 656 eight set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2,t3; +# +# MDEV-25858: Query results are incorrect when indexes are added +# +CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb; +insert into t1 values (1),(2),(3); +CREATE TABLE t2 ( +id int NOT NULL PRIMARY KEY, +id2 int NOT NULL, +d1 datetime, +d2 timestamp NOT NULL, +KEY id2 (id2) +) engine=innodb; +insert into t2 values +(1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'), +(2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'), +(3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00'); +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 desc, dd.d2 desc, dd.id desc limit 1 +); +id id +1 NULL +2 1 +3 3 +create index for_latest_sort on t2 (d1 desc, d2 desc, id desc); +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 desc, dd.d2 desc, dd.id desc limit 1 +); +id id +1 NULL +2 1 +3 3 +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 f4c738263ae..97c043b8dbc 100644 --- a/mysql-test/t/order_by_innodb.test +++ b/mysql-test/t/order_by_innodb.test @@ -135,4 +135,55 @@ set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2,t3; +--echo # +--echo # MDEV-25858: Query results are incorrect when indexes are added +--echo # + +CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb; +insert into t1 values (1),(2),(3); + +CREATE TABLE t2 ( + id int NOT NULL PRIMARY KEY, + id2 int NOT NULL, + d1 datetime, + d2 timestamp NOT NULL, + KEY id2 (id2) +) engine=innodb; + +insert into t2 values + (1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'), + (2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'), + (3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00'); + +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 desc, dd.d2 desc, dd.id desc limit 1 + ); + +create index for_latest_sort on t2 (d1 desc, d2 desc, id desc); + +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 desc, dd.d2 desc, dd.id desc 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 ce706209017..d91be9f4705 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -21917,6 +21917,9 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, if (select->quick == save_quick) save_quick= 0; // make_reverse() consumed it select->set_quick(tmp); + /* Cancel "Range checked for each record" */ + tab->use_quick= 1; + tab->read_first_record= join_init_read_record; } else if (tab->type != JT_NEXT && tab->type != JT_REF_OR_NULL && tab->ref.key >= 0 && tab->ref.key_parts <= used_key_parts) @@ -21929,6 +21932,9 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, */ tab->read_first_record= join_read_last_key; tab->read_record.read_record= join_read_prev_same; + /* Cancel "Range checked for each record" */ + tab->use_quick= 1; + tab->read_first_record= join_init_read_record; /* Cancel Pushed Index Condition, as it doesn't work for reverse scans. */