[tomato.git] / release / src / router / mysql / mysql-test / suite / funcs_1 / r / memory_trig_1011ext.result
| blob | bfd5b36069a9fbf130da71a4c4534be7f688c27b |
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 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'
63 into table tb3;
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%';
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;
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()
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;
204 Testcase 3.5.11.1 (implemented in trig_perf.test)
205 -------------------------------------------------
207 Testcase y.y.y.2: Check for triggers starting triggers
208 ------------------------------------------------------
209 use test;
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
224 BEGIN
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);
229 END//
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;
240 f1
241 12
242 102
243 1002
244 10002
245 drop trigger tr1;
246 drop trigger tr2_1;
247 drop trigger tr2_2;
248 drop trigger tr2_3;
249 drop trigger tr2_4;
250 drop table t1, t2_1, t2_2, t2_3, t2_4, t3;
252 Testcase y.y.y.3: Circular trigger reference
253 --------------------------------------------
254 use test;
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;
275 f1
276 0
277 1
278 select * from t2 order by f2;
279 f2
280 2
281 select * from t3 order by f3;
282 f3
283 3
284 select * from t4 order by f4;
285 f4
286 4
287 drop trigger tr1;
288 drop trigger tr2;
289 drop trigger tr3;
290 drop trigger tr4;
291 drop table t1;
292 drop table t2;
293 drop table t3;
294 drop table t4;
296 Testcase y.y.y.4: Recursive trigger/SP references
297 -------------------------------------------------
298 set @sql_mode='traditional';
299 create table t1_sp (
300 count integer,
301 var136 tinyint,
302 var151 decimal) engine = <engine_to_be_used>;
303 create procedure trig_sp()
304 begin
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;
311 open cur1;
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;
316 END WHILE wl_loop;
317 close cur1;
318 end//
319 create trigger trg before insert on t1_sp
320 for each row call trig_sp();
321 set @counter=0;
322 select @counter;
323 @counter
324 0
325 call trig_sp();
326 ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine trig_sp
327 select @counter;
328 @counter
329 1
330 select count(*) from tb3;
331 count(*)
332 11
333 select count(*) from t1_sp;
334 count(*)
335 0
336 set @@max_sp_recursion_depth= 10;
337 set @counter=0;
338 select @counter;
339 @counter
340 0
341 call trig_sp();
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.
343 select @counter;
344 @counter
345 2
346 select count(*) from tb3;
347 count(*)
348 11
349 select count(*) from t1_sp;
350 count(*)
351 0
352 drop procedure trig_sp;
353 drop trigger trg;
354 drop table t1_sp;
356 Testcase y.y.y.5: Rollback of nested trigger references
357 -------------------------------------------------------
358 set @@sql_mode='traditional';
359 use test;
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;
369 Table Create Table
370 t1 CREATE TABLE `t1` (
371 `f1` int(11) DEFAULT NULL
372 ) ENGINE=MEMORY 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);
380 set autocommit=0;
381 start transaction;
382 insert into t1 values (1);
383 ERROR 22003: Out of range value for column 'f4' at row 1
384 commit;
385 select * from t1 order by f1;
386 f1
387 1
388 1
389 select * from t2 order by f2;
390 f2
391 2
392 select * from t3 order by f3;
393 f3
394 3
395 drop trigger tr1;
396 drop trigger tr2;
397 drop trigger tr3;
398 drop table t1;
399 drop table t2;
400 drop table t3;
401 drop table t4;
402 DROP TABLE test.tb3;