diff options
Diffstat (limited to 'mysql-test/t/multi_update.test')
| -rw-r--r-- | mysql-test/t/multi_update.test | 106 |
1 files changed, 73 insertions, 33 deletions
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index b3a51ff65bc..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)); @@ -80,67 +77,59 @@ while ($1) enable_query_log; delete t1 from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500; drop table t1,t2; -DROP TABLE IF EXISTS a,b,c; -CREATE TABLE a ( + +CREATE TABLE t1 ( id int(11) NOT NULL default '0', name varchar(10) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM; -INSERT INTO a VALUES (1,'aaa'),(2,'aaa'),(3,'aaa'); -CREATE TABLE b ( +INSERT INTO t1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa'); +CREATE TABLE t2 ( id int(11) NOT NULL default '0', name varchar(10) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM; -INSERT INTO b VALUES (2,'bbb'),(3,'bbb'),(4,'bbb'); -CREATE TABLE c ( +INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb'); +CREATE TABLE t3 ( id int(11) NOT NULL default '0', mydate datetime default NULL, PRIMARY KEY (id) ) TYPE=MyISAM; -INSERT INTO c VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22 +INSERT INTO t3 VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22 00:00:00'),(7,'2002-07-22 00:00:00'); -delete a,b,c from a,b,c -where to_days(now())-to_days(c.mydate)>=30 -and c.id=a.id and c.id=b.id; -select * from c; -DROP TABLE IF EXISTS a,b,c; -drop table if exists parent, child; -CREATE TABLE IF NOT EXISTS `parent` ( +delete t1,t2,t3 from t1,t2,t3 where to_days(now())-to_days(t3.mydate)>=30 and t3.id=t1.id and t3.id=t2.id; +select * from t3; +DROP TABLE IF EXISTS t1,t2,t3; + +CREATE TABLE IF NOT EXISTS `t1` ( `id` int(11) NOT NULL auto_increment, `tst` text, `tst1` text, PRIMARY KEY (`id`) ) TYPE=MyISAM; -CREATE TABLE IF NOT EXISTS `child` ( +CREATE TABLE IF NOT EXISTS `t2` ( `ID` int(11) NOT NULL auto_increment, `ParId` int(11) default NULL, `tst` text, `tst1` text, PRIMARY KEY (`ID`), - KEY `IX_ParId_child` (`ParId`), - FOREIGN KEY (`ParId`) REFERENCES `test.parent` (`id`) + KEY `IX_ParId_t2` (`ParId`), + FOREIGN KEY (`ParId`) REFERENCES `t1` (`id`) ) TYPE=MyISAM; -INSERT INTO parent(tst,tst1) -VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE"); +INSERT INTO t1(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE"); -INSERT INTO child(ParId) -VALUES(1), (2), (3); +INSERT INTO t2(ParId) VALUES(1), (2), (3); -select * from child; - -UPDATE child, parent -SET child.tst = parent.tst, -child.tst1 = parent.tst1 -WHERE child.ParId = parent.Id; +select * from t2; -select * from child; +UPDATE t2, t1 SET t2.tst = t1.tst, t2.tst1 = t1.tst1 WHERE t2.ParId = t1.Id; +select * from t2; -drop table parent, child; drop table if exists t1, t2 ; + create table t1 (n numeric(10)); create table t2 (n numeric(10)); insert into t2 values (1),(2),(4),(8),(16),(32); @@ -174,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; |