2
# test of updating of keys
6
drop table if exists t1,t2;
9
create table t1 (a int auto_increment , primary key (a));
10
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
11
update t1 set a=a+10 where a > 34;
12
update t1 set a=a+100 where a > 0;
14
# Some strange updates to test some otherwise unused code
15
update t1 set a=a+100 where a=1 and a=2;
17
update t1 set a=b+100 where a=1 and a=2;
19
update t1 set a=b+100 where c=1 and a=2;
21
update t1 set d=a+100 where a=1;
27
place_id int NOT NULL,
28
shows int DEFAULT '0' NOT NULL,
29
ishows int DEFAULT '0' NOT NULL,
30
ushows int DEFAULT '0' NOT NULL,
31
clicks int DEFAULT '0' NOT NULL,
32
iclicks int DEFAULT '0' NOT NULL,
33
uclicks int DEFAULT '0' NOT NULL,
35
PRIMARY KEY (place_id,ts)
38
INSERT INTO t1 (place_id,shows,ishows,ushows,clicks,iclicks,uclicks,ts)
39
VALUES (1,0,0,0,0,0,0,20000928174434);
40
UPDATE t1 SET shows=shows+1,ishows=ishows+1,ushows=ushows+1,clicks=clicks+1,iclicks=iclicks+1,uclicks=uclicks+1 WHERE place_id=1 AND ts>="2000-09-28 00:00:00";
41
select place_id,shows from t1;
45
# Test bug with update reported by Jan Legenhausen
49
lfdnr int NOT NULL default '0',
50
ticket int NOT NULL default '0',
51
client varchar(255) NOT NULL default '',
52
replyto varchar(255) NOT NULL default '',
53
subject varchar(100) NOT NULL default '',
54
timestamp int NOT NULL default '0',
55
tstamp timestamp NOT NULL,
56
status int NOT NULL default '0',
57
type varchar(15) NOT NULL default '',
58
assignment int NOT NULL default '0',
59
fupcount int NOT NULL default '0',
60
parent int NOT NULL default '0',
61
activity int NOT NULL default '0',
62
priority int NOT NULL default '1',
63
cc varchar(255) NOT NULL default '',
64
bcc varchar(255) NOT NULL default '',
76
INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','','');
78
alter table t1 change lfdnr lfdnr int not null auto_increment;
79
update t1 set status=1 where type='Open';
80
select status from t1;
87
create table t1 (a int not null, b int not null, key (a));
88
insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
90
update t1 set b=(@tmp:=@tmp+1) order by a;
91
update t1 set b=99 where a=1 order by b asc limit 1;
92
select * from t1 order by a,b;
93
update t1 set b=100 where a=1 order by b desc limit 2;
94
update t1 set a=a+10+b where a=1 order by b;
95
select * from t1 order by a,b;
96
create table t2 (a int not null, b int not null);
97
insert into t2 values (1,1),(1,2),(1,3);
98
update t1 set b=(select distinct 1 from (select * from t2) a);
102
# Test with limit (Bug #393)
106
`id_param` int NOT NULL default '0',
107
`nom_option` char(40) NOT NULL default '',
108
`valid` int NOT NULL default '0',
109
KEY `id_param` (`id_param`,`nom_option`)
112
INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1);
114
UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1;
119
# Multi table update test from bugs
122
create table t1 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, KEY groupid_index (groupid));
124
insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6),
125
('0','1','2',1,5), ('0','2','0',1,3), ('1','0','1',1,2),
126
('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4),
128
delete from m1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3);
135
create table t1 (c1 int, c2 char(6), c3 int);
136
create table t2 (c1 int, c2 char(6));
137
insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
138
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
139
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
145
create table t1 (id int not null auto_increment primary key, id_str varchar(32));
146
insert into t1 (id_str) values ("test");
147
update t1 set id_str = concat(id_str, id) where id = last_insert_id();
152
# Bug #8942: a problem with update and partial key part
155
create table t1 (a int, b char(255), key(a, b(20)));
156
insert into t1 values (0, '1');
157
update t1 set b = b + 1 where a = 0;
161
# BUG#9103 "Erroneous data truncation warnings on multi-table updates"
162
create table t1 (a int, b varchar(10), key b(b(5))) engine=myisam;
163
create table t2 (a int, b varchar(10)) engine=myisam;
164
insert into t1 values ( 1, 'abcd1e');
165
insert into t1 values ( 2, 'abcd2e');
166
insert into t2 values ( 1, 'abcd1e');
167
insert into t2 values ( 2, 'abcd2e');
169
update t1, t2 set t1.a = t2.a where t2.b = t1.b;
174
# Bug #11868 Update with subquery with ref built with a key from the updated
175
# table crashes server
177
create table t1(f1 int, f2 int);
178
create table t2(f3 int, f4 int);
179
create index idx on t2(f3);
180
insert into t1 values(1,0),(2,0);
181
insert into t2 values(1,1),(2,2);
182
UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1);
187
# Bug #13180 sometimes server accepts sum func in update/delete where condition
189
create table t1(f1 int);
192
update t1 set f1=1 where count(*)=1;
195
delete from t1 where count(*)=1;
198
# BUG#12915: Optimize "DELETE|UPDATE ... ORDER BY ... LIMIT n" to use an index
199
create table t1 ( a int, b int default 0, index (a) );
200
insert into t1 (a) values (0),(0),(0),(0),(0),(0),(0),(0);
203
select a from t1 order by a limit 1;
204
show status like 'handler_read%';
207
update t1 set a=9999 order by a limit 1;
208
update t1 set b=9999 order by a limit 1;
209
show status like 'handler_read%';
212
delete from t1 order by a limit 1;
213
show status like 'handler_read%';
216
delete from t1 order by a desc limit 1;
217
show status like 'handler_read%';
219
alter table t1 disable keys;
222
delete from t1 order by a limit 1;
223
show status like 'handler_read%';
225
# PBXT: this select returns a different result to
226
# innodb because the 2 updates above change different rows
228
update t1 set a=a+10,b=1 order by a limit 3;
229
update t1 set a=a+11,b=2 order by a limit 3;
230
update t1 set a=a+12,b=3 order by a limit 3;
231
select * from t1 order by a;
236
# Bug#14186 select datefield is null not updated
238
create table t1 (f1 date not null);
239
insert into t1 values('2000-01-01'),('0000-00-00');
240
update t1 set f1='2002-02-02' where f1 is null;
245
# Bug#15028 Multitable update returns different numbers of matched rows
246
# depending on table order
247
create table t1 (f1 int);
248
create table t2 (f2 int);
249
insert into t1 values(1),(2);
250
insert into t2 values(1),(1);
252
update t1,t2 set f1=3,f2=3 where f1=f2 and f1=1;
255
update t1 set f1=1 where f1=3;
257
update t2,t1 set f1=3,f2=3 where f1=f2 and f1=1;
263
create table t1 (a int);
264
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
265
create table t2 (a int, filler1 char(200), filler2 char(200), key(a));
266
insert into t2 select A.a + 10*B.a, 'filler','filler' from t1 A, t1 B;
268
update t2 set a=3 where a=2;
269
show status like 'handler_read%';
273
# Bug #16510 Updating field named like '*name' caused server crash
275
create table t1(f1 int, `*f2` int);
276
insert into t1 values (1,1);
277
update t1 set `*f2`=1;
281
# Bug#25126: Wrongly resolved field leads to a crash
283
create table t1(f1 int);
285
update t1 set f2=1 order by f2;
290
# Bug #24035: performance degradation with condition int_field=big_decimal
294
request_id int NOT NULL auto_increment,
295
user_id varchar(12) default NULL,
296
time_stamp datetime NOT NULL default '0000-00-00 00:00:00',
297
ip_address varchar(15) default NULL,
298
PRIMARY KEY (request_id),
299
KEY user_id_2 (user_id,time_stamp)
302
INSERT INTO t1 (user_id) VALUES ('user1');
303
INSERT INTO t1(user_id) SELECT user_id FROM t1;
304
INSERT INTO t1(user_id) SELECT user_id FROM t1;
305
INSERT INTO t1(user_id) SELECT user_id FROM t1;
306
INSERT INTO t1(user_id) SELECT user_id FROM t1;
307
INSERT INTO t1(user_id) SELECT user_id FROM t1;
308
INSERT INTO t1(user_id) SELECT user_id FROM t1;
309
INSERT INTO t1(user_id) SELECT user_id FROM t1;
310
INSERT INTO t1(user_id) SELECT user_id FROM t1;
313
SELECT user_id FROM t1 WHERE request_id=9999999999999;
314
show status like '%Handler_read%';
315
SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999;
316
show status like '%Handler_read%';
317
UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
318
show status like '%Handler_read%';
319
UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
320
show status like '%Handler_read%';
325
# Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it
331
quux decimal( 31, 30 ),
344
SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1;
351
# Bug #22364: Inconsistent "matched rows" when executing UPDATE
354
connect (con1,localhost,root,,test);
357
set tmp_table_size=1024;
359
# Create the test tables
360
create table t1 (id int, a int, key idx(a));
361
create table t2 (id int not null auto_increment primary key, a int);
362
insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
363
insert into t2(a) select a from t2;
364
insert into t2(a) select a from t2;
365
insert into t2(a) select a from t2;
367
insert into t1 select * from t2;
369
# PBXT: Rows changed are different here between InnoDB and PBXT
370
# because PBXT does not update the rows that are not modified!
371
# InnoDB seems to do this....
372
# Check that the number of matched rows is correct when the temporary
373
# table is small enough to not be converted to MyISAM
374
select count(*) from t1 join t2 on (t1.a=t2.a);
376
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
379
# Increase table sizes
380
insert into t2(a) select a from t2;
383
insert into t1 select * from t2;
385
# Check that the number of matched rows is correct when the temporary
386
# table has to be converted to MyISAM
387
select count(*) from t1 join t2 on (t1.a=t2.a);
389
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
392
# Check that the number of matched rows is correct when there are duplicate
396
select count(*) from t1 join t2 on (t1.a=t2.a);
398
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
406
--echo End of 5.0 tests