Below is the list of changes that have just been committed into a local
5.1 repository of igor. When igor 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, 2008-01-11 00:03:39-08:00, igor@stripped +4 -0
Fixed bug #33697.
When the function test_if_skip_sort_order prefers index backward scan
to ref access the corresponding access functions must be set accordingly.
mysql-test/include/mix1.inc@stripped, 2008-01-11 00:03:33-08:00, igor@stripped +41 -14
Added a test case for bug #33697.
Corrected one previous bad merge.
mysql-test/r/innodb_mysql.result@stripped, 2008-01-11 00:03:33-08:00, igor@stripped +26 -1
Added a test case for bug #33697.
mysql-test/t/disabled.def@stripped, 2008-01-11 00:03:33-08:00, igor@stripped +0 -1
Turned innodb_mysql test on.
sql/sql_select.cc@stripped, 2008-01-11 00:03:34-08:00, igor@stripped +2 -1
Fixed bug #33697.
When the function test_if_skip_sort_order prefers index backward scan
to ref access the corresponding access functions must be set accordingly.
diff -Nrup a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
--- a/mysql-test/include/mix1.inc 2007-12-13 04:10:54 -08:00
+++ b/mysql-test/include/mix1.inc 2008-01-11 00:03:33 -08:00
@@ -723,20 +723,6 @@ set @@sort_buffer_size=default;
DROP TABLE t1,t2;
-#
-# Bug #32815: query with ORDER BY and a possible ref_or_null access
-#
-
-CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
-INSERT INTO t1 VALUES
- (191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2);
-
-EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
-SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
-
-DROP TABLE t1;
-
-
# Test of behaviour with CREATE ... SELECT
#
@@ -1091,6 +1077,19 @@ desc t1;
show create table t1;
drop table t1;
+#
+# Bug #32815: query with ORDER BY and a possible ref_or_null access
+#
+
+CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+ (191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2);
+
+EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
+SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
+
+DROP TABLE t1;
+
--echo End of 5.0 tests
# Fix for BUG#19243 "wrong LAST_INSERT_ID() after ON DUPLICATE KEY
@@ -1382,5 +1381,33 @@ if ($test_foreign_keys)
create table t1 (a int auto_increment primary key) engine=innodb;
alter table t1 order by a;
drop table t1;
+
+#
+# Bug #33697: ORDER BY primary key DESC vs. ref access + filesort
+# (reproduced only with InnoDB tables)
+#
+
+CREATE TABLE t
+ (vid integer NOT NULL,
+ tid integer NOT NULL,
+ idx integer NOT NULL,
+ name varchar(128) NOT NULL,
+ type varchar(128) NULL,
+ PRIMARY KEY(idx, vid, tid),
+ UNIQUE(vid, tid, name)
+) ENGINE=InnoDB;
+
+INSERT INTO t VALUES
+ (1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL),
+ (5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL),
+ (4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL),
+ (4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
+ (2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
+
+EXPLAIN SELECT * FROM t WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+
+SELECT * FROM t WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+
+DROP TABLE t;
--echo End of 5.1 tests
diff -Nrup a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
--- a/mysql-test/r/innodb_mysql.result 2007-12-13 04:10:54 -08:00
+++ b/mysql-test/r/innodb_mysql.result 2008-01-11 00:03:33 -08:00
@@ -1349,7 +1349,7 @@ INSERT INTO t1 VALUES
(191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2);
EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx NULL NULL NULL 3 Using where; Using filesort
+1 SIMPLE t1 ALL idx NULL NULL NULL 4 Using where; Using filesort
SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
id type d
191 member 1
@@ -1609,4 +1609,29 @@ alter table t1 order by a;
Warnings:
Warning 1105 ORDER BY ignored as there is a user-defined clustered index in the table 't1'
drop table t1;
+CREATE TABLE t
+(vid integer NOT NULL,
+tid integer NOT NULL,
+idx integer NOT NULL,
+name varchar(128) NOT NULL,
+type varchar(128) NULL,
+PRIMARY KEY(idx, vid, tid),
+UNIQUE(vid, tid, name)
+) ENGINE=InnoDB;
+INSERT INTO t VALUES
+(1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL),
+(5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL),
+(4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL),
+(4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
+(2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
+EXPLAIN SELECT * FROM t WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t index vid PRIMARY 12 NULL 16 Using where
+SELECT * FROM t WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+vid tid idx name type
+3 1 4 c_extra NULL
+3 1 3 c2 NULL
+3 1 2 c1 NULL
+3 1 1 pk NULL
+DROP TABLE t;
End of 5.1 tests
diff -Nrup a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def
--- a/mysql-test/t/disabled.def 2007-12-11 08:51:35 -08:00
+++ b/mysql-test/t/disabled.def 2008-01-11 00:03:33 -08:00
@@ -18,7 +18,6 @@ federated_transactions : Bug#29523 Tra
events : Bug#32664 events.test fails randomly
lowercase_table3 : Bug#32667 lowercase_table3.test reports to error log
kill : Bug#29149: Test "kill" fails on Windows
-innodb_mysql : Bug#32724: innodb_mysql.test fails randomly
wait_timeout : Bug#32801 wait_timeout.test fails randomly
kill : Bug#29149 Test "kill" fails on Windows
ctype_create : Bug#32965 main.ctype_create fails
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2008-01-08 01:33:38 -08:00
+++ b/sql/sql_select.cc 2008-01-11 00:03:34 -08:00
@@ -13123,7 +13123,8 @@ check_reverse_order:
select->quick=tmp;
}
}
- else if (tab->ref.key >= 0 && tab->ref.key_parts <= used_key_parts)
+ else if (tab->type != JT_NEXT &&
+ tab->ref.key >= 0 && tab->ref.key_parts <= used_key_parts)
{
/*
SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC
| Thread |
|---|
| • bk commit into 5.1 tree (igor:1.2656) BUG#33697 | igor | 11 Jan |
