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

Part 2 : 
There was a special optimization on the ref access method for 
ORDER BY ... DESC that was set without actually looking on the type of the 
selected index for ORDER BY.
Fixed the SELECT ... ORDER BY .. DESC (it uses a different code path compared
to the ASC that has been fixed with the previous fix).

3 files changed, 19 insertions, 1 deletions

diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index f0e5b5fde3d..87b6c3f5455 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -1111,5 +1111,14 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
 col
 1
+# 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 DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+x	x	x	ref_or_null	a_c,a	x	x	x	x	x
+# Must return 1 row
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+col
+1
 DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index d9a6c0f844d..4da279cc8fd 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -778,6 +778,15 @@ 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;
 
+# part 2 of the problem : DESC test cases
+--echo # Must use ref-or-null on the a_c index
+--replace_column 1 x 2 x 3 x 6 x 7 x 8 x 9 x 10 x
+EXPLAIN
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+--echo # Must return 1 row
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+
+
 DROP TABLE t1;
 
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 9f0843fe0db..f655db0fc32 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -12824,7 +12824,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
 	  }
 	  DBUG_RETURN(1);
 	}
-	if (tab->ref.key_parts <= used_key_parts)
+	if (tab->ref.key_parts <= used_key_parts && tab->type == JT_REF)
 	{
 	  /*
 	    SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC