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
Error 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'
68
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/ndb_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=ndb;
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=ndb;
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=ndb;
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
166
insert into db_test.t1_i
167
values (new.f120, new.f136, new.f144, new.f163);
169
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
170
f120 f122 f136 f144 f163
171
1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000
172
select * from db_test.t1_i order by i120;
174
1 00222 0000023456 1.050000000000000000000000000000
175
update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
176
where f122='Test 3.5.8.4';
177
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
178
f120 f122 f136 f144 f163
179
I Test 3.5.8.4-Single Insert 00222 0000023456 1.050000000000000000000000000000
180
select * from db_test.t1_i order by i120;
182
1 00222 0000023456 1.050000000000000000000000000000
183
I 00222 0000023456 1.050000000000000000000000000000
185
3.5.8.4 - single SQL - update
186
-----------------------------
188
Create trigger trg3 BEFORE UPDATE on tb3 for each row
192
update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
193
where f122='Test 3.5.8.4-Single Insert';
194
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
195
f120 f122 f136 f144 f163
196
U Test 3.5.8.4-Single Update 00222 0000023456 1.050000000000000000000000000000
197
select * from db_test.t1_u order by u120;
199
a 00111 0000099999 999.990000000000000000000000000000
200
c 00333 0000099999 999.990000000000000000000000000000
201
f 00333 0000099999 999.990000000000000000000000000000
202
U 00222 0000023456 1.050000000000000000000000000000
203
U 00222 0000023456 1.050000000000000000000000000000
204
U 00222 0000023456 1.050000000000000000000000000000
206
3.5.8.3/4 - single SQL - delete
207
-------------------------------
209
Create trigger trg4 AFTER UPDATE on tb3 for each row
210
delete from db_test.t1_d where d136= new.f136;
211
update tb3 set f120='D', f136=444,
212
f122='Test 3.5.8.4-Single Delete'
213
where f122='Test 3.5.8.4-Single Update';
214
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
215
f120 f122 f136 f144 f163
216
D Test 3.5.8.4-Single Delete 00444 0000023456 1.050000000000000000000000000000
217
select * from db_test.t1_d order by d120;
219
a 00111 0000099999 999.990000000000000000000000000000
220
c 00333 0000099999 999.990000000000000000000000000000
222
3.5.8.3/4 - single SQL - select
223
-------------------------------
225
Create trigger trg5 AFTER UPDATE on tb3 for each row
226
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u
227
where u136= new.f136;
229
update tb3 set f120='S', f136=111,
230
f122='Test 3.5.8.4-Single Select'
231
where f122='Test 3.5.8.4-Single Delete';
232
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
233
f120 f122 f136 f144 f163
234
S Test 3.5.8.4-Single Select 00111 0000023456 1.050000000000000000000000000000
237
999.990000000000000000000000000000
240
drop database if exists db_test;
241
delete from tb3 where f122 like 'Test 3.5.8.4%';
242
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
244
Testcase 3.5.8.5 (IF):
245
----------------------
246
create trigger trg2 before insert on tb3 for each row
249
set @test_var='one', new.f120='2';
250
ELSEIF new.f120='2' then
251
set @test_var='two', new.f120='3';
252
ELSEIF new.f120='3' then
253
set @test_var='three', new.f120='4';
255
IF (new.f120='4') and (new.f136=10) then
256
set @test_var2='2nd if', new.f120='d';
258
set @test_var2='2nd else', new.f120='D';
261
set @test_var='Empty', @test_var2=0;
262
Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
263
select f120, f122, f136, @test_var, @test_var2
264
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
265
f120 f122 f136 @test_var @test_var2
266
D Test 3.5.8.5-if 00101 one 2nd else
267
Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
268
select f120, f122, f136, @test_var, @test_var2
269
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
270
f120 f122 f136 @test_var @test_var2
271
D Test 3.5.8.5-if 00101 two 2nd else
272
D Test 3.5.8.5-if 00102 two 2nd else
273
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
274
select f120, f122, f136, @test_var, @test_var2
275
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
276
f120 f122 f136 @test_var @test_var2
277
d Test 3.5.8.5-if 00010 three 2nd if
278
D Test 3.5.8.5-if 00101 three 2nd if
279
D Test 3.5.8.5-if 00102 three 2nd if
280
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
281
select f120, f122, f136, @test_var, @test_var2
282
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
283
f120 f122 f136 @test_var @test_var2
284
d Test 3.5.8.5-if 00010 three 2nd else
285
D Test 3.5.8.5-if 00101 three 2nd else
286
D Test 3.5.8.5-if 00102 three 2nd else
287
D Test 3.5.8.5-if 00103 three 2nd else
288
create trigger trg3 before update on tb3 for each row
290
ELSEIF new.f120='2' then
293
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
297
create trigger trg4 before update on tb3 for each row
299
IF (new.f120='4') and (new.f136=10) then
300
set @test_var2='2nd if', new.f120='d';
302
set @test_var2='2nd else', new.f120='D';
304
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
307
delete from tb3 where f121='Test 3.5.8.5-if';
309
Testcase 3.5.8.5-case:
310
----------------------
311
create trigger trg3 before insert on tb3 for each row
313
SET new.f120=char(ascii(new.f120)-32);
315
when new.f136<100 then set new.f136=new.f136+120;
316
when new.f136<10 then set new.f144=777;
317
when new.f136>100 then set new.f120=new.f136-1;
320
when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
321
ELSE set @test_var=concat(new.f120, '*');
324
when 1 then set @test_var=concat(@test_var, 'one');
325
when 2 then set @test_var=concat(@test_var, 'two');
326
when 3 then set @test_var=concat(@test_var, 'three');
327
when 4 then set @test_var=concat(@test_var, 'four');
328
when 5 then set @test_var=concat(@test_var, 'five');
329
when 6 then set @test_var=concat(@test_var, 'six');
330
when 7 then set @test_var=concat(@test_var, 'seven');
331
when 8 then set @test_var=concat(@test_var, 'eight');
332
when 9 then set @test_var=concat(@test_var, 'nine');
333
when 10 then set @test_var=concat(@test_var, 'ten');
334
when 11 then set @test_var=concat(@test_var, 'eleven');
335
when 12 then set @test_var=concat(@test_var, 'twelve');
336
when 13 then set @test_var=concat(@test_var, 'thirteen');
337
when 14 then set @test_var=concat(@test_var, 'fourteen');
338
when 15 then set @test_var=concat(@test_var, 'fifteen');
339
ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
342
set @test_var='Empty';
343
Insert into tb3 (f120, f122, f136, f144)
344
values ('a', 'Test 3.5.8.5-case', 5, 7);
345
select f120, f122, f136, f144, @test_var
346
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
347
f120 f122 f136 f144 @test_var
348
A Test 3.5.8.5-case 00125 0000000007 A*seven
349
Insert into tb3 (f120, f122, f136, f144)
350
values ('b', 'Test 3.5.8.5-case', 71,16);
351
select f120, f122, f136, f144, @test_var
352
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
353
f120 f122 f136 f144 @test_var
354
A Test 3.5.8.5-case 00125 0000000007 B*0000000016
355
B Test 3.5.8.5-case 00191 0000000016 B*0000000016
356
Insert into tb3 (f120, f122, f136, f144)
357
values ('c', 'Test 3.5.8.5-case', 80,1);
358
select f120, f122, f136, f144, @test_var
359
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
360
f120 f122 f136 f144 @test_var
361
A Test 3.5.8.5-case 00125 0000000007 C=one
362
B Test 3.5.8.5-case 00191 0000000016 C=one
363
C Test 3.5.8.5-case 00200 0000000001 C=one
364
Insert into tb3 (f120, f122, f136)
365
values ('d', 'Test 3.5.8.5-case', 152);
367
Warning 1265 Data truncated for column 'f120' at row 1
368
select f120, f122, f136, f144, @test_var
369
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
370
f120 f122 f136 f144 @test_var
371
1 Test 3.5.8.5-case 00152 0000099999 1*0000099999
372
A Test 3.5.8.5-case 00125 0000000007 1*0000099999
373
B Test 3.5.8.5-case 00191 0000000016 1*0000099999
374
C Test 3.5.8.5-case 00200 0000000001 1*0000099999
375
Insert into tb3 (f120, f122, f136, f144)
376
values ('e', 'Test 3.5.8.5-case', 200, 8);
378
Warning 1265 Data truncated for column 'f120' at row 1
379
select f120, f122, f136, f144, @test_var
380
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
381
f120 f122 f136 f144 @test_var
382
1 Test 3.5.8.5-case 00152 0000099999 1=eight
383
1 Test 3.5.8.5-case 00200 0000000008 1=eight
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
Insert into tb3 (f120, f122, f136, f144)
388
values ('f', 'Test 3.5.8.5-case', 100, 8);
389
select f120, f122, f136, f144, @test_var
390
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
391
f120 f122 f136 f144 @test_var
392
1 Test 3.5.8.5-case 00152 0000099999 1=eight
393
1 Test 3.5.8.5-case 00200 0000000008 1=eight
394
A Test 3.5.8.5-case 00125 0000000007 1=eight
395
B Test 3.5.8.5-case 00191 0000000016 1=eight
396
C Test 3.5.8.5-case 00200 0000000001 1=eight
397
create trigger trg3a before update on tb3 for each row
400
when new.f136<100 then set new.f120='p';
402
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
405
delete from tb3 where f121='Test 3.5.8.5-case';
407
Testcase 3.5.8.5-loop/leave:
408
----------------------------
409
Create trigger trg4 after insert on tb3 for each row
411
set @counter=0, @flag='Initial';
413
if new.f136<new.f144 then
414
set @counter='Nothing to loop';
417
set @counter=@counter+1;
418
if new.f136=new.f144+@counter then
419
set @counter=concat(@counter, ' loops');
427
Insert into tb3 (f122, f136, f144)
428
values ('Test 3.5.8.5-loop', 2, 8);
429
select @counter, @flag;
431
Nothing to loop Initial
432
Insert into tb3 (f122, f136, f144)
433
values ('Test 3.5.8.5-loop', 11, 8);
434
select @counter, @flag;
437
Create trigger trg4_2 after update on tb3 for each row
440
set @counter=@counter+1;
443
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 ';
447
delete from tb3 where f122='Test 3.5.8.5-loop';
449
Testcase 3.5.8.5-repeat:
450
------------------------
451
Create trigger trg6 after insert on tb3 for each row
454
SET @counter1 = @counter1 + 1;
455
IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label;
457
SET @counter2 = @counter2 + 1;
458
UNTIL @counter1> new.f136 END REPEAT rp_label;
460
set @counter1= 0, @counter2= 0;
461
Insert into tb3 (f122, f136)
462
values ('Test 3.5.8.5-repeat', 13);
463
select @counter1, @counter2;
466
Create trigger trg6_2 after update on tb3 for each row
469
SET @counter2 = @counter2 + 1;
471
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
473
delete from tb3 where f122='Test 3.5.8.5-repeat';
475
Testcase 3.5.8.5-while:
476
-----------------------
477
Create trigger trg7 after insert on tb3 for each row
478
wl_label: WHILE @counter1 < new.f136 DO
479
SET @counter1 = @counter1 + 1;
480
IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label;
482
SET @counter2 = @counter2 + 1;
484
set @counter1= 0, @counter2= 0;
485
Insert into tb3 (f122, f136)
486
values ('Test 3.5.8.5-while', 7);
487
select @counter1, @counter2;
490
Create trigger trg7_2 after update on tb3 for each row
492
WHILE @counter1 < new.f136
493
SET @counter1 = @counter1 + 1;
495
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;
497
delete from tb3 where f122='Test 3.5.8.5-while';
500
Testcase 3.5.8.6: (requirement void)
501
------------------------------------
502
CREATE PROCEDURE sp_01 () BEGIN set @v1=1; END//
503
CREATE TRIGGER trg8_1 BEFORE UPDATE ON tb3 FOR EACH ROW
507
Insert into tb3 (f120, f122, f136) values ('6', 'Test 3.5.8.6-insert', 101);
508
update tb3 set f120='S', f136=111,
509
f122='Test 3.5.8.6-tr8_1'
510
where f122='Test 3.5.8.6-insert';
512
from tb3 where f122 like 'Test 3.5.8.6%' order by f120;
516
DROP PROCEDURE sp_01;
520
Create trigger trg9_1 before update on tb3 for each row
526
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
527
Create trigger trg9_2 before delete on tb3 for each row
533
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
534
drop user test_general@localhost;
535
drop user test_general;
536
drop user test_super@localhost;
added
removed
mysql-test/suite/funcs_1/r/ndb_trig_08.result
