summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorGeorgi Kodinov <joro@sun.com>2009-12-07 16:38:56 +0200
committerGeorgi Kodinov <joro@sun.com>2009-12-07 16:38:56 +0200
commitbd308d1256fa7af7128c24f4a5e2abfb8274437a (patch)
tree3066c35674fd3b5e8ffd1c3be84542e1a837fb23
parent36e019c933f1842dc8cc534dbf5195a12dd99611 (diff)
downloadmariadb-git-bd308d1256fa7af7128c24f4a5e2abfb8274437a.tar.gz
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).
-rw-r--r--mysql-test/r/order_by.result9
-rw-r--r--mysql-test/t/order_by.test9
-rw-r--r--sql/sql_select.cc2
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