List:Commits« Previous MessageNext Message »
From:kgeorge Date:September 14 2007 11:46am
Subject:bk commit into 5.0 tree (gkodinov:1.2526) BUG#31001
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-09-14 14:46:54+03:00, gkodinov@stripped +3 -0
  Bug #31001: ORDER BY DESC in InnoDB not working
  
  The engine sets index traversal in reverse order only if there are 
   used key parts that are not compared to a constant.
  However using the primary key as an ORDER BY suffix rendered the check
  incomplete : going in reverse order must still be used even if 
  all the parts of the secondary key are compared to a constant.
  
  Fixed by having a special return value that signals that going in 
  reverse order must still be used even if all the secondary key parts
  are compared to a constant.
  Also account for the case when all the primary keys are compared to a
  constant.

  mysql-test/r/innodb_mysql.result@stripped, 2007-09-14 14:46:53+03:00, gkodinov@stripped +97 -0
    Bug #31001: test case

  mysql-test/t/innodb_mysql.test@stripped, 2007-09-14 14:46:53+03:00, gkodinov@stripped +23 -0
    Bug #31001: test case

  sql/sql_select.cc@stripped, 2007-09-14 14:46:53+03:00, gkodinov@stripped +20 -4
    Bug #31001: 
     - account for the case when all the primary key parts are compared
       to a constant
     - force test_if_skip_sort_order to go backwards over the key even 
       when there are more ORDER BY parts (because of the primary key 
       suffix) than there are secondary key parts.

diff -Nrup a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
--- a/mysql-test/r/innodb_mysql.result	2007-08-29 11:49:17 +03:00
+++ b/mysql-test/r/innodb_mysql.result	2007-09-14 14:46:53 +03:00
@@ -1114,4 +1114,101 @@ c	b
 3	1
 3	2
 DROP TABLE t1;
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
+INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
+EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
+id	1
+select_type	SIMPLE
+table	t1
+type	ref
+possible_keys	b
+key	b
+key_len	5
+ref	const
+rows	1
+Extra	Using where; Using index
+SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
+a	b
+2	2
+3	2
+EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
+id	1
+select_type	SIMPLE
+table	t1
+type	ref
+possible_keys	b
+key	b
+key_len	5
+ref	const
+rows	1
+Extra	Using where; Using index
+SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
+a	b
+3	2
+2	2
+EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	b
+key_len	5
+ref	NULL
+rows	3
+Extra	Using index
+SELECT * FROM t1 ORDER BY b ASC, a ASC;
+a	b
+1	1
+2	2
+3	2
+EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	b
+key_len	5
+ref	NULL
+rows	3
+Extra	Using index
+SELECT * FROM t1 ORDER BY b DESC, a DESC;
+a	b
+3	2
+2	2
+1	1
+EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	b
+key_len	5
+ref	NULL
+rows	3
+Extra	Using index; Using filesort
+SELECT * FROM t1 ORDER BY b ASC, a DESC;
+a	b
+1	1
+3	2
+2	2
+EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	b
+key_len	5
+ref	NULL
+rows	3
+Extra	Using index; Using filesort
+SELECT * FROM t1 ORDER BY b DESC, a ASC;
+a	b
+2	2
+3	2
+1	1
+DROP TABLE t1;
 End of 5.0 tests
diff -Nrup a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
--- a/mysql-test/t/innodb_mysql.test	2007-08-29 11:45:29 +03:00
+++ b/mysql-test/t/innodb_mysql.test	2007-09-14 14:46:53 +03:00
@@ -937,4 +937,27 @@ SELECT c,b   FROM t1 GROUP BY c,b;
 
 DROP TABLE t1;
 
+#
+# Bug #31001: ORDER BY DESC in InnoDB not working
+#
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
+INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
+
+#The two queries below should produce different results, but they don't.
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
+SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
+SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
+
+query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
+SELECT * FROM t1 ORDER BY b ASC, a ASC;
+query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
+SELECT * FROM t1 ORDER BY b DESC, a DESC;
+query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
+SELECT * FROM t1 ORDER BY b ASC, a DESC;
+query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
+SELECT * FROM t1 ORDER BY b DESC, a ASC;
+
+DROP TABLE t1;
+
 --echo End of 5.0 tests
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-08-29 12:47:22 +03:00
+++ b/sql/sql_select.cc	2007-09-14 14:46:53 +03:00
@@ -12020,6 +12020,7 @@ part_of_refkey(TABLE *table,Field *field
     1   key is ok.
     0   Key can't be used
     -1  Reverse key can be used
+    -2  Reverse key must be used even when all the keyparts are used
 *****************************************************************************/
 
 static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx,
@@ -12030,7 +12031,7 @@ static int test_if_order_by_key(ORDER *o
   key_part_end=key_part+table->key_info[idx].key_parts;
   key_part_map const_key_parts=table->const_key_parts[idx];
   int reverse=0;
-  my_bool on_primary_key= FALSE;
+  my_bool on_primary_key= FALSE, is_const_secondary_key= FALSE;
   DBUG_ENTER("test_if_order_by_key");
 
   for (; order ; order=order->next, const_key_parts>>=1)
@@ -12057,12 +12058,21 @@ static int test_if_order_by_key(ORDER *o
           table->s->primary_key != MAX_KEY)
       {
         on_primary_key= TRUE;
+        if (!reverse)
+          is_const_secondary_key= TRUE;
         key_part= table->key_info[table->s->primary_key].key_part;
         key_part_end=key_part+table->key_info[table->s->primary_key].key_parts;
         const_key_parts=table->const_key_parts[table->s->primary_key];
 
         for (; const_key_parts & 1 ; const_key_parts>>= 1)
-          key_part++; 
+          key_part++;
+
+        /* 
+          The primary key parts were all constants. The sorting doesn't
+          change.
+        */  
+        if (key_part == key_part_end)
+          DBUG_RETURN(reverse);
       }
       else
         DBUG_RETURN(0);
@@ -12084,6 +12094,12 @@ static int test_if_order_by_key(ORDER *o
   if (reverse == -1 && !(table->file->index_flags(idx, *used_key_parts-1, 1) &
                          HA_READ_PREV))
     reverse= 0;                                 // Index can't be used
+  /*
+   Signal to test_if_skip_sort_order() that it must setup going in reverse
+   key order even when all the parts of the secondary key are used.
+  */ 
+  if (reverse == -1 && is_const_secondary_key)
+    reverse= -2;
   DBUG_RETURN(reverse);
 }
 
@@ -12451,7 +12467,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
 	(order_direction = test_if_order_by_key(order,table,ref_key,
 						&used_key_parts)))
     {
-      if (order_direction == -1)		// If ORDER BY ... DESC
+      if (order_direction < 0)		// If ORDER BY ... DESC
       {
 	if (select && select->quick)
 	{
@@ -12480,7 +12496,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 || order_direction == -2)
 	{
 	  /*
 	    SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC
Thread
bk commit into 5.0 tree (gkodinov:1.2526) BUG#31001kgeorge14 Sep