Bug #42760: Select doesn't return desired results when we have null

 values
 
 We should re-set the access method functions when changing the access
 method when switching to another index to avoid sorting.
 
 Fixed by doing a little re-engineering : encapsulating all the function
 assignment into a special function and calling it when flipping the 
 indexes.

1 files changed, 26 insertions, 0 deletions

diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 6d7ee1c1ca7..d9a6c0f844d 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -756,3 +756,29 @@ SELECT
   FROM t3;
 
 DROP TABLE t1, t2, t3;
+
+
+
+--echo #
+--echo # Bug #42760: Select doesn't return desired results when we have null
+--echo # values
+--echo #
+
+CREATE TABLE t1 (
+  a INT,
+  c INT,
+  UNIQUE KEY a_c (a,c),
+  KEY (a));
+
+INSERT INTO t1 VALUES (1, 10), (2, NULL);
+
+--echo # Must use ref-or-null on the a_c index
+EXPLAIN
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+--echo # Must return 1 row
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+
+DROP TABLE t1;
+
+
+--echo End of 5.0 tests