3446 Georgi Kodinov 2009-07-09 [merge]
merge + fix a unstable test case.
modified:
mysql-test/include/mix1.inc
mysql-test/r/innodb_mrr.result
mysql-test/r/innodb_mysql.result
mysql-test/t/innodb_mrr.test
mysql-test/t/innodb_mysql.test
sql/sql_select.cc
3445 V Narayanan 2009-07-09 [merge]
merging with mysql-5.1-bugteam
added:
mysql-test/suite/ibmdb2i/r/ibmdb2i_bug_45983.result
mysql-test/suite/ibmdb2i/t/ibmdb2i_bug_45983.test
modified:
storage/ibmdb2i/ha_ibmdb2i.cc
storage/ibmdb2i/ha_ibmdb2i.h
=== modified file 'mysql-test/include/mix1.inc'
--- a/mysql-test/include/mix1.inc 2009-06-15 16:18:14 +0000
+++ b/mysql-test/include/mix1.inc 2009-07-09 16:11:01 +0000
@@ -1501,9 +1501,9 @@ INSERT INTO t1 VALUES
(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 t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
-SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
DROP TABLE t1;
=== modified file 'mysql-test/r/innodb_mrr.result'
--- a/mysql-test/r/innodb_mrr.result 2009-06-19 09:12:06 +0000
+++ b/mysql-test/r/innodb_mrr.result 2009-07-09 16:11:01 +0000
@@ -393,9 +393,9 @@ SELECT id FROM t1 WHERE parent_id IS NOT
id
60
This must show type=index, extra=Using where
-explain SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
+explain SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index ind_parent_id PRIMARY 4 NULL 2 Using where
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using where
SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
id parent_id name
60 40 F
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result 2009-06-15 16:18:14 +0000
+++ b/mysql-test/r/innodb_mysql.result 2009-07-09 16:11:01 +0000
@@ -1705,10 +1705,10 @@ INSERT INTO t1 VALUES
(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 t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) 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 t1 index vid PRIMARY 12 NULL 16 Using where
-SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+1 SIMPLE t1 index NULL PRIMARY 12 NULL 16 Using where
+SELECT * FROM t1 FORCE INDEX (PRIMARY) 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
@@ -2141,6 +2141,77 @@ GROUP BY t1.b;
a b c d a b e a b
1 1 1 0 1 1 2 1 1
DROP TABLE t1, t2, t3;
+#
+# Bug #45828: Optimizer won't use partial primary key if another
+# index can prevent filesort
+#
+CREATE TABLE `t1` (
+c1 int NOT NULL,
+c2 int NOT NULL,
+c3 int NOT NULL,
+PRIMARY KEY (c1,c2),
+KEY (c3)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (5,2,1246276747);
+INSERT INTO t1 VALUES (2,1,1246281721);
+INSERT INTO t1 VALUES (7,3,1246281756);
+INSERT INTO t1 VALUES (4,2,1246282139);
+INSERT INTO t1 VALUES (3,1,1246282230);
+INSERT INTO t1 VALUES (1,0,1246282712);
+INSERT INTO t1 VALUES (8,3,1246282765);
+INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1;
+INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1;
+INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1;
+INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1;
+INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1;
+INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1;
+SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+c1 c2 c3
+EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref PRIMARY,c3 PRIMARY 4 const 1 Using where; Using filesort
+EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref PRIMARY PRIMARY 4 const 1 Using where; Using filesort
+CREATE TABLE t2 (
+c1 int NOT NULL,
+c2 int NOT NULL,
+c3 int NOT NULL,
+KEY (c1,c2),
+KEY (c3)
+) ENGINE=InnoDB;
+explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref c1,c3 c1 4 const 1 Using where; Using filesort
+DROP TABLE t1,t2;
+#
+# 36259: Optimizing with ORDER BY
+#
+CREATE TABLE t1 (
+a INT NOT NULL AUTO_INCREMENT,
+b INT NOT NULL,
+c INT NOT NULL,
+d VARCHAR(5),
+e INT NOT NULL,
+PRIMARY KEY (a), KEY i2 (b,c,d)
+) ENGINE=InnoDB;
+INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2);
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort
+EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort
+EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using where
+DROP TABLE t1;
End of 5.1 tests
#
# BUG#42744: Crash when using a join buffer to join a table with a blob
=== modified file 'mysql-test/t/innodb_mrr.test'
--- a/mysql-test/t/innodb_mrr.test 2009-01-25 16:59:07 +0000
+++ b/mysql-test/t/innodb_mrr.test 2009-07-09 16:11:01 +0000
@@ -119,6 +119,6 @@ insert into t1 (id, parent_id, name) val
SELECT id FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
--echo This must show type=index, extra=Using where
-explain SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
+explain SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
drop table t1;
=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test 2009-06-07 20:50:14 +0000
+++ b/mysql-test/t/innodb_mysql.test 2009-07-09 16:11:01 +0000
@@ -380,6 +380,87 @@ SELECT * FROM t1, t2, t3
DROP TABLE t1, t2, t3;
+--echo #
+--echo # Bug #45828: Optimizer won't use partial primary key if another
+--echo # index can prevent filesort
+--echo #
+
+# Create the table
+CREATE TABLE `t1` (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 int NOT NULL,
+ PRIMARY KEY (c1,c2),
+ KEY (c3)
+) ENGINE=InnoDB;
+
+# populate with data
+INSERT INTO t1 VALUES (5,2,1246276747);
+INSERT INTO t1 VALUES (2,1,1246281721);
+INSERT INTO t1 VALUES (7,3,1246281756);
+INSERT INTO t1 VALUES (4,2,1246282139);
+INSERT INTO t1 VALUES (3,1,1246282230);
+INSERT INTO t1 VALUES (1,0,1246282712);
+INSERT INTO t1 VALUES (8,3,1246282765);
+INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1;
+INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1;
+INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1;
+INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1;
+INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1;
+INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1;
+
+# query and no rows will match the c1 condition, whereas all will match c3
+SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+
+# SHOULD use the pk.
+# index on c3 will be used instead of primary key
+EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+
+# if we force the primary key, we can see the estimate is 1
+EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+
+
+CREATE TABLE t2 (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 int NOT NULL,
+ KEY (c1,c2),
+ KEY (c3)
+) ENGINE=InnoDB;
+
+# SHOULD use the pk.
+# if we switch it from a primary key to a regular index, it works correctly as well
+explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+
+DROP TABLE t1,t2;
+
+
+--echo #
+--echo # 36259: Optimizing with ORDER BY
+--echo #
+
+CREATE TABLE t1 (
+ a INT NOT NULL AUTO_INCREMENT,
+ b INT NOT NULL,
+ c INT NOT NULL,
+ d VARCHAR(5),
+ e INT NOT NULL,
+ PRIMARY KEY (a), KEY i2 (b,c,d)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2);
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a;
+EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a;
+EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a;
+
+DROP TABLE t1;
+
--echo End of 5.1 tests
--echo #
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-07-06 08:38:21 +0000
+++ b/sql/sql_select.cc 2009-07-09 16:11:01 +0000
@@ -18589,9 +18589,17 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
for (nr=0; nr < table->s->keys ; nr++)
{
int direction;
+
if (keys.is_set(nr) &&
(direction= test_if_order_by_key(order, table, nr, &used_key_parts)))
{
+ /*
+ At this point we are sure that ref_key is a non-ordering
+ key (where "ordering key" is a key that will return rows
+ in the order required by ORDER BY).
+ */
+ DBUG_ASSERT (ref_key != (int) nr);
+
bool is_covering= table->covering_keys.is_set(nr) ||
(nr == table->s->primary_key &&
table->file->primary_key_is_clustered());
@@ -18672,7 +18680,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
*/
index_scan_time= select_limit/rec_per_key *
min(rec_per_key, table->file->scan_time());
- if (is_covering ||
+ if ((ref_key < 0 && is_covering) ||
(ref_key < 0 && (group || table->force_index)) ||
index_scan_time < read_time)
{
Attachment: [text/bzr-bundle] bzr/joro@sun.com-20090709161101-iov57k1sd3q7kve3.bundle
| Thread |
|---|
| • bzr push into mysql-pe branch (joro:3445 to 3446) | Georgi Kodinov | 9 Jul |
