From: Date: September 14 2007 11:26am
Subject: bk commit into 5.0 tree (gkodinov:1.2526) BUG#31001
List-Archive: http://lists.mysql.com/commits/34245
X-Bug: 31001
Message-Id: <200709140926.l8E9Qqt7019360@magare.gmz>

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 12:26:49+03:00, gkodinov@stripped +3 -0
  Bug #31001: ORDER BY DESC in InnoDB not working
  
  When using the primary key as a suffix when ordering on a 
  secondary key for the supporting engines, the optimizer
  was not taking into account that implicitly the order in
  which the secondary key is retrieved is forward and if 
  the first primary key part has a DESC it will simply be
  ignored.
  
  Fixed by not using the primary key suffix if all the 
  secondary key parts were compared to a constant and 
  the first primary key part in the suffix is DESC. 

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

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

  sql/sql_select.cc@stripped, 2007-09-14 12:26:48+03:00, gkodinov@stripped +15 -1
    Bug #31001: don't use the primary key suffix if all the secondary key parts
    are compared to a constant and there is DESC in the first non-const pk part

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 12:26:47 +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; Using filesort
+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 12:26:47 +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 12:26:48 +03:00
@@ -12062,7 +12062,21 @@ static int test_if_order_by_key(ORDER *o
         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);
+
+        /*
+         There was no non-const key-part in the secondary key (=implicit ASC) 
+         and there's DESC on the primary key.
+        */
+        if (!reverse && !order->asc)
+          DBUG_RETURN(0);
       }
       else
         DBUG_RETURN(0);