Fixed some bugs from last multi-table-update push.

More tests for multi-table-update & timestamp handling

6 files changed, 192 insertions, 15 deletions

diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 67c78f34392..1e136acb21d 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1021,3 +1021,18 @@ id	code	name
 7	4	Matt
 COMMIT;
 DROP TABLE t1;
+drop table if exists t1,t2;
+create table t1 (n int(10), d int(10)) type=innodb;
+create table t2 (n int(10), d int(10)) type=innodb;
+insert into t1 values(1,1),(1,2);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+n	d
+1	10
+1	10
+select * from t2;
+n	d
+1	30
+2	20
+drop table t1,t2;
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index 20921816f1e..7d1f5bd53f6 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -166,3 +166,73 @@ n	d
 2	20
 unlock tables;
 drop table t1,t2;
+set sql_safe_updates=1;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
+You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
+set sql_safe_updates=0;
+drop table t1,t2;
+set timestamp=1038401397;
+create table t1 (n int(10) not null primary key, d int(10), t timestamp);
+create table t2 (n int(10) not null primary key, d int(10), t timestamp);
+insert into t1 values(1,1,NULL);
+insert into t2 values(1,10,NULL),(2,20,NULL);
+set timestamp=1038000000;
+UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
+select * from t1;
+n	d	t
+1	10	20021123002000
+select * from t2;
+n	d	t
+1	10	20021127154957
+2	20	20021127154957
+UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n;
+You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '1=2 WHERE t1.n=t2.n' at line 1
+drop table t1,t2;
+set timestamp=0;
+set sql_safe_updates=0;
+create table t1 (n int(10) not null primary key, d int(10));
+create table t2 (n int(10) not null primary key, d int(10));
+insert into t1 values(1,1), (3,3);
+insert into t2 values(1,10),(2,20);
+UPDATE t2 left outer join t1 on t1.n=t2.n  SET t1.d=t2.d;
+select * from t1;
+n	d
+1	10
+3	3
+select * from t2;
+n	d
+1	10
+2	20
+drop table t1,t2;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1),(1,2);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+n	d
+1	10
+1	10
+select * from t2;
+n	d
+1	30
+2	20
+drop table t1,t2;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1),(3,2);
+insert into t2 values(1,10),(1,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+n	d
+1	10
+3	2
+select * from t2;
+n	d
+1	30
+1	30
+drop table t1,t2;
diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result
index bd5e9f04992..088f3b205b9 100644
--- a/mysql-test/r/type_timestamp.result
+++ b/mysql-test/r/type_timestamp.result
@@ -1,11 +1,31 @@
-drop table if exists t1;
-CREATE TABLE t1 ( t timestamp);
+drop table if exists t1,t2;
+CREATE TABLE t1 (a int, t timestamp);
+CREATE TABLE t2 (a int, t datetime);
 SET TIMESTAMP=1234;
-insert into t1 values(NULL);
+insert into t1 values(1,NULL);
+insert into t1 values(2,"2002-03-03");
+SET TIMESTAMP=1235;
+insert into t1 values(3,NULL);
+SET TIMESTAMP=1236;
+insert into t1 (a) values(4);
+insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,"00-00-00");
+SET TIMESTAMP=1237;
+insert into t1 select * from t2;
+SET TIMESTAMP=1238;
+insert into t1 (a) select a+1 from t2 where a=8;
 select * from t1;
-t
-19700101032034
-drop table t1;
+a	t
+1	19700101032034
+2	20020303000000
+3	19700101032035
+4	19700101032036
+5	20020304000000
+6	19700101032037
+7	20020305000000
+8	00000000000000
+9	19700101032038
+drop table t1,t2;
+SET TIMESTAMP=1234;
 CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id));
 INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00");
 SELECT stamp FROM t1 WHERE id="myKey";
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index e6d57899082..8edde83507a 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -660,3 +660,16 @@ insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
 select id, code, name from t1 order by id;
 COMMIT;
 DROP TABLE t1;
+
+#
+# Test of multi-table-update
+#
+drop table if exists t1,t2;
+create table t1 (n int(10), d int(10)) type=innodb;
+create table t2 (n int(10), d int(10)) type=innodb;
+insert into t1 values(1,1),(1,2);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+select * from t2;
+drop table t1,t2;
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index ec1ef76753b..b79b0749c82 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -1,9 +1,6 @@
 #
-# Only run the test if we are using --big-test, because this test takes a
-# long time
+# Test of update statement that uses many tables.
 #
-#-- require r/big_test.require
-#eval select $BIG_TEST as using_big_test;
 
 drop table if exists t1,t2,t3;
 create table t1(id1 int not null auto_increment primary key, t char(12));
@@ -166,3 +163,54 @@ select * from t1;
 select * from t2;
 unlock tables;
 drop table t1,t2;
+
+#
+# Test safe updates and timestamps
+#
+set sql_safe_updates=1;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1);
+insert into t2 values(1,10),(2,20);
+--error 1175
+UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
+set sql_safe_updates=0;
+drop table t1,t2;
+set timestamp=1038401397;
+create table t1 (n int(10) not null primary key, d int(10), t timestamp);
+create table t2 (n int(10) not null primary key, d int(10), t timestamp);
+insert into t1 values(1,1,NULL);
+insert into t2 values(1,10,NULL),(2,20,NULL);
+set timestamp=1038000000;
+UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
+select * from t1;
+select * from t2;
+--error 1064
+UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n;
+drop table t1,t2;
+set timestamp=0;
+set sql_safe_updates=0;
+create table t1 (n int(10) not null primary key, d int(10));
+create table t2 (n int(10) not null primary key, d int(10));
+insert into t1 values(1,1), (3,3);
+insert into t2 values(1,10),(2,20);
+UPDATE t2 left outer join t1 on t1.n=t2.n  SET t1.d=t2.d;
+select * from t1;
+select * from t2;
+drop table t1,t2;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1),(1,2);
+insert into t2 values(1,10),(2,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+select * from t2;
+drop table t1,t2;
+create table t1 (n int(10), d int(10));
+create table t2 (n int(10), d int(10));
+insert into t1 values(1,1),(3,2);
+insert into t2 values(1,10),(1,20);
+UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+select * from t1;
+select * from t2;
+drop table t1,t2;
diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test
index 19af6b0c49c..2929184df93 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -2,14 +2,25 @@
 # Test timestamp
 #
 
-drop table if exists t1;
-CREATE TABLE t1 ( t timestamp);
+drop table if exists t1,t2;
+CREATE TABLE t1 (a int, t timestamp);
+CREATE TABLE t2 (a int, t datetime);
 SET TIMESTAMP=1234;
-insert into t1 values(NULL);
+insert into t1 values(1,NULL);
+insert into t1 values(2,"2002-03-03");
+SET TIMESTAMP=1235;
+insert into t1 values(3,NULL);
+SET TIMESTAMP=1236;
+insert into t1 (a) values(4);
+insert into t2 values(5,"2002-03-04"),(6,NULL),(7,"2002-03-05"),(8,"00-00-00");
+SET TIMESTAMP=1237;
+insert into t1 select * from t2;
+SET TIMESTAMP=1238;
+insert into t1 (a) select a+1 from t2 where a=8;
 select * from t1;
-drop table t1;
-
+drop table t1,t2;
 
+SET TIMESTAMP=1234;
 CREATE TABLE t1 (value TEXT NOT NULL, id VARCHAR(32) NOT NULL, stamp timestamp, PRIMARY KEY (id));
 INSERT INTO t1 VALUES ("my value", "myKey","1999-04-02 00:00:00");
 SELECT stamp FROM t1 WHERE id="myKey";