Debian Codegraph @

mysql-test/suite/funcs_1/r/myisam_storedproc_02.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.2 - Syntax checks for the stored procedure-specific
67 programming statements BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE:
68 --------------------------------------------------------------------------------
69
70 Testcase 3.1.2.8:
71 -----------------
72
73 Ensure that the scope of each BEGIN/END compound statement within a stored
74 procedure definition is properly applied
75 --------------------------------------------------------------------------------
76 DROP PROCEDURE IF EXISTS sp1;
77 SET STATEMENT sql_mode = '' FOR
78 CREATE PROCEDURE sp1( )
79 begin_label: BEGIN
80 declare x char DEFAULT 'x';
81 declare y char DEFAULT 'y';
82 set x = '1';
83 set y = '2';
84 label1: BEGIN
85 declare x char DEFAULT 'X';
86 declare y char DEFAULT 'Y';
87 SELECT f1, f2 into x, y from t2 limit 1;
88 SELECT '1.1', x, y;
89 label2: BEGIN
90 declare x char default 'a';
91 declare y char default 'b';
92 label3: BEGIN
93 declare x char default 'c';
94 declare y char default 'd';
95 label4: BEGIN
96 declare x char default 'e';
97 declare y char default 'f';
98 label5: BEGIN
99 declare x char default 'g';
100 declare y char default 'h';
101 SELECT 5, x, y;
102 END label5;
103 SELECT 4, x, y;
104 END label4;
105 SELECT 3, x, y;
106 END label3;
107 SELECT 2, x, y;
108 END label2;
109 END label1;
110 set @v1 = x;
111 set @v2 = y;
112 SELECT '1.2', @v1, @v2;
113 END begin_label//
114 CALL sp1();
115 1.1 x y
116 1.1 a a
117 5 x y
118 5 g h
119 4 x y
120 4 e f
121 3 x y
122 3 c d
123 2 x y
124 2 a b
125 1.2 @v1 @v2
126 1.2 1 2
127 Warnings:
128 Warning 1265 Data truncated for column 'x' at row 1
129 Warning 1265 Data truncated for column 'y' at row 1
130 DROP PROCEDURE IF EXISTS sp1;
131
132 Testcase 3.1.2.26:
133 ------------------
134
135 Ensure that the initial value of every variable declared for a stored procedure
136 is either NULL or its DEFAULT value, as appropriate.
137 --------------------------------------------------------------------------------
138 DROP PROCEDURE IF EXISTS sp1;
139 set @v1=0;
140 set @v2=0;
141 CREATE PROCEDURE sp1( )
142 BEGIN
143 declare x1 char default 'x';
144 declare y1 char;
145 declare x2 tinytext default 'tinytext';
146 declare y2 tinytext;
147 declare x3 datetime default '2005-10-03 12:13:14';
148 declare y3 datetime;
149 declare x4 float default 1.2;
150 declare y4 float;
151 declare x5 blob default 'b';
152 declare y5 blob;
153 declare x6 smallint default 127;
154 declare y6 smallint;
155 SELECT x1, x2, x3, x4, x5, x6, y1, y2, y3, y4, y5, y6;
156 END//
157 CALL sp1();
158 x1 x2 x3 x4 x5 x6 y1 y2 y3 y4 y5 y6
159 x tinytext 2005-10-03 12:13:14 1.2 b 127 NULL NULL NULL NULL NULL NULL
160 DROP PROCEDURE sp1;
161
162 Testcase 3.1.2.30:
163 ------------------
164
165 Ensure that, when a stored procedure is called/executed, every variable always
166 uses the correct value: either the value with which it is initialized or the
167 value to which it is subsequently SET or otherwise assigned, as appropriate.
168 --------------------------------------------------------------------------------
169 DROP PROCEDURE IF EXISTS sp1;
170 CREATE PROCEDURE sp1( IN invar INT, OUT outvar INT )
171 BEGIN
172 declare x integer;
173 declare y integer default 1;
174 set @x = x;
175 set @y = y;
176 set @z = 234;
177 SELECT f1, f2 into @x, @y from t2 where f1='a`' and f2='a`' limit 1;
178 SELECT @x, @y, @z, invar;
179 BEGIN
180 set @x = 2;
181 SELECT @x, @y, @z;
182 SET outvar = @x * invar + @z * @f;
183 SET invar = outvar;
184 BEGIN
185 set @y = null, @z = 'abcd';
186 SELECT @x, @y, @z;
187 END;
188 END;
189 END//
190 SET @invar = 100;
191 SET @outvar = @invar;
192 SET @f = 10;
193 SELECT @x, @y, @z, @invar, @outvar;
194 @x @y @z @invar @outvar
195 NULL NULL NULL 100 100
196 CALL sp1( @invar, @outvar );
197 @x @y @z invar
198 a` a` 234 100
199 @x @y @z
200 2 a` 234
201 @x @y @z
202 2 NULL abcd
203 SELECT @x, @y, @z, @invar, @outvar;
204 @x @y @z @invar @outvar
205 2 NULL abcd 100 2540
206 DROP PROCEDURE sp1;
207
208 Testcase 3.1.2.31:
209 ------------------
210
211 Ensure that the SELECT ... INTO statement properly assigns values to the
212 variables in its variable list.
213 --------------------------------------------------------------------------------
214 DROP PROCEDURE IF EXISTS sp1;
215 CREATE PROCEDURE sp1( )
216 BEGIN
217 declare x integer; declare y integer;
218 set @x=x;
219 set @y=y;
220 SELECT f4, f3 into @x, @y from t2 where f4=-5000 and f3='1000-01-01' limit 1;
221 SELECT @x, @y;
222 END//
223 CALL sp1();
224 @x @y
225 -5000 1000-01-01
226 DROP PROCEDURE sp1;
227
228 Testcase 3.1.2.32:
229 ------------------
230
231 Ensure that a SELECT ... INTO statement that retrieves multiple rows is
232 rejected, with an appropriate error message.
233 --------------------------------------------------------------------------------
234 DROP PROCEDURE IF EXISTS sp1;
235 CREATE PROCEDURE sp1( )
236 BEGIN
237 declare x integer; declare y integer;
238 set @x=x;
239 set @y=y;
240 SELECT f4, f3 into @x, @y from t2;
241 END//
242 CALL sp1();
243 ERROR 42000: Result consisted of more than one row
244 DROP PROCEDURE sp1;
245
246 Testcase 3.1.2.33:
247 ------------------
248
249 Ensure that a SELECT ... INTO statement that retrieves too many columns for the
250 number of variables in its variable list is rejected, with an appropriate error
251 message.
252 --------------------------------------------------------------------------------
253 DROP PROCEDURE IF EXISTS sp1;
254 CREATE PROCEDURE sp1( )
255 BEGIN
256 declare x integer; declare y integer;
257 set @x=x;
258 set @y=y;
259 SELECT f4, f3, f2, f1 into @x, @y from t2;
260 END//
261 CALL sp1();
262 ERROR 21000: The used SELECT statements have a different number of columns
263 DROP PROCEDURE sp1;
264
265 Testcase 3.1.2.34:
266 ------------------
267
268 Ensure that a SELECT ... INTO statement that retrieves too few columns for the
269 number of variables in its variable list is rejected, with an appropriate error
270 message.
271 --------------------------------------------------------------------------------
272 DROP PROCEDURE IF EXISTS sp1;
273 CREATE PROCEDURE sp1( )
274 BEGIN
275 declare x integer; declare y integer; declare z integer;
276 set @x=x;
277 set @y=y;
278 set @z=z;
279 SELECT f4 into @x, @y, @z from t2;
280 END//
281 CALL sp1();
282 ERROR 21000: The used SELECT statements have a different number of columns
283 DROP PROCEDURE sp1;
284
285 Testcase 3.1.2.38:
286 ------------------
287
288 Ensure that the scope of every condition declared is properly applied.
289 --------------------------------------------------------------------------------
290 DROP PROCEDURE IF EXISTS h1;
291 DROP TABLE IF EXISTS res_t1;
292 create table res_t1(w char unique, x char);
293 insert into res_t1 values('a', 'b');
294 CREATE PROCEDURE h1 ()
295 BEGIN
296 declare x1, x2, x3, x4, x5, x6 int default 0;
297 SELECT '-1-', x1, x2, x3, x4, x5, x6;
298 BEGIN
299 declare condname condition for sqlstate '23000';
300 declare continue handler for condname set x5 = 1;
301 set x6 = 0;
302 insert into res_t1 values ('a', 'b');
303 set x6 = 1;
304 SELECT '-2-', x1, x2, x3, x4, x5, x6;
305 END;
306 begin1_label: BEGIN
307 BEGIN
308 declare condname condition for sqlstate '20000';
309 declare continue handler for condname set x1 = 1;
310 set x2 = 0;
311 case x2
312 when 1 then set x2=10;
313 when 2 then set x2=11;
314 END case;
315 set x2 = 1;
316 SELECT '-3-', x1, x2, x3, x4, x5, x6;
317 begin2_label: BEGIN
318 BEGIN
319 declare condname condition for sqlstate '23000';
320 declare exit handler for condname set x3 = 1;
321 set x4= 1;
322 SELECT '-4a', x1, x2, x3, x4, x5, x6;
323 insert into res_t1 values ('a', 'b');
324 set x4= 2;
325 SELECT '-4b', x1, x2, x3, x4, x5, x6;
326 END;
327 SELECT '-5-', x1, x2, x3, x4, x5, x6;
328 END begin2_label;
329 SELECT '-6-', x1, x2, x3, x4, x5, x6;
330 END;
331 SELECT '-7-', x1, x2, x3, x4, x5, x6;
332 END begin1_label;
333 SELECT 'END', x1, x2, x3, x4, x5, x6;
334 END//
335 CALL h1();
336 -1- x1 x2 x3 x4 x5 x6
337 -1- 0 0 0 0 0 0
338 -2- x1 x2 x3 x4 x5 x6
339 -2- 0 0 0 0 1 1
340 -3- x1 x2 x3 x4 x5 x6
341 -3- 1 1 0 0 1 1
342 -4a x1 x2 x3 x4 x5 x6
343 -4a 1 1 0 1 1 1
344 -5- x1 x2 x3 x4 x5 x6
345 -5- 1 1 1 1 1 1
346 -6- x1 x2 x3 x4 x5 x6
347 -6- 1 1 1 1 1 1
348 -7- x1 x2 x3 x4 x5 x6
349 -7- 1 1 1 1 1 1
350 END x1 x2 x3 x4 x5 x6
351 END 1 1 1 1 1 1
352 DROP TABLE IF EXISTS tnull;
353 DROP PROCEDURE IF EXISTS sp1;
354 CREATE TABLE tnull(f1 int);
355 CREATE PROCEDURE sp1()
356 BEGIN
357 declare cond1 condition for sqlstate '42S02';
358 declare continue handler for cond1 set @var2 = 1;
359 BEGIN
360 declare cond1 condition for sqlstate '23000';
361 declare continue handler for cond1 set @var2 = 1;
362 END;
363 insert into tnull values(1);
364 END//
365 CALL sp1();
366 DROP PROCEDURE h1;
367 drop table res_t1;
368 DROP PROCEDURE sp1;
369 DROP TABLE tnull;
370
371 Testcase 3.1.2.43:
372 ------------------
373
374 Ensure that the DECLARE ... HANDLER FOR statement can not declare any handler
375 for a condition declared outside of the scope of the handler.
376 --------------------------------------------------------------------------------
377 DROP PROCEDURE IF EXISTS h1;
378 DROP PROCEDURE IF EXISTS h2;
379 drop table IF EXISTS res_t1;
380 create table res_t1(w char unique, x char);
381 insert into res_t1 values ('a', 'b');
382 CREATE PROCEDURE h1 ()
383 BEGIN
384 declare x1, x2, x3, x4, x5, x6 int default 0;
385 BEGIN
386 declare cond_1 condition for sqlstate '23000';
387 declare continue handler for cond_1 set x5 = 1;
388 BEGIN
389 declare cond_2 condition for sqlstate '20000';
390 declare continue handler for cond_1 set x1 = 1;
391 BEGIN
392 declare continue handler for cond_2 set x3 = 1;
393 set x2 = 1;
394 END;
395 set x6 = 0;
396 END;
397 BEGIN
398 declare continue handler for cond_1 set x1 = 1;
399 BEGIN
400 declare continue handler for cond_2 set x3 = 1;
401 set x2 = 1;
402 END;
403 set x6 = 0;
404 END;
405 END;
406 SELECT x1, x2, x3, x4, x5, x6;
407 END//
408 ERROR 42000: Undefined CONDITION: cond_2
409 CREATE PROCEDURE h2 ()
410 BEGIN
411 declare x1, x2, x3, x4, x5, x6 int default 0;
412 BEGIN
413 declare condname condition for sqlstate '23000';
414 declare continue handler for condname set x5 = 1;
415 BEGIN
416 declare condname condition for sqlstate '20000';
417 declare continue handler for condname set x1 = 1;
418 BEGIN
419 declare condname condition for sqlstate '42000';
420 declare continue handler for condname set x3 = 1;
421 set x6 = 0;
422 insert into res_t1 values ('a', 'b');
423 set x6 = 1;
424 set x4= 0;
425 CALL sp1();
426 set x4= 1;
427 set x2 = 0;
428 case x2
429 when 1 then set x2=10;
430 when 2 then set x2=11;
431 END case;
432 set x2 = 1;
433 END;
434 set x2 = 0;
435 case x2
436 when 1 then set x2=10;
437 when 2 then set x2=11;
438 END case;
439 set x2 = 1;
440 set x6 = 0;
441 insert into res_t1 values ('a', 'b');
442 set x6 = 1;
443 END;
444 END;
445 SELECT x1, x2, x3, x4, x5, x6;
446 END//
447 CALL h2();
448 x1 x2 x3 x4 x5 x6
449 1 1 1 1 1 1
450 SELECT * FROM res_t1;
451 w x
452 a b
453 DROP PROCEDURE h2;
454 drop table res_t1;
455
456 Testcase 3.1.2.44:
457 ------------------
458
459 Ensure that the DECLARE ... HANDLER FOR statement cannot declare a handler for
460 any invalid, or undeclared, condition.
461 --------------------------------------------------------------------------------
462 DROP PROCEDURE IF EXISTS h1;
463 CREATE PROCEDURE h1 ()
464 BEGIN
465 declare x1, x2, x3, x4, x5, x6 int default 0;
466 BEGIN
467 declare condname1 condition for sqlstate '23000';
468 BEGIN
469 declare condname2 condition for sqlstate '20000';
470 declare continue handler for condname1 set x3 = 1;
471 declare continue handler for condname2 set x1 = 1;
472 END;
473 END;
474 BEGIN
475 declare condname3 condition for sqlstate '42000';
476 declare continue handler for condname1 set x3 = 1;
477 declare continue handler for condname2 set x5 = 1;
478 declare continue handler for condname3 set x1 = 1;
479 END;
480 END//
481 ERROR 42000: Undefined CONDITION: condname1
482 CREATE PROCEDURE h1 ()
483 BEGIN
484 DECLARE x1 INT DEFAULT 0;
485 BEGIN
486 DECLARE condname1 CONDITION CHECK SQLSTATE '23000';
487 END;
488 DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
489 END//
490 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHECK SQLSTATE '23000';
491 END;
492 DECLARE CONTINUE HANDLER FOR condname1 SET x1 = ...' at line 5
493 CREATE PROCEDURE h1 ()
494 BEGIN
495 DECLARE x1 INT DEFAULT 0;
496 BEGIN
497 DECLARE condname1 CONDITION FOR SQLSTATE 'qwert';
498 END;
499 DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
500 END//
501 ERROR 42000: Bad SQLSTATE: 'qwert'
502
503 Testcase 3.1.2.45 + 3.1.2.50:
504 -----------------------------
505
506 45. Ensure that the scope of every handler declared is properly applied.
507 50. Ensure that a CONTINUE handler allows the execution of the stored procedure
508 . to continue once the handler statement has completed its own execution (that
509 . is, once the handler action statement has been executed).
510 --------------------------------------------------------------------------------
511 DROP PROCEDURE IF EXISTS p1;
512 DROP PROCEDURE IF EXISTS p1undo;
513 DROP PROCEDURE IF EXISTS h1;
514 DROP PROCEDURE IF EXISTS sp1;
515 drop table IF EXISTS res_t1;
516 ==> 'UNDO' is still not supported.
517 create procedure p1undo ()
518 begin
519 declare undo handler for sqlexception select '1';
520 select * from tqq;
521 SELECT 'end of 1';
522 end;//
523 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'undo handler for sqlexception select '1';
524 select * from tqq;
525 SELECT 'end of 1...' at line 3
526 create procedure p1 ()
527 begin
528 declare exit handler for sqlexception select 'exit handler 1';
529 begin
530 declare exit handler for sqlexception select 'exit handler 2';
531 begin
532 declare continue handler for sqlexception select 'continue handler 3';
533 drop table if exists tqq;
534 select * from tqq;
535 SELECT 'end of BEGIN/END 3';
536 end;
537 drop table if exists tqq;
538 select * from tqq;
539 SELECT 'end of BEGIN/END 2';
540 end;
541 select * from tqq;
542 SELECT 'end of BEGIN/END 1';
543 end;//
544 call p1()//
545 continue handler 3
546 continue handler 3
547 end of BEGIN/END 3
548 end of BEGIN/END 3
549 exit handler 2
550 exit handler 2
551 exit handler 1
552 exit handler 1
553 create table res_t1(w char unique, x char);
554 insert into res_t1 values ('a', 'b');
555 CREATE PROCEDURE h1 ()
556 BEGIN
557 declare x1, x2, x3, x4, x5, x6 int default 0;
558 BEGIN
559 declare continue handler for sqlstate '23000' set x5 = 1;
560 insert into res_t1 values ('a', 'b');
561 set x6 = 1;
562 END;
563 begin1_label: BEGIN
564 BEGIN
565 declare continue handler for sqlstate '23000' set x1 = 1;
566 insert into res_t1 values ('a', 'b');
567 set x2 = 1;
568 begin2_label: BEGIN
569 BEGIN
570 declare exit handler for sqlstate '23000' set x3 = 1;
571 set x4= 1;
572 insert into res_t1 values ('a', 'b');
573 set x4= 0;
574 END;
575 END begin2_label;
576 END;
577 END begin1_label;
578 SELECT x1, x2, x3, x4, x5, x6;
579 END//
580 CALL h1();
581 x1 x2 x3 x4 x5 x6
582 1 1 1 1 1 1
583 This will fail, SQLSTATE 00000 is not allowed
584 CREATE PROCEDURE sp1()
585 begin1_label:BEGIN
586 declare exit handler for sqlstate '00000' set @var1 = 5;
587 set @var2 = 6;
588 begin2_label:BEGIN
589 declare continue handler for sqlstate '00000' set @var3 = 7;
590 set @var4 = 8;
591 SELECT @var3, @var4;
592 END begin2_label;
593 SELECT @var1, @var2;
594 END begin1_label//
595 ERROR 42000: Bad SQLSTATE: '00000'
596 Verify SP wasn't created
597 CALL sp1();
598 ERROR 42000: PROCEDURE db_storedproc.sp1 does not exist
599 DROP PROCEDURE p1;
600 DROP PROCEDURE h1;
601 DROP PROCEDURE IF EXISTS sp1;
602 DROP TABLE res_t1;
603
604 Testcase 3.1.2.50:
605 ------------------
606 DROP PROCEDURE IF EXISTS sp1;
607 DROP PROCEDURE IF EXISTS sp2;
608 CREATE PROCEDURE sp1 (x int, y int)
609 BEGIN
610 set @y=0;
611 END//
612 CREATE PROCEDURE sp2 ()
613 BEGIN
614 declare continue handler for sqlstate '42000' set @x2 = 1;
615 set @x=1;
616 SELECT @x2;
617 CALL sp1(1);
618 set @x=2;
619 SELECT @x2, @x;
620 END//
621 CALL sp2();
622 @x2
623 NULL
624 @x2 @x
625 1 2
626 DROP PROCEDURE sp1;
627 DROP PROCEDURE sp2;
628
629 Testcase 3.2.2.51:
630 ------------------
631
632 Ensure that an EXIT handler causes the execution of the stored procedure to
633 terminate, within its scope, once the handler action statement has been
634 executed.
635 --------------------------------------------------------------------------------
636 DROP PROCEDURE IF EXISTS sp1;
637 DROP PROCEDURE IF EXISTS sp2;
638 CREATE PROCEDURE sp1 (x int, y int)
639 BEGIN
640 set @x=0;
641 END//
642 CREATE PROCEDURE sp2 ()
643 BEGIN
644 declare exit handler for sqlstate '42000' set @x2 = 1;
645 set @x2=0;
646 set @x=1;
647 SELECT '-1-', @x2, @x;
648 CALL sp1(1);
649 SELECT '-2-', @x2, @x;
650 set @x=2;
651 END//
652 CALL sp1(1);
653 ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1
654 CALL sp2();
655 -1- @x2 @x
656 -1- 0 1
657 SELECT '-3-', @x2, @x;
658 -3- @x2 @x
659 -3- 1 1
660 DROP PROCEDURE sp1;
661 DROP PROCEDURE sp2;
662
663 Testcase 3.1.2.52:
664 ------------------
665
666 Ensure that an EXIT handler does not cause the execution of the stored procedure
667 to terminate outside of its scope.
668 --------------------------------------------------------------------------------
669 DROP PROCEDURE IF EXISTS sp1;
670 DROP PROCEDURE IF EXISTS sp2;
671 CREATE PROCEDURE sp1 (x int, y int)
672 BEGIN
673 set @x=0;
674 END//
675 CREATE PROCEDURE sp2()
676 BEGIN
677 declare continue handler for sqlstate '42000' set @x2 = 2;
678 set @x2 = 1;
679 set @x =20;
680 SELECT '-1-', @x2, @x;
681 BEGIN
682 declare exit handler for sqlstate '42000' set @x2 = 11;
683 SELECT '-2-', @x2, @x;
684 CALL sp1(1);
685 SELECT '-3a', @x2, @x;
686 set @x=21;
687 SELECT '-3b', @x2, @x;
688 END;
689 set @x=22;
690 SELECT '-4-', @x2, @x;
691 END//
692 CALL sp2();
693 -1- @x2 @x
694 -1- 1 20
695 -2- @x2 @x
696 -2- 1 20
697 -4- @x2 @x
698 -4- 11 22
699 DROP PROCEDURE sp1;
700 DROP PROCEDURE sp2;
701
702 Testcase 3.1.2.54:
703 ------------------
704
705 Ensure that a handler with a condition defined with an SQLSTATE that begins with
706 “01“ is always exactly equivalent in action to a handler with an SQLWARNING
707 condition.
708 --------------------------------------------------------------------------------
709 DROP PROCEDURE IF EXISTS sp0;
710 DROP PROCEDURE IF EXISTS sp1;
711 DROP PROCEDURE IF EXISTS sp2;
712 DROP PROCEDURE IF EXISTS sp3;
713 DROP PROCEDURE IF EXISTS sp4;
714 DROP TABLE IF EXISTS temp;
715 CREATE TABLE temp( f1 CHAR, f2 CHAR);
716 SET STATEMENT sql_mode = '' FOR
717 CREATE PROCEDURE sp0()
718 BEGIN
719 set @done=0;
720 set @x=0;
721 insert into temp values('xxx', 'yy');
722 set @x=1;
723 END//
724 SET STATEMENT sql_mode = '' FOR
725 CREATE PROCEDURE sp1()
726 BEGIN
727 declare continue handler for sqlstate '01000' set @done = 1;
728 set @done=0;
729 set @x=0;
730 insert into temp values('xxx', 'yy');
731 set @x=1;
732 END//
733 SET STATEMENT sql_mode = '' FOR
734 CREATE PROCEDURE sp2()
735 BEGIN
736 declare continue handler for sqlwarning set @done = 1;
737 set @done=0;
738 set @x=0;
739 insert into temp values('xxx', 'yy');
740 set @x=1;
741 END//
742 SET STATEMENT sql_mode = '' FOR
743 CREATE PROCEDURE sp3()
744 BEGIN
745 declare exit handler for sqlstate '01000' set @done = 1;
746 set @done=0;
747 set @x=0;
748 insert into temp values('xxx', 'yy');
749 set @x=1;
750 END//
751 SET STATEMENT sql_mode = '' FOR
752 CREATE PROCEDURE sp4()
753 BEGIN
754 declare exit handler for sqlwarning set @done = 1;
755 set @done=0;
756 set @x=0;
757 insert into temp values('xxx', 'yy');
758 set @x=1;
759 END//
760 INSERT INTO temp VALUES('0', NULL);
761 CALL sp0();
762 Warnings:
763 Warning 1265 Data truncated for column 'f1' at row 1
764 Warning 1265 Data truncated for column 'f2' at row 1
765 SELECT @done, @x;
766 @done @x
767 0 1
768 INSERT INTO temp VALUES('1', NULL);
769 CALL sp1();
770 SELECT @done, @x;
771 @done @x
772 1 1
773 INSERT INTO temp VALUES('2', NULL);
774 CALL sp2();
775 SELECT @done, @x;
776 @done @x
777 1 1
778 INSERT INTO temp VALUES('3', NULL);
779 CALL sp3();
780 SELECT @done, @x;
781 @done @x
782 1 0
783 INSERT INTO temp VALUES('4', NULL);
784 CALL sp4();
785 SELECT @done, @x;
786 @done @x
787 1 0
788 SELECT * FROM temp;
789 f1 f2
790 0 NULL
791 x y
792 1 NULL
793 x y
794 2 NULL
795 x y
796 3 NULL
797 x y
798 4 NULL
799 x y
800 DROP PROCEDURE sp1;
801 DROP PROCEDURE sp2;
802 DROP PROCEDURE sp3;
803 DROP PROCEDURE sp4;
804 DROP TABLE temp;
805
806 Testcase 3.1.2.56:
807 ------------------
808
809 Ensure that a handler with a condition defined with an SQLSTATE that begins with
810 “02“ is always exactly equivalent in action to a handler with a NOT FOUND
811 condition.
812 --------------------------------------------------------------------------------
813 DROP PROCEDURE IF EXISTS sp0;
814 DROP PROCEDURE IF EXISTS sp1;
815 DROP PROCEDURE IF EXISTS sp2;
816 DROP PROCEDURE IF EXISTS sp3;
817 DROP PROCEDURE IF EXISTS sp4;
818 CREATE PROCEDURE sp0()
819 BEGIN
820 DECLARE f1_value CHAR(20);
821 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
822 SET @done = 0;
823 SET @x = 0;
824 OPEN cur1;
825 FETCH cur1 INTO f1_value;
826 SET @x = 1;
827 FETCH cur1 INTO f1_value;
828 SET @x = 2;
829 CLOSE cur1;
830 END//
831 CREATE PROCEDURE sp1()
832 BEGIN
833 DECLARE f1_value CHAR(20);
834 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
835 declare continue handler for sqlstate '02000' set @done = 1;
836 SET @done = 0;
837 SET @x = 0;
838 OPEN cur1;
839 FETCH cur1 INTO f1_value;
840 SET @x = 1;
841 FETCH cur1 INTO f1_value;
842 SET @x = 2;
843 CLOSE cur1;
844 END//
845 CREATE PROCEDURE sp2()
846 BEGIN
847 DECLARE f1_value CHAR(20);
848 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
849 declare continue handler for not found set @done = 1;
850 SET @done = 0;
851 SET @x = 0;
852 OPEN cur1;
853 FETCH cur1 INTO f1_value;
854 SET @x = 1;
855 FETCH cur1 INTO f1_value;
856 SET @x = 2;
857 CLOSE cur1;
858 END//
859 CREATE PROCEDURE sp3()
860 BEGIN
861 DECLARE f1_value CHAR(20);
862 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
863 declare exit handler for sqlstate '02000' set @done = 1;
864 SET @done = 0;
865 SET @x = 0;
866 OPEN cur1;
867 FETCH cur1 INTO f1_value;
868 SET @x = 1;
869 FETCH cur1 INTO f1_value;
870 SET @x = 2;
871 CLOSE cur1;
872 END//
873 CREATE PROCEDURE sp4()
874 BEGIN
875 DECLARE f1_value CHAR(20);
876 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
877 declare exit handler for not found set @done = 1;
878 SET @done = 0;
879 SET @x = 0;
880 OPEN cur1;
881 FETCH cur1 INTO f1_value;
882 SET @x = 1;
883 FETCH cur1 INTO f1_value;
884 SET @x = 2;
885 CLOSE cur1;
886 END//
887 CALL sp0();
888 ERROR 02000: No data - zero rows fetched, selected, or processed
889 SELECT @done, @x;
890 @done @x
891 0 1
892 CALL sp1();
893 SELECT @done, @x;
894 @done @x
895 1 2
896 CALL sp2();
897 SELECT @done, @x;
898 @done @x
899 1 2
900 CALL sp3();
901 SELECT @done, @x;
902 @done @x
903 1 1
904 CALL sp4();
905 SELECT @done, @x;
906 @done @x
907 1 1
908 DROP PROCEDURE sp0;
909 DROP PROCEDURE sp1;
910 DROP PROCEDURE sp2;
911 DROP PROCEDURE sp3;
912 DROP PROCEDURE sp4;
913
914 Testcase 3.1.2.58:
915 ------------------
916
917 Ensure that a handler with a condition defined with an SQLSTATE that begins with
918 anything other that “01“ or “02“ is always exactly equivalent in action to a
919 handler with an SQLEXCEPTION condition.
920 --------------------------------------------------------------------------------
921 DROP PROCEDURE IF EXISTS sp0;
922 DROP PROCEDURE IF EXISTS sp1;
923 DROP PROCEDURE IF EXISTS sp2;
924 DROP PROCEDURE IF EXISTS sp3;
925 DROP PROCEDURE IF EXISTS sp4;
926 CREATE PROCEDURE sp0()
927 BEGIN
928 DECLARE f1_value CHAR(20);
929 DECLARE cv INT DEFAULT 0;
930 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
931 SET @x = 1;
932 CASE cv
933 WHEN 2 THEN SET @x = 2;
934 WHEN 3 THEN SET @x = 3;
935 END case;
936 SET @x = 4;
937 SELECT f1, f2 FROM t2
938 UNION
939 SELECT f1, f2,3 FROM t2;
940 SET @x = 5;
941 FETCH cur1 INTO f1_value;
942 SET @x = 6;
943 END//
944 CREATE PROCEDURE sp1()
945 BEGIN
946 DECLARE f1_value CHAR(20);
947 DECLARE cv INT DEFAULT 0;
948 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
949 DECLARE continue HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE';
950 DECLARE continue HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE';
951 DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
952 SET @x = 1;
953 CASE cv
954 WHEN 2 THEN SET @x = 2;
955 WHEN 3 THEN SET @x = 3;
956 END case;
957 SET @x = 4;
958 SELECT f1, f2 FROM t2
959 UNION
960 SELECT f1, f2,3 FROM t2;
961 SET @x = 5;
962 FETCH cur1 INTO f1_value;
963 SET @x = 6;
964 END//
965 CREATE PROCEDURE sp2()
966 BEGIN
967 DECLARE f1_value CHAR(20);
968 DECLARE cv INT DEFAULT 0;
969 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
970 DECLARE continue HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE';
971 DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
972 SET @x = 1;
973 CASE cv
974 WHEN 2 THEN SET @x = 2;
975 WHEN 3 THEN SET @x = 3;
976 END case;
977 SET @x = 4;
978 SELECT f1, f2 FROM t2
979 UNION
980 SELECT f1, f2,3 FROM t2;
981 SET @x = 5;
982 FETCH cur1 INTO f1_value;
983 SET @x = 6;
984 END//
985 CREATE PROCEDURE sp3()
986 BEGIN
987 DECLARE f1_value CHAR(20);
988 DECLARE cv INT DEFAULT 0;
989 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
990 DECLARE EXIT HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE';
991 DECLARE EXIT HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE';
992 DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
993 SET @x = 1;
994 CASE cv
995 WHEN 2 THEN SET @x = 2;
996 WHEN 3 THEN SET @x = 3;
997 END case;
998 SET @x = 4;
999 SELECT f1, f2 FROM t2
1000 UNION
1001 SELECT f1, f2,3 FROM t2;
1002 SET @x = 5;
1003 FETCH cur1 INTO f1_value;
1004 SET @x = 6;
1005 END//
1006 CREATE PROCEDURE sp4()
1007 BEGIN
1008 DECLARE f1_value CHAR(20);
1009 DECLARE cv INT DEFAULT 0;
1010 DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
1011 DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE';
1012 DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
1013 SET @x = 1;
1014 CASE cv
1015 WHEN 2 THEN SET @x = 2;
1016 WHEN 3 THEN SET @x = 3;
1017 END case;
1018 SET @x = 4;
1019 SELECT f1, f2 FROM t2
1020 UNION
1021 SELECT f1, f2,3 FROM t2;
1022 SET @x = 5;
1023 FETCH cur1 INTO f1_value;
1024 SET @x = 6;
1025 CLOSE cur1;
1026 END//
1027 CALL sp0();
1028 ERROR 20000: Case not found for CASE statement
1029 SELECT '-0-', @x;
1030 -0- @x
1031 -0- 1
1032 CALL sp1();
1033 SQLSTATE
1034 20000
1035 SQLSTATE
1036 21000
1037 SQLSTATE
1038 24000
1039 SELECT '-1-', @x;
1040 -1- @x
1041 -1- 6
1042 CALL sp2();
1043 SQLSTATE
1044 SQLEXCEPTION
1045 SQLSTATE
1046 SQLEXCEPTION
1047 SQLSTATE
1048 24000
1049 SELECT '-2-', @x;
1050 -2- @x
1051 -2- 6
1052 CALL sp3();
1053 SQLSTATE
1054 20000
1055 SELECT '-3-', @x;
1056 -3- @x
1057 -3- 1
1058 CALL sp4();
1059 SQLSTATE
1060 SQLEXCEPTION
1061 SELECT '-4-', @x;
1062 -4- @x
1063 -4- 1
1064 DROP PROCEDURE sp0;
1065 DROP PROCEDURE sp1;
1066 DROP PROCEDURE sp2;
1067 DROP PROCEDURE sp3;
1068 DROP PROCEDURE sp4;
1069
1070 Testcase 3.1.2.65:
1071 ------------------
1072
1073 Ensure that FETCH <cursor name> returns the first row of the cursor_s result set
1074 the first time FETCH is executed, that it returns each subsequent row of the
1075 cursor_s result set each of the subsequent times FETCH is executed, and that it
1076 returns a NOT FOUND warning if it is executed after the last row of the cursor_s
1077 result set has already been fetched.
1078 --------------------------------------------------------------------------------
1079 DROP PROCEDURE IF EXISTS sp1;
1080 DROP TABLE IF EXISTS temp;
1081 CREATE TABLE temp(
1082 cnt INT,
1083 f1 CHAR(20),
1084 f2 CHAR(20),
1085 f3 INT,
1086 f4 CHAR(20),
1087 f5 INT);
1088 INSERT INTO temp VALUES(0, 'onip', 'abc', 8760, 'xyz', 10);
1089 CREATE PROCEDURE sp1( )
1090 BEGIN
1091 declare proceed int default 1;
1092 declare count integer default 1;
1093 declare f1_value char(20);
1094 declare f2_value char(20);
1095 declare f5_value char(20);
1096 declare f4_value integer;
1097 declare f6_value integer;
1098 declare cur1 cursor for SELECT f1, f2, f4, f5, f6 from t2
1099 where f4 >=-5000 order by f4 limit 3;
1100 open cur1;
1101 while proceed do
1102 SELECT count AS 'loop';
1103 fetch cur1 into f1_value, f2_value, f4_value, f5_value, f6_value;
1104 insert into temp values (count, f1_value, f2_value, f4_value, f5_value, f6_value);
1105 set count = count + 1;
1106 END while;
1107 END//
1108 CALL sp1();
1109 loop
1110 1
1111 loop
1112 2
1113 loop
1114 3
1115 loop
1116 4
1117 ERROR 02000: No data - zero rows fetched, selected, or processed
1118 SELECT * FROM temp;
1119 cnt f1 f2 f3 f4 f5
1120 0 onip abc 8760 xyz 10
1121 1 a` a` -5000 a` -5000
1122 2 aaa aaa -4999 aaa -4999
1123 3 abaa abaa -4998 abaa -4998
1124 DROP TABLE temp;
1125 DROP PROCEDURE sp1;
1126
1127 Testcase 3.1.2.68:
1128 ------------------
1129
1130 Ensure that FETCH <cursor name> fails with an appropriate error message if the
1131 number of columns to be fetched does not match the number of variables specified
1132 by the FETCH statement.
1133 --------------------------------------------------------------------------------
1134 DROP PROCEDURE IF EXISTS sp1;
1135 DROP PROCEDURE IF EXISTS sp2;
1136 --> not enough columns in FETCH statement
1137 CREATE PROCEDURE sp1( )
1138 BEGIN
1139 declare newf1 char(20);
1140 declare cur1 cursor for SELECT f1, f2 from t2 limit 10;
1141 declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS '';
1142 BEGIN
1143 open cur1;
1144 fetch cur1 into newf1;
1145 SELECT newf1;
1146 close cur1;
1147 END;
1148 END//
1149 --> too many columns in FETCH statement
1150 CREATE PROCEDURE sp2( )
1151 BEGIN
1152 declare newf1 char(20);
1153 declare newf2 char(20);
1154 declare cur1 cursor for SELECT f1 from t2 limit 10;
1155 declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS '';
1156 BEGIN
1157 open cur1;
1158 fetch cur1 into newf1, newf2;
1159 SELECT newf1, newf2;
1160 close cur1;
1161 END;
1162 END//
1163 --> not enough columns in FETCH statement
1164 CALL sp1();
1165 ERROR HY000: Incorrect number of FETCH variables
1166 --> too many columns in FETCH statement
1167 CALL sp2();
1168 ERROR HY000: Incorrect number of FETCH variables
1169 DROP PROCEDURE sp1;
1170 DROP PROCEDURE sp2;
1171
1172 Testcase 3.1.2.75:
1173 ------------------
1174
1175 Ensure that, for nested compound statements, a cursor that was declared and
1176 opened during an outer level of the statement is not closed when an inner level
1177 of a compound statement ends.
1178 --------------------------------------------------------------------------------
1179 DROP TABLE IF EXISTS temp1;
1180 DROP PROCEDURE IF EXISTS sp1;
1181 create table temp1( f0 char(20), f1 char(20), f2 char(20), f3 int, f4 char(20) );
1182 SELECT f1, f2, f4, f5 from t2 order by f4;
1183 f1 f2 f4 f5
1184 a` a` -5000 a`
1185 aaa aaa -4999 aaa
1186 abaa abaa -4998 abaa
1187 acaaa acaaa -4997 acaaa
1188 adaaaa adaaaa -4996 adaaaa
1189 aeaaaaa aeaaaaa -4995 aeaaaaa
1190 afaaaaaa afaaaaaa -4994 afaaaaaa
1191 agaaaaaaa agaaaaaaa -4993 agaaaaaaa
1192 a^aaaaaaaa a^aaaaaaaa -4992 a^aaaaaaaa
1193 a_aaaaaaaaa a_aaaaaaaaa -4991 a_aaaaaaaaa
1194 CREATE PROCEDURE sp1( )
1195 BEGIN
1196 declare count integer;
1197 declare from0 char(20);
1198 declare newf1 char(20);
1199 declare newf2 char(20);
1200 declare newf5 char(20);
1201 declare newf4 integer;
1202 declare cur1 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5;
1203 declare cur2 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5;
1204 open cur1;
1205 open cur2;
1206 BEGIN
1207 declare continue handler for sqlstate '02000' set count = 1;
1208 fetch cur1 into newf1, newf2, newf4, newf5;
1209 SELECT '-1-', count, newf1, newf2, newf4, newf5;
1210 insert into temp1 values ('cur1_out', newf1, newf2, newf4, newf5);
1211 set count = 4;
1212 BEGIN
1213 while count > 0 do
1214 fetch cur1 into newf1, newf2, newf4, newf5;
1215 SELECT '-2-', count, newf1, newf2, newf4, newf5;
1216 set count = count - 1;
1217 END while;
1218 SELECT '-3-', count, newf1, newf2, newf4, newf4;
1219 END;
1220 BEGIN
1221 fetch cur1 into newf1, newf2, newf4, newf5;
1222 SELECT '-4-', newf1, newf2, newf4, newf5;
1223 insert into temp1 values ('cur1_in', newf1, newf2, newf4, newf5);
1224 END;
1225 fetch cur2 into newf1, newf2, newf4, newf5;
1226 SELECT '-5-', newf1, newf2, newf4, newf5;
1227 insert into temp1 values ('cur2', newf1, newf2, newf4, newf5);
1228 close cur1;
1229 END;
1230 fetch cur2 into newf1, newf2, newf4, newf5;
1231 SELECT '-6-', newf1, newf2, newf4, newf5;
1232 close cur2;
1233 END//
1234 CALL sp1();
1235 -1- count newf1 newf2 newf4 newf5
1236 -1- NULL a` a` -5000 a`
1237 -2- count newf1 newf2 newf4 newf5
1238 -2- 4 aaa aaa -4999 aaa
1239 -2- count newf1 newf2 newf4 newf5
1240 -2- 3 abaa abaa -4998 abaa
1241 -2- count newf1 newf2 newf4 newf5
1242 -2- 2 acaaa acaaa -4997 acaaa
1243 -2- count newf1 newf2 newf4 newf5
1244 -2- 1 adaaaa adaaaa -4996 adaaaa
1245 -3- count newf1 newf2 newf4 newf4
1246 -3- 0 adaaaa adaaaa -4996 -4996
1247 -4- newf1 newf2 newf4 newf5
1248 -4- adaaaa adaaaa -4996 adaaaa
1249 -5- newf1 newf2 newf4 newf5
1250 -5- a` a` -5000 a`
1251 -6- newf1 newf2 newf4 newf5
1252 -6- aaa aaa -4999 aaa
1253 SELECT * from temp1;
1254 f0 f1 f2 f3 f4
1255 cur1_out a` a` -5000 a`
1256 cur1_in adaaaa adaaaa -4996 adaaaa
1257 cur2 a` a` -5000 a`
1258 DROP PROCEDURE sp1;
1259 drop table temp1;
1260
1261 Testcase 3.1.2.76:
1262 ------------------
1263
1264 Ensure that all cursors operate asensitively, so that there is no concurrency
1265 conflict between cursors operating on the same, or similar, sets of results
1266 during execution of one or more stored procedures.
1267 --------------------------------------------------------------------------------
1268 DROP PROCEDURE IF EXISTS sp1;
1269 drop table IF EXISTS temp1;
1270 drop table IF EXISTS temp2;
1271 create table temp1( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer );
1272 create table temp2( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer );
1273 CREATE PROCEDURE sp_inner( )
1274 BEGIN
1275 declare proceed int default 1;
1276 declare i_count integer default 20;
1277 declare i_newf1 char(20);
1278 declare i_newf2 char(20);
1279 declare i_newf3 date;
1280 declare i_newf4 integer;
1281 declare i_newf11 char(20);
1282 declare i_newf12 char(20);
1283 declare i_newf13 date;
1284 declare i_newf14 integer;
1285 declare cur1 cursor for SELECT f1, f2, f3, f4 from t2
1286 where f4>=-5000 order by f4 limit 4;
1287 declare cur2 cursor for SELECT f1, f2, f3, f4 from t2
1288 where f4>=-5000 order by f4 limit 3;
1289 declare continue handler for sqlstate '02000' set proceed=0;
1290 open cur1;
1291 open cur2;
1292 set i_count = 10;
1293 while proceed do
1294 fetch cur1 into i_newf1, i_newf2, i_newf3, i_newf4;
1295 IF proceed THEN
1296 insert into temp1 values ('sp_inner', i_count, i_newf1, i_newf2, i_newf3, i_newf4);
1297 fetch cur2 into i_newf11, i_newf12, i_newf13, i_newf14;
1298 IF proceed THEN
1299 insert into temp2 values ('sp_inner', i_count, i_newf11, i_newf12, i_newf13, i_newf14);
1300 END IF;
1301 END IF;
1302 set i_count = i_count - 1;
1303 END while;
1304 close cur1;
1305 close cur2;
1306 END//
1307 CREATE PROCEDURE sp_outer( )
1308 BEGIN
1309 DECLARE proceed INT DEFAULT 1;
1310 DECLARE o_count INTEGER DEFAULT 20;
1311 DECLARE o_newf1 CHAR(20);
1312 DECLARE o_newf2 CHAR(20);
1313 DECLARE o_newf3 DATE;
1314 DECLARE o_newf4 INTEGER;
1315 DECLARE o_newf11 CHAR(20);
1316 DECLARE o_newf12 CHAR(20);
1317 DECLARE o_newf13 DATE;
1318 DECLARE o_newf14 INTEGER;
1319 DECLARE cur1 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2
1320 WHERE f4>=-5000 ORDER BY f4 LIMIT 5;
1321 DECLARE cur2 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2
1322 WHERE f4>=-5000 ORDER BY f4 LIMIT 5;
1323 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET proceed=0;
1324 OPEN cur1;
1325 OPEN cur2;
1326 SET o_count = 1;
1327 WHILE proceed DO
1328 FETCH cur1 INTO o_newf1, o_newf2, o_newf3, o_newf4;
1329 IF proceed THEN
1330 INSERT INTO temp1 VALUES ('_sp_out_', o_count, o_newf1, o_newf2, o_newf3, o_newf4);
1331 CALL sp_inner();
1332 FETCH cur2 INTO o_newf11, o_newf12, o_newf13, o_newf14;
1333 IF proceed THEN
1334 INSERT INTO temp2 VALUES ('_sp_out_', o_count, o_newf11, o_newf12, o_newf13, o_newf14);
1335 END IF;
1336 END IF;
1337 SET o_count = o_count + 1;
1338 END WHILE;
1339 CLOSE cur1;
1340 CLOSE cur2;
1341 END//
1342 CALL sp_outer();
1343 SELECT * FROM temp1;
1344 f0 cnt f1 f2 f3 f4
1345 _sp_out_ 1 a` a` 1000-01-01 -5000
1346 sp_inner 10 a` a` 1000-01-01 -5000
1347 sp_inner 9 aaa aaa 1000-01-02 -4999
1348 sp_inner 8 abaa abaa 1000-01-03 -4998
1349 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1350 _sp_out_ 2 aaa aaa 1000-01-02 -4999
1351 sp_inner 10 a` a` 1000-01-01 -5000
1352 sp_inner 9 aaa aaa 1000-01-02 -4999
1353 sp_inner 8 abaa abaa 1000-01-03 -4998
1354 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1355 _sp_out_ 3 abaa abaa 1000-01-03 -4998
1356 sp_inner 10 a` a` 1000-01-01 -5000
1357 sp_inner 9 aaa aaa 1000-01-02 -4999
1358 sp_inner 8 abaa abaa 1000-01-03 -4998
1359 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1360 _sp_out_ 4 acaaa acaaa 1000-01-04 -4997
1361 sp_inner 10 a` a` 1000-01-01 -5000
1362 sp_inner 9 aaa aaa 1000-01-02 -4999
1363 sp_inner 8 abaa abaa 1000-01-03 -4998
1364 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1365 _sp_out_ 5 adaaaa adaaaa 1000-01-05 -4996
1366 sp_inner 10 a` a` 1000-01-01 -5000
1367 sp_inner 9 aaa aaa 1000-01-02 -4999
1368 sp_inner 8 abaa abaa 1000-01-03 -4998
1369 sp_inner 7 acaaa acaaa 1000-01-04 -4997
1370 SELECT * FROM temp2;
1371 f0 cnt f1 f2 f3 f4
1372 sp_inner 10 a` a` 1000-01-01 -5000
1373 sp_inner 9 aaa aaa 1000-01-02 -4999
1374 sp_inner 8 abaa abaa 1000-01-03 -4998
1375 _sp_out_ 1 a` a` 1000-01-01 -5000
1376 sp_inner 10 a` a` 1000-01-01 -5000
1377 sp_inner 9 aaa aaa 1000-01-02 -4999
1378 sp_inner 8 abaa abaa 1000-01-03 -4998
1379 _sp_out_ 2 aaa aaa 1000-01-02 -4999
1380 sp_inner 10 a` a` 1000-01-01 -5000
1381 sp_inner 9 aaa aaa 1000-01-02 -4999
1382 sp_inner 8 abaa abaa 1000-01-03 -4998
1383 _sp_out_ 3 abaa abaa 1000-01-03 -4998
1384 sp_inner 10 a` a` 1000-01-01 -5000
1385 sp_inner 9 aaa aaa 1000-01-02 -4999
1386 sp_inner 8 abaa abaa 1000-01-03 -4998
1387 _sp_out_ 4 acaaa acaaa 1000-01-04 -4997
1388 sp_inner 10 a` a` 1000-01-01 -5000
1389 sp_inner 9 aaa aaa 1000-01-02 -4999
1390 sp_inner 8 abaa abaa 1000-01-03 -4998
1391 _sp_out_ 5 adaaaa adaaaa 1000-01-05 -4996
1392 DROP PROCEDURE sp_outer;
1393 DROP PROCEDURE sp_inner;
1394 DROP TABLE temp1;
1395 DROP TABLE temp2;
1396
1397 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
1398 --------------------------------------------------------------------------------
1399 DROP DATABASE IF EXISTS db_storedproc;
1400 DROP DATABASE IF EXISTS db_storedproc_1;
1401
1402 . +++ END OF SCRIPT +++
1403 --------------------------------------------------------------------------------