Debian Codegraph @

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

raw
1
2 --source suite/funcs_1/storedproc/load_sp_tb.inc
3 --------------------------------------------------------------------------------
4
5 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
6 --------------------------------------------------------------------------------
7 DROP DATABASE IF EXISTS db_storedproc;
8 DROP DATABASE IF EXISTS db_storedproc_1;
9 CREATE DATABASE db_storedproc;
10 CREATE DATABASE db_storedproc_1;
11 USE db_storedproc;
12 create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
13 engine = <engine_to_be_tested>;
14 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
15 create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
16 engine = <engine_to_be_tested>;
17 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
18 create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
19 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
20 create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
21 engine = <engine_to_be_tested>;
22 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
23 USE db_storedproc_1;
24 create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
25 engine = <engine_to_be_tested>;
26 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
27 USE db_storedproc;
28 create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
29 engine = <engine_to_be_tested>;
30 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7;
31 Warnings:
32 Warning 1265 Data truncated for column 'f3' at row 1
33 Warning 1265 Data truncated for column 'f3' at row 2
34 Warning 1265 Data truncated for column 'f3' at row 3
35 Warning 1265 Data truncated for column 'f3' at row 4
36 Warning 1265 Data truncated for column 'f3' at row 5
37 Warning 1265 Data truncated for column 'f3' at row 6
38 Warning 1265 Data truncated for column 'f3' at row 7
39 Warning 1265 Data truncated for column 'f3' at row 8
40 Warning 1265 Data truncated for column 'f3' at row 9
41 Warning 1265 Data truncated for column 'f3' at row 10
42 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
43 engine = <engine_to_be_tested>;
44 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8;
45 Warnings:
46 Warning 1265 Data truncated for column 'f3' at row 1
47 Warning 1265 Data truncated for column 'f3' at row 2
48 Warning 1265 Data truncated for column 'f3' at row 3
49 Warning 1265 Data truncated for column 'f3' at row 4
50 Warning 1265 Data truncated for column 'f3' at row 5
51 Warning 1265 Data truncated for column 'f3' at row 6
52 Warning 1265 Data truncated for column 'f3' at row 7
53 Warning 1265 Data truncated for column 'f3' at row 8
54 Warning 1265 Data truncated for column 'f3' at row 9
55 Warning 1265 Data truncated for column 'f3' at row 10
56 create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
57 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
58 create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
59 engine = <engine_to_be_tested>;
60 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
61 create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
62 engine = <engine_to_be_tested>;
63 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
64
65 Section 3.1.3 - Syntax checks for the stored procedure-specific flow
66 control statements IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE:
67 --------------------------------------------------------------------------------
68
69 Testcase 3.1.3.7:
70 -----------------
71
72 Ensure that the IF statement acts correctly for all variants, including cases
73 where statements are nested.
74 --------------------------------------------------------------------------------
75 DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
76 DROP PROCEDURE IF EXISTS sp9;
77 CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
78 CREATE PROCEDURE sp9( action char(20), subaction char(20) )
79 BEGIN
80 if action = 'action' then
81 if subaction = 'subaction' then
82 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction' , 1);
83 else
84 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 2);
85 END if;
86 else
87 if subaction = 'subaction'
88 then
89 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction' , 3);
90 elseif subaction = 'subaction1'
91 then
92 BEGIN
93 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values ('none', 'subaction1', 4);
94 END;
95 else
96 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 5);
97 END if;
98 END if;
99 END//
100 CALL sp9( 'action', 'subaction' );
101 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=1;
102 f1 f2 f3
103 action subaction 1
104 CALL sp9( 'temp', 'subaction' );
105 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=3;
106 f1 f2 f3
107 none subaction 3
108 CALL sp9( 'temp', 'subaction1' );
109 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=4;
110 f1 f2 f3
111 none subaction1 4
112 CALL sp9( 'action', 'temp' );
113 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=2;
114 f1 f2 f3
115 action none 2
116 CALL sp9( 'temp', 'temp' );
117 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=5;
118 f1 f2 f3
119 none none 5
120 DROP PROCEDURE sp9;
121 DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
122
123 Testcase 3.1.3.8.:
124 ------------------
125
126 Ensure that the CASE statement acts correctly for all variants, including cases
127 where statements are nested.
128 --------------------------------------------------------------------------------
129 drop table IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
130 DROP PROCEDURE IF EXISTS sp10;
131 create table res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 char(20), f2 varchar(20), f3 smallint);
132 CREATE PROCEDURE sp10( action char(20), subaction char(20) )
133 BEGIN
134 case action
135 when 'action' then
136 case
137 when subaction = 'subaction_1' then
138 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 1);
139 when subaction = 'subaction_2' then
140 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 2);
141 else
142 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 3);
143 END case;
144 else
145 case
146 when subaction = 'subaction_1' then
147 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_1' , 4);
148 when subaction = 'subaction_2' then
149 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_2' , 5);
150 else
151 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 6);
152 END case;
153 END case;
154 END//
155 CALL sp10( 'action', 'subaction_1' );
156 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
157 f1 f2 f3
158 action subaction_2 1
159 delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
160 CALL sp10( 'action', 'subaction_2' );
161 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
162 f1 f2 f3
163 action subaction_2 2
164 delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
165 CALL sp10( 'temp', 'subaction_1' );
166 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
167 f1 f2 f3
168 none subaction_1 4
169 delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
170 CALL sp10( 'temp', 'subaction_2' );
171 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
172 f1 f2 f3
173 none subaction_2 5
174 delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
175 CALL sp10( 'action', 'temp' );
176 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
177 f1 f2 f3
178 action none 3
179 delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
180 CALL sp10( 'temp', 'temp' );
181 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
182 f1 f2 f3
183 none none 6
184 DROP PROCEDURE sp10;
185 DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
186
187 Testcase 3.1.3.9 + 3.1.3.15:
188 ----------------------------
189
190 09. Ensure that the LOOP statement acts correctly for all variants, including
191 . cases where statements are nested.
192 15. Ensure that the LEAVE statement acts correctly for all variants, including
193 . cases where statements are nested.
194 --------------------------------------------------------------------------------
195 DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
196 DROP PROCEDURE IF EXISTS sp11;
197 CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
198 CREATE PROCEDURE sp11( )
199 BEGIN
200 declare count1 integer default 1;
201 declare count2 integer default 1;
202 label1: loop
203 if count2 > 3 then leave label1;
204 END if;
205 set count1 = 1;
206 label2: loop
207 if count1 > 4 then leave label2;
208 END if;
209 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
210 set count1 = count1 + 1;
211 iterate label2;
212 END loop label2;
213 set count2 = count2 + 1;
214 iterate label1;
215 END loop label1;
216 END//
217 CALL sp11();
218 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
219 f1 f2 f3
220 xyz pqr 1
221 xyz pqr 2
222 xyz pqr 3
223 xyz pqr 4
224 xyz pqr 1
225 xyz pqr 2
226 xyz pqr 3
227 xyz pqr 4
228 xyz pqr 1
229 xyz pqr 2
230 xyz pqr 3
231 xyz pqr 4
232 DROP PROCEDURE sp11;
233 DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
234
235 Testcase 3.1.3.16:
236 ------------------
237
238 Ensure that the ITERATE statement acts correctly for all variants, including
239 cases where statements are nested.
240 (tests for this testcase are also included in other testcases)
241 --------------------------------------------------------------------------------
242 DROP PROCEDURE IF EXISTS sp31316;
243 CREATE PROCEDURE sp31316( )
244 BEGIN
245 declare count1 integer default 1;
246 declare count2 integer default 1;
247 label1: loop
248 if count2 > 3 then leave label1;
249 END if;
250 set count1 = 1;
251 label2: loop
252 if count1 > 4 then leave label2;
253 END if;
254 insert into temp values( count1, count2);
255 set count1 = count1 + 1;
256 iterate label3;
257 END loop label2;
258 set count2 = count2 + 1;
259 iterate label1;
260 END loop label1;
261 END//
262 ERROR 42000: ITERATE with no matching label: label3
263
264 Testcase 3.1.3.18:
265 ------------------
266
267 Ensure that the REPEAT statement acts correctly for all variants, including
268 cases where statements are nested.
269 --------------------------------------------------------------------------------
270 DROP PROCEDURE IF EXISTS sp17;
271 DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
272 CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
273 CREATE PROCEDURE sp17( )
274 BEGIN
275 declare count1 integer default 1;
276 declare count2 integer default 1;
277 repeat
278 set count1 = count1 + 1;
279 set count2 = 1;
280 label1: repeat
281 set count2 = count2 + 1;
282 insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
283 until count2 > 3
284 END repeat label1;
285 until count1 > 3
286 END repeat;
287 END//
288 CALL sp17();
289 SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
290 f1 f2 f3
291 xyz pqr 2
292 xyz pqr 2
293 xyz pqr 2
294 xyz pqr 3
295 xyz pqr 3
296 xyz pqr 3
297 xyz pqr 4
298 xyz pqr 4
299 xyz pqr 4
300 DROP PROCEDURE sp17;
301 DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
302
303 Testcase 3.1.3.24:
304 ------------------
305
306 Ensure that the WHILE statement acts correctly for all variants, including cases
307 where statements are nested.
308 --------------------------------------------------------------------------------
309 drop table IF EXISTS res_t21;
310 DROP PROCEDURE IF EXISTS sp21;
311 create table res_t21(name text(10), surname blob(20), age_averylongfieldname_averylongname_1234569 smallint);
312 insert into res_t21 values('ashwin', 'mokadam', 25);
313 CREATE PROCEDURE sp21( )
314 BEGIN
315 declare count1 integer default 0;
316 declare count2 integer default 0;
317 while count1 < 3 do
318 BEGIN
319 declare ithisissamevariablename int default 100;
320 SELECT ithisissamevariablename;
321 BEGIN
322 declare ithisissamevariablename int default 200;
323 SELECT ithisissamevariablename;
324 END;
325 set count2 = 0;
326 label1: while count2 < 3 do
327 BEGIN
328 declare count1 integer default 7;
329 set count2 = count2 + 1;
330 insert into res_t21 values( 'xyz' , 'pqr', count2);
331 label2: while count1 < 10 do
332 set count1 = count1 + 1;
333 insert into res_t21 values( 'xyz' , 'pqr', count1);
334 END while label2;
335 END;
336 END while label1;
337 set count1 = count1 + 1;
338 END;
339 END while;
340 END//
341 CALL sp21();
342 ithisissamevariablename
343 100
344 ithisissamevariablename
345 200
346 ithisissamevariablename
347 100
348 ithisissamevariablename
349 200
350 ithisissamevariablename
351 100
352 ithisissamevariablename
353 200
354 SELECT * from res_t21;
355 name surname age_averylongfieldname_averylongname_1234569
356 ashwin mokadam 25
357 xyz pqr 1
358 xyz pqr 8
359 xyz pqr 9
360 xyz pqr 10
361 xyz pqr 2
362 xyz pqr 8
363 xyz pqr 9
364 xyz pqr 10
365 xyz pqr 3
366 xyz pqr 8
367 xyz pqr 9
368 xyz pqr 10
369 xyz pqr 1
370 xyz pqr 8
371 xyz pqr 9
372 xyz pqr 10
373 xyz pqr 2
374 xyz pqr 8
375 xyz pqr 9
376 xyz pqr 10
377 xyz pqr 3
378 xyz pqr 8
379 xyz pqr 9
380 xyz pqr 10
381 xyz pqr 1
382 xyz pqr 8
383 xyz pqr 9
384 xyz pqr 10
385 xyz pqr 2
386 xyz pqr 8
387 xyz pqr 9
388 xyz pqr 10
389 xyz pqr 3
390 xyz pqr 8
391 xyz pqr 9
392 xyz pqr 10
393 DROP PROCEDURE sp21;
394 drop table res_t21;
395
396 Testcase 3.1.3.30:
397 ------------------
398
399 Ensure that multiple cases of all possible combinations of the control flow
400 statements, nested within multiple compound statements within a stored
401 procedure, always act correctly and return the expected result.
402 --------------------------------------------------------------------------------
403 DROP TABLE IF EXISTS res_tbl;
404 DROP PROCEDURE IF EXISTS sp31330;
405 create table res_tbl (f1 int, f2 text, f3 blob, f4 date,
406 f5 set('one', 'two', 'three', 'four', 'five') default 'one');
407 CREATE PROCEDURE sp31330 (path int)
408 BEGIN
409 declare count int default 1;
410 declare var1 text;
411 declare var2 blob;
412 declare var3 date;
413 declare var4 set('one', 'two', 'three', 'four', 'five') DEFAULT 'five';
414 case
415 when path=1 then
416 set var3 = '2000-11-09';
417 set var1 = 'flowing through case 1';
418 label1: loop
419 if count > 5 then
420 if var4=1000 then
421 set var2 = 'exiting out of case 1 - invalid SET';
422 END if;
423 if var4='two' then
424 set var2 = 'exiting out of case 1';
425 END if;
426 insert into res_tbl values (1, var1, var2, var3, (count-2));
427 leave label1;
428 elseif count = 5 then
429 set count= count + 2;
430 set var4='two';
431 iterate label1;
432 else
433 set count= count + 1;
434 END if;
435 set var4='one';
436 END loop label1;
437 when path=2 then
438 set var3 = '1989-11-09';
439 set var1 = 'flowing through case 2';
440 set @count3=0;
441 label2: repeat
442 set count=count + 1;
443 set @count2=1;
444 while @count2 <= 5 do
445 set @count2 = @count2 + 1;
446 END while;
447 SELECT @count2;
448 set @count3=@count3 + @count2;
449 until count > 5
450 END repeat label2;
451 set var2 = 'exiting out of case 2';
452 set var4 = count-3;
453 SELECT @count3;
454 insert into res_tbl values (2, var1, var2, var3, var4);
455 ELSE BEGIN
456 set @error_opt='undefined path specified';
457 SELECT @error_opt;
458 END;
459 END case;
460 END//
461 CALL sp31330();
462 ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp31330; expected 1, got 0
463 CALL sp31330(1);
464 SELECT * from res_tbl;
465 f1 f2 f3 f4 f5
466 1 flowing through case 1 exiting out of case 1 2000-11-09 one,three
467 CALL sp31330(2);
468 @count2
469 6
470 @count2
471 6
472 @count2
473 6
474 @count2
475 6
476 @count2
477 6
478 @count3
479 30
480 SELECT * from res_tbl;
481 f1 f2 f3 f4 f5
482 1 flowing through case 1 exiting out of case 1 2000-11-09 one,three
483 2 flowing through case 2 exiting out of case 2 1989-11-09 one,two
484 CALL sp31330(4);
485 @error_opt
486 undefined path specified
487 DROP PROCEDURE sp31330;
488 drop table res_tbl;
489
490 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
491 --------------------------------------------------------------------------------
492 DROP DATABASE IF EXISTS db_storedproc;
493 DROP DATABASE IF EXISTS db_storedproc_1;
494
495 . +++ END OF SCRIPT +++
496 --------------------------------------------------------------------------------