Debian Codegraph @

mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result

raw
1 USE test;
2 drop table if exists tb3;
3 create table 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',
7 f121 char(50),
8 f122 char(50),
9 f129 binary not null DEFAULT b'101',
10 f130 tinyint not null DEFAULT 99,
11 f131 tinyint unsigned not null DEFAULT 99,
12 f132 tinyint zerofill not null DEFAULT 99,
13 f133 tinyint unsigned zerofill not null DEFAULT 99,
14 f134 smallint not null DEFAULT 999,
15 f135 smallint unsigned not null DEFAULT 999,
16 f136 smallint zerofill not null DEFAULT 999,
17 f137 smallint unsigned zerofill not null DEFAULT 999,
18 f138 mediumint not null DEFAULT 9999,
19 f139 mediumint unsigned not null DEFAULT 9999,
20 f140 mediumint zerofill not null DEFAULT 9999,
21 f141 mediumint unsigned zerofill not null DEFAULT 9999,
22 f142 int not null DEFAULT 99999,
23 f143 int unsigned not null DEFAULT 99999,
24 f144 int zerofill not null DEFAULT 99999,
25 f145 int unsigned zerofill not null DEFAULT 99999,
26 f146 bigint not null DEFAULT 999999,
27 f147 bigint unsigned not null DEFAULT 999999,
28 f148 bigint zerofill not null DEFAULT 999999,
29 f149 bigint unsigned zerofill not null DEFAULT 999999,
30 f150 decimal not null DEFAULT 999.999,
31 f151 decimal unsigned not null DEFAULT 999.17,
32 f152 decimal zerofill not null DEFAULT 999.999,
33 f153 decimal unsigned zerofill,
34 f154 decimal (0),
35 f155 decimal (64),
36 f156 decimal (0) unsigned,
37 f157 decimal (64) unsigned,
38 f158 decimal (0) zerofill,
39 f159 decimal (64) zerofill,
40 f160 decimal (0) unsigned zerofill,
41 f161 decimal (64) unsigned zerofill,
42 f162 decimal (0,0),
43 f163 decimal (63,30),
44 f164 decimal (0,0) unsigned,
45 f165 decimal (63,30) unsigned,
46 f166 decimal (0,0) zerofill,
47 f167 decimal (63,30) zerofill,
48 f168 decimal (0,0) unsigned zerofill,
49 f169 decimal (63,30) unsigned zerofill,
50 f170 numeric,
51 f171 numeric unsigned,
52 f172 numeric zerofill,
53 f173 numeric unsigned zerofill,
54 f174 numeric (0),
55 f175 numeric (64)
56 ) engine = <engine_to_be_used>;
57 Warnings:
58 Note 1265 Data truncated for column 'f150' at row 0
59 Note 1265 Data truncated for column 'f151' at row 0
60 Note 1265 Data truncated for column 'f152' at row 0
61 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/memory_tb3.txt'
62 into table tb3;
63
64 Testcase 3.5.10.1/2/3:
65 ----------------------
66 Create view vw11 as select * from tb3
67 where f122 like 'Test 3.5.10.1/2/3%';
68 Create trigger trg1a before insert on tb3
69 for each row set new.f163=111.11;
70 Create trigger trg1b after insert on tb3
71 for each row set @test_var='After Insert';
72 Create trigger trg1c before update on tb3
73 for each row set new.f121='Y', new.f122='Test 3.5.10.1/2/3-Update';
74 Create trigger trg1d after update on tb3
75 for each row set @test_var='After Update';
76 Create trigger trg1e before delete on tb3
77 for each row set @test_var=5;
78 Create trigger trg1f after delete on tb3
79 for each row set @test_var= 2* @test_var+7;
80 Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 1);
81 Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 2);
82 Insert into vw11 (f122, f151) values ('Not in View', 3);
83 select f121, f122, f151, f163
84 from tb3 where f122 like 'Test 3.5.10.1/2/3%' order by f151;
85 f121 f122 f151 f163
86 NULL Test 3.5.10.1/2/3 1 111.110000000000000000000000000000
87 NULL Test 3.5.10.1/2/3 2 111.110000000000000000000000000000
88 select f121, f122, f151, f163 from vw11;
89 f121 f122 f151 f163
90 NULL Test 3.5.10.1/2/3 1 111.110000000000000000000000000000
91 NULL Test 3.5.10.1/2/3 2 111.110000000000000000000000000000
92 select f121, f122, f151, f163
93 from tb3 where f122 like 'Not in View';
94 f121 f122 f151 f163
95 NULL Not in View 3 111.110000000000000000000000000000
96 Update vw11 set f163=1;
97 select f121, f122, f151, f163 from tb3
98 where f122 like 'Test 3.5.10.1/2/3%' order by f151;
99 f121 f122 f151 f163
100 Y Test 3.5.10.1/2/3-Update 1 1.000000000000000000000000000000
101 Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
102 select f121, f122, f151, f163 from vw11;
103 f121 f122 f151 f163
104 Y Test 3.5.10.1/2/3-Update 1 1.000000000000000000000000000000
105 Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
106 set @test_var=0;
107 Select @test_var as 'before delete';
108 before delete
109 0
110 delete from vw11 where f151=1;
111 select f121, f122, f151, f163 from tb3
112 where f122 like 'Test 3.5.10.1/2/3%' order by f151;
113 f121 f122 f151 f163
114 Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
115 select f121, f122, f151, f163 from vw11;
116 f121 f122 f151 f163
117 Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
118 Select @test_var as 'after delete';
119 after delete
120 17
121 drop view vw11;
122 drop trigger trg1a;
123 drop trigger trg1b;
124 drop trigger trg1c;
125 drop trigger trg1d;
126 drop trigger trg1e;
127 drop trigger trg1f;
128 delete from tb3 where f122 like 'Test 3.5.10.1/2/3%';
129
130 Testcase 3.5.10.4:
131 ------------------
132 create table tb_load (f1 int, f2 char(25),f3 int) engine = <engine_to_be_used>;
133 Create trigger trg4 before insert on tb_load
134 for each row set new.f3=-(new.f1 div 5), @counter= @counter+1;
135 set @counter= 0;
136 select @counter as 'Rows Loaded Before';
137 Rows Loaded Before
138 0
139 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table tb_load;
140 select @counter as 'Rows Loaded After';
141 Rows Loaded After
142 10
143 Select * from tb_load order by f1 limit 10;
144 f1 f2 f3
145 -5000 a` 1000
146 -4999 aaa 999
147 -4998 abaa 999
148 -4997 acaaa 999
149 -4996 adaaaa 999
150 -4995 aeaaaaa 999
151 -4994 afaaaaaa 998
152 -4993 agaaaaaaa 998
153 -4992 a^aaaaaaaa 998
154 -4991 a_aaaaaaaaa 998
155 drop trigger trg4;
156 drop table tb_load;
157
158 Testcase 3.5.10.5: (implemented in trig_frkey.test)
159 ---------------------------------------------------
160
161 Testcase 3.5.10.6: (implemented in trig_frkey.test)
162 ---------------------------------------------------
163
164 Testcase 3.5.10.extra:
165 ----------------------
166 set sql_mode = 'NO_ENGINE_SUBSTITUTION';
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()
171 begin
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;
177 open cur1;
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;
182 END WHILE wl_loop;
183 close cur1;
184 end//
185 set @counter=0;
186 select @counter;
187 @counter
188 0
189 call trig_sp();
190 ERROR 02000: No data - zero rows fetched, selected, or processed
191 select @counter;
192 @counter
193 11
194 select count(*) from tb3;
195 count(*)
196 11
197 select count(*) from t1_sp;
198 count(*)
199 11
200 drop procedure trig_sp;
201 drop trigger trg;
202 drop table t1_sp;
203 set sql_mode = default;
204
205 Testcase 3.5.11.1 (implemented in trig_perf.test)
206 -------------------------------------------------
207
208 Testcase y.y.y.2: Check for triggers starting triggers
209 ------------------------------------------------------
210 use test;
211 drop table if exists t1;
212 drop table if exists t2_1;
213 drop table if exists t2_2;
214 drop table if exists t2_3;
215 drop table if exists t2_4;
216 drop table if exists t3;
217 create table t1 (f1 integer) engine = <engine_to_be_used>;
218 create table t2_1 (f1 integer) engine = <engine_to_be_used>;
219 create table t2_2 (f1 integer) engine = <engine_to_be_used>;
220 create table t2_3 (f1 integer) engine = <engine_to_be_used>;
221 create table t2_4 (f1 integer) engine = <engine_to_be_used>;
222 create table t3 (f1 integer) engine = <engine_to_be_used>;
223 insert into t1 values (1);
224 create trigger tr1 after insert on t1 for each row
225 BEGIN
226 insert into t2_1 (f1) values (new.f1+1);
227 insert into t2_2 (f1) values (new.f1+1);
228 insert into t2_3 (f1) values (new.f1+1);
229 insert into t2_4 (f1) values (new.f1+1);
230 END//
231 create trigger tr2_1 after insert on t2_1 for each row
232 insert into t3 (f1) values (new.f1+10);
233 create trigger tr2_2 after insert on t2_2 for each row
234 insert into t3 (f1) values (new.f1+100);
235 create trigger tr2_3 after insert on t2_3 for each row
236 insert into t3 (f1) values (new.f1+1000);
237 create trigger tr2_4 after insert on t2_4 for each row
238 insert into t3 (f1) values (new.f1+10000);
239 insert into t1 values (1);
240 select * from t3 order by f1;
241 f1
242 12
243 102
244 1002
245 10002
246 drop trigger tr1;
247 drop trigger tr2_1;
248 drop trigger tr2_2;
249 drop trigger tr2_3;
250 drop trigger tr2_4;
251 drop table t1, t2_1, t2_2, t2_3, t2_4, t3;
252
253 Testcase y.y.y.3: Circular trigger reference
254 --------------------------------------------
255 use test;
256 drop table if exists t1;
257 drop table if exists t2;
258 drop table if exists t3;
259 drop table if exists t4;
260 create table t1 (f1 integer) engine = <engine_to_be_used>;
261 create table t2 (f2 integer) engine = <engine_to_be_used>;
262 create table t3 (f3 integer) engine = <engine_to_be_used>;
263 create table t4 (f4 integer) engine = <engine_to_be_used>;
264 insert into t1 values (0);
265 create trigger tr1 after insert on t1
266 for each row insert into t2 (f2) values (new.f1+1);
267 create trigger tr2 after insert on t2
268 for each row insert into t3 (f3) values (new.f2+1);
269 create trigger tr3 after insert on t3
270 for each row insert into t4 (f4) values (new.f3+1);
271 create trigger tr4 after insert on t4
272 for each row insert into t1 (f1) values (new.f4+1);
273 insert into t1 values (1);
274 ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
275 select * from t1 order by f1;
276 f1
277 0
278 select * from t2 order by f2;
279 f2
280 select * from t3 order by f3;
281 f3
282 select * from t4 order by f4;
283 f4
284 drop trigger tr1;
285 drop trigger tr2;
286 drop trigger tr3;
287 drop trigger tr4;
288 drop table t1;
289 drop table t2;
290 drop table t3;
291 drop table t4;
292
293 Testcase y.y.y.4: Recursive trigger/SP references
294 -------------------------------------------------
295 set sql_mode = 'NO_ENGINE_SUBSTITUTION';
296 create table t1_sp (
297 count integer,
298 var136 tinyint,
299 var151 decimal) engine = <engine_to_be_used>;
300 create procedure trig_sp()
301 begin
302 declare done int default 0;
303 declare var151 decimal;
304 declare var136 tinyint;
305 declare cur1 cursor for select f136, f151 from tb3;
306 declare continue handler for sqlstate '01000' set done = 1;
307 set @counter= @counter+1;
308 open cur1;
309 fetch cur1 into var136, var151;
310 wl_loop: WHILE NOT done DO
311 insert into t1_sp values (@counter, var136, var151);
312 fetch cur1 into var136, var151;
313 END WHILE wl_loop;
314 close cur1;
315 end//
316 create trigger trg before insert on t1_sp
317 for each row call trig_sp();
318 set @counter=0;
319 select @counter;
320 @counter
321 0
322 call trig_sp();
323 ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine trig_sp
324 select @counter;
325 @counter
326 1
327 select count(*) from tb3;
328 count(*)
329 11
330 select count(*) from t1_sp;
331 count(*)
332 0
333 set @@max_sp_recursion_depth= 10;
334 set @counter=0;
335 select @counter;
336 @counter
337 0
338 call trig_sp();
339 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
340 select @counter;
341 @counter
342 2
343 select count(*) from tb3;
344 count(*)
345 11
346 select count(*) from t1_sp;
347 count(*)
348 0
349 drop procedure trig_sp;
350 drop trigger trg;
351 drop table t1_sp;
352 set sql_mode = default;
353
354 Testcase y.y.y.5: Rollback of nested trigger references
355 -------------------------------------------------------
356 set @@sql_mode='traditional';
357 use test;
358 drop table if exists t1;
359 drop table if exists t2;
360 drop table if exists t3;
361 drop table if exists t4;
362 create table t1 (f1 integer) engine = <engine_to_be_used>;
363 create table t2 (f2 integer) engine = <engine_to_be_used>;
364 create table t3 (f3 integer) engine = <engine_to_be_used>;
365 create table t4 (f4 tinyint) engine = <engine_to_be_used>;
366 show create table t1;
367 Table Create Table
368 t1 CREATE TABLE `t1` (
369 `f1` int(11) DEFAULT NULL
370 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
371 insert into t1 values (1);
372 create trigger tr1 after insert on t1
373 for each row insert into t2 (f2) values (new.f1+1);
374 create trigger tr2 after insert on t2
375 for each row insert into t3 (f3) values (new.f2+1);
376 create trigger tr3 after insert on t3
377 for each row insert into t4 (f4) values (new.f3+1000);
378 set autocommit=0;
379 start transaction;
380 insert into t1 values (1);
381 ERROR 22003: Out of range value for column 'f4' at row 1
382 commit;
383 select * from t1 order by f1;
384 f1
385 1
386 select * from t2 order by f2;
387 f2
388 select * from t3 order by f3;
389 f3
390 drop trigger tr1;
391 drop trigger tr2;
392 drop trigger tr3;
393 drop table t1;
394 drop table t2;
395 drop table t3;
396 drop table t4;
397 DROP TABLE test.tb3;