bzr commit into mysql-5.0-bugteam branch (joro:2865) Bug#42760

[Georgi Kodinov]

#At file:///home/kgeorge/mysql/work/B42760-5.0-bugteam/ based on revid:ramil@stripped

 2865 Georgi Kodinov	2009-12-07
      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).

    modified:
      mysql-test/r/order_by.result
      mysql-test/t/order_by.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/order_by.result'
--- a/mysql-test/r/order_by.result	2009-11-10 08:21:41 +0000
+++ b/mysql-test/r/order_by.result	2009-12-07 14:38:56 +0000
@@ -1111,5 +1111,14 @@ id	select_type	table	type	possible_keys	
 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

=== modified file 'mysql-test/t/order_by.test'
--- a/mysql-test/t/order_by.test	2009-11-10 08:21:41 +0000
+++ b/mysql-test/t/order_by.test	2009-12-07 14:38:56 +0000
@@ -778,6 +778,15 @@ SELECT 1 AS col FROM t1 WHERE a=2 AND (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;
 
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-12-04 17:58:40 +0000
+++ b/sql/sql_select.cc	2009-12-07 14:38:56 +0000
@@ -12824,7 +12824,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
 	  }
 	  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


Attachment: [text/bzr-bundle] bzr/[e-mail]