diff options
| -rw-r--r-- | mysql-test/include/mix1.inc | 28 | ||||
| -rw-r--r-- | mysql-test/r/index_merge_innodb.result | 40 | ||||
| -rw-r--r-- | mysql-test/r/index_merge_myisam.result | 40 | ||||
| -rw-r--r-- | mysql-test/r/innodb_mysql.result | 25 | ||||
| -rw-r--r-- | mysql-test/t/disabled.def | 3 | ||||
| -rw-r--r-- | mysql-test/t/index_merge_innodb.test | 6 | ||||
| -rw-r--r-- | mysql-test/t/innodb_mysql.test | 2 |
7 files changed, 127 insertions, 17 deletions
diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index 9a3efa94075..fb4d095ed6d 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -6,6 +6,8 @@ # $other_engine_type must point to an all # time available storage engine # 2006-08 MySQL 5.1 MyISAM and MEMORY only +# $test_foreign_keys -- 0, skip foreign key tests +# -- 1, do not skip foreign key tests # have to be set before sourcing this script. # # Note: The comments/expectations refer to InnoDB. @@ -138,9 +140,9 @@ CREATE TABLE `t2` ( `id4` INT NOT NULL, UNIQUE (`id2`,`id4`), KEY (`id1`) -) ENGINE=InnoDB; +); -INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES +INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES (1,1,1,0), (1,1,2,1), (5,1,2,2), @@ -150,6 +152,7 @@ INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2); DROP TABLE t1, t2; + # # Bug #12882 min/max inconsistent on empty table # @@ -294,21 +297,26 @@ drop table t1; # # Test for bug #17164: ORed FALSE blocked conversion of outer join into join -# +# CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), - INDEX (name)) ENGINE=InnoDB; -CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11), - FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB; + INDEX (name)); +CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11)); +# CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11), +# FOREIGN KEY (fkey) REFERENCES t2(id)); +if ($test_foreign_keys) +{ + ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id); +} INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); EXPLAIN -SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id +SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%'; EXPLAIN -SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id +SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; DROP TABLE t1,t2; @@ -378,8 +386,8 @@ CREATE TABLE `t2` ( `c` int(11) default NULL, PRIMARY KEY (`k`), UNIQUE KEY `idx_1` (`a`) -) ENGINE=InnoDB; - insert into t2 ( a ) values ( 6 ) on duplicate key update c = +); +insert into t2 ( a ) values ( 6 ) on duplicate key update c = ifnull( c, 0 ) + 1; insert into t2 ( a ) values ( 7 ) on duplicate key update c = diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index d8cb4cab96d..588de70e6e5 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -284,6 +284,46 @@ kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND kp5 = 'R '; COUNT(*) 1 drop table t1; +create table t1 +( +key1 int not null, +key2 int not null default 0, +key3 int not null default 0 +); +insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8); +set @d=8; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +alter table t1 add index i2(key2); +alter table t1 add index i3(key3); +update t1 set key2=key1,key3=key1; +explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 9 Using sort_union(i3,i2); Using where +select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); +key1 key2 key3 +31 31 31 +32 32 32 +33 33 33 +34 34 34 +35 35 35 +36 36 36 +37 37 37 +38 38 38 +39 39 39 +drop table t1; #---------------- 2-sweeps read Index merge test 2 ------------------------------- SET SESSION STORAGE_ENGINE = InnoDB; drop table if exists t1; diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index e5bac6140b6..1c60e53a335 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -982,6 +982,46 @@ kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND kp5 = 'R '; COUNT(*) 1 drop table t1; +create table t1 +( +key1 int not null, +key2 int not null default 0, +key3 int not null default 0 +); +insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8); +set @d=8; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +insert into t1 (key1) select key1+@d from t1; +set @d=@d*2; +alter table t1 add index i2(key2); +alter table t1 add index i3(key3); +update t1 set key2=key1,key3=key1; +explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 11 Using sort_union(i3,i2); Using where +select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); +key1 key2 key3 +31 31 31 +32 32 32 +33 33 33 +34 34 34 +35 35 35 +36 36 36 +37 37 37 +38 38 38 +39 39 39 +drop table t1; #---------------- 2-sweeps read Index merge test 2 ------------------------------- SET SESSION STORAGE_ENGINE = MyISAM; drop table if exists t1; diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 5612713a76e..1b3f0f021be 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -99,8 +99,8 @@ CREATE TABLE `t2` ( `id4` INT NOT NULL, UNIQUE (`id2`,`id4`), KEY (`id1`) -) ENGINE=InnoDB; -INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES +); +INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES (1,1,1,0), (1,1,2,1), (5,1,2,2), @@ -271,6 +271,25 @@ explain select distinct f1, f2 from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL PRIMARY 5 NULL 3 Using index for group-by; Using temporary drop table t1; +CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), +INDEX (name)); +CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11)); +ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id); +INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); +INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); +EXPLAIN +SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id +WHERE t1.name LIKE 'A%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index +1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index +EXPLAIN +SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id +WHERE t1.name LIKE 'A%' OR FALSE; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where +DROP TABLE t1,t2; CREATE TABLE t1 (a int, b int); insert into t1 values (1,1),(1,2); CREATE TABLE t2 (primary key (a)) select * from t1; @@ -340,7 +359,7 @@ CREATE TABLE `t2` ( `c` int(11) default NULL, PRIMARY KEY (`k`), UNIQUE KEY `idx_1` (`a`) -) ENGINE=InnoDB; +); insert into t2 ( a ) values ( 6 ) on duplicate key update c = ifnull( c, 0 ) + 1; diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index bf49ea004e2..bba1dc424d7 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -17,9 +17,6 @@ im_options : Bug#20294 2006-07-24 stewart Instance manager test #im_life_cycle : Bug#20368 2006-06-10 alik im_life_cycle test fails im_daemon_life_cycle : BUG#22379 2006-09-15 ingo im_daemon_life_cycle.test fails on merge of 5.1 -> 5.1-engines im_instance_conf : BUG#20294 2006-09-16 ingo Instance manager test im_instance_conf fails randomly -index_merge_innodb : BUG#22398 2006-09-15 ingo index_merge_innodb.test fails on wrong result file -index_merge_myisam : BUG#22404 2006-09-15 ingo index_merge_myisam.test fails on wrong result file -innodb_mysql : BUG#22405 2006-09-15 ingo innodb_mysql.test fails on wrong result file concurrent_innodb : BUG#21579 2006-08-11 mleich innodb_concurrent random failures with varying differences ndb_autodiscover : BUG#18952 2006-02-16 jmiller Needs to be fixed w.r.t binlog ndb_autodiscover2 : BUG#18952 2006-02-16 jmiller Needs to be fixed w.r.t binlog diff --git a/mysql-test/t/index_merge_innodb.test b/mysql-test/t/index_merge_innodb.test index 509ad733578..a3bda0ad00c 100644 --- a/mysql-test/t/index_merge_innodb.test +++ b/mysql-test/t/index_merge_innodb.test @@ -15,7 +15,11 @@ let $engine_type= InnoDB; # InnoDB does not support Merge tables (affects include/index_merge1.inc) let $merge_table_support= 0; -# Tests disabled because of open Bug#21277 and non deterministic explain output. +# The first two tests are disabled because of non deterministic explain output. +# If include/index_merge1.inc can be enabled for InnoDB and all other +# storage engines, please remove the subtest for Bug#21277 from +# include/index_merge2.inc. +# This test exists already in include/index_merge1.inc. # --source include/index_merge1.inc # --source include/index_merge_ror.inc diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index d7f1a40eaf0..93495538141 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -8,5 +8,7 @@ -- source include/have_innodb.inc let $engine_type= InnoDB; let $other_engine_type= MEMORY; +# InnoDB does support FOREIGN KEYFOREIGN KEYs +let $test_foreign_keys= 1; --source include/mix1.inc |