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
9
drop table if exists t1,t2,t3;
10
drop database if exists mysqltest;
11
drop view if exists v1;
13
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
15
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
16
delete from mysql.user where user=_binary'mysqltest_1';
19
create table t1(id1 int not null auto_increment primary key, t char(12));
20
create table t2(id2 int not null, t char(12));
21
create table t3(id3 int not null, t char(12), index(id3));
27
eval insert into t1(t) values ('$1');
30
eval insert into t2(id2,t) values ($1,'$2');
34
eval insert into t3(id3,t) values ($1,'$2');
43
select count(*) from t1 where id1 > 95;
44
select count(*) from t2 where id2 > 95;
45
select count(*) from t3 where id3 > 95;
47
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;
48
select count(*) from t1 where t = "aaa";
49
select count(*) from t1 where id1 > 90;
50
select count(*) from t2 where t = "bbb";
51
select count(*) from t2 where id2 > 90;
52
select count(*) from t3 where t = "cc";
53
select count(*) from t3 where id3 > 90;
54
delete t1.*, t2.*, t3.* from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 95;
56
check table t1, t2, t3;
58
select count(*) from t1 where id1 > 95;
59
select count(*) from t2 where id2 > 95;
60
select count(*) from t3 where id3 > 95;
62
delete t1, t2, t3 from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 5;
63
select count(*) from t1 where id1 > 5;
64
select count(*) from t2 where id2 > 5;
65
select count(*) from t3 where id3 > 5;
67
delete from t1, t2, t3 using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 0;
69
# These queries will force a scan of the table
70
select count(*) from t1 where id1;
71
select count(*) from t2 where id2;
72
select count(*) from t3 where id3;
75
create table t1(id1 int not null primary key, t varchar(100)) pack_keys = 1;
76
create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1;
82
eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa');
85
eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb');
91
delete t1 from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500;
95
id int(11) NOT NULL default '0',
96
name varchar(10) default NULL,
99
INSERT INTO t1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
101
id int(11) NOT NULL default '0',
102
name varchar(10) default NULL,
105
INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
107
id int(11) NOT NULL default '0',
108
mydate datetime default NULL,
111
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
112
00:00:00'),(7,'2002-07-22 00:00:00');
113
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;
117
CREATE TABLE IF NOT EXISTS `t1` (
118
`id` int(11) NOT NULL auto_increment,
124
CREATE TABLE IF NOT EXISTS `t2` (
125
`ID` int(11) NOT NULL auto_increment,
126
`ParId` int(11) default NULL,
130
KEY `IX_ParId_t2` (`ParId`),
131
FOREIGN KEY (`ParId`) REFERENCES `t1` (`id`)
134
INSERT INTO t1(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");
136
INSERT INTO t2(ParId) VALUES(1), (2), (3);
140
UPDATE t2, t1 SET t2.tst = t1.tst, t2.tst1 = t1.tst1 WHERE t2.ParId = t1.Id;
145
create table t1 (n numeric(10));
146
create table t2 (n numeric(10));
147
insert into t2 values (1),(2),(4),(8),(16),(32);
148
select * from t2 left outer join t1 using (n);
149
delete t1,t2 from t2 left outer join t1 using (n);
150
select * from t2 left outer join t1 using (n);
157
create table t1 (n int(10) not null primary key, d int(10));
158
create table t2 (n int(10) not null primary key, d int(10));
159
insert into t1 values(1,1);
160
insert into t2 values(1,10),(2,20);
161
LOCK TABLES t1 write, t2 read;
163
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
165
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
166
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
168
LOCK TABLES t1 write, t2 write;
169
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
171
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
178
# Test safe updates and timestamps
180
set sql_safe_updates=1;
181
create table t1 (n int(10), d int(10));
182
create table t2 (n int(10), d int(10));
183
insert into t1 values(1,1);
184
insert into t2 values(1,10),(2,20);
186
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
187
set sql_safe_updates=0;
189
set timestamp=1038401397;
190
create table t1 (n int(10) not null primary key, d int(10), t timestamp);
191
create table t2 (n int(10) not null primary key, d int(10), t timestamp);
192
insert into t1 values(1,1,NULL);
193
insert into t2 values(1,10,NULL),(2,20,NULL);
194
set timestamp=1038000000;
195
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
196
select n,d,unix_timestamp(t) from t1;
197
select n,d,unix_timestamp(t) from t2;
199
UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n;
202
set sql_safe_updates=0;
203
create table t1 (n int(10) not null primary key, d int(10));
204
create table t2 (n int(10) not null primary key, d int(10));
205
insert into t1 values(1,1), (3,3);
206
insert into t2 values(1,10),(2,20);
207
UPDATE t2 left outer join t1 on t1.n=t2.n SET t1.d=t2.d;
211
create table t1 (n int(10), d int(10));
212
create table t2 (n int(10), d int(10));
213
insert into t1 values(1,1),(1,2);
214
insert into t2 values(1,10),(2,20);
215
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
219
create table t1 (n int(10), d int(10));
220
create table t2 (n int(10), d int(10));
221
insert into t1 values(1,1),(3,2);
222
insert into t2 values(1,10),(1,20);
223
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
226
UPDATE t1 a ,t2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n;
229
DELETE a, b FROM t1 a,t2 b where a.n=b.n;
234
CREATE TABLE t1 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;
235
INSERT INTO t1 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a'),(10,''),(11,''),(12,''),(13,'');
236
CREATE TABLE t2 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;
237
INSERT INTO t2 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a');
238
CREATE TABLE t3 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;
239
INSERT INTO t3 VALUES (1,'jedan'),(2,'dva');
240
update t1,t2 set t1.naziv="aaaa" where t1.broj=t2.broj;
241
update t1,t2,t3 set t1.naziv="bbbb", t2.naziv="aaaa" where t1.broj=t2.broj and t2.broj=t3.broj;
245
# Test multi update with different join methods
248
CREATE TABLE t1 (a int not null primary key, b int not null, key (b));
249
CREATE TABLE t2 (a int not null primary key, b int not null, key (b));
250
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
251
INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
253
# Full join, without key
254
update t1,t2 set t1.a=t1.a+100;
258
update t1,t2 set t1.a=t1.a+100 where t1.a=101;
262
update t1,t2 set t1.b=t1.b+10 where t1.b=2;
266
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;
270
# test for non-updating table which is also used in sub-select
272
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);
275
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;
276
INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL);
277
create table t1 (A varchar(1));
278
insert into t1 values ("A") ,("B"),("C"),("D");
279
create table t2(Z varchar(15));
280
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;
281
update t2,t3 set Z =param_scenario_costs;
283
create table t1 (a int, b int);
284
create table t2 (a int, b int);
285
insert into t1 values (1,1),(2,1),(3,1);
286
insert into t2 values (1,1), (3,1);
287
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;
288
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;
292
# Test reuse of same table
295
create table t1 (a int not null auto_increment primary key, b int not null);
296
insert into t1 (b) values (1),(2),(3),(4);
297
update t1, t1 as t2 set t1.b=t2.b+1 where t1.a=t2.a;
301
# Test multi-update and multi-delete with impossible where
303
create table t1(id1 smallint(5), field char(5));
304
create table t2(id2 smallint(5), field char(5));
306
insert into t1 values (1, 'a'), (2, 'aa');
307
insert into t2 values (1, 'b'), (2, 'bb');
312
update t2 inner join t1 on t1.id1=t2.id2
313
set t2.field=t1.field
315
update t2, t1 set t2.field=t1.field
316
where t1.id1=t2.id2 and 0=1;
318
delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2
320
delete t1, t2 from t2,t1
321
where t1.id1=t2.id2 and 0=1;
326
# Test for bug #1820.
329
create table t1 ( a int not null, b int not null) ;
331
insert into t1 values (1,1),(2,2),(3,3),(4,4);
336
eval insert into t1 select a+@d,b+@d from t1;
342
alter table t1 add index i1(a);
343
delete from t1 where a > 2000000;
344
create table t2 like t1;
345
insert into t2 select * from t1;
347
select 't2 rows before small delete', count(*) from t1;
348
delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 2;
349
select 't2 rows after small delete', count(*) from t2;
350
select 't1 rows after small delete', count(*) from t1;
352
## Try deleting many rows
354
delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 100*1000;
355
select 't2 rows after big delete', count(*) from t2;
356
select 't1 rows after big delete', count(*) from t1;
361
# Test alias (this is not correct in 4.0)
364
CREATE TABLE t1 ( a int );
365
CREATE TABLE t2 ( a int );
366
DELETE t1 FROM t1, t2 AS t3;
367
DELETE t4 FROM t1, t1 AS t4;
368
DELETE t3 FROM t1 AS t3, t1 AS t4;
370
DELETE t1 FROM t1 AS t3, t2 AS t4;
371
INSERT INTO t1 values (1),(2);
372
INSERT INTO t2 values (1),(2);
373
DELETE t1 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=1;
376
DELETE t2 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=2;
382
# Test update with const tables
384
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`) );
385
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`) );
386
insert into t1 values (0,'A01-Comp',1);
387
insert into t1 values (0,'B01-Comp',1);
388
insert into t2 values (0,1,'A Note',1);
389
update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2;
395
# privilege check for multiupdate with other tables
398
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
401
create database mysqltest;
403
create table mysqltest.t1 (a int, b int, primary key (a));
404
create table mysqltest.t2 (a int, b int, primary key (a));
405
create table mysqltest.t3 (a int, b int, primary key (a));
406
grant select on mysqltest.* to mysqltest_1@localhost;
407
grant update on mysqltest.t1 to mysqltest_1@localhost;
408
connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
410
update t1, t2 set t1.b=1 where t1.a=t2.a;
411
update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a;
413
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
414
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
415
delete from mysql.user where user=_binary'mysqltest_1';
416
drop database mysqltest;
419
# multi delete wrong table check
421
create table t1 (a int, primary key (a));
422
create table t2 (a int, primary key (a));
423
create table t3 (a int, primary key (a));
425
delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a=t3.a);
426
drop table t1, t2, t3;
429
# multi* unique updating table check
431
create table t1 (col1 int);
432
create table t2 (col1 int);
434
update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
436
delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1;
439
# Test for BUG#5837 - delete with outer join and const tables
442
aclid bigint not null primary key,
443
status tinyint(1) not null
447
refid bigint not null primary key,
448
aclid bigint, index idx_acl(aclid)
451
insert into t2 values(1,null);
452
delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1';
456
# Bug#19225: unchecked error leads to server crash
458
create table t1(a int);
459
create table t2(a int);
461
delete from t1,t2 using t1,t2 where t1.a=(select a from t1);
466
# Test for bug #1980.
469
create table t1 ( c char(8) not null ) engine=innodb;
472
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
473
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
475
alter table t1 add b char(8) not null;
476
alter table t1 add a char(8) not null;
477
alter table t1 add primary key (a,b,c);
478
update t1 set a=c, b=c;
480
create table t2 like t1;
481
insert into t2 select * from t1;
483
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
488
create table t1 ( c char(8) not null ) engine=bdb;
491
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
492
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
494
alter table t1 add b char(8) not null;
495
alter table t1 add a char(8) not null;
496
alter table t1 add primary key (a,b,c);
497
update t1 set a=c, b=c;
499
create table t2 like t1;
500
insert into t2 select * from t1;
502
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
506
create table t1 (a int, b int);
507
insert into t1 values (1, 2), (2, 3), (3, 4);
508
create table t2 (a int);
509
insert into t2 values (10), (20), (30);
510
create view v1 as select a as b, a/10 as a from t2;
512
connect (locker,localhost,root,,test);
516
connect (changer,localhost,root,,test);
518
send alter table t1 add column c int default 100 after a;
520
connect (updater,localhost,root,,test);
522
send update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a;