From: Date: September 14 2007 4:43pm Subject: bk commit into 5.0 tree (gkodinov:1.2526) BUG#31001 List-Archive: http://lists.mysql.com/commits/34279 X-Bug: 31001 Message-Id: <200709141443.l8EEhGj3018689@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 17:43:14+03:00, gkodinov@stripped +3 -0 Bug #31001: ORDER BY DESC in InnoDB not working The optimizer 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 relaxing the check and set reverse traversal even when all the secondary index keyparts are compared to a const. 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 17:43:12+03:00, gkodinov@stripped +97 -0 Bug #31001: test case mysql-test/t/innodb_mysql.test@stripped, 2007-09-14 17:43:12+03:00, gkodinov@stripped +23 -0 Bug #31001: test case sql/sql_select.cc@stripped, 2007-09-14 17:43:12+03:00, gkodinov@stripped +7 -1 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 the number of keyparts used is the same as the number of keyparts equal to a constant. (because of the primary key suffix). 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 17:43:12 +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 17:43:12 +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 17:43:12 +03:00 @@ -12063,6 +12063,12 @@ static int test_if_order_by_key(ORDER *o for (; const_key_parts & 1 ; const_key_parts>>= 1) key_part++; + /* + The primary and secondary key parts were all const (i.e. there's + one row). The sorting doesn't matter. + */ + if (key_part == key_part_end && reverse == 0) + DBUG_RETURN(1); } else DBUG_RETURN(0); @@ -12480,7 +12486,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) { /* SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC