2
drop table if exists tb3 ;
4
f118 char not null DEFAULT 'a',
5
f119 char binary not null DEFAULT b'101',
6
f120 char ascii not null DEFAULT b'101',
10
f124 longtext unicode,
15
f129 binary not null DEFAULT b'101',
16
f130 tinyint not null DEFAULT 99,
17
f131 tinyint unsigned not null DEFAULT 99,
18
f132 tinyint zerofill not null DEFAULT 99,
19
f133 tinyint unsigned zerofill not null DEFAULT 99,
20
f134 smallint not null DEFAULT 999,
21
f135 smallint unsigned not null DEFAULT 999,
22
f136 smallint zerofill not null DEFAULT 999,
23
f137 smallint unsigned zerofill not null DEFAULT 999,
24
f138 mediumint not null DEFAULT 9999,
25
f139 mediumint unsigned not null DEFAULT 9999,
26
f140 mediumint zerofill not null DEFAULT 9999,
27
f141 mediumint unsigned zerofill not null DEFAULT 9999,
28
f142 int not null DEFAULT 99999,
29
f143 int unsigned not null DEFAULT 99999,
30
f144 int zerofill not null DEFAULT 99999,
31
f145 int unsigned zerofill not null DEFAULT 99999,
32
f146 bigint not null DEFAULT 999999,
33
f147 bigint unsigned not null DEFAULT 999999,
34
f148 bigint zerofill not null DEFAULT 999999,
35
f149 bigint unsigned zerofill not null DEFAULT 999999,
36
f150 decimal not null DEFAULT 999.999,
37
f151 decimal unsigned not null DEFAULT 999.17,
38
f152 decimal zerofill not null DEFAULT 999.999,
39
f153 decimal unsigned zerofill,
42
f156 decimal (0) unsigned,
43
f157 decimal (64) unsigned,
44
f158 decimal (0) zerofill,
45
f159 decimal (64) zerofill,
46
f160 decimal (0) unsigned zerofill,
47
f161 decimal (64) unsigned zerofill,
50
f164 decimal (0,0) unsigned,
51
f165 decimal (63,30) unsigned,
52
f166 decimal (0,0) zerofill,
53
f167 decimal (63,30) zerofill,
54
f168 decimal (0,0) unsigned zerofill,
55
f169 decimal (63,30) unsigned zerofill,
57
f171 numeric unsigned,
58
f172 numeric zerofill,
59
f173 numeric unsigned zerofill,
64
Note 1265 Data truncated for column 'f150' at row 1
65
Note 1265 Data truncated for column 'f151' at row 1
66
Note 1265 Data truncated for column 'f152' at row 1
67
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/innodb_tb3.txt' into table tb3 ;
71
create User test_general@localhost;
72
set password for test_general@localhost = password('PWD');
73
revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
74
create User test_super@localhost;
75
set password for test_super@localhost = password('PWD');
76
grant ALL on *.* to test_super@localhost with grant OPTION;
77
connect(localhost,test_general,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
78
connect(localhost,test_super,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
80
Testcase 3.5.8.1: (implied in previous tests)
81
---------------------------------------------
83
Testcase 3.5.8.2: (implied in previous tests)
84
---------------------------------------------
88
create database db_test;
89
grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
90
grant LOCK TABLES on db_test.* to test_general;
93
i120 char ascii not null DEFAULT b'101',
94
i136 smallint zerofill not null DEFAULT 999,
95
i144 int zerofill not null DEFAULT 99999,
96
i163 decimal (63,30)) engine=falcon;
98
u120 char ascii not null DEFAULT b'101',
99
u136 smallint zerofill not null DEFAULT 999,
100
u144 int zerofill not null DEFAULT 99999,
101
u163 decimal (63,30)) engine=falcon;
103
d120 char ascii not null DEFAULT b'101',
104
d136 smallint zerofill not null DEFAULT 999,
105
d144 int zerofill not null DEFAULT 99999,
106
d163 decimal (63,30)) engine=falcon;
107
Insert into t1_u values ('a',111,99999,999.99);
108
Insert into t1_u values ('b',222,99999,999.99);
109
Insert into t1_u values ('c',333,99999,999.99);
110
Insert into t1_u values ('d',222,99999,999.99);
111
Insert into t1_u values ('e',222,99999,999.99);
112
Insert into t1_u values ('f',333,99999,999.99);
113
Insert into t1_d values ('a',111,99999,999.99);
114
Insert into t1_d values ('b',222,99999,999.99);
115
Insert into t1_d values ('c',333,99999,999.99);
116
Insert into t1_d values ('d',444,99999,999.99);
117
Insert into t1_d values ('e',222,99999,999.99);
118
Insert into t1_d values ('f',222,99999,999.99);
120
3.5.8.4 - multiple SQL
121
----------------------
123
Create trigger trg1 AFTER INSERT on tb3 for each row
125
insert into db_test.t1_i
126
values (new.f120, new.f136, new.f144, new.f163);
128
set u144=new.f144, u163=new.f163
130
delete from db_test.t1_d where d136= new.f136;
131
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
132
where u136= new.f136;
136
Insert into tb3 (f120, f122, f136, f144, f163)
137
values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
138
Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
139
f120 f122 f136 f144 f163
140
1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000
141
select * from db_test.t1_i;
143
1 00222 0000023456 1.050000000000000000000000000000
144
select * from db_test.t1_u;
146
a 00111 0000099999 999.990000000000000000000000000000
147
b 00222 0000023456 1.050000000000000000000000000000
148
c 00333 0000099999 999.990000000000000000000000000000
149
d 00222 0000023456 1.050000000000000000000000000000
150
e 00222 0000023456 1.050000000000000000000000000000
151
f 00333 0000099999 999.990000000000000000000000000000
152
select * from db_test.t1_d;
154
a 00111 0000099999 999.990000000000000000000000000000
155
c 00333 0000099999 999.990000000000000000000000000000
156
d 00444 0000099999 999.990000000000000000000000000000
159
3.150000000000000000000000000000
161
3.5.8.4 - single SQL - insert
162
-----------------------------
163
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);
168
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
169
f120 f122 f136 f144 f163
170
1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000
171
select * from db_test.t1_i order by i120;
173
1 00222 0000023456 1.050000000000000000000000000000
174
update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
175
where f122='Test 3.5.8.4';
176
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
177
f120 f122 f136 f144 f163
178
I Test 3.5.8.4-Single Insert 00222 0000023456 1.050000000000000000000000000000
179
select * from db_test.t1_i order by i120;
181
1 00222 0000023456 1.050000000000000000000000000000
182
I 00222 0000023456 1.050000000000000000000000000000
184
3.5.8.4 - single SQL - update
185
-----------------------------
187
Create trigger trg3 BEFORE UPDATE on tb3 for each row
191
update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
192
where f122='Test 3.5.8.4-Single Insert';
193
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
194
f120 f122 f136 f144 f163
195
U Test 3.5.8.4-Single Update 00222 0000023456 1.050000000000000000000000000000
196
select * from db_test.t1_u order by u120;
198
a 00111 0000099999 999.990000000000000000000000000000
199
c 00333 0000099999 999.990000000000000000000000000000
200
f 00333 0000099999 999.990000000000000000000000000000
201
U 00222 0000023456 1.050000000000000000000000000000
202
U 00222 0000023456 1.050000000000000000000000000000
203
U 00222 0000023456 1.050000000000000000000000000000
205
3.5.8.3/4 - single SQL - delete
206
-------------------------------
208
Create trigger trg4 AFTER UPDATE on tb3 for each row
209
delete from db_test.t1_d where d136= new.f136;
210
update tb3 set f120='D', f136=444,
211
f122='Test 3.5.8.4-Single Delete'
212
where f122='Test 3.5.8.4-Single Update';
213
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
214
f120 f122 f136 f144 f163
215
D Test 3.5.8.4-Single Delete 00444 0000023456 1.050000000000000000000000000000
216
select * from db_test.t1_d order by d120;
218
a 00111 0000099999 999.990000000000000000000000000000
219
c 00333 0000099999 999.990000000000000000000000000000
221
3.5.8.3/4 - single SQL - select
222
-------------------------------
224
Create trigger trg5 AFTER UPDATE on tb3 for each row
225
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
226
where u136= new.f136;
228
update tb3 set f120='S', f136=111,
229
f122='Test 3.5.8.4-Single Select'
230
where f122='Test 3.5.8.4-Single Delete';
231
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
232
f120 f122 f136 f144 f163
233
S Test 3.5.8.4-Single Select 00111 0000023456 1.050000000000000000000000000000
236
999.990000000000000000000000000000
239
drop database if exists db_test;
240
delete from tb3 where f122 like 'Test 3.5.8.4%';
241
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
243
Testcase 3.5.8.5 (IF):
244
----------------------
245
create trigger trg2 before insert on tb3 for each row
248
set @test_var='one', new.f120='2';
249
ELSEIF new.f120='2' then
250
set @test_var='two', new.f120='3';
251
ELSEIF new.f120='3' then
252
set @test_var='three', new.f120='4';
254
IF (new.f120='4') and (new.f136=10) then
255
set @test_var2='2nd if', new.f120='d';
257
set @test_var2='2nd else', new.f120='D';
260
set @test_var='Empty', @test_var2=0;
261
Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
262
select f120, f122, f136, @test_var, @test_var2
263
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
264
f120 f122 f136 @test_var @test_var2
265
D Test 3.5.8.5-if 00101 one 2nd else
266
Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
267
select f120, f122, f136, @test_var, @test_var2
268
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
269
f120 f122 f136 @test_var @test_var2
270
D Test 3.5.8.5-if 00101 two 2nd else
271
D Test 3.5.8.5-if 00102 two 2nd else
272
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
273
select f120, f122, f136, @test_var, @test_var2
274
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
275
f120 f122 f136 @test_var @test_var2
276
d Test 3.5.8.5-if 00010 three 2nd if
277
D Test 3.5.8.5-if 00101 three 2nd if
278
D Test 3.5.8.5-if 00102 three 2nd if
279
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
280
select f120, f122, f136, @test_var, @test_var2
281
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
282
f120 f122 f136 @test_var @test_var2
283
d Test 3.5.8.5-if 00010 three 2nd else
284
D Test 3.5.8.5-if 00101 three 2nd else
285
D Test 3.5.8.5-if 00102 three 2nd else
286
D Test 3.5.8.5-if 00103 three 2nd else
287
create trigger trg3 before update on tb3 for each row
289
ELSEIF new.f120='2' then
292
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
296
create trigger trg4 before update on tb3 for each row
298
IF (new.f120='4') and (new.f136=10) then
299
set @test_var2='2nd if', new.f120='d';
301
set @test_var2='2nd else', new.f120='D';
303
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
306
delete from tb3 where f121='Test 3.5.8.5-if';
308
Testcase 3.5.8.5-case:
309
----------------------
310
create trigger trg3 before insert on tb3 for each row
312
SET new.f120=char(ascii(new.f120)-32);
314
when new.f136<100 then set new.f136=new.f136+120;
315
when new.f136<10 then set new.f144=777;
316
when new.f136>100 then set new.f120=new.f136-1;
319
when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
320
ELSE set @test_var=concat(new.f120, '*');
323
when 1 then set @test_var=concat(@test_var, 'one');
324
when 2 then set @test_var=concat(@test_var, 'two');
325
when 3 then set @test_var=concat(@test_var, 'three');
326
when 4 then set @test_var=concat(@test_var, 'four');
327
when 5 then set @test_var=concat(@test_var, 'five');
328
when 6 then set @test_var=concat(@test_var, 'six');
329
when 7 then set @test_var=concat(@test_var, 'seven');
330
when 8 then set @test_var=concat(@test_var, 'eight');
331
when 9 then set @test_var=concat(@test_var, 'nine');
332
when 10 then set @test_var=concat(@test_var, 'ten');
333
when 11 then set @test_var=concat(@test_var, 'eleven');
334
when 12 then set @test_var=concat(@test_var, 'twelve');
335
when 13 then set @test_var=concat(@test_var, 'thirteen');
336
when 14 then set @test_var=concat(@test_var, 'fourteen');
337
when 15 then set @test_var=concat(@test_var, 'fifteen');
338
ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
341
set @test_var='Empty';
342
Insert into tb3 (f120, f122, f136, f144)
343
values ('a', 'Test 3.5.8.5-case', 5, 7);
344
select f120, f122, f136, f144, @test_var
345
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
346
f120 f122 f136 f144 @test_var
347
A Test 3.5.8.5-case 00125 0000000007 A*seven
348
Insert into tb3 (f120, f122, f136, f144)
349
values ('b', 'Test 3.5.8.5-case', 71,16);
350
select f120, f122, f136, f144, @test_var
351
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
352
f120 f122 f136 f144 @test_var
353
A Test 3.5.8.5-case 00125 0000000007 B*0000000016
354
B Test 3.5.8.5-case 00191 0000000016 B*0000000016
355
Insert into tb3 (f120, f122, f136, f144)
356
values ('c', 'Test 3.5.8.5-case', 80,1);
357
select f120, f122, f136, f144, @test_var
358
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
359
f120 f122 f136 f144 @test_var
360
A Test 3.5.8.5-case 00125 0000000007 C=one
361
B Test 3.5.8.5-case 00191 0000000016 C=one
362
C Test 3.5.8.5-case 00200 0000000001 C=one
363
Insert into tb3 (f120, f122, f136)
364
values ('d', 'Test 3.5.8.5-case', 152);
366
Warning 1265 Data truncated for column 'f120' at row 1
367
select f120, f122, f136, f144, @test_var
368
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
369
f120 f122 f136 f144 @test_var
370
1 Test 3.5.8.5-case 00152 0000099999 1*0000099999
371
A Test 3.5.8.5-case 00125 0000000007 1*0000099999
372
B Test 3.5.8.5-case 00191 0000000016 1*0000099999
373
C Test 3.5.8.5-case 00200 0000000001 1*0000099999
374
Insert into tb3 (f120, f122, f136, f144)
375
values ('e', 'Test 3.5.8.5-case', 200, 8);
377
Warning 1265 Data truncated for column 'f120' at row 1
378
select f120, f122, f136, f144, @test_var
379
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
380
f120 f122 f136 f144 @test_var
381
1 Test 3.5.8.5-case 00152 0000099999 1=eight
382
1 Test 3.5.8.5-case 00200 0000000008 1=eight
383
A Test 3.5.8.5-case 00125 0000000007 1=eight
384
B Test 3.5.8.5-case 00191 0000000016 1=eight
385
C Test 3.5.8.5-case 00200 0000000001 1=eight
386
Insert into tb3 (f120, f122, f136, f144)
387
values ('f', 'Test 3.5.8.5-case', 100, 8);
388
select f120, f122, f136, f144, @test_var
389
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
390
f120 f122 f136 f144 @test_var
391
1 Test 3.5.8.5-case 00152 0000099999 1=eight
392
1 Test 3.5.8.5-case 00200 0000000008 1=eight
393
A Test 3.5.8.5-case 00125 0000000007 1=eight
394
B Test 3.5.8.5-case 00191 0000000016 1=eight
395
C Test 3.5.8.5-case 00200 0000000001 1=eight
396
create trigger trg3a before update on tb3 for each row
399
when new.f136<100 then set new.f120='p';
401
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
404
delete from tb3 where f121='Test 3.5.8.5-case';
406
Testcase 3.5.8.5-loop/leave:
407
----------------------------
408
Create trigger trg4 after insert on tb3 for each row
410
set @counter=0, @flag='Initial';
412
if new.f136<new.f144 then
413
set @counter='Nothing to loop';
416
set @counter=@counter+1;
417
if new.f136=new.f144+@counter then
418
set @counter=concat(@counter, ' loops');
426
Insert into tb3 (f122, f136, f144)
427
values ('Test 3.5.8.5-loop', 2, 8);
428
select @counter, @flag;
430
Nothing to loop Initial
431
Insert into tb3 (f122, f136, f144)
432
values ('Test 3.5.8.5-loop', 11, 8);
433
select @counter, @flag;
436
Create trigger trg4_2 after update on tb3 for each row
439
set @counter=@counter+1;
442
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 ';
446
delete from tb3 where f122='Test 3.5.8.5-loop';
448
Testcase 3.5.8.5-repeat:
449
------------------------
450
Create trigger trg6 after insert on tb3 for each row
453
SET @counter1 = @counter1 + 1;
454
IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label;
456
SET @counter2 = @counter2 + 1;
457
UNTIL @counter1> new.f136 END REPEAT rp_label;
459
set @counter1= 0, @counter2= 0;
460
Insert into tb3 (f122, f136)
461
values ('Test 3.5.8.5-repeat', 13);
462
select @counter1, @counter2;
465
Create trigger trg6_2 after update on tb3 for each row
468
SET @counter2 = @counter2 + 1;
470
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
472
delete from tb3 where f122='Test 3.5.8.5-repeat';
474
Testcase 3.5.8.5-while:
475
-----------------------
476
Create trigger trg7 after insert on tb3 for each row
477
wl_label: WHILE @counter1 < new.f136 DO
478
SET @counter1 = @counter1 + 1;
479
IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label;
481
SET @counter2 = @counter2 + 1;
483
set @counter1= 0, @counter2= 0;
484
Insert into tb3 (f122, f136)
485
values ('Test 3.5.8.5-while', 7);
486
select @counter1, @counter2;
489
Create trigger trg7_2 after update on tb3 for each row
491
WHILE @counter1 < new.f136
492
SET @counter1 = @counter1 + 1;
494
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;
496
delete from tb3 where f122='Test 3.5.8.5-while';
499
Testcase 3.5.8.6: (requirement void)
500
------------------------------------
501
CREATE PROCEDURE sp_01 () BEGIN set @v1=1; END//
502
CREATE TRIGGER trg8_1 BEFORE UPDATE ON tb3 FOR EACH ROW
506
Insert into tb3 (f120, f122, f136) values ('6', 'Test 3.5.8.6-insert', 101);
507
update tb3 set f120='S', f136=111,
508
f122='Test 3.5.8.6-tr8_1'
509
where f122='Test 3.5.8.6-insert';
511
from tb3 where f122 like 'Test 3.5.8.6%' order by f120;
515
DROP PROCEDURE sp_01;
519
Create trigger trg9_1 before update on tb3 for each row
525
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
526
Create trigger trg9_2 before delete on tb3 for each row
532
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
533
drop user test_general@localhost;
534
drop user test_general;
535
drop user test_super@localhost;
added
removed
mysql-test/suite/funcs_1/r/falcon_trig_08.result
