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.

1 files changed, 27 insertions, 0 deletions

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;