1
SET @NO_REFRESH = IF( '' = '', 0, 1);
3
drop table if exists tb3 ;
5
f118 char not null DEFAULT 'a',
6
f119 char binary not null DEFAULT b'101',
7
f120 char ascii not null DEFAULT b'101',
11
f124 longtext unicode,
16
f129 binary not null DEFAULT b'101',
17
f130 tinyint not null DEFAULT 99,
18
f131 tinyint unsigned not null DEFAULT 99,
19
f132 tinyint zerofill not null DEFAULT 99,
20
f133 tinyint unsigned zerofill not null DEFAULT 99,
21
f134 smallint not null DEFAULT 999,
22
f135 smallint unsigned not null DEFAULT 999,
23
f136 smallint zerofill not null DEFAULT 999,
24
f137 smallint unsigned zerofill not null DEFAULT 999,
25
f138 mediumint not null DEFAULT 9999,
26
f139 mediumint unsigned not null DEFAULT 9999,
27
f140 mediumint zerofill not null DEFAULT 9999,
28
f141 mediumint unsigned zerofill not null DEFAULT 9999,
29
f142 int not null DEFAULT 99999,
30
f143 int unsigned not null DEFAULT 99999,
31
f144 int zerofill not null DEFAULT 99999,
32
f145 int unsigned zerofill not null DEFAULT 99999,
33
f146 bigint not null DEFAULT 999999,
34
f147 bigint unsigned not null DEFAULT 999999,
35
f148 bigint zerofill not null DEFAULT 999999,
36
f149 bigint unsigned zerofill not null DEFAULT 999999,
37
f150 decimal not null DEFAULT 999.999,
38
f151 decimal unsigned not null DEFAULT 999.17,
39
f152 decimal zerofill not null DEFAULT 999.999,
40
f153 decimal unsigned zerofill,
43
f156 decimal (0) unsigned,
44
f157 decimal (64) unsigned,
45
f158 decimal (0) zerofill,
46
f159 decimal (64) zerofill,
47
f160 decimal (0) unsigned zerofill,
48
f161 decimal (64) unsigned zerofill,
51
f164 decimal (0,0) unsigned,
52
f165 decimal (63,30) unsigned,
53
f166 decimal (0,0) zerofill,
54
f167 decimal (63,30) zerofill,
55
f168 decimal (0,0) unsigned zerofill,
56
f169 decimal (63,30) unsigned zerofill,
58
f171 numeric unsigned,
59
f172 numeric zerofill,
60
f173 numeric unsigned zerofill,
65
Note 1265 Data truncated for column 'f150' at row 1
66
Note 1265 Data truncated for column 'f151' at row 1
67
Note 1265 Data truncated for column 'f152' at row 1
68
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/innodb_tb3.txt' into table tb3 ;
72
create User test_general@localhost;
73
set password for test_general@localhost = password('PWD');
74
revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
75
create User test_super@localhost;
76
set password for test_super@localhost = password('PWD');
77
grant ALL on *.* to test_super@localhost with grant OPTION;
78
connect(localhost,test_general,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
79
connect(localhost,test_super,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
81
Testcase 3.5.8.1: (implied in previous tests)
82
---------------------------------------------
84
Testcase 3.5.8.2: (implied in previous tests)
85
---------------------------------------------
89
create database db_test;
90
grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
91
grant LOCK TABLES on db_test.* to test_general;
94
i120 char ascii not null DEFAULT b'101',
95
i136 smallint zerofill not null DEFAULT 999,
96
i144 int zerofill not null DEFAULT 99999,
97
i163 decimal (63,30)) engine=innodb;
99
u120 char ascii not null DEFAULT b'101',
100
u136 smallint zerofill not null DEFAULT 999,
101
u144 int zerofill not null DEFAULT 99999,
102
u163 decimal (63,30)) engine=innodb;
104
d120 char ascii not null DEFAULT b'101',
105
d136 smallint zerofill not null DEFAULT 999,
106
d144 int zerofill not null DEFAULT 99999,
107
d163 decimal (63,30)) engine=innodb;
108
Insert into t1_u values ('a',111,99999,999.99);
109
Insert into t1_u values ('b',222,99999,999.99);
110
Insert into t1_u values ('c',333,99999,999.99);
111
Insert into t1_u values ('d',222,99999,999.99);
112
Insert into t1_u values ('e',222,99999,999.99);
113
Insert into t1_u values ('f',333,99999,999.99);
114
Insert into t1_d values ('a',111,99999,999.99);
115
Insert into t1_d values ('b',222,99999,999.99);
116
Insert into t1_d values ('c',333,99999,999.99);
117
Insert into t1_d values ('d',444,99999,999.99);
118
Insert into t1_d values ('e',222,99999,999.99);
119
Insert into t1_d values ('f',222,99999,999.99);
121
3.5.8.4 - multiple SQL
122
----------------------
124
Create trigger trg1 AFTER INSERT on tb3 for each row
126
insert into db_test.t1_i
127
values (new.f120, new.f136, new.f144, new.f163);
129
set u144=new.f144, u163=new.f163
131
delete from db_test.t1_d where d136= new.f136;
132
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
133
where u136= new.f136;
137
Insert into tb3 (f120, f122, f136, f144, f163)
138
values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
139
Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
140
f120 f122 f136 f144 f163
141
1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000
142
select * from db_test.t1_i;
144
1 00222 0000023456 1.050000000000000000000000000000
145
select * from db_test.t1_u;
147
a 00111 0000099999 999.990000000000000000000000000000
148
b 00222 0000023456 1.050000000000000000000000000000
149
c 00333 0000099999 999.990000000000000000000000000000
150
d 00222 0000023456 1.050000000000000000000000000000
151
e 00222 0000023456 1.050000000000000000000000000000
152
f 00333 0000099999 999.990000000000000000000000000000
153
select * from db_test.t1_d;
155
a 00111 0000099999 999.990000000000000000000000000000
156
c 00333 0000099999 999.990000000000000000000000000000
157
d 00444 0000099999 999.990000000000000000000000000000
160
3.150000000000000000000000000000
162
3.5.8.4 - single SQL - insert
163
-----------------------------
164
Create trigger trg2 BEFORE UPDATE on tb3 for each row
165
insert into db_test.t1_i
166
values (new.f120, new.f136, new.f144, new.f163);
167
update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
168
where f122='Test 3.5.8.4';
169
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
170
f120 f122 f136 f144 f163
171
I Test 3.5.8.4-Single Insert 00222 0000023456 1.050000000000000000000000000000
172
select * from db_test.t1_i;
174
1 00222 0000023456 1.050000000000000000000000000000
175
I 00222 0000023456 1.050000000000000000000000000000
177
3.5.8.4 - single SQL - update
178
-----------------------------
180
Create trigger trg3 BEFORE UPDATE on tb3 for each row
184
update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
185
where f122='Test 3.5.8.4-Single Insert';
186
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
187
f120 f122 f136 f144 f163
188
U Test 3.5.8.4-Single Update 00222 0000023456 1.050000000000000000000000000000
189
select * from db_test.t1_u;
191
a 00111 0000099999 999.990000000000000000000000000000
192
U 00222 0000023456 1.050000000000000000000000000000
193
c 00333 0000099999 999.990000000000000000000000000000
194
U 00222 0000023456 1.050000000000000000000000000000
195
U 00222 0000023456 1.050000000000000000000000000000
196
f 00333 0000099999 999.990000000000000000000000000000
198
3.5.8.3/4 - single SQL - delete
199
-------------------------------
201
Create trigger trg4 AFTER UPDATE on tb3 for each row
202
delete from db_test.t1_d where d136= new.f136;
203
update tb3 set f120='D', f136=444,
204
f122='Test 3.5.8.4-Single Delete'
205
where f122='Test 3.5.8.4-Single Update';
206
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
207
f120 f122 f136 f144 f163
208
D Test 3.5.8.4-Single Delete 00444 0000023456 1.050000000000000000000000000000
209
select * from db_test.t1_d;
211
a 00111 0000099999 999.990000000000000000000000000000
212
c 00333 0000099999 999.990000000000000000000000000000
214
3.5.8.3/4 - single SQL - select
215
-------------------------------
217
Create trigger trg5 AFTER UPDATE on tb3 for each row
218
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
219
where u136= new.f136;
221
update tb3 set f120='S', f136=111,
222
f122='Test 3.5.8.4-Single Select'
223
where f122='Test 3.5.8.4-Single Delete';
224
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
225
f120 f122 f136 f144 f163
226
S Test 3.5.8.4-Single Select 00111 0000023456 1.050000000000000000000000000000
229
999.990000000000000000000000000000
232
drop database if exists db_test;
233
delete from tb3 where f122 like 'Test 3.5.8.4%';
234
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
236
Testcase 3.5.8.5 (IF):
237
----------------------
238
create trigger trg2 before insert on tb3 for each row
241
set @test_var='one', new.f120='2';
242
ELSEIF new.f120='2' then
243
set @test_var='two', new.f120='3';
244
ELSEIF new.f120='3' then
245
set @test_var='three', new.f120='4';
247
IF (new.f120='4') and (new.f136=10) then
248
set @test_var2='2nd if', new.f120='d';
250
set @test_var2='2nd else', new.f120='D';
253
set @test_var='Empty', @test_var2=0;
254
Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
255
select f120, f122, f136, @test_var, @test_var2
256
from tb3 where f122 = 'Test 3.5.8.5-if';
257
f120 f122 f136 @test_var @test_var2
258
D Test 3.5.8.5-if 00101 one 2nd else
259
Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
260
select f120, f122, f136, @test_var, @test_var2
261
from tb3 where f122 = 'Test 3.5.8.5-if';
262
f120 f122 f136 @test_var @test_var2
263
D Test 3.5.8.5-if 00101 two 2nd else
264
D Test 3.5.8.5-if 00102 two 2nd else
265
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
266
select f120, f122, f136, @test_var, @test_var2
267
from tb3 where f122 = 'Test 3.5.8.5-if';
268
f120 f122 f136 @test_var @test_var2
269
D Test 3.5.8.5-if 00101 three 2nd if
270
D Test 3.5.8.5-if 00102 three 2nd if
271
d Test 3.5.8.5-if 00010 three 2nd if
272
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
273
select f120, f122, f136, @test_var, @test_var2
274
from tb3 where f122 = 'Test 3.5.8.5-if';
275
f120 f122 f136 @test_var @test_var2
276
D Test 3.5.8.5-if 00101 three 2nd else
277
D Test 3.5.8.5-if 00102 three 2nd else
278
d Test 3.5.8.5-if 00010 three 2nd else
279
D Test 3.5.8.5-if 00103 three 2nd else
280
create trigger trg3 before update on tb3 for each row
282
ELSEIF new.f120='2' then
285
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSEIF new.f120='2' then
289
create trigger trg4 before update on tb3 for each row
291
IF (new.f120='4') and (new.f136=10) then
292
set @test_var2='2nd if', new.f120='d';
294
set @test_var2='2nd else', new.f120='D';
296
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7
299
delete from tb3 where f121='Test 3.5.8.5-if';
301
Testcase 3.5.8.5-case:
302
----------------------
303
create trigger trg3 before insert on tb3 for each row
305
SET new.f120=char(ascii(new.f120)-32);
307
when new.f136<100 then set new.f136=new.f136+120;
308
when new.f136<10 then set new.f144=777;
309
when new.f136>100 then set new.f120=new.f136-1;
312
when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
313
ELSE set @test_var=concat(new.f120, '*');
316
when 1 then set @test_var=concat(@test_var, 'one');
317
when 2 then set @test_var=concat(@test_var, 'two');
318
when 3 then set @test_var=concat(@test_var, 'three');
319
when 4 then set @test_var=concat(@test_var, 'four');
320
when 5 then set @test_var=concat(@test_var, 'five');
321
when 6 then set @test_var=concat(@test_var, 'six');
322
when 7 then set @test_var=concat(@test_var, 'seven');
323
when 8 then set @test_var=concat(@test_var, 'eight');
324
when 9 then set @test_var=concat(@test_var, 'nine');
325
when 10 then set @test_var=concat(@test_var, 'ten');
326
when 11 then set @test_var=concat(@test_var, 'eleven');
327
when 12 then set @test_var=concat(@test_var, 'twelve');
328
when 13 then set @test_var=concat(@test_var, 'thirteen');
329
when 14 then set @test_var=concat(@test_var, 'fourteen');
330
when 15 then set @test_var=concat(@test_var, 'fifteen');
331
ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
334
set @test_var='Empty';
335
Insert into tb3 (f120, f122, f136, f144)
336
values ('a', 'Test 3.5.8.5-case', 5, 7);
337
select f120, f122, f136, f144, @test_var
338
from tb3 where f122 = 'Test 3.5.8.5-case';
339
f120 f122 f136 f144 @test_var
340
A Test 3.5.8.5-case 00125 0000000007 A*seven
341
Insert into tb3 (f120, f122, f136, f144)
342
values ('b', 'Test 3.5.8.5-case', 71,16);
343
select f120, f122, f136, f144, @test_var
344
from tb3 where f122 = 'Test 3.5.8.5-case';
345
f120 f122 f136 f144 @test_var
346
A Test 3.5.8.5-case 00125 0000000007 B*0000000016
347
B Test 3.5.8.5-case 00191 0000000016 B*0000000016
348
Insert into tb3 (f120, f122, f136, f144)
349
values ('c', 'Test 3.5.8.5-case', 80,1);
350
select f120, f122, f136, f144, @test_var
351
from tb3 where f122 = 'Test 3.5.8.5-case';
352
f120 f122 f136 f144 @test_var
353
A Test 3.5.8.5-case 00125 0000000007 C=one
354
B Test 3.5.8.5-case 00191 0000000016 C=one
355
C Test 3.5.8.5-case 00200 0000000001 C=one
356
Insert into tb3 (f120, f122, f136)
357
values ('d', 'Test 3.5.8.5-case', 152);
359
Warning 1265 Data truncated for column 'f120' at row 1
360
select f120, f122, f136, f144, @test_var
361
from tb3 where f122 = 'Test 3.5.8.5-case';
362
f120 f122 f136 f144 @test_var
363
A Test 3.5.8.5-case 00125 0000000007 1*0000099999
364
B Test 3.5.8.5-case 00191 0000000016 1*0000099999
365
C Test 3.5.8.5-case 00200 0000000001 1*0000099999
366
1 Test 3.5.8.5-case 00152 0000099999 1*0000099999
367
Insert into tb3 (f120, f122, f136, f144)
368
values ('e', 'Test 3.5.8.5-case', 200, 8);
370
Warning 1265 Data truncated for column 'f120' at row 1
371
select f120, f122, f136, f144, @test_var
372
from tb3 where f122 = 'Test 3.5.8.5-case';
373
f120 f122 f136 f144 @test_var
374
A Test 3.5.8.5-case 00125 0000000007 1=eight
375
B Test 3.5.8.5-case 00191 0000000016 1=eight
376
C Test 3.5.8.5-case 00200 0000000001 1=eight
377
1 Test 3.5.8.5-case 00152 0000099999 1=eight
378
1 Test 3.5.8.5-case 00200 0000000008 1=eight
379
Insert into tb3 (f120, f122, f136, f144)
380
values ('f', 'Test 3.5.8.5-case', 100, 8);
381
select f120, f122, f136, f144, @test_var
382
from tb3 where f122 = 'Test 3.5.8.5-case';
383
f120 f122 f136 f144 @test_var
384
A Test 3.5.8.5-case 00125 0000000007 1=eight
385
B Test 3.5.8.5-case 00191 0000000016 1=eight
386
C Test 3.5.8.5-case 00200 0000000001 1=eight
387
1 Test 3.5.8.5-case 00152 0000099999 1=eight
388
1 Test 3.5.8.5-case 00200 0000000008 1=eight
389
create trigger trg3a before update on tb3 for each row
392
when new.f136<100 then set new.f120='p';
394
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
397
delete from tb3 where f121='Test 3.5.8.5-case';
399
Testcase 3.5.8.5-loop/leave:
400
----------------------------
401
Create trigger trg4 after insert on tb3 for each row
403
set @counter=0, @flag='Initial';
405
if new.f136<new.f144 then
406
set @counter='Nothing to loop';
409
set @counter=@counter+1;
410
if new.f136=new.f144+@counter then
411
set @counter=concat(@counter, ' loops');
419
Insert into tb3 (f122, f136, f144)
420
values ('Test 3.5.8.5-loop', 2, 8);
421
select @counter, @flag;
423
Nothing to loop Initial
424
Insert into tb3 (f122, f136, f144)
425
values ('Test 3.5.8.5-loop', 11, 8);
426
select @counter, @flag;
429
Create trigger trg4_2 after update on tb3 for each row
432
set @counter=@counter+1;
435
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
439
delete from tb3 where f122='Test 3.5.8.5-loop';
441
Testcase 3.5.8.5-repeat:
442
------------------------
443
Create trigger trg6 after insert on tb3 for each row
446
SET @counter1 = @counter1 + 1;
447
IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label;
449
SET @counter2 = @counter2 + 1;
450
UNTIL @counter1> new.f136 END REPEAT rp_label;
452
set @counter1= 0, @counter2= 0;
453
Insert into tb3 (f122, f136)
454
values ('Test 3.5.8.5-repeat', 13);
455
select @counter1, @counter2;
458
Create trigger trg6_2 after update on tb3 for each row
461
SET @counter2 = @counter2 + 1;
463
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 5
465
delete from tb3 where f122='Test 3.5.8.5-repeat';
467
Testcase 3.5.8.5-while:
468
-----------------------
469
Create trigger trg7 after insert on tb3 for each row
470
wl_label: WHILE @counter1 < new.f136 DO
471
SET @counter1 = @counter1 + 1;
472
IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label;
474
SET @counter2 = @counter2 + 1;
476
set @counter1= 0, @counter2= 0;
477
Insert into tb3 (f122, f136)
478
values ('Test 3.5.8.5-while', 7);
479
select @counter1, @counter2;
482
Create trigger trg7_2 after update on tb3 for each row
484
WHILE @counter1 < new.f136
485
SET @counter1 = @counter1 + 1;
487
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @counter1 = @counter1 + 1;
489
delete from tb3 where f122='Test 3.5.8.5-while';
492
Testcase 3.5.8.6: (requirement void)
493
------------------------------------
495
Testcase 3.5.8.7: (Disabled as a result of bug _____)
496
-----------------------------------------------------
497
drop user test_general@localhost;
498
drop user test_general;
499
drop user test_super@localhost;