revision-id: 0873cdf6bc5139f606dc9ff834f81bea9bab66aa (mariadb-10.6.1-233-g0873cdf6bc5) parent(s): 53f0a8cdb8000b97ec079172f1911685714ab216 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-12-14 15:35:10 +0300 message: Descending indexes code exposed a gap in fix for MDEV-25858. Extend the fix for MDEV-25858 to handle non-reverse-ordered ORDER BY: If test_if_skip_sort_order() decides to use an index to produce rows in the required ordering, it should disable "Range Checked for Each Record". The fix needs to be backported to earlier versions. --- mysql-test/main/order_by_innodb.result | 34 ++++++++++++++++++-- mysql-test/main/order_by_innodb.test | 37 ++++++++++++++++++++-- .../suite/engines/funcs/r/ix_using_order.result | 4 +-- sql/sql_select.cc | 8 +++++ 4 files changed, 77 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/order_by_innodb.result b/mysql-test/main/order_by_innodb.result index 94bc5a3a858..74dd964cdce 100644 --- a/mysql-test/main/order_by_innodb.result +++ b/mysql-test/main/order_by_innodb.result @@ -180,7 +180,7 @@ id id 1 NULL 2 1 3 3 -create index for_latest_sort on t2 (d1 desc, d2 desc, id desc); +create index for_latest_sort on t2 (d1, d2, id); select t1.id,t2.id from @@ -198,7 +198,37 @@ id id 1 NULL 2 1 3 3 -drop table t1,t2; +# Now, same as above but use a DESC index +CREATE TABLE t3 ( +id int NOT NULL PRIMARY KEY, +id2 int NOT NULL, +d1 datetime, +d2 timestamp NOT NULL, +KEY id2 (id2) +) engine=innodb; +insert into t3 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'); +create index for_latest_sort on t3 (d1 desc, d2 desc, id desc); +select +t1.id,t3.id +from +t1 left join +t3 on t3.id2 = t1.id and +t3.id = (select dd.id +from t3 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,t3; # End of 10.2 tests # # MDEV-26938 Support descending indexes internally in InnoDB diff --git a/mysql-test/main/order_by_innodb.test b/mysql-test/main/order_by_innodb.test index 1eee4e3b626..ac737d9f20f 100644 --- a/mysql-test/main/order_by_innodb.test +++ b/mysql-test/main/order_by_innodb.test @@ -170,7 +170,7 @@ from dd.d1 desc, dd.d2 desc, dd.id desc limit 1 ); -create index for_latest_sort on t2 (d1 desc, d2 desc, id desc); +create index for_latest_sort on t2 (d1, d2, id); select t1.id,t2.id @@ -185,7 +185,40 @@ from order by dd.d1 desc, dd.d2 desc, dd.id desc limit 1 ); -drop table t1,t2; + +--echo # Now, same as above but use a DESC index + +CREATE TABLE t3 ( + id int NOT NULL PRIMARY KEY, + id2 int NOT NULL, + d1 datetime, + d2 timestamp NOT NULL, + KEY id2 (id2) +) engine=innodb; + +insert into t3 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'); +create index for_latest_sort on t3 (d1 desc, d2 desc, id desc); + + +select + t1.id,t3.id +from + t1 left join + t3 on t3.id2 = t1.id and + t3.id = (select dd.id + from t3 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,t3; --echo # End of 10.2 tests diff --git a/mysql-test/suite/engines/funcs/r/ix_using_order.result b/mysql-test/suite/engines/funcs/r/ix_using_order.result index 645b3fcfbc4..1e4389ce251 100644 --- a/mysql-test/suite/engines/funcs/r/ix_using_order.result +++ b/mysql-test/suite/engines/funcs/r/ix_using_order.result @@ -17,7 +17,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL, - KEY `i1` (`c1`) USING BTREE + KEY `i1` (`c1` DESC) USING BTREE ) ENGINE=ENGINE DEFAULT CHARSET=latin1 DROP TABLE t1; SHOW TABLES; @@ -40,7 +40,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL, - UNIQUE KEY `i1` (`c1`) USING BTREE + UNIQUE KEY `i1` (`c1` DESC) USING BTREE ) ENGINE=ENGINE DEFAULT CHARSET=latin1 DROP INDEX i1 ON t1; DROP TABLE t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0dfe95e81b0..33a5dd95a90 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -24260,7 +24260,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, } } 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(); + } if (tab->type == JT_EQ_REF) tab->read_record.unlock_row= join_read_key_unlock_row;