revision-id: dbc0683f13d07461c50ddd61b93f3c102bc1a9b3 (mariadb-10.2.31-1001-gdbc0683f13d) parent(s): 152c83d49ca821c54aa49f6b43e33cba63e4d19f author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-11 12:37:32 +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" optimization. This is because Range-Checked-for-each-record may decide to use an index (or an 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 | 12 +++++++++ 3 files changed, 115 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..b6f5385d653 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -21917,6 +21917,12 @@ 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" */ + if (tab->use_quick == 2) + { + 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 +21935,12 @@ 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" */ + if (tab->use_quick == 2) + { + tab->use_quick= 1; + tab->read_first_record= join_init_read_record; + } /* Cancel Pushed Index Condition, as it doesn't work for reverse scans. */