diff options
author | Michael Widenius <monty@askmonty.org> | 2011-11-23 10:25:27 +0200 |
---|---|---|
committer | Michael Widenius <monty@askmonty.org> | 2011-11-23 10:25:27 +0200 |
commit | c8768a091ac2d876216582813aaab7d9663008f7 (patch) | |
tree | 5ac8535bf70cda9f82f23d5acf8bcfa388b83266 /mysql-test/suite/innodb/r/innodb_mysql.result | |
parent | ccb2c805debe442f373afa37e70e1f923eb1fb75 (diff) | |
download | mariadb-git-c8768a091ac2d876216582813aaab7d9663008f7.tar.gz |
Fixes of testcases after merge with MySQL 5.1.59
Diffstat (limited to 'mysql-test/suite/innodb/r/innodb_mysql.result')
-rw-r--r-- | mysql-test/suite/innodb/r/innodb_mysql.result | 79 |
1 files changed, 53 insertions, 26 deletions
diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index df2fb3f34cf..42aca8b6464 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -889,13 +889,13 @@ EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; id 1 select_type SIMPLE table t1 -type range +type index possible_keys bkey -key bkey -key_len 5 +key PRIMARY +key_len 4 ref NULL -rows 16 -Extra Using where; Using index; Using filesort +rows 32 +Extra Using where SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; a b 1 2 @@ -934,12 +934,12 @@ EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a; id 1 select_type SIMPLE table t1 -type range +type index possible_keys bkey key bkey key_len 5 ref NULL -rows 16 +rows 32 Extra Using where; Using index SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a; a b @@ -989,7 +989,7 @@ possible_keys bkey key bkey key_len 5 ref const -rows 8 +rows 16 Extra Using where; Using index; Using filesort SELECT * FROM t2 WHERE b=1 ORDER BY a; a b c @@ -1018,7 +1018,7 @@ possible_keys bkey key bkey key_len 10 ref const,const -rows 8 +rows 16 Extra Using where; Using index SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a; a b c @@ -1047,7 +1047,7 @@ possible_keys bkey key bkey key_len 10 ref const,const -rows 8 +rows 16 Extra Using where; Using index SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a; a b c @@ -1076,7 +1076,7 @@ possible_keys bkey key bkey key_len 10 ref const,const -rows 8 +rows 16 Extra Using where; Using index SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a; a b c @@ -1211,7 +1211,7 @@ possible_keys b key b key_len 5 ref const -rows 1 +rows 2 Extra Using where; Using index SELECT * FROM t1 WHERE b=2 ORDER BY a ASC; a b @@ -1226,7 +1226,7 @@ possible_keys b key b key_len 5 ref const -rows 1 +rows 2 Extra Using where; Using index SELECT * FROM t1 WHERE b=2 ORDER BY a DESC; a b @@ -1370,7 +1370,7 @@ INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1); INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1; EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index t1_b PRIMARY 4 NULL 8 Using where +1 SIMPLE t1 range t1_b t1_b 5 NULL 8 Using where SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; a b c 8 1 1 @@ -1739,7 +1739,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 -2 DERIVED t1 index c3,c2 c2 10 NULL 5 +2 DERIVED t1 ALL c3,c2 c3 5 5 Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3)) ENGINE=InnoDB; @@ -1753,7 +1753,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 -2 DERIVED t1 index c3,c2 c2 18 NULL 5 +2 DERIVED t1 ALL c3,c2 c3 9 5 Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), KEY (c3), KEY (c2, c3)) @@ -1768,7 +1768,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> system NULL NULL NULL NULL 1 -2 DERIVED t1 index c3,c2 c2 14 NULL 5 +2 DERIVED t1 ALL c3,c2 c3 7 5 Using filesort DROP TABLE t1; End of 5.1 tests drop table if exists t1, t2, t3; @@ -1844,7 +1844,7 @@ possible_keys b key b key_len 5 ref NULL -rows 3 +rows 5 Extra Using where; Using index EXPLAIN SELECT c FROM bar WHERE c>2;; id 1 @@ -2306,6 +2306,28 @@ id select_type table type possible_keys key key_len ref rows Extra drop table t1,t2; # # +# Bug #39653: find_shortest_key in sql_select.cc does not consider +# clustered primary keys +# +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT, +KEY (b,c)) ENGINE=INNODB; +INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3), +(4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6), +(7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9), +(11,11,11,11,11,11); +EXPLAIN SELECT COUNT(*) FROM t1; +id 1 +select_type SIMPLE +table t1 +type index +possible_keys NULL +key PRIMARY +key_len 4 +ref NULL +rows 10 +Extra Using index +DROP TABLE t1; +# # Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may # corrupt definition at engine # @@ -2515,7 +2537,7 @@ f1 f2 f3 f4 EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE ORDER BY f1 DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range f2,f4 f4 1 NULL 11 Using where +1 SIMPLE t1 range f2,f4 f4 1 NULL 22 Using where DROP TABLE t1; # # Bug#54117 crash in thr_multi_unlock, temporary table @@ -2617,13 +2639,8 @@ rows 3 Extra Using index DROP TABLE t1; # -# ALTER TABLE IGNORE didn't ignore duplicates for unique add index +# Bug#56862 Execution of a query that uses index merge returns a wrong result # -create table t1 (a int primary key, b int) engine = innodb; -insert into t1 values (1,1),(2,1); -alter ignore table t1 add unique `main` (b); -drop table t1; -End of 5.1 tests CREATE TABLE t1 ( pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, a int, @@ -2654,7 +2671,7 @@ SELECT COUNT(*) FROM WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 3537 Using sort_union(idx,PRIMARY); Using where +2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 1536 Using sort_union(idx,PRIMARY); Using where SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; @@ -2663,6 +2680,16 @@ COUNT(*) SET SESSION sort_buffer_size = DEFAULT; DROP TABLE t1; # +# ALTER TABLE IGNORE didn't ignore duplicates for unique add index +# +create table t1 (a int primary key, b int) engine = innodb; +insert into t1 values (1,1),(2,1); +alter ignore table t1 add unique `main` (b); +select * from t1; +a b +1 1 +drop table t1; +# # Test for bug #39932 "create table fails if column for FK is in different # case than in corr index". # |