revision-id: c10e10c693c58dd1ce488945fa8fd5b4ff998b18 (mariadb-10.6.1-282-gc10e10c693c) parent(s): 8c004b8f1d8f2579b73bbd62f6f5417c4a356733 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-19 10:05:50 +0300 message: MDEV-27529: Wrong result upon query using index_merge with DESC key (#2) ROR-index_merge relies on Rowid-ordered-retrieval property: a ROR scan, e.g. a scan on equality range tbl.key=const should return rows ordered by their Rowid. Also, handler->cmp_ref() should compare rowids according to the Rowid ordering. When the table's primary key uses DESC keyparts, ROR scans return rows according to the PK's ordering. But ha_innobase::cmp_ref() compared rowids as if PK used ASC keyparts. This caused wrong query results with index_merge. Fixed this by making ha_innobase::cmp_ref() compare according to the PK defintion, including keypart's DESC property. --- mysql-test/main/desc_index_range.result | 25 ++++++++++++++++++++++++- mysql-test/main/desc_index_range.test | 27 +++++++++++++++++++++++++++ storage/innobase/handler/ha_innodb.cc | 3 ++- 3 files changed, 53 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/desc_index_range.result b/mysql-test/main/desc_index_range.result index edf13010829..94176270dbf 100644 --- a/mysql-test/main/desc_index_range.result +++ b/mysql-test/main/desc_index_range.result @@ -186,6 +186,29 @@ CREATE OR REPLACE TABLE t1 (pk INT, a INT, b int, KEY(a), PRIMARY KEY(pk DESC)) INSERT INTO t1 VALUES (1,4,5),(2,9,6),(3,NULL,7),(4,NULL,8); SELECT * FROM t1 WHERE pk > 10 OR a > 0; pk a b -1 4 5 2 9 6 +1 4 5 DROP TABLE t1; +# +# MDEV-27529: Wrong result upon query using index_merge with DESC key (#2) +# +create table t1 ( +pk int, +a int, +b int, +primary key(pk desc), +key(a), +key(b) +) engine=innodb; +insert into t1 values (0, 111111, 255); +insert into t1 select seq+50000, NULL, seq+1000 from seq_1_to_260; +insert into t1 values (10000, NULL, 255); +insert into t1 select seq+20000, seq+20000, seq+20000 from seq_1_to_1500; +# Must use ROR-intersect: +explain select * from t1 where b = 255 AND a IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b b,a 5,5 NULL 1 Using intersect(b,a); Using where; Using index +select * from t1 where b = 255 AND a IS NULL; +pk a b +10000 NULL 255 +drop table t1; diff --git a/mysql-test/main/desc_index_range.test b/mysql-test/main/desc_index_range.test index 2f3d36c8305..db59922e177 100644 --- a/mysql-test/main/desc_index_range.test +++ b/mysql-test/main/desc_index_range.test @@ -96,3 +96,30 @@ INSERT INTO t1 VALUES (1,4,5),(2,9,6),(3,NULL,7),(4,NULL,8); SELECT * FROM t1 WHERE pk > 10 OR a > 0; DROP TABLE t1; + + +--echo # +--echo # MDEV-27529: Wrong result upon query using index_merge with DESC key (#2) +--echo # + +create table t1 ( + pk int, + a int, + b int, + primary key(pk desc), + key(a), + key(b) +) engine=innodb; + +insert into t1 values (0, 111111, 255); + +insert into t1 select seq+50000, NULL, seq+1000 from seq_1_to_260; + +insert into t1 values (10000, NULL, 255); +insert into t1 select seq+20000, seq+20000, seq+20000 from seq_1_to_1500; + +--echo # Must use ROR-intersect: +explain select * from t1 where b = 255 AND a IS NULL; +select * from t1 where b = 255 AND a IS NULL; + +drop table t1; diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index d19c8cfa2a7..1eba9478b74 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -16812,7 +16812,8 @@ ha_innobase::cmp_ref( } if (result) { - + if (key_part->key_part_flag & HA_REVERSE_SORT) + result = -result; return(result); }