2
# Test of update statement that uses many tables.
5
# Requires grants, so won't work with embedded server test
6
source include/not_embedded.inc;
7
source include/have_log_bin.inc;
10
drop table if exists t1,t2,t3;
11
drop database if exists mysqltest;
12
drop view if exists v1;
14
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
16
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
17
delete from mysql.user where user=_binary'mysqltest_1';
20
create table t1(id1 int not null auto_increment primary key, t char(12));
21
create table t2(id2 int not null, t char(12));
22
create table t3(id3 int not null, t char(12), index(id3));
28
eval insert into t1(t) values ('$1');
31
eval insert into t2(id2,t) values ($1,'$2');
35
eval insert into t3(id3,t) values ($1,'$2');
44
select count(*) from t1 where id1 > 95;
45
select count(*) from t2 where id2 > 95;
46
select count(*) from t3 where id3 > 95;
48
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;
49
select count(*) from t1 where t = "aaa";
50
select count(*) from t1 where id1 > 90;
51
select count(*) from t2 where t = "bbb";
52
select count(*) from t2 where id2 > 90;
53
select count(*) from t3 where t = "cc";
54
select count(*) from t3 where id3 > 90;
55
delete t1.*, t2.*, t3.* from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 95;
57
check table t1, t2, t3;
59
select count(*) from t1 where id1 > 95;
60
select count(*) from t2 where id2 > 95;
61
select count(*) from t3 where id3 > 95;
63
delete t1, t2, t3 from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 5;
64
select count(*) from t1 where id1 > 5;
65
select count(*) from t2 where id2 > 5;
66
select count(*) from t3 where id3 > 5;
68
delete from t1, t2, t3 using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 0;
70
# These queries will force a scan of the table
71
select count(*) from t1 where id1;
72
select count(*) from t2 where id2;
73
select count(*) from t3 where id3;
76
create table t1(id1 int not null primary key, t varchar(100)) pack_keys = 1;
77
create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1;
83
eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa');
86
eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb');
92
delete t1 from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500;
96
id int(11) NOT NULL default '0',
97
name varchar(10) default NULL,
100
INSERT INTO t1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
102
id int(11) NOT NULL default '0',
103
name varchar(10) default NULL,
106
INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
108
id int(11) NOT NULL default '0',
109
mydate datetime default NULL,
112
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
113
00:00:00'),(7,'2002-07-22 00:00:00');
114
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;
118
CREATE TABLE IF NOT EXISTS `t1` (
119
`id` int(11) NOT NULL auto_increment,
125
CREATE TABLE IF NOT EXISTS `t2` (
126
`ID` int(11) NOT NULL auto_increment,
127
`ParId` int(11) default NULL,
131
KEY `IX_ParId_t2` (`ParId`),
132
FOREIGN KEY (`ParId`) REFERENCES `t1` (`id`)
135
INSERT INTO t1(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");
137
INSERT INTO t2(ParId) VALUES(1), (2), (3);
141
UPDATE t2, t1 SET t2.tst = t1.tst, t2.tst1 = t1.tst1 WHERE t2.ParId = t1.Id;
146
create table t1 (n numeric(10));
147
create table t2 (n numeric(10));
148
insert into t2 values (1),(2),(4),(8),(16),(32);
149
select * from t2 left outer join t1 using (n);
150
delete t1,t2 from t2 left outer join t1 using (n);
151
select * from t2 left outer join t1 using (n);
158
create table t1 (n int(10) not null primary key, d int(10));
159
create table t2 (n int(10) not null primary key, d int(10));
160
insert into t1 values(1,1);
161
insert into t2 values(1,10),(2,20);
162
LOCK TABLES t1 write, t2 read;
164
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
166
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
167
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
169
LOCK TABLES t1 write, t2 write;
170
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
172
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
179
# Test safe updates and timestamps
181
set sql_safe_updates=1;
182
create table t1 (n int(10), d int(10));
183
create table t2 (n int(10), d int(10));
184
insert into t1 values(1,1);
185
insert into t2 values(1,10),(2,20);
187
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
188
set sql_safe_updates=0;
190
set timestamp=1038401397;
191
create table t1 (n int(10) not null primary key, d int(10), t timestamp);
192
create table t2 (n int(10) not null primary key, d int(10), t timestamp);
193
insert into t1 values(1,1,NULL);
194
insert into t2 values(1,10,NULL),(2,20,NULL);
195
set timestamp=1038000000;
196
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
197
select n,d,unix_timestamp(t) from t1;
198
select n,d,unix_timestamp(t) from t2;
200
UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n;
203
set sql_safe_updates=0;
204
create table t1 (n int(10) not null primary key, d int(10));
205
create table t2 (n int(10) not null primary key, d int(10));
206
insert into t1 values(1,1), (3,3);
207
insert into t2 values(1,10),(2,20);
208
UPDATE t2 left outer join t1 on t1.n=t2.n SET t1.d=t2.d;
212
create table t1 (n int(10), d int(10));
213
create table t2 (n int(10), d int(10));
214
insert into t1 values(1,1),(1,2);
215
insert into t2 values(1,10),(2,20);
216
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
220
create table t1 (n int(10), d int(10));
221
create table t2 (n int(10), d int(10));
222
insert into t1 values(1,1),(3,2);
223
insert into t2 values(1,10),(1,20);
224
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
227
UPDATE t1 a ,t2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n;
230
DELETE a, b FROM t1 a,t2 b where a.n=b.n;
235
CREATE TABLE t1 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;
236
INSERT INTO t1 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a'),(10,''),(11,''),(12,''),(13,'');
237
CREATE TABLE t2 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;
238
INSERT INTO t2 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a');
239
CREATE TABLE t3 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;
240
INSERT INTO t3 VALUES (1,'jedan'),(2,'dva');
241
update t1,t2 set t1.naziv="aaaa" where t1.broj=t2.broj;
242
update t1,t2,t3 set t1.naziv="bbbb", t2.naziv="aaaa" where t1.broj=t2.broj and t2.broj=t3.broj;
246
# Test multi update with different join methods
249
CREATE TABLE t1 (a int not null primary key, b int not null, key (b));
250
CREATE TABLE t2 (a int not null primary key, b int not null, key (b));
251
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
252
INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
254
# Full join, without key
255
update t1,t2 set t1.a=t1.a+100;
259
update t1,t2 set t1.a=t1.a+100 where t1.a=101;
263
update t1,t2 set t1.b=t1.b+10 where t1.b=2;
267
update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t2.a=t1.a-100;
271
# test for non-updating table which is also used in sub-select
273
update t1,t2 set t1.b=t2.b, t1.a=t2.a where t1.a=t2.a and not exists (select * from t2 where t2.a > 10);
276
CREATE TABLE t3 ( KEY1 varchar(50) NOT NULL default '', PARAM_CORR_DISTANCE_RUSH double default NULL, PARAM_CORR_DISTANCE_GEM double default NULL, PARAM_AVG_TARE double default NULL, PARAM_AVG_NB_DAYS double default NULL, PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL, PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL, PARAM_SCENARIO_COSTS varchar(50) default NULL, PARAM_DEFAULT_WAGON_COST double default NULL, tmp int(11) default NULL, PRIMARY KEY (KEY1)) ENGINE=MyISAM;
277
INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL);
278
create table t1 (A varchar(1));
279
insert into t1 values ("A") ,("B"),("C"),("D");
280
create table t2(Z varchar(15));
281
insert into t2(Z) select concat(a.a,b.a,c.a,d.a) from t1 as a, t1 as b, t1 as c, t1 as d;
282
update t2,t3 set Z =param_scenario_costs;
284
create table t1 (a int, b int);
285
create table t2 (a int, b int);
286
insert into t1 values (1,1),(2,1),(3,1);
287
insert into t2 values (1,1), (3,1);
288
update t1 left join t2 on t1.a=t2.a set t1.b=2, t2.b=2 where t1.b=1 and t2.b=1 or t2.a is NULL;
289
select t1.a, t1.b,t2.a, t2.b from t1 left join t2 on t1.a=t2.a where t1.b=1 and t2.b=1 or t2.a is NULL;
293
# Test reuse of same table
296
create table t1 (a int not null auto_increment primary key, b int not null);
297
insert into t1 (b) values (1),(2),(3),(4);
298
update t1, t1 as t2 set t1.b=t2.b+1 where t1.a=t2.a;
302
# Test multi-update and multi-delete with impossible where
304
create table t1(id1 smallint(5), field char(5));
305
create table t2(id2 smallint(5), field char(5));
307
insert into t1 values (1, 'a'), (2, 'aa');
308
insert into t2 values (1, 'b'), (2, 'bb');
313
update t2 inner join t1 on t1.id1=t2.id2
314
set t2.field=t1.field
316
update t2, t1 set t2.field=t1.field
317
where t1.id1=t2.id2 and 0=1;
319
delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2
321
delete t1, t2 from t2,t1
322
where t1.id1=t2.id2 and 0=1;
327
# Test for bug #1820.
330
create table t1 ( a int not null, b int not null) ;
332
insert into t1 values (1,1),(2,2),(3,3),(4,4);
337
eval insert into t1 select a+@d,b+@d from t1;
343
alter table t1 add index i1(a);
344
delete from t1 where a > 2000000;
345
create table t2 like t1;
346
insert into t2 select * from t1;
348
select 't2 rows before small delete', count(*) from t1;
349
delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 2;
350
select 't2 rows after small delete', count(*) from t2;
351
select 't1 rows after small delete', count(*) from t1;
353
## Try deleting many rows
355
delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 100*1000;
356
select 't2 rows after big delete', count(*) from t2;
357
select 't1 rows after big delete', count(*) from t1;
362
# Test alias (this is not correct in 4.0)
365
CREATE TABLE t1 ( a int );
366
CREATE TABLE t2 ( a int );
367
DELETE t1 FROM t1, t2 AS t3;
368
DELETE t4 FROM t1, t1 AS t4;
369
DELETE t3 FROM t1 AS t3, t1 AS t4;
371
DELETE t1 FROM t1 AS t3, t2 AS t4;
372
INSERT INTO t1 values (1),(2);
373
INSERT INTO t2 values (1),(2);
374
DELETE t1 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=1;
377
DELETE t2 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=2;
383
# Test update with const tables
385
create table `t1` (`p_id` int(10) unsigned NOT NULL auto_increment, `p_code` varchar(20) NOT NULL default '', `p_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`p_id`) );
386
create table `t2` (`c2_id` int(10) unsigned NULL auto_increment, `c2_p_id` int(10) unsigned NOT NULL default '0', `c2_note` text NOT NULL, `c2_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`c2_id`), KEY `c2_p_id` (`c2_p_id`) );
387
insert into t1 values (0,'A01-Comp',1);
388
insert into t1 values (0,'B01-Comp',1);
389
insert into t2 values (0,1,'A Note',1);
390
update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2;
396
# privilege check for multiupdate with other tables
399
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
402
create database mysqltest;
404
create table mysqltest.t1 (a int, b int, primary key (a));
405
create table mysqltest.t2 (a int, b int, primary key (a));
406
create table mysqltest.t3 (a int, b int, primary key (a));
407
grant select on mysqltest.* to mysqltest_1@localhost;
408
grant update on mysqltest.t1 to mysqltest_1@localhost;
409
connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
411
update t1, t2 set t1.b=1 where t1.a=t2.a;
412
update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a;
414
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
415
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
416
delete from mysql.user where user=_binary'mysqltest_1';
417
drop database mysqltest;
420
# multi delete wrong table check
422
create table t1 (a int, primary key (a));
423
create table t2 (a int, primary key (a));
424
create table t3 (a int, primary key (a));
426
delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a=t3.a);
427
drop table t1, t2, t3;
430
# multi* unique updating table check
432
create table t1 (col1 int);
433
create table t2 (col1 int);
435
update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
437
delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1;
440
# Test for BUG#5837 - delete with outer join and const tables
443
aclid bigint not null primary key,
444
status tinyint(1) not null
448
refid bigint not null primary key,
449
aclid bigint, index idx_acl(aclid)
452
insert into t2 values(1,null);
453
delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1';
457
# Bug#19225: unchecked error leads to server crash
459
create table t1(a int);
460
create table t2(a int);
462
delete from t1,t2 using t1,t2 where t1.a=(select a from t1);
467
# Test for bug #1980.
470
create table t1 ( c char(8) not null ) engine=innodb;
473
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
474
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
476
alter table t1 add b char(8) not null;
477
alter table t1 add a char(8) not null;
478
alter table t1 add primary key (a,b,c);
479
update t1 set a=c, b=c;
481
create table t2 like t1;
482
insert into t2 select * from t1;
484
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
489
create table t1 ( c char(8) not null ) engine=innodb;
492
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
493
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
495
alter table t1 add b char(8) not null;
496
alter table t1 add a char(8) not null;
497
alter table t1 add primary key (a,b,c);
498
update t1 set a=c, b=c;
500
create table t2 like t1;
501
insert into t2 select * from t1;
503
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
508
# Test alter table and a concurrent multi update
509
# (This will force update to reopen tables)
512
create table t1 (a int, b int);
513
insert into t1 values (1, 2), (2, 3), (3, 4);
514
create table t2 (a int);
515
insert into t2 values (10), (20), (30);
516
create view v1 as select a as b, a/10 as a from t2;
518
connect (locker,localhost,root,,test);
522
connect (changer,localhost,root,,test);
524
send alter table t1 add column c int default 100 after a;
526
connect (updater,localhost,root,,test);
529
send update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a;
546
# Test multi updates and deletes using primary key and without.
548
create table t1 (i1 int, i2 int, i3 int);
549
create table t2 (id int, c1 varchar(20), c2 varchar(20));
550
insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
551
insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
552
select * from t1 order by i1;
554
update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
555
select * from t1 order by i1;
556
select * from t2 order by id;
557
delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;
558
select * from t1 order by i1;
559
select * from t2 order by id;
561
create table t1 (i1 int auto_increment not null, i2 int, i3 int, primary key (i1));
562
create table t2 (id int auto_increment not null, c1 varchar(20), c2 varchar(20), primary key(id));
563
insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
564
insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
565
select * from t1 order by i1;
566
select * from t2 order by id;
567
update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
568
select * from t1 order by i1;
569
select * from t2 order by id;
570
delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;
571
select * from t1 order by i1;
572
select * from t2 order by id;
576
# Bug#27716 multi-update did partially and has not binlogged
579
`a` int(11) NOT NULL auto_increment,
580
`b` int(11) default NULL,
582
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
585
`a` int(11) NOT NULL auto_increment,
586
`b` int(11) default NULL,
588
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
590
# as the test is about to see erroed queries in binlog
591
set @sav_binlog_format= @@session.binlog_format;
592
set @@session.binlog_format= mixed;
595
# A. testing multi_update::send_error() effective update
596
insert into t1 values (1,1),(2,2);
597
insert into t2 values (1,1),(4,4);
600
UPDATE t2,t1 SET t2.a=t1.a+2;
602
select * from t2 /* must be (3,1), (4,4) */;
603
show master status /* there must be the UPDATE query event */;
605
# B. testing multi_update::send_error() ineffective update
606
# (as there is a policy described at mysql_update() still go to binlog)
609
insert into t1 values (1,2),(3,4),(4,4);
610
insert into t2 values (1,2),(3,4),(4,4);
613
UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a;
614
show master status /* there must be the UPDATE query event */;
618
set @@session.binlog_format= @sav_binlog_format;
621
# Bug #29136 erred multi-delete on trans table does not rollback
626
drop table if exists t1, t2, t3;
628
CREATE TABLE t1 (a int, PRIMARY KEY (a));
629
CREATE TABLE t2 (a int, PRIMARY KEY (a));
630
CREATE TABLE t3 (a int, PRIMARY KEY (a)) ENGINE=MyISAM;
631
create trigger trg_del_t3 before delete on t3 for each row insert into t1 values (1);
633
insert into t2 values (1),(2);
634
insert into t3 values (1),(2);
637
# exec cases B, A - see innodb.test
639
# B. send_eof() and send_error() afterward
642
delete t3.* from t2,t3 where t2.a=t3.a;
645
select count(*) from t1 /* must be 1 */;
646
select count(*) from t3 /* must be 1 */;
649
drop table t1, t2, t3;
652
# Add further tests from here
added
removed
mysql-test/t/multi_update.test
