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#31001 | kgeorge | 14 Sep |
