summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSergei Petrunia <psergey@askmonty.org>2022-01-19 10:05:50 +0300
committerSergei Petrunia <psergey@askmonty.org>2022-01-19 10:05:50 +0300
commitc10e10c693c58dd1ce488945fa8fd5b4ff998b18 (patch)
tree6436726aa6c64affe5809dec91571242447d0e7e
parent8c004b8f1d8f2579b73bbd62f6f5417c4a356733 (diff)
downloadmariadb-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.result25
-rw-r--r--mysql-test/main/desc_index_range.test27
-rw-r--r--storage/innobase/handler/ha_innodb.cc3
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);
}