diff options
author | Sergei Petrunia <psergey@askmonty.org> | 2022-01-19 10:05:50 +0300 |
---|---|---|
committer | Sergei Petrunia <psergey@askmonty.org> | 2022-01-19 10:05:50 +0300 |
commit | c10e10c693c58dd1ce488945fa8fd5b4ff998b18 (patch) | |
tree | 6436726aa6c64affe5809dec91571242447d0e7e | |
parent | 8c004b8f1d8f2579b73bbd62f6f5417c4a356733 (diff) | |
download | mariadb-git-preview-10.8-MDEV-13756-desc-indexes.tar.gz |
MDEV-27529: Wrong result upon query using index_merge with DESC key (#2)preview-10.8-MDEV-13756-desc-indexes
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.
-rw-r--r-- | mysql-test/main/desc_index_range.result | 25 | ||||
-rw-r--r-- | mysql-test/main/desc_index_range.test | 27 | ||||
-rw-r--r-- | 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); } |