Below is the list of changes that have just been committed into a local
5.1 repository of mleich. When mleich 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, 2006-09-18 16:55:56+02:00, mleich@stripped +7 -0
1. Cumulated fix for Bug#22398, #22404 and #22405
2. Correct some logical bugs within the tests, which were caused by
automatic resolve of BitKeeper
mysql-test/include/mix1.inc@stripped, 2006-09-18 16:55:52+02:00, mleich@stripped +18 -10
- Remove hardcoded assignment of storage engine, because this violates the logics of
refactored tests
- Remove trailing spaces
- Move the FOREIGN KEY creation(probably not really needed for the testcase) out of
the CREATE TABLE statement + let the FK creation depend on the storage engine capabilities
mysql-test/r/index_merge_innodb.result@stripped, 2006-09-18 16:55:52+02:00, mleich@stripped +40 -0
Updated result
mysql-test/r/index_merge_myisam.result@stripped, 2006-09-18 16:55:52+02:00, mleich@stripped +40 -0
Updated result
mysql-test/r/innodb_mysql.result@stripped, 2006-09-18 16:55:52+02:00, mleich@stripped +22 -3
Updated result
mysql-test/t/disabled.def@stripped, 2006-09-18 16:55:52+02:00, mleich@stripped +0 -3
Reenable the fixed tests
mysql-test/t/index_merge_innodb.test@stripped, 2006-09-18 16:55:52+02:00, mleich@stripped +5 -1
Important comment describing how to modify the tests when some issues
(most probably no bugs) are fixed.
mysql-test/t/innodb_mysql.test@stripped, 2006-09-18 16:55:52+02:00, mleich@stripped +2 -0
Introduction of a variable for switching FOREIGN KEY on/off
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: mleich
# Host: three.local.lan
# Root: /work/mysql-5.1-engines/src-3
--- 1.10/mysql-test/r/innodb_mysql.result 2006-09-18 16:56:04 +02:00
+++ 1.11/mysql-test/r/innodb_mysql.result 2006-09-18 16:56:04 +02:00
@@ -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;
--- 1.11/mysql-test/include/mix1.inc 2006-09-18 16:56:04 +02:00
+++ 1.12/mysql-test/include/mix1.inc 2006-09-18 16:56:04 +02:00
@@ -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`
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 =
--- 1.202/mysql-test/t/disabled.def 2006-09-18 16:56:04 +02:00
+++ 1.203/mysql-test/t/disabled.def 2006-09-18 16:56:04 +02:00
@@ -17,9 +17,6 @@ im_options : Bug#20294 20
#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
--- 1.1/mysql-test/r/index_merge_innodb.result 2006-09-18 16:56:04 +02:00
+++ 1.2/mysql-test/r/index_merge_innodb.result 2006-09-18 16:56:04 +02:00
@@ -284,6 +284,46 @@ kp1='279' AND kp2='ELM0678' AND kp3='6'
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;
--- 1.3/mysql-test/r/index_merge_myisam.result 2006-09-18 16:56:04 +02:00
+++ 1.4/mysql-test/r/index_merge_myisam.result 2006-09-18 16:56:04 +02:00
@@ -982,6 +982,46 @@ kp1='279' AND kp2='ELM0678' AND kp3='6'
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;
--- 1.1/mysql-test/t/index_merge_innodb.test 2006-09-18 16:56:04 +02:00
+++ 1.2/mysql-test/t/index_merge_innodb.test 2006-09-18 16:56:04 +02:00
@@ -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
--- 1.2/mysql-test/t/innodb_mysql.test 2006-09-18 16:56:04 +02:00
+++ 1.3/mysql-test/t/innodb_mysql.test 2006-09-18 16:56:04 +02:00
@@ -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
| Thread |
|---|
| • bk commit into 5.1 tree (mleich:1.2322) BUG#22398 | mleich | 18 Sep |
