Debian Codegraph @

mysql-test/suite/funcs_1/storedproc/storedproc_03.inc

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