diff options
| -rw-r--r-- | mysql-test/r/order_by.result | 9 | ||||
| -rw-r--r-- | mysql-test/t/order_by.test | 9 | ||||
| -rw-r--r-- | sql/sql_select.cc | 2 |
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 |