« back to all changes in this revision
Viewing changes to tests/r/pbxt/update.result
- browse files at revision 505
- compare with another revision
- compare with revision 759.1.4
- download diff
- download tarball
- view history from revision 505
- Committer: Brian Aker
- Date: 2008-10-11 18:46:03 UTC
- mfrom: (499.1.1 codestyle)
- Revision ID: brian@tangent.org-20081011184603-acnvl454409fzhvq
Merging Monty
- files added:
- tests/r/pbxt
- files modified:
- client/drizzletest.cc
added
removed
tests/r/pbxt/update.result
1
drop table if exists t1,t2;
2
create table t1 (a int auto_increment , primary key (a));
3
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);
4
update t1 set a=a+10 where a > 34;
5
update t1 set a=a+100 where a > 0;
6
update t1 set a=a+100 where a=1 and a=2;
7
update t1 set a=b+100 where a=1 and a=2;
8
ERROR 42S22: Unknown column 'b' in 'field list'
9
update t1 set a=b+100 where c=1 and a=2;
10
ERROR 42S22: Unknown column 'c' in 'where clause'
11
update t1 set d=a+100 where a=1;
12
ERROR 42S22: Unknown column 'd' in 'field list'
13
select * from t1;
14
a
15
101
16
102
17
103
18
104
19
105
20
106
21
107
22
108
23
109
24
110
25
111
26
112
27
113
28
114
29
115
30
116
31
117
32
118
33
119
34
120
35
121
36
122
37
123
38
124
39
125
40
126
41
127
42
128
43
129
44
130
45
131
46
132
47
133
48
134
49
145
50
146
51
drop table t1;
52
CREATE TABLE t1
53
(
54
place_id int NOT NULL,
55
shows int DEFAULT '0' NOT NULL,
56
ishows int DEFAULT '0' NOT NULL,
57
ushows int DEFAULT '0' NOT NULL,
58
clicks int DEFAULT '0' NOT NULL,
59
iclicks int DEFAULT '0' NOT NULL,
60
uclicks int DEFAULT '0' NOT NULL,
61
ts timestamp,
62
PRIMARY KEY (place_id,ts)
63
);
64
INSERT INTO t1 (place_id,shows,ishows,ushows,clicks,iclicks,uclicks,ts)
65
VALUES (1,0,0,0,0,0,0,20000928174434);
66
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";
67
select place_id,shows from t1;
68
place_id shows
69
1 1
70
drop table t1;
71
CREATE TABLE t1 (
72
lfdnr int NOT NULL default '0',
73
ticket int NOT NULL default '0',
74
client varchar(255) NOT NULL default '',
75
replyto varchar(255) NOT NULL default '',
76
subject varchar(100) NOT NULL default '',
77
timestamp int NOT NULL default '0',
78
tstamp timestamp NOT NULL,
79
status int NOT NULL default '0',
80
type varchar(15) NOT NULL default '',
81
assignment int NOT NULL default '0',
82
fupcount int NOT NULL default '0',
83
parent int NOT NULL default '0',
84
activity int NOT NULL default '0',
85
priority int NOT NULL default '1',
86
cc varchar(255) NOT NULL default '',
87
bcc varchar(255) NOT NULL default '',
88
body text NOT NULL,
89
comment text,
90
header text,
91
PRIMARY KEY (lfdnr),
92
KEY k1 (timestamp),
93
KEY k2 (type),
94
KEY k3 (parent),
95
KEY k4 (assignment),
96
KEY ticket (ticket)
97
) ENGINE=MyISAM;
98
INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','','');
99
alter table t1 change lfdnr lfdnr int not null auto_increment;
100
update t1 set status=1 where type='Open';
101
select status from t1;
102
status
103
1
104
drop table t1;
105
create table t1 (a int not null, b int not null, key (a));
106
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);
107
SET @tmp=0;
108
update t1 set b=(@tmp:=@tmp+1) order by a;
109
update t1 set b=99 where a=1 order by b asc limit 1;
110
select * from t1 order by a,b;
111
a b
112
1 2
113
1 3
114
1 99
115
2 4
116
2 5
117
2 6
118
3 7
119
3 8
120
3 9
121
3 10
122
3 11
123
3 12
124
update t1 set b=100 where a=1 order by b desc limit 2;
125
update t1 set a=a+10+b where a=1 order by b;
126
select * from t1 order by a,b;
127
a b
128
2 4
129
2 5
130
2 6
131
3 7
132
3 8
133
3 9
134
3 10
135
3 11
136
3 12
137
13 2
138
111 100
139
111 100
140
create table t2 (a int not null, b int not null);
141
insert into t2 values (1,1),(1,2),(1,3);
142
update t1 set b=(select distinct 1 from (select * from t2) a);
143
drop table t1,t2;
144
CREATE TABLE t1 (
145
`id_param` int NOT NULL default '0',
146
`nom_option` char(40) NOT NULL default '',
147
`valid` int NOT NULL default '0',
148
KEY `id_param` (`id_param`,`nom_option`)
149
) ENGINE=MyISAM;
150
INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1);
151
UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1;
152
select * from t1;
153
id_param nom_option valid
154
185 test 1
155
drop table t1;
156
create table t1 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, KEY groupid_index (groupid));
157
insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6),
158
('0','1','2',1,5), ('0','2','0',1,3), ('1','0','1',1,2),
159
('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4),
160
('2','2','0',1,7);
161
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);
162
select * from t1;
163
F1 F2 F3 cnt groupid
164
0 0 0 1 6
165
0 1 2 1 5
166
0 2 0 1 3
167
1 0 1 1 2
168
1 2 1 1 1
169
2 0 1 2 4
170
2 2 0 1 7
171
drop table t1;
172
create table t1 (c1 int, c2 char(6), c3 int);
173
create table t2 (c1 int, c2 char(6));
174
insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
175
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
176
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
177
drop table t1, t2;
178
create table t1 (id int not null auto_increment primary key, id_str varchar(32));
179
insert into t1 (id_str) values ("test");
180
update t1 set id_str = concat(id_str, id) where id = last_insert_id();
181
select * from t1;
182
id id_str
183
1 test1
184
drop table t1;
185
create table t1 (a int, b char(255), key(a, b(20)));
186
insert into t1 values (0, '1');
187
update t1 set b = b + 1 where a = 0;
188
select * from t1;
189
a b
190
0 2
191
drop table t1;
192
create table t1 (a int, b varchar(10), key b(b(5))) engine=myisam;
193
create table t2 (a int, b varchar(10)) engine=myisam;
194
insert into t1 values ( 1, 'abcd1e');
195
insert into t1 values ( 2, 'abcd2e');
196
insert into t2 values ( 1, 'abcd1e');
197
insert into t2 values ( 2, 'abcd2e');
198
analyze table t1,t2;
199
Table Op Msg_type Msg_text
200
test.t1 analyze status OK
201
test.t2 analyze status OK
202
update t1, t2 set t1.a = t2.a where t2.b = t1.b;
203
show warnings;
204
Level Code Message
205
drop table t1, t2;
206
create table t1(f1 int, f2 int);
207
create table t2(f3 int, f4 int);
208
create index idx on t2(f3);
209
insert into t1 values(1,0),(2,0);
210
insert into t2 values(1,1),(2,2);
211
UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1);
212
select * from t1;
213
f1 f2
214
1 1
215
2 2
216
drop table t1,t2;
217
create table t1(f1 int);
218
select DATABASE();
219
DATABASE()
220
test
221
update t1 set f1=1 where count(*)=1;
222
ERROR HY000: Invalid use of group function
223
select DATABASE();
224
DATABASE()
225
test
226
delete from t1 where count(*)=1;
227
ERROR HY000: Invalid use of group function
228
drop table t1;
229
create table t1 ( a int, b int default 0, index (a) );
230
insert into t1 (a) values (0),(0),(0),(0),(0),(0),(0),(0);
231
flush status;
232
select a from t1 order by a limit 1;
233
a
234
0
235
show status like 'handler_read%';
236
Variable_name Value
237
Handler_read_first 0
238
Handler_read_key 0
239
Handler_read_next 0
240
Handler_read_prev 0
241
Handler_read_rnd 0
242
Handler_read_rnd_next 0
243
flush status;
244
update t1 set a=9999 order by a limit 1;
245
update t1 set b=9999 order by a limit 1;
246
show status like 'handler_read%';
247
Variable_name Value
248
Handler_read_first 0
249
Handler_read_key 0
250
Handler_read_next 0
251
Handler_read_prev 0
252
Handler_read_rnd 0
253
Handler_read_rnd_next 0
254
flush status;
255
delete from t1 order by a limit 1;
256
show status like 'handler_read%';
257
Variable_name Value
258
Handler_read_first 0
259
Handler_read_key 0
260
Handler_read_next 0
261
Handler_read_prev 0
262
Handler_read_rnd 0
263
Handler_read_rnd_next 0
264
flush status;
265
delete from t1 order by a desc limit 1;
266
show status like 'handler_read%';
267
Variable_name Value
268
Handler_read_first 0
269
Handler_read_key 0
270
Handler_read_next 0
271
Handler_read_prev 0
272
Handler_read_rnd 0
273
Handler_read_rnd_next 0
274
alter table t1 disable keys;
275
Warnings:
276
Note 1031 Table storage engine for 't1' doesn't have this option
277
flush status;
278
delete from t1 order by a limit 1;
279
show status like 'handler_read%';
280
Variable_name Value
281
Handler_read_first 0
282
Handler_read_key 0
283
Handler_read_next 0
284
Handler_read_prev 0
285
Handler_read_rnd 0
286
Handler_read_rnd_next 0
287
select * from t1;
288
a b
289
0 0
290
0 0
291
0 0
292
0 0
293
0 9999
294
update t1 set a=a+10,b=1 order by a limit 3;
295
update t1 set a=a+11,b=2 order by a limit 3;
296
update t1 set a=a+12,b=3 order by a limit 3;
297
select * from t1 order by a;
298
a b
299
11 2
300
21 2
301
22 3
302
22 3
303
23 3
304
drop table t1;
305
create table t1 (f1 date not null);
306
insert into t1 values('2000-01-01'),('0000-00-00');
307
update t1 set f1='2002-02-02' where f1 is null;
308
select * from t1;
309
f1
310
2000-01-01
311
2002-02-02
312
drop table t1;
313
create table t1 (f1 int);
314
create table t2 (f2 int);
315
insert into t1 values(1),(2);
316
insert into t2 values(1),(1);
317
update t1,t2 set f1=3,f2=3 where f1=f2 and f1=1;
318
affected rows: 3
319
info: Rows matched: 3 Changed: 3 Warnings: 0
320
update t2 set f2=1;
321
update t1 set f1=1 where f1=3;
322
update t2,t1 set f1=3,f2=3 where f1=f2 and f1=1;
323
affected rows: 3
324
info: Rows matched: 3 Changed: 3 Warnings: 0
325
drop table t1,t2;
326
create table t1 (a int);
327
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
328
create table t2 (a int, filler1 char(200), filler2 char(200), key(a));
329
insert into t2 select A.a + 10*B.a, 'filler','filler' from t1 A, t1 B;
330
flush status;
331
update t2 set a=3 where a=2;
332
show status like 'handler_read%';
333
Variable_name Value
334
Handler_read_first 0
335
Handler_read_key 0
336
Handler_read_next 0
337
Handler_read_prev 0
338
Handler_read_rnd 0
339
Handler_read_rnd_next 0
340
drop table t1, t2;
341
create table t1(f1 int, `*f2` int);
342
insert into t1 values (1,1);
343
update t1 set `*f2`=1;
344
drop table t1;
345
create table t1(f1 int);
346
update t1 set f2=1 order by f2;
347
ERROR 42S22: Unknown column 'f2' in 'order clause'
348
drop table t1;
349
CREATE TABLE t1 (
350
request_id int NOT NULL auto_increment,
351
user_id varchar(12) default NULL,
352
time_stamp datetime NOT NULL default '0000-00-00 00:00:00',
353
ip_address varchar(15) default NULL,
354
PRIMARY KEY (request_id),
355
KEY user_id_2 (user_id,time_stamp)
356
);
357
INSERT INTO t1 (user_id) VALUES ('user1');
358
INSERT INTO t1(user_id) SELECT user_id FROM t1;
359
INSERT INTO t1(user_id) SELECT user_id FROM t1;
360
INSERT INTO t1(user_id) SELECT user_id FROM t1;
361
INSERT INTO t1(user_id) SELECT user_id FROM t1;
362
INSERT INTO t1(user_id) SELECT user_id FROM t1;
363
INSERT INTO t1(user_id) SELECT user_id FROM t1;
364
INSERT INTO t1(user_id) SELECT user_id FROM t1;
365
INSERT INTO t1(user_id) SELECT user_id FROM t1;
366
flush status;
367
SELECT user_id FROM t1 WHERE request_id=9999999999999;
368
user_id
369
show status like '%Handler_read%';
370
Variable_name Value
371
Handler_read_first 0
372
Handler_read_key 0
373
Handler_read_next 0
374
Handler_read_prev 0
375
Handler_read_rnd 0
376
Handler_read_rnd_next 0
377
SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999;
378
user_id
379
show status like '%Handler_read%';
380
Variable_name Value
381
Handler_read_first 0
382
Handler_read_key 0
383
Handler_read_next 0
384
Handler_read_prev 0
385
Handler_read_rnd 0
386
Handler_read_rnd_next 0
387
UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
388
show status like '%Handler_read%';
389
Variable_name Value
390
Handler_read_first 0
391
Handler_read_key 0
392
Handler_read_next 0
393
Handler_read_prev 0
394
Handler_read_rnd 0
395
Handler_read_rnd_next 0
396
UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
397
show status like '%Handler_read%';
398
Variable_name Value
399
Handler_read_first 0
400
Handler_read_key 0
401
Handler_read_next 0
402
Handler_read_prev 0
403
Handler_read_rnd 0
404
Handler_read_rnd_next 0
405
DROP TABLE t1;
406
CREATE TABLE t1 (
407
a int,
408
quux decimal( 31, 30 ),
409
UNIQUE KEY bar (a),
410
KEY quux (quux)
411
);
412
INSERT INTO
413
t1 ( a, quux )
414
VALUES
415
( 1, 1 ),
416
( 2, 0.1 );
417
INSERT INTO t1( a )
418
SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1;
419
SELECT * FROM t1;
420
a quux
421
1 1.000000000000000000000000000000
422
2 0.100000000000000000000000000000
423
3 NULL
424
DROP TABLE t1;
425
set tmp_table_size=1024;
426
create table t1 (id int, a int, key idx(a));
427
create table t2 (id int not null auto_increment primary key, a int);
428
insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
429
insert into t2(a) select a from t2;
430
insert into t2(a) select a from t2;
431
insert into t2(a) select a from t2;
432
update t2 set a=id;
433
insert into t1 select * from t2;
434
select count(*) from t1 join t2 on (t1.a=t2.a);
435
count(*)
436
64
437
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
438
affected rows: 0
439
info: Rows matched: 64 Changed: 0 Warnings: 0
440
insert into t2(a) select a from t2;
441
update t2 set a=id;
442
truncate t1;
443
insert into t1 select * from t2;
444
select count(*) from t1 join t2 on (t1.a=t2.a);
445
count(*)
446
128
447
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
448
affected rows: 0
449
info: Rows matched: 128 Changed: 0 Warnings: 0
450
update t1 set a=1;
451
update t2 set a=1;
452
select count(*) from t1 join t2 on (t1.a=t2.a);
453
count(*)
454
16384
455
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
456
affected rows: 127
457
info: Rows matched: 128 Changed: 127 Warnings: 0
458
drop table t1,t2;
459
End of 5.0 tests
Loggerhead is a web-based interface for Breezy
Version: 2.0.1