1
#### suite/funcs_1/storedproc/storedproc_03.inc
3
--source suite/funcs_1/storedproc/load_sp_tb.inc
5
# ==============================================================================
6
# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
8
# 3.1.3 Syntax checks for the stored procedure-specific flow control statements IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE:
10
#- 1. Ensure that all subclauses that should be supported are supported.
11
#- 2. Ensure that all subclauses that should not be supported are disallowed with an appropriate error message.
12
#- 3. Ensure that all supported subclauses are supported only in the correct order.
13
#- 4. Ensure that an appropriate error message is returned if a subclause is out-of-order in a stored procedure definition.
14
#- 5. Ensure that all subclauses that are defined to be mandatory are indeed required to be mandatory by the MySQL server and tools.
15
#- 6. Ensure that any subclauses that are defined to be optional are indeed treated as optional by the MySQL server and tools.
16
## 7. Ensure that the IF statement acts correctly for all variants, including cases where statements are nested.
17
## 8. Ensure that the CASE statement acts correctly for all variants, including cases where statements are nested.
18
## 9. Ensure that the LOOP statement acts correctly for all variants, including cases where statements are nested.
19
#- 10. Ensure that the labels enclosing each LOOP statement must match.
20
#- 11. Ensure that it is possible to put a beginning label at the start of a LOOP statement without also requiring an ending label at the end of the same statement.
21
#- 12. Ensure that it is not possible to put an ending label at the end of a LOOP statement without also requiring a matching beginning label at the start of the same statement.
22
#- 13. Ensure that every beginning label must end with a colon (:).
23
#- 14. Ensure that every beginning label with the same scope must be unique.
24
## 15. Ensure that the LEAVE statement acts correctly for all variants, including cases where statements are nested.
25
## 16. Ensure that the ITERATE statement acts correctly for all variants, including cases where statements are nested.
26
#- 17. Ensure that the ITERATE statement fails, with an appropriate error message, if it appears in any context other than within LOOP, REPEAT, or WHILE statements.
27
## 18. Ensure that the REPEAT statement acts correctly for all variants, including cases where statements are nested.
28
#- 19. Ensure that the labels enclosing each REPEAT statement must match.
29
#- 20. Ensure that it is possible to put a beginning label at the start of a REPEAT statement without also requiring an ending label at the end of the same statement.
30
#- 21. Ensure that it is not possible to put an ending label at the end of a REPEAT statement without also requiring a matching beginning label at the start of the same statement.
31
#- 22. Ensure that every beginning label must end with a colon (:).
32
#- 23. Ensure that every beginning label with the same scope must be unique.
33
## 24. Ensure that the WHILE statement acts correctly for all variants, including cases where statements are nested.
34
#- 25. Ensure that the labels enclosing each WHILE statement must match.
35
#- 26. Ensure that it is possible to put a beginning label at the start of a WHILE statement without also requiring an ending label at the end of the same statement.
36
#- 27. Ensure that it is not possible to put an ending label at the end of a WHILE statement without also requiring a matching beginning label at the start of the same statement.
37
#- 28. Ensure that every beginning label must end with a colon (:).
38
#- 29. Ensure that every beginning label with the same scope must be unique.
39
## 30. Ensure that multiple cases of all possible combinations of the control flow statements, nested within multiple compound statements within a stored procedure, always act correctly and return the expected result.
41
# ==============================================================================
42
let $message= Section 3.1.3 - Syntax checks for the stored procedure-specific flow
43
control statements IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE:;
44
--source include/show_msg80.inc
46
#FIXME # 3.1.3: enhance syntax checks with very complicated checks
48
# ------------------------------------------------------------------------------
49
let $message= Testcase 3.1.3.7:;
50
--source include/show_msg.inc
52
Ensure that the IF statement acts correctly for all variants, including cases
53
where statements are nested.;
54
--source include/show_msg80.inc
57
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
58
DROP PROCEDURE IF EXISTS sp9;
61
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
64
CREATE PROCEDURE sp9( action char(20), subaction char(20) )
66
if action = 'action' then
67
if subaction = 'subaction' then
68
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction' , 1);
70
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 2);
73
if subaction = 'subaction'
75
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction' , 3);
76
elseif subaction = 'subaction1'
79
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values ('none', 'subaction1', 4);
82
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 5);
88
CALL sp9( 'action', 'subaction' );
89
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=1;
91
CALL sp9( 'temp', 'subaction' );
92
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=3;
94
CALL sp9( 'temp', 'subaction1' );
95
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=4;
97
CALL sp9( 'action', 'temp' );
98
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=2;
100
CALL sp9( 'temp', 'temp' );
101
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=5;
105
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
108
# ------------------------------------------------------------------------------
109
let $message= Testcase 3.1.3.8.:;
110
--source include/show_msg.inc
112
Ensure that the CASE statement acts correctly for all variants, including cases
113
where statements are nested.;
114
--source include/show_msg80.inc
117
drop table IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
118
DROP PROCEDURE IF EXISTS sp10;
121
create table res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 char(20), f2 varchar(20), f3 smallint);
124
CREATE PROCEDURE sp10( action char(20), subaction char(20) )
129
when subaction = 'subaction_1' then
130
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 1);
131
when subaction = 'subaction_2' then
132
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 2);
134
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 3);
138
when subaction = 'subaction_1' then
139
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_1' , 4);
140
when subaction = 'subaction_2' then
141
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_2' , 5);
143
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 6);
149
CALL sp10( 'action', 'subaction_1' );
150
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
151
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
153
CALL sp10( 'action', 'subaction_2' );
154
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
155
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
157
CALL sp10( 'temp', 'subaction_1' );
158
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
159
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
161
CALL sp10( 'temp', 'subaction_2' );
162
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
163
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
165
CALL sp10( 'action', 'temp' );
166
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
167
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
169
CALL sp10( 'temp', 'temp' );
170
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
174
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
177
# ------------------------------------------------------------------------------
178
let $message= Testcase 3.1.3.9 + 3.1.3.15:;
179
--source include/show_msg.inc
181
09. Ensure that the LOOP statement acts correctly for all variants, including
182
. cases where statements are nested.
183
15. Ensure that the LEAVE statement acts correctly for all variants, including
184
. cases where statements are nested.;
185
--source include/show_msg80.inc
188
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
189
DROP PROCEDURE IF EXISTS sp11;
192
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
195
CREATE PROCEDURE sp11( )
197
declare count1 integer default 1;
198
declare count2 integer default 1;
200
if count2 > 3 then leave label1;
204
if count1 > 4 then leave label2;
206
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
207
set count1 = count1 + 1;
210
set count2 = count2 + 1;
217
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
221
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
224
# ------------------------------------------------------------------------------
225
let $message= Testcase 3.1.3.16:;
226
--source include/show_msg.inc
228
Ensure that the ITERATE statement acts correctly for all variants, including
229
cases where statements are nested.
230
(tests for this testcase are also included in other testcases);
231
--source include/show_msg80.inc
234
DROP PROCEDURE IF EXISTS sp31316;
239
# wrong label at iterate
240
#Error: 1308 SQLSTATE: 42000 (ER_SP_LILABEL_MISMATCH) Message: %s with no matching label: %s
242
CREATE PROCEDURE sp31316( )
244
declare count1 integer default 1;
245
declare count2 integer default 1;
247
if count2 > 3 then leave label1;
251
if count1 > 4 then leave label2;
253
insert into temp values( count1, count2);
254
set count1 = count1 + 1;
257
set count2 = count2 + 1;
264
#DROP PROCEDURE sp31316;
267
# ------------------------------------------------------------------------------
268
let $message= Testcase 3.1.3.18:;
269
--source include/show_msg.inc
271
Ensure that the REPEAT statement acts correctly for all variants, including
272
cases where statements are nested.;
273
--source include/show_msg80.inc
276
DROP PROCEDURE IF EXISTS sp17;
277
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
280
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
283
CREATE PROCEDURE sp17( )
285
declare count1 integer default 1;
286
declare count2 integer default 1;
288
set count1 = count1 + 1;
291
set count2 = count2 + 1;
292
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
301
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
305
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
309
# ------------------------------------------------------------------------------
310
let $message= Testcase 3.1.3.24:;
311
--source include/show_msg.inc
313
Ensure that the WHILE statement acts correctly for all variants, including cases
314
where statements are nested.;
315
--source include/show_msg80.inc
318
drop table IF EXISTS res_t21;
319
DROP PROCEDURE IF EXISTS sp21;
322
create table res_t21(name text(10), surname blob(20), age_averylongfieldname_averylongname_1234569 smallint);
323
insert into res_t21 values('ashwin', 'mokadam', 25);
326
CREATE PROCEDURE sp21( )
328
declare count1 integer default 0;
329
declare count2 integer default 0;
332
declare ithisissamevariablename int default 100;
333
SELECT ithisissamevariablename;
335
declare ithisissamevariablename int default 200;
336
SELECT ithisissamevariablename;
339
label1: while count2 < 3 do
341
declare count1 integer default 7;
342
set count2 = count2 + 1;
343
insert into res_t21 values( 'xyz' , 'pqr', count2);
344
label2: while count1 < 10 do
345
set count1 = count1 + 1;
346
insert into res_t21 values( 'xyz' , 'pqr', count1);
350
set count1 = count1 + 1;
357
SELECT * from res_t21;
364
# ------------------------------------------------------------------------------
365
let $message= Testcase 3.1.3.30:;
366
--source include/show_msg.inc
368
Ensure that multiple cases of all possible combinations of the control flow
369
statements, nested within multiple compound statements within a stored
370
procedure, always act correctly and return the expected result.;
371
--source include/show_msg80.inc
374
DROP TABLE IF EXISTS res_tbl;
375
DROP PROCEDURE IF EXISTS sp31330;
378
create table res_tbl (f1 int, f2 text, f3 blob, f4 date,
379
f5 set('one', 'two', 'three', 'four', 'five') default 'one');
382
#FIXME: can be enhanced more and more ...
383
CREATE PROCEDURE sp31330 (path int)
385
declare count int default 1;
389
declare var4 set('one', 'two', 'three', 'four', 'five') DEFAULT 'five';
392
set var3 = '2000-11-09';
393
set var1 = 'flowing through case 1';
397
set var2 = 'exiting out of case 1 - invalid SET';
400
set var2 = 'exiting out of case 1';
402
insert into res_tbl values (1, var1, var2, var3, (count-2));
404
elseif count = 5 then
405
set count= count + 2;
409
set count= count + 1;
414
set var3 = '1989-11-09';
415
set var1 = 'flowing through case 2';
420
while @count2 <= 5 do
421
set @count2 = @count2 + 1;
424
set @count3=@count3 + @count2;
427
set var2 = 'exiting out of case 2';
430
insert into res_tbl values (2, var1, var2, var3, var4);
432
set @error_opt='undefined path specified';
439
#Error: 1318 SQLSTATE: 42000 (ER_SP_WRONG_NO_OF_ARGS) Message: Incorrect number of arguments for %s %s; expected %u, got %u
444
SELECT * from res_tbl;
447
SELECT * from res_tbl;
452
DROP PROCEDURE sp31330;
457
# ==============================================================================
458
# USE the same .inc to cleanup before and after the test
459
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
461
# ==============================================================================
462
let $message= . +++ END OF SCRIPT +++;
463
--source include/show_msg80.inc
464
# ==============================================================================