# # Only run the test if we are using --big-test, because this test takes a # long time # #-- 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)); create table t2(id2 int not null, t char(12)); create table t3(id3 int not null, t char(12), index(id3)); disable_query_log; let $1 = 100; while ($1) { let $2 = 5; eval insert into t1(t) values ('$1'); while ($2) { eval insert into t2(id2,t) values ($1,'$2'); let $3 = 10; while ($3) { eval insert into t3(id3,t) values ($1,'$2'); dec $3; } dec $2; } dec $1; } enable_query_log; select count(*) from t1 where id1 > 95; select count(*) from t2 where id2 > 95; select count(*) from t3 where id3 > 95; update t1,t2,t3 set t1.t="aaa", t2.t="bbb", t3.t="cc" where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 90; select count(*) from t1 where t = "aaa"; select count(*) from t1 where id1 > 90; select count(*) from t2 where t = "bbb"; select count(*) from t2 where id2 > 90; select count(*) from t3 where t = "cc"; select count(*) from t3 where id3 > 90; delete t1.*, t2.*, t3.* from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 95; check table t1, t2, t3; select count(*) from t1 where id1 > 95; select count(*) from t2 where id2 > 95; select count(*) from t3 where id3 > 95; delete t1, t2, t3 from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 5; select count(*) from t1 where id1 > 5; select count(*) from t2 where id2 > 5; select count(*) from t3 where id3 > 5; delete from t1, t2, t3 using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 0; # These queries will force a scan of the table select count(*) from t1 where id1; select count(*) from t2 where id2; select count(*) from t3 where id3; drop table t1,t2,t3; create table t1(id1 int not null primary key, t varchar(100)) pack_keys = 1; create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1; disable_query_log; let $1 = 1000; while ($1) { let $2 = 5; eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa'); while ($2) { eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb'); dec $2; } dec $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 ( 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 ( 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 ( 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 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` ( `id` int(11) NOT NULL auto_increment, `tst` text, `tst1` text, PRIMARY KEY (`id`) ) TYPE=MyISAM; CREATE TABLE IF NOT EXISTS `child` ( `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`) ) TYPE=MyISAM; INSERT INTO parent(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE"); INSERT INTO child(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 child; 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); select * from t2 left outer join t1 using (n); delete t1,t2 from t2 left outer join t1 using (n); select * from t2 left outer join t1 using (n); drop table t1,t2 ; # # Test with locking # 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); insert into t2 values(1,10),(2,20); LOCK TABLES t1 write, t2 read; --error 1099 DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n; --error 1099 UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; # The following should be fixed to not give an error --error 1099 UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; unlock tables; LOCK TABLES t1 write, t2 write; UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; select * from t1; DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n; select * from t1; select * from t2; unlock tables; drop table t1,t2;