From ddd90017e75cf1241da12cdc3fd55b44da0c4a9d Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Tue, 10 Nov 2009 10:21:41 +0200 Subject: 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. --- mysql-test/r/order_by.result | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) (limited to 'mysql-test/r/order_by.result') diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index f64bbc79cbd..f0e5b5fde3d 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1092,3 +1092,24 @@ FROM t3; 2 NULL DROP TABLE t1, t2, t3; +# +# Bug #42760: Select doesn't return desired results when we have null +# values +# +CREATE TABLE t1 ( +a INT, +c INT, +UNIQUE KEY a_c (a,c), +KEY (a)); +INSERT INTO t1 VALUES (1, 10), (2, NULL); +# 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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a_c,a a_c 10 const,const 1 Using where +# Must return 1 row +SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; +col +1 +DROP TABLE t1; +End of 5.0 tests -- cgit v1.2.1