1
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
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',
10
f129 binary not null DEFAULT b'101',
11
f130 tinyint not null DEFAULT 99,
12
f131 tinyint unsigned not null DEFAULT 99,
13
f132 tinyint zerofill not null DEFAULT 99,
14
f133 tinyint unsigned zerofill not null DEFAULT 99,
15
f134 smallint not null DEFAULT 999,
16
f135 smallint unsigned not null DEFAULT 999,
17
f136 smallint zerofill not null DEFAULT 999,
18
f137 smallint unsigned zerofill not null DEFAULT 999,
19
f138 mediumint not null DEFAULT 9999,
20
f139 mediumint unsigned not null DEFAULT 9999,
21
f140 mediumint zerofill not null DEFAULT 9999,
22
f141 mediumint unsigned zerofill not null DEFAULT 9999,
23
f142 int not null DEFAULT 99999,
24
f143 int unsigned not null DEFAULT 99999,
25
f144 int zerofill not null DEFAULT 99999,
26
f145 int unsigned zerofill not null DEFAULT 99999,
27
f146 bigint not null DEFAULT 999999,
28
f147 bigint unsigned not null DEFAULT 999999,
29
f148 bigint zerofill not null DEFAULT 999999,
30
f149 bigint unsigned zerofill not null DEFAULT 999999,
31
f150 decimal not null DEFAULT 999.999,
32
f151 decimal unsigned not null DEFAULT 999.17,
33
f152 decimal zerofill not null DEFAULT 999.999,
34
f153 decimal unsigned zerofill,
37
f156 decimal (0) unsigned,
38
f157 decimal (64) unsigned,
39
f158 decimal (0) zerofill,
40
f159 decimal (64) zerofill,
41
f160 decimal (0) unsigned zerofill,
42
f161 decimal (64) unsigned zerofill,
45
f164 decimal (0,0) unsigned,
46
f165 decimal (63,30) unsigned,
47
f166 decimal (0,0) zerofill,
48
f167 decimal (63,30) zerofill,
49
f168 decimal (0,0) unsigned zerofill,
50
f169 decimal (63,30) unsigned zerofill,
52
f171 numeric unsigned,
53
f172 numeric zerofill,
54
f173 numeric unsigned zerofill,
57
) engine = <engine_to_be_used>;
59
Note 1265 Data truncated for column 'f150' at row 1
60
Note 1265 Data truncated for column 'f151' at row 1
61
Note 1265 Data truncated for column 'f152' at row 1
62
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/memory_tb3.txt'
65
Testcase 3.5.10.1/2/3:
66
----------------------
67
Create view vw11 as select * from tb3
68
where f122 like 'Test 3.5.10.1/2/3%';
69
Create trigger trg1a before insert on tb3
70
for each row set new.f163=111.11;
71
Create trigger trg1b after insert on tb3
72
for each row set @test_var='After Insert';
73
Create trigger trg1c before update on tb3
74
for each row set new.f121='Y', new.f122='Test 3.5.10.1/2/3-Update';
75
Create trigger trg1d after update on tb3
76
for each row set @test_var='After Update';
77
Create trigger trg1e before delete on tb3
78
for each row set @test_var=5;
79
Create trigger trg1f after delete on tb3
80
for each row set @test_var= 2* @test_var+7;
81
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 1);
82
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 2);
83
Insert into vw11 (f122, f151) values ('Not in View', 3);
84
select f121, f122, f151, f163
85
from tb3 where f122 like 'Test 3.5.10.1/2/3%' order by f151;
87
NULL Test 3.5.10.1/2/3 1 111.110000000000000000000000000000
88
NULL Test 3.5.10.1/2/3 2 111.110000000000000000000000000000
89
select f121, f122, f151, f163 from vw11;
91
NULL Test 3.5.10.1/2/3 1 111.110000000000000000000000000000
92
NULL Test 3.5.10.1/2/3 2 111.110000000000000000000000000000
93
select f121, f122, f151, f163
94
from tb3 where f122 like 'Not in View';
96
NULL Not in View 3 111.110000000000000000000000000000
97
Update vw11 set f163=1;
98
select f121, f122, f151, f163 from tb3
99
where f122 like 'Test 3.5.10.1/2/3%' order by f151;
101
Y Test 3.5.10.1/2/3-Update 1 1.000000000000000000000000000000
102
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
103
select f121, f122, f151, f163 from vw11;
105
Y Test 3.5.10.1/2/3-Update 1 1.000000000000000000000000000000
106
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
108
Select @test_var as 'before delete';
111
delete from vw11 where f151=1;
112
select f121, f122, f151, f163 from tb3
113
where f122 like 'Test 3.5.10.1/2/3%' order by f151;
115
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
116
select f121, f122, f151, f163 from vw11;
118
Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
119
Select @test_var as 'after delete';
129
delete from tb3 where f122 like 'Test 3.5.10.1/2/3%';
133
create table tb_load (f1 int, f2 char(25),f3 int) engine = <engine_to_be_used>;
134
Create trigger trg4 before insert on tb_load
135
for each row set new.f3=-(new.f1 div 5), @counter= @counter+1;
137
select @counter as 'Rows Loaded Before';
140
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table tb_load;
141
select @counter as 'Rows Loaded After';
144
Select * from tb_load order by f1 limit 10;
155
-4991 a_aaaaaaaaa 998
159
Testcase 3.5.10.5: (implemented in trig_frkey.test)
160
---------------------------------------------------
162
Testcase 3.5.10.6: (implemented in trig_frkey.test)
163
---------------------------------------------------
165
Testcase 3.5.10.extra:
166
----------------------
167
create table t1_sp (var136 tinyint, var151 decimal) engine = <engine_to_be_used>;
168
create trigger trg before insert on t1_sp
169
for each row set @counter=@counter+1;
170
create procedure trig_sp()
172
declare done int default 0;
173
declare var151 decimal;
174
declare var136 tinyint;
175
declare cur1 cursor for select f136, f151 from tb3;
176
declare continue handler for sqlstate '01000' set done = 1;
178
fetch cur1 into var136, var151;
179
wl_loop: WHILE NOT done DO
180
insert into t1_sp values (var136, var151);
181
fetch cur1 into var136, var151;
190
ERROR 02000: No data - zero rows fetched, selected, or processed
194
select count(*) from tb3;
197
select count(*) from t1_sp;
200
drop procedure trig_sp;
204
Testcase 3.5.11.1 (implemented in trig_perf.test)
205
-------------------------------------------------
207
Testcase y.y.y.2: Check for triggers starting triggers
208
------------------------------------------------------
210
drop table if exists t1;
211
drop table if exists t2_1;
212
drop table if exists t2_2;
213
drop table if exists t2_3;
214
drop table if exists t2_4;
215
drop table if exists t3;
216
create table t1 (f1 integer) engine = <engine_to_be_used>;
217
create table t2_1 (f1 integer) engine = <engine_to_be_used>;
218
create table t2_2 (f1 integer) engine = <engine_to_be_used>;
219
create table t2_3 (f1 integer) engine = <engine_to_be_used>;
220
create table t2_4 (f1 integer) engine = <engine_to_be_used>;
221
create table t3 (f1 integer) engine = <engine_to_be_used>;
222
insert into t1 values (1);
223
create trigger tr1 after insert on t1 for each row
225
insert into t2_1 (f1) values (new.f1+1);
226
insert into t2_2 (f1) values (new.f1+1);
227
insert into t2_3 (f1) values (new.f1+1);
228
insert into t2_4 (f1) values (new.f1+1);
230
create trigger tr2_1 after insert on t2_1 for each row
231
insert into t3 (f1) values (new.f1+10);
232
create trigger tr2_2 after insert on t2_2 for each row
233
insert into t3 (f1) values (new.f1+100);
234
create trigger tr2_3 after insert on t2_3 for each row
235
insert into t3 (f1) values (new.f1+1000);
236
create trigger tr2_4 after insert on t2_4 for each row
237
insert into t3 (f1) values (new.f1+10000);
238
insert into t1 values (1);
239
select * from t3 order by f1;
250
drop table t1, t2_1, t2_2, t2_3, t2_4, t3;
252
Testcase y.y.y.3: Circular trigger reference
253
--------------------------------------------
255
drop table if exists t1;
256
drop table if exists t2;
257
drop table if exists t3;
258
drop table if exists t4;
259
create table t1 (f1 integer) engine = <engine_to_be_used>;
260
create table t2 (f2 integer) engine = <engine_to_be_used>;
261
create table t3 (f3 integer) engine = <engine_to_be_used>;
262
create table t4 (f4 integer) engine = <engine_to_be_used>;
263
insert into t1 values (0);
264
create trigger tr1 after insert on t1
265
for each row insert into t2 (f2) values (new.f1+1);
266
create trigger tr2 after insert on t2
267
for each row insert into t3 (f3) values (new.f2+1);
268
create trigger tr3 after insert on t3
269
for each row insert into t4 (f4) values (new.f3+1);
270
create trigger tr4 after insert on t4
271
for each row insert into t1 (f1) values (new.f4+1);
272
insert into t1 values (1);
273
ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
274
select * from t1 order by f1;
278
select * from t2 order by f2;
281
select * from t3 order by f3;
284
select * from t4 order by f4;
296
Testcase y.y.y.4: Recursive trigger/SP references
297
-------------------------------------------------
298
set @sql_mode='traditional';
302
var151 decimal) engine = <engine_to_be_used>;
303
create procedure trig_sp()
305
declare done int default 0;
306
declare var151 decimal;
307
declare var136 tinyint;
308
declare cur1 cursor for select f136, f151 from tb3;
309
declare continue handler for sqlstate '01000' set done = 1;
310
set @counter= @counter+1;
312
fetch cur1 into var136, var151;
313
wl_loop: WHILE NOT done DO
314
insert into t1_sp values (@counter, var136, var151);
315
fetch cur1 into var136, var151;
319
create trigger trg before insert on t1_sp
320
for each row call trig_sp();
326
ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine trig_sp
330
select count(*) from tb3;
333
select count(*) from t1_sp;
336
set @@max_sp_recursion_depth= 10;
342
ERROR HY000: Can't update table 't1_sp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
346
select count(*) from tb3;
349
select count(*) from t1_sp;
352
drop procedure trig_sp;
356
Testcase y.y.y.5: Rollback of nested trigger references
357
-------------------------------------------------------
358
set @@sql_mode='traditional';
360
drop table if exists t1;
361
drop table if exists t2;
362
drop table if exists t3;
363
drop table if exists t4;
364
create table t1 (f1 integer) engine = <engine_to_be_used>;
365
create table t2 (f2 integer) engine = <engine_to_be_used>;
366
create table t3 (f3 integer) engine = <engine_to_be_used>;
367
create table t4 (f4 tinyint) engine = <engine_to_be_used>;
368
show create table t1;
370
t1 CREATE TABLE `t1` (
371
`f1` int(11) DEFAULT NULL
372
) ENGINE=MyISAM DEFAULT CHARSET=latin1
373
insert into t1 values (1);
374
create trigger tr1 after insert on t1
375
for each row insert into t2 (f2) values (new.f1+1);
376
create trigger tr2 after insert on t2
377
for each row insert into t3 (f3) values (new.f2+1);
378
create trigger tr3 after insert on t3
379
for each row insert into t4 (f4) values (new.f3+1000);
382
insert into t1 values (1);
383
ERROR 22003: Out of range value for column 'f4' at row 1
385
select * from t1 order by f1;
389
select * from t2 order by f2;
392
select * from t3 order by f3;