Debian Codegraph @

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

raw
1 SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
2 USE test;
3 drop table if exists tb3;
4 create table 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',
8 f121 char(50),
9 f122 char(50),
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,
35 f154 decimal (0),
36 f155 decimal (64),
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,
43 f162 decimal (0,0),
44 f163 decimal (63,30),
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,
51 f170 numeric,
52 f171 numeric unsigned,
53 f172 numeric zerofill,
54 f173 numeric unsigned zerofill,
55 f174 numeric (0),
56 f175 numeric (64)
57 ) engine = <engine_to_be_used>;
58 Warnings:
59 Note 1265 Data truncated for column 'f150' at row 0
60 Note 1265 Data truncated for column 'f151' at row 0
61 Note 1265 Data truncated for column 'f152' at row 0
62 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/memory_tb3.txt'
63 into table tb3;
64
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;
86 f121 f122 f151 f163
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;
90 f121 f122 f151 f163
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';
95 f121 f122 f151 f163
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;
100 f121 f122 f151 f163
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;
104 f121 f122 f151 f163
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
107 set @test_var=0;
108 Select @test_var as 'before delete';
109 before delete
110 0
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;
114 f121 f122 f151 f163
115 Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
116 select f121, f122, f151, f163 from vw11;
117 f121 f122 f151 f163
118 Y Test 3.5.10.1/2/3-Update 2 1.000000000000000000000000000000
119 Select @test_var as 'after delete';
120 after delete
121 17
122 drop view vw11;
123 drop trigger trg1a;
124 drop trigger trg1b;
125 drop trigger trg1c;
126 drop trigger trg1d;
127 drop trigger trg1e;
128 drop trigger trg1f;
129 delete from tb3 where f122 like 'Test 3.5.10.1/2/3%';
130
131 Testcase 3.5.10.4:
132 ------------------
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;
136 set @counter= 0;
137 select @counter as 'Rows Loaded Before';
138 Rows Loaded Before
139 0
140 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table tb_load;
141 select @counter as 'Rows Loaded After';
142 Rows Loaded After
143 10
144 Select * from tb_load order by f1 limit 10;
145 f1 f2 f3
146 -5000 a` 1000
147 -4999 aaa 999
148 -4998 abaa 999
149 -4997 acaaa 999
150 -4996 adaaaa 999
151 -4995 aeaaaaa 999
152 -4994 afaaaaaa 998
153 -4993 agaaaaaaa 998
154 -4992 a^aaaaaaaa 998
155 -4991 a_aaaaaaaaa 998
156 drop trigger trg4;
157 drop table tb_load;
158
159 Testcase 3.5.10.5: (implemented in trig_frkey.test)
160 ---------------------------------------------------
161
162 Testcase 3.5.10.6: (implemented in trig_frkey.test)
163 ---------------------------------------------------
164
165 Testcase 3.5.10.extra:
166 ----------------------
167 set sql_mode = 'NO_ENGINE_SUBSTITUTION';
168 create table t1_sp (var136 tinyint, var151 decimal) engine = <engine_to_be_used>;
169 create trigger trg before insert on t1_sp
170 for each row set @counter=@counter+1;
171 create procedure trig_sp()
172 begin
173 declare done int default 0;
174 declare var151 decimal;
175 declare var136 tinyint;
176 declare cur1 cursor for select f136, f151 from tb3;
177 declare continue handler for sqlstate '01000' set done = 1;
178 open cur1;
179 fetch cur1 into var136, var151;
180 wl_loop: WHILE NOT done DO
181 insert into t1_sp values (var136, var151);
182 fetch cur1 into var136, var151;
183 END WHILE wl_loop;
184 close cur1;
185 end//
186 set @counter=0;
187 select @counter;
188 @counter
189 0
190 call trig_sp();
191 ERROR 02000: No data - zero rows fetched, selected, or processed
192 select @counter;
193 @counter
194 11
195 select count(*) from tb3;
196 count(*)
197 11
198 select count(*) from t1_sp;
199 count(*)
200 11
201 drop procedure trig_sp;
202 drop trigger trg;
203 drop table t1_sp;
204 set sql_mode = default;
205
206 Testcase 3.5.11.1 (implemented in trig_perf.test)
207 -------------------------------------------------
208
209 Testcase y.y.y.2: Check for triggers starting triggers
210 ------------------------------------------------------
211 use test;
212 drop table if exists t1;
213 drop table if exists t2_1;
214 drop table if exists t2_2;
215 drop table if exists t2_3;
216 drop table if exists t2_4;
217 drop table if exists t3;
218 create table t1 (f1 integer) engine = <engine_to_be_used>;
219 create table t2_1 (f1 integer) engine = <engine_to_be_used>;
220 create table t2_2 (f1 integer) engine = <engine_to_be_used>;
221 create table t2_3 (f1 integer) engine = <engine_to_be_used>;
222 create table t2_4 (f1 integer) engine = <engine_to_be_used>;
223 create table t3 (f1 integer) engine = <engine_to_be_used>;
224 insert into t1 values (1);
225 create trigger tr1 after insert on t1 for each row
226 BEGIN
227 insert into t2_1 (f1) values (new.f1+1);
228 insert into t2_2 (f1) values (new.f1+1);
229 insert into t2_3 (f1) values (new.f1+1);
230 insert into t2_4 (f1) values (new.f1+1);
231 END//
232 create trigger tr2_1 after insert on t2_1 for each row
233 insert into t3 (f1) values (new.f1+10);
234 create trigger tr2_2 after insert on t2_2 for each row
235 insert into t3 (f1) values (new.f1+100);
236 create trigger tr2_3 after insert on t2_3 for each row
237 insert into t3 (f1) values (new.f1+1000);
238 create trigger tr2_4 after insert on t2_4 for each row
239 insert into t3 (f1) values (new.f1+10000);
240 insert into t1 values (1);
241 select * from t3 order by f1;
242 f1
243 12
244 102
245 1002
246 10002
247 drop trigger tr1;
248 drop trigger tr2_1;
249 drop trigger tr2_2;
250 drop trigger tr2_3;
251 drop trigger tr2_4;
252 drop table t1, t2_1, t2_2, t2_3, t2_4, t3;
253
254 Testcase y.y.y.3: Circular trigger reference
255 --------------------------------------------
256 use test;
257 drop table if exists t1;
258 drop table if exists t2;
259 drop table if exists t3;
260 drop table if exists t4;
261 create table t1 (f1 integer) engine = <engine_to_be_used>;
262 create table t2 (f2 integer) engine = <engine_to_be_used>;
263 create table t3 (f3 integer) engine = <engine_to_be_used>;
264 create table t4 (f4 integer) engine = <engine_to_be_used>;
265 insert into t1 values (0);
266 create trigger tr1 after insert on t1
267 for each row insert into t2 (f2) values (new.f1+1);
268 create trigger tr2 after insert on t2
269 for each row insert into t3 (f3) values (new.f2+1);
270 create trigger tr3 after insert on t3
271 for each row insert into t4 (f4) values (new.f3+1);
272 create trigger tr4 after insert on t4
273 for each row insert into t1 (f1) values (new.f4+1);
274 insert into t1 values (1);
275 ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
276 select * from t1 order by f1;
277 f1
278 0
279 1
280 select * from t2 order by f2;
281 f2
282 2
283 select * from t3 order by f3;
284 f3
285 3
286 select * from t4 order by f4;
287 f4
288 4
289 drop trigger tr1;
290 drop trigger tr2;
291 drop trigger tr3;
292 drop trigger tr4;
293 drop table t1;
294 drop table t2;
295 drop table t3;
296 drop table t4;
297
298 Testcase y.y.y.4: Recursive trigger/SP references
299 -------------------------------------------------
300 set sql_mode = 'NO_ENGINE_SUBSTITUTION';
301 create table t1_sp (
302 count integer,
303 var136 tinyint,
304 var151 decimal) engine = <engine_to_be_used>;
305 create procedure trig_sp()
306 begin
307 declare done int default 0;
308 declare var151 decimal;
309 declare var136 tinyint;
310 declare cur1 cursor for select f136, f151 from tb3;
311 declare continue handler for sqlstate '01000' set done = 1;
312 set @counter= @counter+1;
313 open cur1;
314 fetch cur1 into var136, var151;
315 wl_loop: WHILE NOT done DO
316 insert into t1_sp values (@counter, var136, var151);
317 fetch cur1 into var136, var151;
318 END WHILE wl_loop;
319 close cur1;
320 end//
321 create trigger trg before insert on t1_sp
322 for each row call trig_sp();
323 set @counter=0;
324 select @counter;
325 @counter
326 0
327 call trig_sp();
328 ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine trig_sp
329 select @counter;
330 @counter
331 1
332 select count(*) from tb3;
333 count(*)
334 11
335 select count(*) from t1_sp;
336 count(*)
337 0
338 set @@max_sp_recursion_depth= 10;
339 set @counter=0;
340 select @counter;
341 @counter
342 0
343 call trig_sp();
344 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
345 select @counter;
346 @counter
347 2
348 select count(*) from tb3;
349 count(*)
350 11
351 select count(*) from t1_sp;
352 count(*)
353 0
354 drop procedure trig_sp;
355 drop trigger trg;
356 drop table t1_sp;
357 set sql_mode = default;
358
359 Testcase y.y.y.5: Rollback of nested trigger references
360 -------------------------------------------------------
361 set @@sql_mode='traditional';
362 use test;
363 drop table if exists t1;
364 drop table if exists t2;
365 drop table if exists t3;
366 drop table if exists t4;
367 create table t1 (f1 integer) engine = <engine_to_be_used>;
368 create table t2 (f2 integer) engine = <engine_to_be_used>;
369 create table t3 (f3 integer) engine = <engine_to_be_used>;
370 create table t4 (f4 tinyint) engine = <engine_to_be_used>;
371 show create table t1;
372 Table Create Table
373 t1 CREATE TABLE `t1` (
374 `f1` int(11) DEFAULT NULL
375 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
376 insert into t1 values (1);
377 create trigger tr1 after insert on t1
378 for each row insert into t2 (f2) values (new.f1+1);
379 create trigger tr2 after insert on t2
380 for each row insert into t3 (f3) values (new.f2+1);
381 create trigger tr3 after insert on t3
382 for each row insert into t4 (f4) values (new.f3+1000);
383 set autocommit=0;
384 start transaction;
385 insert into t1 values (1);
386 ERROR 22003: Out of range value for column 'f4' at row 1
387 commit;
388 select * from t1 order by f1;
389 f1
390 1
391 1
392 select * from t2 order by f2;
393 f2
394 2
395 select * from t3 order by f3;
396 f3
397 3
398 drop trigger tr1;
399 drop trigger tr2;
400 drop trigger tr3;
401 drop table t1;
402 drop table t2;
403 drop table t3;
404 drop table t4;
405 DROP TABLE test.tb3;