Debian Codegraph @

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

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