Debian Codegraph @

mysql-test/suite/compat/oracle/r/sp-package.result

raw
1 SET sql_mode=ORACLE;
2 #
3 # Creating a body of a non-existing package
4 #
5 CREATE PACKAGE BODY test2 AS
6 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
7 END;
8 $$
9 ERROR 42000: PACKAGE test.test2 does not exist
10 #
11 # Dropping a non-existing package
12 #
13 DROP PACKAGE test2;
14 ERROR 42000: PACKAGE test.test2 does not exist
15 DROP PACKAGE IF EXISTS test2;
16 Warnings:
17 Note 1305 PACKAGE test.test2 does not exist
18 DROP PACKAGE BODY test2;
19 ERROR 42000: PACKAGE BODY test.test2 does not exist
20 #
21 # Bad combinations of OR REPLACE and IF EXISTS
22 #
23 CREATE OR REPLACE PACKAGE IF NOT EXISTS pkg AS
24 PROCEDURE p1;
25 END;
26 $$
27 ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
28 CREATE OR REPLACE PACKAGE BODY IF NOT EXISTS pkg AS
29 PROCEDURE p1 AS BEGIN NULL; END;
30 END;
31 $$
32 ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
33 #
34 # PACKAGE and PS
35 #
36 PREPARE stmt FROM 'CREATE PACKAGE test2 AS FUNCTION f1 RETURN INT; END test2';
37 CREATE PACKAGE test2 AS
38 FUNCTION f1 RETURN INT;
39 END;
40 $$
41 PREPARE stmt FROM 'CREATE PACKAGE BODY test2 AS'
42 ' FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;'
43 'END test2';
44 DROP PACKAGE test2;
45 #
46 # Package and READ ONLY transactions
47 #
48 SET SESSION TRANSACTION READ ONLY;
49 CREATE PACKAGE test2 AS
50 FUNCTION f1 RETURN INT;
51 PROCEDURE p1;
52 END
53 $$
54 ERROR 25006: Cannot execute statement in a READ ONLY transaction
55 SET SESSION TRANSACTION READ WRITE;
56 CREATE PACKAGE test2 AS
57 FUNCTION f1 RETURN INT;
58 FUNCTION f2 RETURN INT;
59 END;
60 $$
61 SET SESSION TRANSACTION READ ONLY
62 $$
63 CREATE PACKAGE BODY test2 AS
64 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
65 FUNCTION f2 RETURN INT AS BEGIN RETURN f1(); END;
66 PROCEDURE p1 AS
67 BEGIN
68 SELECT f2();
69 END;
70 END;
71 $$
72 ERROR 25006: Cannot execute statement in a READ ONLY transaction
73 SET SESSION TRANSACTION READ WRITE;
74 DROP PACKAGE test2;
75 SET SESSION TRANSACTION READ ONLY;
76 DROP PACKAGE test2;
77 ERROR 25006: Cannot execute statement in a READ ONLY transaction
78 DROP PACKAGE BODY test2;
79 ERROR 25006: Cannot execute statement in a READ ONLY transaction
80 SET SESSION TRANSACTION READ WRITE;
81 #
82 # Syntax error inside a CREATE PACKAGE, inside a routine definition
83 #
84 CREATE PACKAGE test2 AS
85 FUNCTION f1 RETURN INT;
86 FUNCTION f2 RETURN INT;
87 FUNCTION f3;
88 FUNCTION f4 RETURN INT;
89 END
90 $$
91 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 ';
92 FUNCTION f4 RETURN INT;
93 END' at line 4
94 #
95 # Syntax error inside a CREATE PACKAGE, outside of a routine definition
96 #
97 CREATE PACKAGE test2 AS
98 FUNCTION f1 RETURN INT;
99 FUNCTION f2 RETURN INT;
100 FUNCTION f3 RETURN INT AS BEGIN RETURN 10; END;
101 FUNCTION f4 RETURN INT;
102 END
103 $$
104 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 'AS BEGIN RETURN 10; END;
105 FUNCTION f4 RETURN INT;
106 END' at line 4
107 #
108 # Syntax error inside a CREATE PACKAGE BODY, inside a routine definition
109 #
110 CREATE PACKAGE test2 AS
111 FUNCTION f1 RETURN INT;
112 FUNCTION f2 RETURN INT;
113 END;
114 $$
115 CREATE PACKAGE BODY test2 AS
116 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
117 FUNCTION f2 RETURN INT SA BEGIN RETURN 10; END; -- Notice "SA" vs "AS"
118 END
119 $$
120 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 'SA BEGIN RETURN 10; END; -- Notice "SA" vs "AS"
121 END' at line 3
122 DROP PACKAGE test2;
123 #
124 # Syntax error inside a CREATE PACKAGE BODY, outside a routine definition
125 #
126 CREATE PACKAGE test2 AS
127 FUNCTION f1 RETURN INT;
128 FUNCTION f2 RETURN INT;
129 END;
130 $$
131 CREATE PACKAGE BODY test2 AS
132 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
133 SOME SYNTAX ERROR;
134 FUNCTION f2 RETURN INT AS BEGIN RETURN 10; END;
135 END
136 $$
137 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 'SOME SYNTAX ERROR;
138 FUNCTION f2 RETURN INT AS BEGIN RETURN 10; END;
139 END' at line 3
140 DROP PACKAGE test2;
141 #
142 # Syntax error inside a CREATE PACKAGE BODY executable section
143 #
144 CREATE PACKAGE test2 AS
145 FUNCTION f1 RETURN INT;
146 END;
147 $$
148 CREATE PACKAGE BODY test2 AS
149 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
150 BEGIN
151 SOME SYNTAX ERROR;
152 END
153 $$
154 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 'SYNTAX ERROR;
155 END' at line 4
156 DROP PACKAGE test2;
157 #
158 # CREATE PROCEDURE inside a package PROCEDURE is not allowed
159 #
160 CREATE PACKAGE test2 AS
161 PROCEDURE p1;
162 END;
163 $$
164 CREATE PACKAGE BODY test2 AS
165 PROCEDURE p1 AS
166 BEGIN
167 CREATE PROCEDURE p1 AS BEGIN NULL; END;
168 END;
169 END;
170 $$
171 ERROR 2F003: Can't create a PROCEDURE from within another stored routine
172 DROP PACKAGE test2;
173 #
174 # CREATE PACKAGE inside a package PROCEDURE is not allowed
175 #
176 CREATE PACKAGE test2 AS
177 PROCEDURE p1;
178 END;
179 $$
180 CREATE PACKAGE BODY test2 AS
181 PROCEDURE p1 AS
182 BEGIN
183 CREATE PACKAGE p1 AS PROCEDURE p1; END;
184 END;
185 END;
186 $$
187 ERROR 2F003: Can't create a PACKAGE from within another stored routine
188 DROP PACKAGE test2;
189 #
190 # CREATE PROCEDURE inside a package executable section is not allowed
191 #
192 CREATE PACKAGE test2 AS
193 PROCEDURE p1;
194 END;
195 $$
196 CREATE PACKAGE BODY test2 AS
197 PROCEDURE p1 AS BEGIN NULL; END;
198 BEGIN
199 CREATE PROCEDURE p1 AS BEGIN NULL; END;
200 END;
201 $$
202 ERROR 2F003: Can't create a PROCEDURE from within another stored routine
203 DROP PACKAGE test2;
204 #
205 # CREATE FUNCTION inside a package executable section is not allowed
206 #
207 CREATE PACKAGE test2 AS
208 PROCEDURE p1;
209 END;
210 $$
211 CREATE PACKAGE BODY test2 AS
212 PROCEDURE p1 AS BEGIN NULL; END;
213 BEGIN
214 CREATE FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
215 END;
216 $$
217 ERROR 2F003: Can't create a FUNCTION from within another stored routine
218 DROP PACKAGE test2;
219 #
220 # CREATE PACKAGE inside a package executable section is not allowed
221 #
222 CREATE PACKAGE test2 AS
223 PROCEDURE p1;
224 END;
225 $$
226 CREATE PACKAGE BODY test2 AS
227 PROCEDURE p1 AS BEGIN NULL; END;
228 BEGIN
229 CREATE PACKAGE p1 AS PROCEDURE p1; END;
230 END;
231 $$
232 ERROR 2F003: Can't create a PACKAGE from within another stored routine
233 DROP PACKAGE test2;
234 #
235 # Broken CREATE PACKAGE at CREATE PACKAGE BODY time
236 #
237 CREATE PACKAGE test2 AS
238 FUNCTION f1 RETURN INT;
239 END;
240 $$
241 UPDATE mysql.proc SET `body`='garbage'
242 WHERE db='test' AND name='test2' AND type='PACKAGE';
243 CREATE PACKAGE BODY test2 AS
244 FUNCTION f1 RETURN INT
245 AS BEGIN
246 RETURN f2();
247 END;
248 END;
249 $$
250 ERROR HY000: Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
251 show warnings;
252 Level Code Message
253 Error 1064 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 'garbage' at line 1
254 Error 1457 Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
255 DROP PACKAGE test2;
256 #
257 # Broken CREATE PACKAGE at a package function call time
258 #
259 CREATE PACKAGE test2 AS
260 FUNCTION f1 RETURN INT;
261 END;
262 $$
263 CREATE PACKAGE BODY test2 AS
264 FUNCTION f1 RETURN INT
265 AS BEGIN
266 RETURN f2();
267 END;
268 END;
269 $$
270 SELECT test2.f1();
271 ERROR 42000: FUNCTION test.f2 does not exist
272 UPDATE mysql.proc SET `body`='garbage'
273 WHERE db='test' AND name='test2' AND type='PACKAGE';
274 # sp-cache-invalidate
275 SELECT test2.f1();
276 ERROR 42000: FUNCTION test2.f1 does not exist
277 show warnings;
278 Level Code Message
279 Error 1305 FUNCTION test2.f1 does not exist
280 SELECT test2.f1();
281 ERROR 42000: FUNCTION test2.f1 does not exist
282 show warnings;
283 Level Code Message
284 Error 1305 FUNCTION test2.f1 does not exist
285 SELECT test2.f1();
286 ERROR 42000: FUNCTION test2.f1 does not exist
287 show warnings;
288 Level Code Message
289 Error 1305 FUNCTION test2.f1 does not exist
290 DROP PACKAGE test2;
291 #
292 # Broken CREATE PACKAGE at a package procedure call time
293 #
294 CREATE PACKAGE test2 AS
295 PROCEDURE p1;
296 END;
297 $$
298 CREATE PACKAGE BODY test2 AS
299 PROCEDURE p1
300 AS BEGIN
301 CALL p2;
302 END;
303 END;
304 $$
305 CALL test2.f1();
306 ERROR 42000: PROCEDURE test2.f1 does not exist
307 UPDATE mysql.proc SET `body`='garbage'
308 WHERE db='test' AND name='test2' AND type='PACKAGE';
309 # sp-cache-invalidate
310 CALL test2.p1();
311 ERROR 42000: PROCEDURE test2.p1 does not exist
312 show warnings;
313 Level Code Message
314 Error 1305 PROCEDURE test2.p1 does not exist
315 CALL test2.p1();
316 ERROR 42000: PROCEDURE test2.p1 does not exist
317 show warnings;
318 Level Code Message
319 Error 1305 PROCEDURE test2.p1 does not exist
320 CALL test2.p1();
321 ERROR 42000: PROCEDURE test2.p1 does not exist
322 show warnings;
323 Level Code Message
324 Error 1305 PROCEDURE test2.p1 does not exist
325 DROP PACKAGE test2;
326 #
327 # Bad routine names
328 #
329 CREATE PACKAGE p1 AS
330 PROCEDURE pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp1;
331 END;
332 $$
333 ERROR 42000: Identifier name 'pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp1' is too long
334 CREATE PACKAGE p1 AS
335 FUNCTION fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff1
336 RETURN INT;
337 END;
338 $$
339 ERROR 42000: Identifier name 'fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff1' is too long
340 CREATE PACKAGE p1 AS
341 PROCEDURE "p1 ";
342 END;
343 $$
344 ERROR 42000: Incorrect routine name 'p1 '
345 CREATE PACKAGE p1 AS
346 FUNCTION "f1 " RETURN INT;
347 END;
348 $$
349 ERROR 42000: Incorrect routine name 'f1 '
350 CREATE PACKAGE p1 AS
351 PROCEDURE "p1.p1";
352 END;
353 $$
354 ERROR 42000: Incorrect routine name 'p1.p1'
355 CREATE PACKAGE p1 AS
356 FUNCTION "f1.f1" RETURN INT;
357 END;
358 $$
359 ERROR 42000: Incorrect routine name 'f1.f1'
360 #
361 # Duplicate PROCEDURE in CREATE PACKAGE
362 #
363 CREATE PACKAGE test2 AS
364 PROCEDURE p1;
365 PROCEDURE p1;
366 END;
367 $$
368 ERROR 42000: PROCEDURE test2.p1 already exists
369 CREATE PACKAGE test2 AS
370 PROCEDURE p1;
371 PROCEDURE P1;
372 END;
373 $$
374 ERROR 42000: PROCEDURE test2.P1 already exists
375 #
376 # Duplicate FUNCTION in CREATE PACKAGE
377 #
378 CREATE PACKAGE test2 AS
379 FUNCTION f1 RETURN INT;
380 FUNCTION f1 RETURN INT;
381 END;
382 $$
383 ERROR 42000: FUNCTION test2.f1 already exists
384 CREATE PACKAGE test2 AS
385 FUNCTION f1 RETURN INT;
386 FUNCTION F1 RETURN INT;
387 END;
388 $$
389 ERROR 42000: FUNCTION test2.F1 already exists
390 #
391 # Duplicate PROCEDURE in CREATE PACKAGE BODY
392 #
393 CREATE PACKAGE test2 AS
394 PROCEDURE p1;
395 END;
396 $$
397 CREATE PACKAGE BODY test2 AS
398 PROCEDURE p1 AS BEGIN NULL; END;
399 PROCEDURE p1 AS BEGIN NULL; END;
400 END;
401 $$
402 ERROR 42000: PROCEDURE test2.p1 already exists
403 CREATE PACKAGE BODY test2 AS
404 PROCEDURE p1 AS BEGIN NULL; END;
405 PROCEDURE P1 AS BEGIN NULL; END;
406 END;
407 $$
408 ERROR 42000: PROCEDURE test2.P1 already exists
409 DROP PACKAGE test2;
410 #
411 # Duplicate FUNCTION in CREATE PACKAGE BODY
412 #
413 CREATE PACKAGE test2 AS
414 FUNCTION f1 RETURN INT;
415 END;
416 $$
417 CREATE PACKAGE BODY test2 AS
418 FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
419 FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
420 END;
421 $$
422 ERROR 42000: FUNCTION test2.f1 already exists
423 CREATE PACKAGE BODY test2 AS
424 FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
425 FUNCTION F1 RETURN INT AS BEGIN RETURN 0; END;
426 END;
427 $$
428 ERROR 42000: FUNCTION test2.F1 already exists
429 DROP PACKAGE test2;
430 #
431 # Routines declared in CREATE PACKAGE missing in CREATE PACKAGE BODY
432 #
433 CREATE PACKAGE test2 AS
434 PROCEDURE p1;
435 END;
436 $$
437 CREATE PACKAGE BODY test2 AS
438 PROCEDURE p2 AS BEGIN NULL; END;
439 END;
440 $$
441 ERROR HY000: PROCEDURE `test.test2.p1` is declared in the package specification but is not defined in the package body
442 DROP PACKAGE test2;
443 CREATE PACKAGE test2 AS
444 FUNCTION f1 RETURN INT;
445 END;
446 $$
447 CREATE PACKAGE BODY test2 AS
448 FUNCTION f2 RETURN INT AS BEGIN RETURN 10; END;
449 END;
450 $$
451 ERROR HY000: FUNCTION `test.test2.f1` is declared in the package specification but is not defined in the package body
452 DROP PACKAGE test2;
453 CREATE PACKAGE test2 AS
454 PROCEDURE p1;
455 END;
456 $$
457 CREATE PACKAGE BODY test2 AS
458 FUNCTION p1 RETURN INT AS BEGIN RETURN 10; END;
459 END;
460 $$
461 ERROR HY000: PROCEDURE `test.test2.p1` is declared in the package specification but is not defined in the package body
462 DROP PACKAGE test2;
463 CREATE PACKAGE test2 AS
464 PROCEDURE p1;
465 END;
466 $$
467 CREATE PACKAGE BODY test2 AS
468 PROCEDURE p1(a INT) AS BEGIN NULL; END; -- Notice different prototype
469 END;
470 $$
471 ERROR HY000: PROCEDURE `test.test2.p1` is declared in the package specification but is not defined in the package body
472 DROP PACKAGE test2;
473 #
474 # Forward declarations in CREATE PACKAGE BODY with missing implementations
475 #
476 CREATE PACKAGE test2 AS
477 PROCEDURE p1;
478 END;
479 $$
480 CREATE PACKAGE BODY test2 AS
481 PROCEDURE p1 AS BEGIN NULL; END;
482 PROCEDURE p2;
483 END;
484 $$
485 ERROR HY000: PROCEDURE `test.test2.p2` has a forward declaration but is not defined
486 CREATE PACKAGE BODY test2 AS
487 FUNCTION f1 RETURN INT;
488 PROCEDURE p1 AS BEGIN NULL; END;
489 END;
490 $$
491 ERROR HY000: FUNCTION `test.test2.f1` has a forward declaration but is not defined
492 DROP PACKAGE test2;
493 #
494 # Creating a new package
495 #
496 CREATE PACKAGE test2 COMMENT 'package-test2-comment' AS
497 FUNCTION f1 RETURN INT DETERMINISTIC;
498 FUNCTION f2(a INT) RETURN INT;
499 FUNCTION concat RETURN INT;
500 PROCEDURE p1;
501 PROCEDURE p2(a INT);
502 END
503 $$
504 Warnings:
505 Note 1585 This function 'concat' has the same name as a native function
506 SELECT * FROM mysql.proc WHERE db='test' AND name='test2';
507 db test
508 name test2
509 type PACKAGE
510 specific_name test2
511 language SQL
512 sql_data_access CONTAINS_SQL
513 is_deterministic NO
514 security_type DEFINER
515 param_list
516 returns
517 body AS
518 FUNCTION f1 RETURN INT DETERMINISTIC;
519 FUNCTION f2(a INT) RETURN INT;
520 FUNCTION concat RETURN INT;
521 PROCEDURE p1;
522 PROCEDURE p2(a INT);
523 END
524 definer root@localhost
525 created #
526 modified #
527 sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
528 comment package-test2-comment
529 character_set_client latin1
530 collation_connection latin1_swedish_ci
531 db_collation utf8mb4_uca1400_ai_ci
532 body_utf8 AS
533 FUNCTION f1 RETURN INT DETERMINISTIC;
534 FUNCTION f2(a INT) RETURN INT;
535 FUNCTION concat RETURN INT;
536 PROCEDURE p1;
537 PROCEDURE p2(a INT);
538 END
539 aggregate NONE
540 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='test2';
541 SPECIFIC_NAME test2
542 ROUTINE_CATALOG def
543 ROUTINE_SCHEMA test
544 ROUTINE_NAME test2
545 ROUTINE_TYPE PACKAGE
546 DATA_TYPE
547 CHARACTER_MAXIMUM_LENGTH NULL
548 CHARACTER_OCTET_LENGTH NULL
549 NUMERIC_PRECISION NULL
550 NUMERIC_SCALE NULL
551 DATETIME_PRECISION NULL
552 CHARACTER_SET_NAME NULL
553 COLLATION_NAME NULL
554 DTD_IDENTIFIER NULL
555 ROUTINE_BODY SQL
556 ROUTINE_DEFINITION AS
557 FUNCTION f1 RETURN INT DETERMINISTIC;
558 FUNCTION f2(a INT) RETURN INT;
559 FUNCTION concat RETURN INT;
560 PROCEDURE p1;
561 PROCEDURE p2(a INT);
562 END
563 EXTERNAL_NAME NULL
564 EXTERNAL_LANGUAGE NULL
565 PARAMETER_STYLE SQL
566 IS_DETERMINISTIC NO
567 SQL_DATA_ACCESS CONTAINS SQL
568 SQL_PATH NULL
569 SECURITY_TYPE DEFINER
570 CREATED #
571 LAST_ALTERED #
572 SQL_MODE PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
573 ROUTINE_COMMENT package-test2-comment
574 DEFINER root@localhost
575 CHARACTER_SET_CLIENT latin1
576 COLLATION_CONNECTION latin1_swedish_ci
577 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
578 CREATE PACKAGE IF NOT EXISTS test2 AS
579 FUNCTION f1 RETURN INT;
580 END test2
581 $$
582 Warnings:
583 Note 1304 PACKAGE test2 already exists
584 CREATE PACKAGE BODY test2 COMMENT 'package-body-test2-comment' AS
585 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
586 FUNCTION f2(a INT) RETURN INT AS BEGIN RETURN f1()+a; END;
587 FUNCTION concat RETURN INT AS BEGIN RETURN 1; END;
588 PROCEDURE p1 AS
589 BEGIN
590 SELECT f2(0);
591 END;
592 PROCEDURE p2(a INT) AS
593 BEGIN
594 SELECT f2(a);
595 END;
596 END;
597 $$
598 Warnings:
599 Note 1585 This function 'concat' has the same name as a native function
600 Note 1585 This function 'concat' has the same name as a native function
601 CREATE PACKAGE BODY IF NOT EXISTS test2 AS
602 FUNCTION f1 RETURN INT AS BEGIN RETURN 20; END;
603 FUNCTION f2(a INT) RETURN INT AS BEGIN RETURN f1()+a; END;
604 FUNCTION concat RETURN INT AS BEGIN RETURN 1; END;
605 PROCEDURE p1 AS
606 BEGIN
607 SELECT f2(0);
608 END;
609 PROCEDURE p2(a INT) AS
610 BEGIN
611 SELECT f2(a);
612 END;
613 END;
614 $$
615 Warnings:
616 Note 1585 This function 'concat' has the same name as a native function
617 Note 1585 This function 'concat' has the same name as a native function
618 Note 1304 PACKAGE BODY test2 already exists
619 SELECT test2.f1();
620 test2.f1()
621 10
622 SELECT test2.f2(1);
623 test2.f2(1)
624 11
625 CALL test2.p1();
626 f2(0)
627 10
628 CALL test2.p2(1);
629 f2(a)
630 11
631 SELECT * FROM mysql.proc WHERE db='test' AND name LIKE 'test2.%';
632 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='test2';
633 SPECIFIC_NAME test2
634 ROUTINE_CATALOG def
635 ROUTINE_SCHEMA test
636 ROUTINE_NAME test2
637 ROUTINE_TYPE PACKAGE
638 DATA_TYPE
639 CHARACTER_MAXIMUM_LENGTH NULL
640 CHARACTER_OCTET_LENGTH NULL
641 NUMERIC_PRECISION NULL
642 NUMERIC_SCALE NULL
643 DATETIME_PRECISION NULL
644 CHARACTER_SET_NAME NULL
645 COLLATION_NAME NULL
646 DTD_IDENTIFIER NULL
647 ROUTINE_BODY SQL
648 ROUTINE_DEFINITION AS
649 FUNCTION f1 RETURN INT DETERMINISTIC;
650 FUNCTION f2(a INT) RETURN INT;
651 FUNCTION concat RETURN INT;
652 PROCEDURE p1;
653 PROCEDURE p2(a INT);
654 END
655 EXTERNAL_NAME NULL
656 EXTERNAL_LANGUAGE NULL
657 PARAMETER_STYLE SQL
658 IS_DETERMINISTIC NO
659 SQL_DATA_ACCESS CONTAINS SQL
660 SQL_PATH NULL
661 SECURITY_TYPE DEFINER
662 CREATED #
663 LAST_ALTERED #
664 SQL_MODE PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
665 ROUTINE_COMMENT package-test2-comment
666 DEFINER root@localhost
667 CHARACTER_SET_CLIENT latin1
668 COLLATION_CONNECTION latin1_swedish_ci
669 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
670 SPECIFIC_NAME test2
671 ROUTINE_CATALOG def
672 ROUTINE_SCHEMA test
673 ROUTINE_NAME test2
674 ROUTINE_TYPE PACKAGE BODY
675 DATA_TYPE
676 CHARACTER_MAXIMUM_LENGTH NULL
677 CHARACTER_OCTET_LENGTH NULL
678 NUMERIC_PRECISION NULL
679 NUMERIC_SCALE NULL
680 DATETIME_PRECISION NULL
681 CHARACTER_SET_NAME NULL
682 COLLATION_NAME NULL
683 DTD_IDENTIFIER NULL
684 ROUTINE_BODY SQL
685 ROUTINE_DEFINITION AS
686 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
687 FUNCTION f2(a INT) RETURN INT AS BEGIN RETURN f1()+a; END;
688 FUNCTION concat RETURN INT AS BEGIN RETURN 1; END;
689 PROCEDURE p1 AS
690 BEGIN
691 SELECT f2(0);
692 END;
693 PROCEDURE p2(a INT) AS
694 BEGIN
695 SELECT f2(a);
696 END;
697 END
698 EXTERNAL_NAME NULL
699 EXTERNAL_LANGUAGE NULL
700 PARAMETER_STYLE SQL
701 IS_DETERMINISTIC NO
702 SQL_DATA_ACCESS CONTAINS SQL
703 SQL_PATH NULL
704 SECURITY_TYPE DEFINER
705 CREATED #
706 LAST_ALTERED #
707 SQL_MODE PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
708 ROUTINE_COMMENT package-body-test2-comment
709 DEFINER root@localhost
710 CHARACTER_SET_CLIENT latin1
711 COLLATION_CONNECTION latin1_swedish_ci
712 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
713 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME LIKE 'test2.%';
714 SHOW PACKAGE STATUS;
715 Db test
716 Name test2
717 Type PACKAGE
718 Definer root@localhost
719 Modified 0000-00-00 00:00:00
720 Created 0000-00-00 00:00:00
721 Security_type DEFINER
722 Comment package-test2-comment
723 character_set_client latin1
724 collation_connection latin1_swedish_ci
725 Database Collation utf8mb4_uca1400_ai_ci
726 SHOW PACKAGE BODY STATUS;
727 Db test
728 Name test2
729 Type PACKAGE BODY
730 Definer root@localhost
731 Modified 0000-00-00 00:00:00
732 Created 0000-00-00 00:00:00
733 Security_type DEFINER
734 Comment package-body-test2-comment
735 character_set_client latin1
736 collation_connection latin1_swedish_ci
737 Database Collation utf8mb4_uca1400_ai_ci
738 SHOW CREATE PACKAGE test2;
739 Package test2
740 sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
741 Create Package CREATE DEFINER="root"@"localhost" PACKAGE "test2" COMMENT 'package-test2-comment'
742 AS
743 FUNCTION f1 RETURN INT DETERMINISTIC;
744 FUNCTION f2(a INT) RETURN INT;
745 FUNCTION concat RETURN INT;
746 PROCEDURE p1;
747 PROCEDURE p2(a INT);
748 END
749 character_set_client latin1
750 collation_connection latin1_swedish_ci
751 Database Collation utf8mb4_uca1400_ai_ci
752 Warnings:
753 Level Note
754 Code 1585
755 Message This function 'concat' has the same name as a native function
756 SHOW CREATE PACKAGE BODY test2;
757 Package body test2
758 sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
759 Create Package Body CREATE DEFINER="root"@"localhost" PACKAGE BODY "test2" COMMENT 'package-body-test2-comment'
760 AS
761 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
762 FUNCTION f2(a INT) RETURN INT AS BEGIN RETURN f1()+a; END;
763 FUNCTION concat RETURN INT AS BEGIN RETURN 1; END;
764 PROCEDURE p1 AS
765 BEGIN
766 SELECT f2(0);
767 END;
768 PROCEDURE p2(a INT) AS
769 BEGIN
770 SELECT f2(a);
771 END;
772 END
773 character_set_client latin1
774 collation_connection latin1_swedish_ci
775 Database Collation utf8mb4_uca1400_ai_ci
776 Warnings:
777 Level Note
778 Code 1585
779 Message This function 'concat' has the same name as a native function
780 DROP PACKAGE BODY test2;
781 SELECT test2.f1();
782 ERROR 42000: FUNCTION test.test2.f1 does not exist
783 SELECT test2.f2();
784 ERROR 42000: FUNCTION test.test2.f2 does not exist
785 CALL test2.p1();
786 ERROR 42000: PROCEDURE test.test2.p1 does not exist
787 DROP PACKAGE BODY IF EXISTS test2;
788 Warnings:
789 Note 1305 PACKAGE BODY test.test2 does not exist
790 DROP PACKAGE BODY test2;
791 ERROR 42000: PACKAGE BODY test.test2 does not exist
792 DROP PACKAGE test2;
793 #
794 # Creating a new package in a remote database
795 #
796 CREATE DATABASE test2;
797 CREATE PACKAGE test2.test2 COMMENT 'package-test2-comment' AS
798 FUNCTION f1 RETURN INT;
799 PROCEDURE p1;
800 END
801 $$
802 CREATE PACKAGE BODY test2.test2 COMMENT 'package-body-test2-comment' AS
803 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
804 PROCEDURE p1 AS BEGIN SELECT f1(); END;
805 END;
806 $$
807 SHOW PACKAGE STATUS;
808 Db test2
809 Name test2
810 Type PACKAGE
811 Definer root@localhost
812 Modified 0000-00-00 00:00:00
813 Created 0000-00-00 00:00:00
814 Security_type DEFINER
815 Comment package-test2-comment
816 character_set_client latin1
817 collation_connection latin1_swedish_ci
818 Database Collation utf8mb4_uca1400_ai_ci
819 SHOW PACKAGE BODY STATUS;
820 Db test2
821 Name test2
822 Type PACKAGE BODY
823 Definer root@localhost
824 Modified 0000-00-00 00:00:00
825 Created 0000-00-00 00:00:00
826 Security_type DEFINER
827 Comment package-body-test2-comment
828 character_set_client latin1
829 collation_connection latin1_swedish_ci
830 Database Collation utf8mb4_uca1400_ai_ci
831 USE test2;
832 SELECT test2.f1();
833 test2.f1()
834 10
835 CALL test2.p1();
836 f1()
837 10
838 USE test;
839 DROP PACKAGE BODY test2.test2;
840 DROP PACKAGE test2.test2;
841 DROP DATABASE test2;
842 #
843 # Only public routines are available outside
844 #
845 CREATE PACKAGE test2 AS
846 FUNCTION f1 RETURN INT;
847 PROCEDURE p1;
848 END;
849 $$
850 CREATE PACKAGE BODY test2 AS
851 -- Public routines
852 FUNCTION f1 RETURN TEXT AS
853 BEGIN
854 RETURN 'This is test2.f1';
855 END;
856 PROCEDURE p1 AS
857 BEGIN
858 SELECT 'This is test2.p1';
859 END;
860 -- Private routines
861 FUNCTION f2 RETURN TEXT AS
862 BEGIN
863 RETURN 'This is test2.f2';
864 END;
865 PROCEDURE p2 AS
866 BEGIN
867 SELECT 'This is test2.p2';
868 END;
869 END;
870 $$
871 SELECT test2.f1();
872 test2.f1()
873 This is test2.f1
874 CALL test2.p1();
875 This is test2.p1
876 This is test2.p1
877 SELECT test2.f2();
878 ERROR 42000: FUNCTION test2.f2 does not exist
879 CALL test2.p2();
880 ERROR 42000: PROCEDURE test2.p2 does not exist
881 DROP PACKAGE test2;
882 #
883 # PACKAGE BODY with forward declarations
884 #
885 CREATE PACKAGE test2 AS
886 FUNCTION f1 RETURN INT;
887 PROCEDURE p1;
888 END;
889 $$
890 CREATE PACKAGE BODY test2 AS
891 -- Forward declarations
892 FUNCTION f2private RETURN TEXT;
893 PROCEDURE p2private;
894 -- Public routines
895 FUNCTION f1 RETURN TEXT AS
896 BEGIN
897 RETURN f2private();
898 END;
899 PROCEDURE p1 AS
900 BEGIN
901 CALL p2private;
902 END;
903 -- Definitions for the forward declarations
904 FUNCTION f2private RETURN TEXT AS
905 BEGIN
906 RETURN 'This is f2private';
907 END;
908 PROCEDURE p2private AS
909 BEGIN
910 SELECT 'This is p2private';
911 END;
912 END;
913 $$
914 SELECT test2.f1();
915 test2.f1()
916 This is f2private
917 CALL test2.p1();
918 This is p2private
919 This is p2private
920 DROP PACKAGE test2;
921 #
922 # Calling private routines with forward declarations,
923 # using qualified notation, e.g. "CALL pkg.proc"
924 #
925 CREATE PACKAGE test2 AS
926 FUNCTION f1 RETURN INT;
927 PROCEDURE p1;
928 END;
929 $$
930 CREATE PACKAGE BODY test2 AS
931 -- Forward declarations
932 FUNCTION f2private RETURN TEXT;
933 PROCEDURE p2private;
934 -- Public routines
935 FUNCTION f1 RETURN TEXT AS
936 BEGIN
937 RETURN test2.f2private();
938 END;
939 PROCEDURE p1 AS
940 BEGIN
941 CALL test2.p2private;
942 END;
943 -- Definitions for the forward declarations
944 FUNCTION f2private RETURN TEXT AS
945 BEGIN
946 RETURN 'This is f2private';
947 END;
948 PROCEDURE p2private AS
949 BEGIN
950 SELECT 'This is p2private' AS msg;
951 END;
952 END;
953 $$
954 SELECT test2.f1();
955 test2.f1()
956 This is f2private
957 CALL test2.p1();
958 msg
959 This is p2private
960 DROP PACKAGE test2;
961 #
962 # Calling private routines, using qualified notation, e.g. "pkg.proc"
963 #
964 CREATE PACKAGE test2 AS
965 FUNCTION f1 RETURN INT;
966 PROCEDURE p1;
967 END;
968 $$
969 CREATE PACKAGE BODY test2 AS
970 -- Private routines
971 FUNCTION f2private RETURN TEXT AS
972 BEGIN
973 RETURN 'This is f2private';
974 END;
975 PROCEDURE p2private AS
976 BEGIN
977 SELECT 'This is p2private' AS msg;
978 END;
979 -- Public routines
980 FUNCTION f1 RETURN TEXT AS
981 BEGIN
982 RETURN test2.f2private();
983 END;
984 PROCEDURE p1 AS
985 BEGIN
986 CALL test2.p2private;
987 END;
988 END;
989 $$
990 SELECT test2.f1();
991 test2.f1()
992 This is f2private
993 CALL test2.p1();
994 msg
995 This is p2private
996 DROP PACKAGE test2;
997 #
998 # Calling private routines from the package initialization section,
999 # using qualified notation, e.g. "pkg.proc"
1000 #
1001 CREATE PACKAGE test2 AS
1002 PROCEDURE p1;
1003 END;
1004 $$
1005 CREATE PACKAGE BODY test2 AS
1006 -- Private routines
1007 FUNCTION f2private RETURN TEXT AS
1008 BEGIN
1009 RETURN 'This is f2private';
1010 END;
1011 PROCEDURE p2private AS
1012 BEGIN
1013 SELECT 'This is p2private' AS msg;
1014 END;
1015 -- Public routines
1016 PROCEDURE p1 AS
1017 BEGIN
1018 SELECT 'This is p1' AS msg;
1019 END;
1020 BEGIN
1021 SELECT test2.f2private();
1022 CALL test2.p2private();
1023 END;
1024 $$
1025 CALL test2.p1();
1026 test2.f2private()
1027 This is f2private
1028 msg
1029 This is p2private
1030 msg
1031 This is p1
1032 DROP PACKAGE test2;
1033 #
1034 # Testing OR REPLACE
1035 #
1036 CREATE OR REPLACE PACKAGE pkg AS
1037 FUNCTION f0 RETURN INT;
1038 END;
1039 $$
1040 CREATE OR REPLACE PACKAGE pkg AS
1041 FUNCTION f1 RETURN INT;
1042 END;
1043 $$
1044 SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
1045 name type body
1046 pkg PACKAGE AS
1047 FUNCTION f1 RETURN INT;
1048 END
1049 CREATE OR REPLACE PACKAGE BODY pkg AS
1050 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
1051 END;
1052 $$
1053 SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
1054 name type body
1055 pkg PACKAGE AS
1056 FUNCTION f1 RETURN INT;
1057 END
1058 pkg PACKAGE BODY AS
1059 FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
1060 END
1061 SELECT pkg.f1();
1062 pkg.f1()
1063 10
1064 CREATE OR REPLACE PACKAGE BODY pkg AS
1065 FUNCTION f1 RETURN INT AS BEGIN RETURN 20; END;
1066 END;
1067 $$
1068 SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
1069 name type body
1070 pkg PACKAGE AS
1071 FUNCTION f1 RETURN INT;
1072 END
1073 pkg PACKAGE BODY AS
1074 FUNCTION f1 RETURN INT AS BEGIN RETURN 20; END;
1075 END
1076 SELECT pkg.f1();
1077 pkg.f1()
1078 20
1079 CREATE OR REPLACE PACKAGE pkg AS
1080 FUNCTION f1 RETURN BIGINT;
1081 END;
1082 $$
1083 SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
1084 name type body
1085 pkg PACKAGE AS
1086 FUNCTION f1 RETURN BIGINT;
1087 END
1088 SELECT pkg.f1();
1089 ERROR 42000: FUNCTION test.pkg.f1 does not exist
1090 CREATE OR REPLACE PACKAGE BODY pkg AS
1091 FUNCTION f1 RETURN INT AS BEGIN RETURN 30; END;
1092 END;
1093 $$
1094 SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
1095 name type body
1096 pkg PACKAGE AS
1097 FUNCTION f1 RETURN BIGINT;
1098 END
1099 pkg PACKAGE BODY AS
1100 FUNCTION f1 RETURN INT AS BEGIN RETURN 30; END;
1101 END
1102 SELECT pkg.f1();
1103 pkg.f1()
1104 30
1105 DROP PACKAGE pkg;
1106 #
1107 # Package routines accessing tables
1108 #
1109 CREATE TABLE t1 (a INT);
1110 CREATE PACKAGE test2 AS
1111 PROCEDURE p1(a INT);
1112 END;
1113 $$
1114 CREATE PACKAGE BODY test2 AS
1115 PROCEDURE p1(a INT) AS
1116 BEGIN
1117 INSERT INTO t1 VALUES (10);
1118 END;
1119 END;
1120 $$
1121 CALL test2.p1(10);
1122 SELECT * FROM t1;
1123 a
1124 10
1125 DROP PACKAGE test2;
1126 DROP TABLE t1;
1127 #
1128 # CREATE PACKAGE: Optional package name after the "END" keyword
1129 #
1130 CREATE PACKAGE test2 AS
1131 FUNCTION f1 RETURN INT;
1132 PROCEDURE p1;
1133 END test2.test2
1134 $$
1135 ERROR HY000: END identifier 'test2.test2' does not match 'test.test2'
1136 CREATE PACKAGE test2 AS
1137 FUNCTION f1 RETURN INT;
1138 PROCEDURE p1;
1139 END test3
1140 $$
1141 ERROR HY000: END identifier 'test3' does not match 'test2'
1142 CREATE PACKAGE test2 AS
1143 FUNCTION f1 RETURN INT;
1144 PROCEDURE p1;
1145 END test2
1146 $$
1147 DROP PACKAGE test2;
1148 #
1149 # MDEV-12089 sql_mode=ORACLE: Understand optional routine name after the END keyword
1150 #
1151 CREATE PACKAGE test2 AS
1152 FUNCTION f1 RETURN INT;
1153 PROCEDURE p1;
1154 END test2;
1155 $$
1156 CREATE PACKAGE BODY test2 AS
1157 FUNCTION f1 RETURN INT AS
1158 BEGIN
1159 RETURN 10;
1160 END f1.f1;
1161 END test2;
1162 $$
1163 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 '.f1;
1164 END test2' at line 5
1165 CREATE PACKAGE BODY test2 AS
1166 FUNCTION f1 RETURN INT AS
1167 BEGIN
1168 RETURN 10;
1169 END f2;
1170 END test2;
1171 $$
1172 ERROR HY000: END identifier 'f2' does not match 'f1'
1173 CREATE PACKAGE BODY test2 AS
1174 PROCEDURE p1 AS
1175 BEGIN
1176 NULL;
1177 END p1.p1;
1178 END test2;
1179 $$
1180 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 '.p1;
1181 END test2' at line 5
1182 CREATE PACKAGE BODY test2 AS
1183 PROCEDURE p1 AS
1184 BEGIN
1185 NULL;
1186 END p2;
1187 END test2;
1188 $$
1189 ERROR HY000: END identifier 'p2' does not match 'p1'
1190 CREATE PACKAGE BODY test2 AS
1191 FUNCTION f1 RETURN INT AS
1192 BEGIN
1193 RETURN 10;
1194 END f1;
1195 PROCEDURE p1 AS
1196 BEGIN
1197 NULL;
1198 END p1;
1199 END test2;
1200 $$
1201 DROP PACKAGE test2;
1202 #
1203 # Package and package routine name and end name are case insensitive
1204 #
1205 CREATE PACKAGE test2 AS
1206 FUNCTION f1 RETURN TEXT;
1207 PROCEDURE p1;
1208 END TEST2;
1209 $$
1210 CREATE PACKAGE BODY test2 AS
1211 FUNCTION f1 RETURN TEXT AS
1212 BEGIN
1213 RETURN 'This is f1';
1214 END F1;
1215 PROCEDURE P1 AS
1216 BEGIN
1217 SELECT 'This is p1' AS msg;
1218 END p1;
1219 END TEST2;
1220 $$
1221 SELECT TEST2.F1();
1222 TEST2.F1()
1223 This is f1
1224 SELECT test2.f1();
1225 test2.f1()
1226 This is f1
1227 CALL TEST2.p1();
1228 msg
1229 This is p1
1230 CALL test2.P1();
1231 msg
1232 This is p1
1233 DROP PACKAGE BODY TEST2;
1234 DROP PACKAGE TEST2;
1235 #
1236 # Testing various qualified/non-qualified db/package SP call chains
1237 #
1238 CREATE FUNCTION f3() RETURN TEXT AS
1239 BEGIN
1240 SET @track= @track || ' ' || 'test.f3()';
1241 RETURN '';
1242 END;
1243 $$
1244 CREATE PROCEDURE p3() AS
1245 BEGIN
1246 SET @track= @track || ' ' || 'test.p3()';
1247 END;
1248 $$
1249 CREATE FUNCTION ff2(task TEXT) RETURN TEXT AS
1250 step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
1251 tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
1252 rc TEXT;
1253 BEGIN
1254 SET @track= @track || ' ' || 'test.ff2()';
1255 CASE step
1256 WHEN '' THEN NULL;
1257 WHEN 'p3' THEN CALL p3();
1258 WHEN 'f3' THEN rc:= f3();
1259 WHEN 'pack.p2' THEN CALL pack.p2(tail);
1260 WHEN 'pack.f2' THEN rc:= pack.f2(tail);
1261 WHEN 'pack.p3' THEN CALL pack.p3();
1262 WHEN 'pack.f3' THEN rc:= pack.f3();
1263 WHEN 'test.p3' THEN CALL test.p3();
1264 WHEN 'test.f3' THEN rc:= test.f3();
1265 WHEN 'test.pp2' THEN CALL test.pp2(tail);
1266 WHEN 'test.ff2' THEN rc:= test.ff2(tail);
1267 ELSE SET @track= @track || ' ' || step || ' [unknown step]';
1268 END CASE;
1269 RETURN '';
1270 END;
1271 $$
1272 CREATE PROCEDURE pp2(task TEXT) AS
1273 step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
1274 tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
1275 rc TEXT;
1276 BEGIN
1277 SET @track= @track || ' ' || 'test.pp2()';
1278 CASE step
1279 WHEN '' THEN NULL;
1280 WHEN 'p3' THEN CALL p3();
1281 WHEN 'f3' THEN rc:= f3();
1282 WHEN 'pack.p2' THEN CALL pack.p2(tail);
1283 WHEN 'pack.f2' THEN rc:= pack.f2(tail);
1284 WHEN 'pack.p3' THEN CALL pack.p3();
1285 WHEN 'pack.f3' THEN rc:= pack.f3();
1286 WHEN 'test.p3' THEN CALL test.p3();
1287 WHEN 'test.f3' THEN rc:= test.f3();
1288 WHEN 'test.pp2' THEN CALL test.pp2(tail);
1289 WHEN 'test.ff2' THEN rc:= test.ff2(tail);
1290 ELSE SET @track= @track || ' ' || step || ' [unknown step]';
1291 END CASE;
1292 END;
1293 $$
1294 CREATE PACKAGE pack AS
1295 PROCEDURE p1(task TEXT);
1296 PROCEDURE p2(task TEXT);
1297 FUNCTION f1(task TEXT) RETURN TEXT;
1298 FUNCTION f2(step2 TEXT) RETURN TEXT;
1299 FUNCTION f3 RETURN TEXT;
1300 PROCEDURE p3;
1301 END;
1302 $$
1303 CREATE PACKAGE BODY pack AS
1304 PROCEDURE p1(task TEXT) AS
1305 step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
1306 tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
1307 rc TEXT;
1308 BEGIN
1309 SET @track= 'test.pack.p1()';
1310 CASE step
1311 WHEN '' THEN NULL;
1312 WHEN 'p2' THEN CALL p2(tail);
1313 WHEN 'f2' THEN rc:= f2(tail);
1314 WHEN 'p3' THEN CALL p3();
1315 WHEN 'f3' THEN rc:= f3();
1316 WHEN 'px' THEN CALL px();
1317 WHEN 'fx' THEN rc:= fx();
1318 WHEN 'pp2' THEN CALL pp2(tail);
1319 WHEN 'ff2' THEN rc:= ff2(tail);
1320 WHEN 'pack.p2' THEN CALL pack.p2(tail);
1321 WHEN 'pack.f2' THEN rc:= pack.f2(tail);
1322 WHEN 'pack.p3' THEN CALL pack.p3();
1323 WHEN 'pack.f3' THEN rc:= pack.f3();
1324 WHEN 'pack.px' THEN CALL pack.px();
1325 WHEN 'pack.fx' THEN rc:= pack.fx();
1326 WHEN 'test.p3' THEN CALL test.p3();
1327 WHEN 'test.f3' THEN rc:= test.f3();
1328 WHEN 'test.pp2' THEN CALL test.pp2(tail);
1329 WHEN 'test.ff2' THEN rc:= test.ff2(tail);
1330 ELSE SET @track= @track || ' ' || step || ' [unknown step]';
1331 END CASE;
1332 SELECT @track;
1333 END;
1334 FUNCTION f1(task TEXT) RETURN TEXT AS
1335 step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
1336 tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
1337 rc TEXT;
1338 BEGIN
1339 SET @track= 'test.pack.f1()';
1340 CASE step
1341 WHEN '' THEN NULL;
1342 WHEN 'p2' THEN CALL p2(tail);
1343 WHEN 'f2' THEN rc:= f2(tail);
1344 WHEN 'p3' THEN CALL p3();
1345 WHEN 'f3' THEN rc:= f3();
1346 WHEN 'px' THEN CALL px();
1347 WHEN 'fx' THEN rc:= fx();
1348 WHEN 'pp2' THEN CALL pp2(tail);
1349 WHEN 'ff2' THEN rc:= ff2(tail);
1350 WHEN 'pack.p2' THEN CALL pack.p2(tail);
1351 WHEN 'pack.f2' THEN rc:= pack.f2(tail);
1352 WHEN 'pack.p3' THEN CALL pack.p3();
1353 WHEN 'pack.f3' THEN rc:= pack.f3();
1354 WHEN 'pack.px' THEN CALL pack.px();
1355 WHEN 'pack.fx' THEN rc:= pack.fx();
1356 WHEN 'test.p3' THEN CALL test.p3();
1357 WHEN 'test.f3' THEN rc:= test.f3();
1358 WHEN 'test.pp2' THEN CALL test.pp2(tail);
1359 WHEN 'test.ff2' THEN rc:= test.ff2(tail);
1360 ELSE SET @track= @track || ' ' || step || ' [unknown step]';
1361 END CASE;
1362 SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT=@track;
1363 RETURN '';
1364 END;
1365 PROCEDURE p2(task TEXT) AS
1366 step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
1367 tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
1368 rc TEXT;
1369 BEGIN
1370 SET @track= @track || ' ' || 'test.pack.p2()';
1371 CASE step
1372 WHEN '' THEN NULL;
1373 WHEN 'p2' THEN CALL p2(tail);
1374 WHEN 'f2' THEN rc:= f2(tail);
1375 WHEN 'p3' THEN CALL p3();
1376 WHEN 'f3' THEN rc:= f3();
1377 WHEN 'px' THEN CALL px();
1378 WHEN 'fx' THEN rc:= fx();
1379 WHEN 'pp2' THEN CALL pp2(tail);
1380 WHEN 'ff2' THEN rc:= ff2(tail);
1381 WHEN 'pack.p2' THEN CALL pack.p2(tail);
1382 WHEN 'pack.f2' THEN rc:= pack.f2(tail);
1383 WHEN 'pack.p3' THEN CALL pack.p3();
1384 WHEN 'pack.f3' THEN rc:= pack.f3();
1385 WHEN 'pack.px' THEN CALL pack.px();
1386 WHEN 'pack.fx' THEN rc:= pack.fx();
1387 WHEN 'test.p3' THEN CALL test.p3();
1388 WHEN 'test.f3' THEN rc:= test.f3();
1389 WHEN 'test.pp2' THEN CALL test.pp2(tail);
1390 WHEN 'test.ff2' THEN rc:= test.ff2(tail);
1391 ELSE SET @track= @track || ' ' || step || ' [unknown step]';
1392 END CASE;
1393 END;
1394 FUNCTION f2(task TEXT) RETURN TEXT AS
1395 step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
1396 tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
1397 rc TEXT;
1398 BEGIN
1399 SET @track= @track || ' ' || 'test.pack.f2()';
1400 CASE step
1401 WHEN '' THEN NULL;
1402 WHEN 'p2' THEN CALL p2(tail);
1403 WHEN 'f2' THEN rc:= f2(tail);
1404 WHEN 'p3' THEN CALL p3();
1405 WHEN 'f3' THEN rc:= f3();
1406 WHEN 'px' THEN CALL px();
1407 WHEN 'fx' THEN rc:= fx();
1408 WHEN 'pp2' THEN CALL pp2(tail);
1409 WHEN 'ff2' THEN rc:= ff2(tail);
1410 WHEN 'pack.p2' THEN CALL pack.p2(tail);
1411 WHEN 'pack.f2' THEN rc:= pack.f2(tail);
1412 WHEN 'pack.p3' THEN CALL pack.p3();
1413 WHEN 'pack.f3' THEN rc:= pack.f3();
1414 WHEN 'pack.px' THEN CALL pack.px();
1415 WHEN 'pack.fx' THEN rc:= pack.fx();
1416 WHEN 'test.p3' THEN CALL test.p3();
1417 WHEN 'test.f3' THEN rc:= test.f3();
1418 WHEN 'test.pp2' THEN CALL test.pp2(tail);
1419 WHEN 'test.ff2' THEN rc:= test.ff2(tail);
1420 ELSE SET @track= @track || ' ' || step || ' [unknown step]';
1421 END CASE;
1422 RETURN '';
1423 END;
1424 PROCEDURE p3 AS
1425 BEGIN
1426 SET @track= @track || ' ' || 'test.pack.p3()';
1427 END;
1428 FUNCTION f3 RETURN TEXT AS
1429 BEGIN
1430 SET @track= @track || ' ' || 'test.pack.f3()';
1431 RETURN '';
1432 END;
1433 END pack;
1434 $$
1435 SET max_sp_recursion_depth=10;
1436 # pack.routine -> *
1437 CALL pack.p1('p2');
1438 @track
1439 test.pack.p1() test.pack.p2()
1440 CALL pack.p1('f2');
1441 @track
1442 test.pack.p1() test.pack.f2()
1443 CALL pack.p1('px');
1444 ERROR 42000: PROCEDURE test.px does not exist
1445 CALL pack.p1('fx');
1446 ERROR 42000: FUNCTION test.fx does not exist
1447 CALL pack.p1('pp2');
1448 @track
1449 test.pack.p1() test.pp2()
1450 CALL pack.p1('ff2');
1451 @track
1452 test.pack.p1() test.ff2()
1453 CALL pack.p1('pack.p2');
1454 @track
1455 test.pack.p1() test.pack.p2()
1456 CALL pack.p1('pack.f2');
1457 @track
1458 test.pack.p1() test.pack.f2()
1459 CALL pack.p1('pack.px');
1460 ERROR 42000: PROCEDURE pack.px does not exist
1461 CALL pack.p1('pack.fx');
1462 ERROR 42000: FUNCTION pack.fx does not exist
1463 CALL pack.p1('test.pp2');
1464 @track
1465 test.pack.p1() test.pp2()
1466 CALL pack.p1('test.ff2');
1467 @track
1468 test.pack.p1() test.ff2()
1469 DO pack.f1('p2');
1470 Warnings:
1471 Warning 1642 test.pack.f1() test.pack.p2()
1472 DO pack.f1('f2');
1473 Warnings:
1474 Warning 1642 test.pack.f1() test.pack.f2()
1475 DO pack.p1('px');
1476 ERROR 42000: FUNCTION pack.p1 does not exist
1477 DO pack.p1('fx');
1478 ERROR 42000: FUNCTION pack.p1 does not exist
1479 DO pack.f1('pp2');
1480 Warnings:
1481 Warning 1642 test.pack.f1() test.pp2()
1482 DO pack.f1('ff2');
1483 Warnings:
1484 Warning 1642 test.pack.f1() test.ff2()
1485 DO pack.f1('pack.p2');
1486 Warnings:
1487 Warning 1642 test.pack.f1() test.pack.p2()
1488 DO pack.f1('pack.f2');
1489 Warnings:
1490 Warning 1642 test.pack.f1() test.pack.f2()
1491 SELECT pack.f1('pack.px');
1492 ERROR 42000: PROCEDURE pack.px does not exist
1493 SELECT pack.f1('pack.fx');
1494 ERROR 42000: FUNCTION pack.fx does not exist
1495 DO pack.f1('test.pp2');
1496 Warnings:
1497 Warning 1642 test.pack.f1() test.pp2()
1498 DO pack.f1('test.ff2');
1499 Warnings:
1500 Warning 1642 test.pack.f1() test.ff2()
1501 #
1502 # Qualified_package_routine -> Non_qualified_package_routine
1503 #
1504 # pack.routine -> [pack.]routine -> pack.routine
1505 CALL pack.p1('p2 pack.p3');
1506 @track
1507 test.pack.p1() test.pack.p2() test.pack.p3()
1508 CALL pack.p1('p2 pack.f3');
1509 @track
1510 test.pack.p1() test.pack.p2() test.pack.f3()
1511 CALL pack.p1('f2 pack.p3');
1512 @track
1513 test.pack.p1() test.pack.f2() test.pack.p3()
1514 CALL pack.p1('f2 pack.f3');
1515 @track
1516 test.pack.p1() test.pack.f2() test.pack.f3()
1517 DO pack.f1('p2 pack.p3');
1518 Warnings:
1519 Warning 1642 test.pack.f1() test.pack.p2() test.pack.p3()
1520 DO pack.f1('p2 pack.f3');
1521 Warnings:
1522 Warning 1642 test.pack.f1() test.pack.p2() test.pack.f3()
1523 DO pack.f1('f2 pack.p3');
1524 Warnings:
1525 Warning 1642 test.pack.f1() test.pack.f2() test.pack.p3()
1526 DO pack.f1('f2 pack.f3');
1527 Warnings:
1528 Warning 1642 test.pack.f1() test.pack.f2() test.pack.f3()
1529 # pack.routine -> [pack.]routine -> [pack]routine
1530 CALL pack.p1('p2 p3');
1531 @track
1532 test.pack.p1() test.pack.p2() test.pack.p3()
1533 CALL pack.p1('p2 f3');
1534 @track
1535 test.pack.p1() test.pack.p2() test.pack.f3()
1536 CALL pack.p1('f2 p3');
1537 @track
1538 test.pack.p1() test.pack.f2() test.pack.p3()
1539 CALL pack.p1('f2 f3');
1540 @track
1541 test.pack.p1() test.pack.f2() test.pack.f3()
1542 DO pack.f1('p2 p3');
1543 Warnings:
1544 Warning 1642 test.pack.f1() test.pack.p2() test.pack.p3()
1545 DO pack.f1('p2 f3');
1546 Warnings:
1547 Warning 1642 test.pack.f1() test.pack.p2() test.pack.f3()
1548 DO pack.f1('f2 p3');
1549 Warnings:
1550 Warning 1642 test.pack.f1() test.pack.f2() test.pack.p3()
1551 DO pack.f1('f2 f3');
1552 Warnings:
1553 Warning 1642 test.pack.f1() test.pack.f2() test.pack.f3()
1554 # pack.routine -> [pack.]routine -> test.routine
1555 CALL pack.p1('p2 test.p3');
1556 @track
1557 test.pack.p1() test.pack.p2() test.p3()
1558 CALL pack.p1('p2 test.f3');
1559 @track
1560 test.pack.p1() test.pack.p2() test.f3()
1561 CALL pack.p1('f2 test.p3');
1562 @track
1563 test.pack.p1() test.pack.f2() test.p3()
1564 CALL pack.p1('f2 test.f3');
1565 @track
1566 test.pack.p1() test.pack.f2() test.f3()
1567 DO pack.f1('p2 test.p3');
1568 Warnings:
1569 Warning 1642 test.pack.f1() test.pack.p2() test.p3()
1570 DO pack.f1('p2 test.f3');
1571 Warnings:
1572 Warning 1642 test.pack.f1() test.pack.p2() test.f3()
1573 DO pack.f1('f2 test.p3');
1574 Warnings:
1575 Warning 1642 test.pack.f1() test.pack.f2() test.p3()
1576 DO pack.f1('f2 test.f3');
1577 Warnings:
1578 Warning 1642 test.pack.f1() test.pack.f2() test.f3()
1579 # pack.routine -> [pack.]routine -> [test.]routine
1580 CALL pack.p1('p2 pp2');
1581 @track
1582 test.pack.p1() test.pack.p2() test.pp2()
1583 CALL pack.p1('p2 ff2');
1584 @track
1585 test.pack.p1() test.pack.p2() test.ff2()
1586 CALL pack.p1('f2 pp2');
1587 @track
1588 test.pack.p1() test.pack.f2() test.pp2()
1589 CALL pack.p1('f2 ff2');
1590 @track
1591 test.pack.p1() test.pack.f2() test.ff2()
1592 DO pack.f1('p2 pp2');
1593 Warnings:
1594 Warning 1642 test.pack.f1() test.pack.p2() test.pp2()
1595 DO pack.f1('p2 ff2');
1596 Warnings:
1597 Warning 1642 test.pack.f1() test.pack.p2() test.ff2()
1598 DO pack.f1('f2 pp2');
1599 Warnings:
1600 Warning 1642 test.pack.f1() test.pack.f2() test.pp2()
1601 DO pack.f1('f2 ff2');
1602 Warnings:
1603 Warning 1642 test.pack.f1() test.pack.f2() test.ff2()
1604 #
1605 # Qualified_package_routine -> Non_qualified_database_routine
1606 #
1607 # pack.routine -> [test.]routine -> pack.routine
1608 CALL pack.p1('pp2 pack.p3');
1609 @track
1610 test.pack.p1() test.pp2() test.pack.p3()
1611 CALL pack.p1('pp2 pack.f3');
1612 @track
1613 test.pack.p1() test.pp2() test.pack.f3()
1614 CALL pack.p1('ff2 pack.p3');
1615 @track
1616 test.pack.p1() test.ff2() test.pack.p3()
1617 CALL pack.p1('ff2 pack.f3');
1618 @track
1619 test.pack.p1() test.ff2() test.pack.f3()
1620 DO pack.f1('pp2 pack.p3');
1621 Warnings:
1622 Warning 1642 test.pack.f1() test.pp2() test.pack.p3()
1623 DO pack.f1('pp2 pack.f3');
1624 Warnings:
1625 Warning 1642 test.pack.f1() test.pp2() test.pack.f3()
1626 DO pack.f1('ff2 pack.p3');
1627 Warnings:
1628 Warning 1642 test.pack.f1() test.ff2() test.pack.p3()
1629 DO pack.f1('ff2 pack.f3');
1630 Warnings:
1631 Warning 1642 test.pack.f1() test.ff2() test.pack.f3()
1632 # pack.routine -> [test.]routine -> test.routine
1633 CALL pack.p1('pp2 test.p3');
1634 @track
1635 test.pack.p1() test.pp2() test.p3()
1636 CALL pack.p1('pp2 test.f3');
1637 @track
1638 test.pack.p1() test.pp2() test.f3()
1639 CALL pack.p1('ff2 test.p3');
1640 @track
1641 test.pack.p1() test.ff2() test.p3()
1642 CALL pack.p1('ff2 test.f3');
1643 @track
1644 test.pack.p1() test.ff2() test.f3()
1645 DO pack.f1('pp2 test.p3');
1646 Warnings:
1647 Warning 1642 test.pack.f1() test.pp2() test.p3()
1648 DO pack.f1('pp2 test.f3');
1649 Warnings:
1650 Warning 1642 test.pack.f1() test.pp2() test.f3()
1651 DO pack.f1('ff2 test.p3');
1652 Warnings:
1653 Warning 1642 test.pack.f1() test.ff2() test.p3()
1654 DO pack.f1('ff2 test.f3');
1655 Warnings:
1656 Warning 1642 test.pack.f1() test.ff2() test.f3()
1657 # pack.routine -> [test.]routine -> [test.]routine
1658 CALL pack.p1('pp2 p3');
1659 @track
1660 test.pack.p1() test.pp2() test.p3()
1661 CALL pack.p1('pp2 f3');
1662 @track
1663 test.pack.p1() test.pp2() test.f3()
1664 CALL pack.p1('ff2 p3');
1665 @track
1666 test.pack.p1() test.ff2() test.p3()
1667 CALL pack.p1('ff2 f3');
1668 @track
1669 test.pack.p1() test.ff2() test.f3()
1670 DO pack.f1('pp2 p3');
1671 Warnings:
1672 Warning 1642 test.pack.f1() test.pp2() test.p3()
1673 DO pack.f1('pp2 f3');
1674 Warnings:
1675 Warning 1642 test.pack.f1() test.pp2() test.f3()
1676 DO pack.f1('ff2 p3');
1677 Warnings:
1678 Warning 1642 test.pack.f1() test.ff2() test.p3()
1679 DO pack.f1('ff2 f3');
1680 Warnings:
1681 Warning 1642 test.pack.f1() test.ff2() test.f3()
1682 #
1683 # Qualified_package_routine -> Qualified_package_routine
1684 #
1685 # pack.routine -> pack.routine -> pack.routine
1686 CALL pack.p1('pack.p2 pack.p3');
1687 @track
1688 test.pack.p1() test.pack.p2() test.pack.p3()
1689 CALL pack.p1('pack.p2 pack.f3');
1690 @track
1691 test.pack.p1() test.pack.p2() test.pack.f3()
1692 CALL pack.p1('pack.f2 pack.p3');
1693 @track
1694 test.pack.p1() test.pack.f2() test.pack.p3()
1695 CALL pack.p1('pack.f2 pack.f3');
1696 @track
1697 test.pack.p1() test.pack.f2() test.pack.f3()
1698 DO pack.f1('pack.p2 pack.p3');
1699 Warnings:
1700 Warning 1642 test.pack.f1() test.pack.p2() test.pack.p3()
1701 DO pack.f1('pack.p2 pack.f3');
1702 Warnings:
1703 Warning 1642 test.pack.f1() test.pack.p2() test.pack.f3()
1704 DO pack.f1('pack.f2 pack.p3');
1705 Warnings:
1706 Warning 1642 test.pack.f1() test.pack.f2() test.pack.p3()
1707 DO pack.f1('pack.f2 pack.f3');
1708 Warnings:
1709 Warning 1642 test.pack.f1() test.pack.f2() test.pack.f3()
1710 # pack.routine -> pack.routine -> [pack.]routine
1711 CALL pack.p1('pack.p2 p3');
1712 @track
1713 test.pack.p1() test.pack.p2() test.pack.p3()
1714 CALL pack.p1('pack.p2 f3');
1715 @track
1716 test.pack.p1() test.pack.p2() test.pack.f3()
1717 CALL pack.p1('pack.f2 p3');
1718 @track
1719 test.pack.p1() test.pack.f2() test.pack.p3()
1720 CALL pack.p1('pack.f2 f3');
1721 @track
1722 test.pack.p1() test.pack.f2() test.pack.f3()
1723 DO pack.f1('pack.p2 p3');
1724 Warnings:
1725 Warning 1642 test.pack.f1() test.pack.p2() test.pack.p3()
1726 DO pack.f1('pack.p2 f3');
1727 Warnings:
1728 Warning 1642 test.pack.f1() test.pack.p2() test.pack.f3()
1729 DO pack.f1('pack.f2 p3');
1730 Warnings:
1731 Warning 1642 test.pack.f1() test.pack.f2() test.pack.p3()
1732 DO pack.f1('pack.f2 f3');
1733 Warnings:
1734 Warning 1642 test.pack.f1() test.pack.f2() test.pack.f3()
1735 # pack.routine -> pack.routine -> test.routine
1736 CALL pack.p1('pack.p2 test.p3');
1737 @track
1738 test.pack.p1() test.pack.p2() test.p3()
1739 CALL pack.p1('pack.p2 test.f3');
1740 @track
1741 test.pack.p1() test.pack.p2() test.f3()
1742 CALL pack.p1('pack.f2 test.p3');
1743 @track
1744 test.pack.p1() test.pack.f2() test.p3()
1745 CALL pack.p1('pack.f2 test.f3');
1746 @track
1747 test.pack.p1() test.pack.f2() test.f3()
1748 DO pack.f1('pack.p2 test.p3');
1749 Warnings:
1750 Warning 1642 test.pack.f1() test.pack.p2() test.p3()
1751 DO pack.f1('pack.p2 test.f3');
1752 Warnings:
1753 Warning 1642 test.pack.f1() test.pack.p2() test.f3()
1754 DO pack.f1('pack.f2 test.p3');
1755 Warnings:
1756 Warning 1642 test.pack.f1() test.pack.f2() test.p3()
1757 DO pack.f1('pack.f2 test.f3');
1758 Warnings:
1759 Warning 1642 test.pack.f1() test.pack.f2() test.f3()
1760 # pack.routine -> pack.routine -> [test.]routine
1761 CALL pack.p1('pack.p2 pp2');
1762 @track
1763 test.pack.p1() test.pack.p2() test.pp2()
1764 CALL pack.p1('pack.p2 ff2');
1765 @track
1766 test.pack.p1() test.pack.p2() test.ff2()
1767 CALL pack.p1('pack.f2 pp2');
1768 @track
1769 test.pack.p1() test.pack.f2() test.pp2()
1770 CALL pack.p1('pack.f2 ff2');
1771 @track
1772 test.pack.p1() test.pack.f2() test.ff2()
1773 DO pack.f1('pack.p2 pp2');
1774 Warnings:
1775 Warning 1642 test.pack.f1() test.pack.p2() test.pp2()
1776 DO pack.f1('pack.p2 ff2');
1777 Warnings:
1778 Warning 1642 test.pack.f1() test.pack.p2() test.ff2()
1779 DO pack.f1('pack.f2 pp2');
1780 Warnings:
1781 Warning 1642 test.pack.f1() test.pack.f2() test.pp2()
1782 DO pack.f1('pack.f2 ff2');
1783 Warnings:
1784 Warning 1642 test.pack.f1() test.pack.f2() test.ff2()
1785 #
1786 # Qualified_package_routine -> Qualified_database_routine
1787 #
1788 pack.routine -> test.routine -> pack.routine
1789 CALL pack.p1('test.pp2 pack.p3');
1790 @track
1791 test.pack.p1() test.pp2() test.pack.p3()
1792 CALL pack.p1('test.pp2 pack.f3');
1793 @track
1794 test.pack.p1() test.pp2() test.pack.f3()
1795 CALL pack.p1('test.ff2 pack.p3');
1796 @track
1797 test.pack.p1() test.ff2() test.pack.p3()
1798 CALL pack.p1('test.ff2 pack.f3');
1799 @track
1800 test.pack.p1() test.ff2() test.pack.f3()
1801 DO pack.f1('test.pp2 pack.p3');
1802 Warnings:
1803 Warning 1642 test.pack.f1() test.pp2() test.pack.p3()
1804 DO pack.f1('test.pp2 pack.f3');
1805 Warnings:
1806 Warning 1642 test.pack.f1() test.pp2() test.pack.f3()
1807 DO pack.f1('test.ff2 pack.p3');
1808 Warnings:
1809 Warning 1642 test.pack.f1() test.ff2() test.pack.p3()
1810 DO pack.f1('test.ff2 pack.f3');
1811 Warnings:
1812 Warning 1642 test.pack.f1() test.ff2() test.pack.f3()
1813 pack.routine -> test.routine -> test.routine
1814 CALL pack.p1('test.pp2 test.p3');
1815 @track
1816 test.pack.p1() test.pp2() test.p3()
1817 CALL pack.p1('test.pp2 test.f3');
1818 @track
1819 test.pack.p1() test.pp2() test.f3()
1820 CALL pack.p1('test.ff2 test.p3');
1821 @track
1822 test.pack.p1() test.ff2() test.p3()
1823 CALL pack.p1('test.ff2 test.f3');
1824 @track
1825 test.pack.p1() test.ff2() test.f3()
1826 DO pack.f1('test.pp2 test.p3');
1827 Warnings:
1828 Warning 1642 test.pack.f1() test.pp2() test.p3()
1829 DO pack.f1('test.pp2 test.f3');
1830 Warnings:
1831 Warning 1642 test.pack.f1() test.pp2() test.f3()
1832 DO pack.f1('test.ff2 test.p3');
1833 Warnings:
1834 Warning 1642 test.pack.f1() test.ff2() test.p3()
1835 DO pack.f1('test.ff2 test.f3');
1836 Warnings:
1837 Warning 1642 test.pack.f1() test.ff2() test.f3()
1838 pack.routine -> test.routine -> [test.]routine
1839 CALL pack.p1('test.pp2 p3');
1840 @track
1841 test.pack.p1() test.pp2() test.p3()
1842 CALL pack.p1('test.pp2 f3');
1843 @track
1844 test.pack.p1() test.pp2() test.f3()
1845 CALL pack.p1('test.ff2 p3');
1846 @track
1847 test.pack.p1() test.ff2() test.p3()
1848 CALL pack.p1('test.ff2 f3');
1849 @track
1850 test.pack.p1() test.ff2() test.f3()
1851 DO pack.f1('test.pp2 p3');
1852 Warnings:
1853 Warning 1642 test.pack.f1() test.pp2() test.p3()
1854 DO pack.f1('test.pp2 f3');
1855 Warnings:
1856 Warning 1642 test.pack.f1() test.pp2() test.f3()
1857 DO pack.f1('test.ff2 p3');
1858 Warnings:
1859 Warning 1642 test.pack.f1() test.ff2() test.p3()
1860 DO pack.f1('test.ff2 f3');
1861 Warnings:
1862 Warning 1642 test.pack.f1() test.ff2() test.f3()
1863 # Longer chains
1864 CALL pack.p1('p2 f2 p2 test.pp2 test.ff2 pack.p3');
1865 @track
1866 test.pack.p1() test.pack.p2() test.pack.f2() test.pack.p2() test.pp2() test.ff2() test.pack.p3()
1867 CALL pack.p1('p2 test.pp2 pack.p2 pack.f2 test.ff2 pack.p3');
1868 @track
1869 test.pack.p1() test.pack.p2() test.pp2() test.pack.p2() test.pack.f2() test.ff2() test.pack.p3()
1870 DROP PACKAGE pack;
1871 DROP FUNCTION f3;
1872 DROP PROCEDURE p3;
1873 DROP FUNCTION ff2;
1874 DROP PROCEDURE pp2;
1875 #
1876 # Calling a standalone function from a non-current database,
1877 # which calls a package routine from the same non-current database.
1878 #
1879 CREATE PROCEDURE p1 AS
1880 BEGIN
1881 CALL pkg1.p1;
1882 END;
1883 $$
1884 CREATE PACKAGE pkg1 AS
1885 PROCEDURE p1;
1886 END;
1887 $$
1888 CREATE PACKAGE BODY pkg1 AS
1889 PROCEDURE p1 AS
1890 BEGIN
1891 SELECT database();
1892 END;
1893 END;
1894 $$
1895 CALL p1;
1896 database()
1897 test
1898 CREATE DATABASE test2;
1899 USE test2;
1900 CALL test.p1;
1901 database()
1902 test
1903 DROP DATABASE test2;
1904 CALL test.p1;
1905 database()
1906 test
1907 USE test;
1908 DROP PACKAGE pkg1;
1909 DROP PROCEDURE p1;
1910 #
1911 # Creating a package with a different DEFINER
1912 #
1913 CREATE USER xxx@localhost;
1914 CREATE DEFINER=xxx@localhost PACKAGE p1 AS
1915 PROCEDURE p1;
1916 END;
1917 $$
1918 CREATE DEFINER=xxx@localhost PACKAGE BODY p1 AS
1919 PROCEDURE p1 AS
1920 BEGIN
1921 NULL;
1922 END;
1923 END;
1924 $$
1925 SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
1926 definer name security_type type
1927 xxx@localhost p1 DEFINER PACKAGE
1928 xxx@localhost p1 DEFINER PACKAGE BODY
1929 DROP PACKAGE p1;
1930 DROP USER xxx@localhost;
1931 #
1932 # Creating a package with a different DEFINER, with SQL SECURITY INVOKER
1933 #
1934 CREATE USER xxx@localhost;
1935 CREATE DEFINER=xxx@localhost PACKAGE p1 SQL SECURITY INVOKER AS
1936 PROCEDURE p1;
1937 END;
1938 $$
1939 CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER AS
1940 PROCEDURE p1 AS
1941 BEGIN
1942 NULL;
1943 END;
1944 END;
1945 $$
1946 SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
1947 definer name security_type type
1948 xxx@localhost p1 INVOKER PACKAGE
1949 xxx@localhost p1 INVOKER PACKAGE BODY
1950 DROP PACKAGE p1;
1951 DROP USER xxx@localhost;
1952 #
1953 # A package with an initialization section
1954 #
1955 CREATE PACKAGE p1 AS
1956 PROCEDURE p1;
1957 FUNCTION f1 RETURN INT;
1958 END;
1959 $$
1960 CREATE PACKAGE BODY p1 AS
1961 PROCEDURE p1 AS BEGIN SET @a=@a+1; SELECT @a; END;
1962 FUNCTION f1 RETURN INT AS BEGIN SET @a=@a+1; RETURN @a; END;
1963 BEGIN
1964 SET @a:=10;
1965 END;
1966 $$
1967 CALL p1.p1();
1968 @a
1969 11
1970 CALL p1.p1();
1971 @a
1972 12
1973 SELECT p1.f1();
1974 p1.f1()
1975 13
1976 SELECT p1.f1();
1977 p1.f1()
1978 14
1979 # sp-cache-invalidate
1980 SELECT p1.f1();
1981 p1.f1()
1982 11
1983 CALL p1.p1();
1984 @a
1985 12
1986 SELECT p1.f1();
1987 p1.f1()
1988 13
1989 CALL p1.p1();
1990 @a
1991 14
1992 DROP PACKAGE p1;
1993 #
1994 # A package with an initialization section calling
1995 # routines from the same package, and standalone routines.
1996 #
1997 CREATE PROCEDURE init20 AS
1998 BEGIN
1999 SET @msg= @msg || '[init20]';
2000 END;
2001 $$
2002 CREATE PACKAGE p1 AS
2003 PROCEDURE init1;
2004 PROCEDURE init2;
2005 FUNCTION init3 RETURN INT;
2006 PROCEDURE p1;
2007 FUNCTION f1 RETURN TEXT;
2008 END;
2009 $$
2010 CREATE PACKAGE BODY p1 AS
2011 PROCEDURE init1 AS
2012 BEGIN
2013 SET @msg= @msg || '[p1.init1]';
2014 END;
2015 PROCEDURE init2 AS
2016 BEGIN
2017 SET @msg= @msg || '[p1.init2]';
2018 END;
2019 FUNCTION init3 RETURN INT AS
2020 BEGIN
2021 SET @msg= @msg || '[p1.init3]';
2022 RETURN 0;
2023 END;
2024 PROCEDURE p1 AS
2025 BEGIN
2026 SET @msg= @msg || '[p1.p1]';
2027 SELECT @msg;
2028 END;
2029 FUNCTION f1 RETURN TEXT AS
2030 BEGIN
2031 SET @msg= @msg || '[p1.f1]';
2032 RETURN @msg;
2033 END;
2034 BEGIN
2035 SET @msg= '';
2036 init1();
2037 init2();
2038 DO init3();
2039 init20();
2040 END;
2041 $$
2042 CALL p1.p1();
2043 @msg
2044 [p1.init1][p1.init2][p1.init3][init20][p1.p1]
2045 CALL p1.p1();
2046 @msg
2047 [p1.init1][p1.init2][p1.init3][init20][p1.p1][p1.p1]
2048 SELECT p1.f1();
2049 p1.f1()
2050 [p1.init1][p1.init2][p1.init3][init20][p1.p1][p1.p1][p1.f1]
2051 SELECT p1.f1();
2052 p1.f1()
2053 [p1.init1][p1.init2][p1.init3][init20][p1.p1][p1.p1][p1.f1][p1.f1]
2054 # sp-cache-invalidate
2055 SELECT p1.f1();
2056 p1.f1()
2057 [p1.init1][p1.init2][p1.init3][init20][p1.f1]
2058 CALL p1.p1();
2059 @msg
2060 [p1.init1][p1.init2][p1.init3][init20][p1.f1][p1.p1]
2061 SELECT p1.f1();
2062 p1.f1()
2063 [p1.init1][p1.init2][p1.init3][init20][p1.f1][p1.p1][p1.f1]
2064 CALL p1.p1();
2065 @msg
2066 [p1.init1][p1.init2][p1.init3][init20][p1.f1][p1.p1][p1.f1][p1.p1]
2067 DROP PACKAGE p1;
2068 DROP PROCEDURE init20;
2069 #
2070 # EXECUTE IMMEDIATE in the package initialization section
2071 #
2072 SET @a=1000;
2073 CREATE TABLE t1 AS SELECT 10 AS a;
2074 CREATE PACKAGE p1 AS
2075 PROCEDURE p1;
2076 FUNCTION f1 RETURN INT;
2077 END;
2078 $$
2079 CREATE PACKAGE BODY p1 AS
2080 PROCEDURE p1 AS BEGIN SET @a=@a+1; SELECT @a; END;
2081 FUNCTION f1 RETURN INT AS BEGIN SET @a=@a+1; RETURN @a; END;
2082 BEGIN
2083 EXECUTE IMMEDIATE 'SELECT MAX(a) FROM t1 INTO @a';
2084 END;
2085 $$
2086 CALL p1.p1();
2087 @a
2088 11
2089 CALL p1.p1();
2090 @a
2091 12
2092 SELECT p1.f1();
2093 p1.f1()
2094 13
2095 SELECT p1.f1();
2096 p1.f1()
2097 14
2098 # sp-cache-invalidate
2099 SELECT p1.f1();
2100 ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
2101 DROP PACKAGE p1;
2102 DROP TABLE t1;
2103 #
2104 # A package with an initialization section, loading table data into a user variable
2105 #
2106 SET @a=1000;
2107 CREATE TABLE t1 AS SELECT 10 AS a;
2108 CREATE PACKAGE p1 AS
2109 PROCEDURE p1;
2110 FUNCTION f1 RETURN INT;
2111 END;
2112 $$
2113 CREATE PACKAGE BODY p1 AS
2114 PROCEDURE p1 AS BEGIN SET @a=@a+1; SELECT @a; END;
2115 FUNCTION f1 RETURN INT AS BEGIN SET @a=@a+1; RETURN @a; END;
2116 BEGIN
2117 SELECT MAX(a) FROM t1 INTO @a;
2118 END;
2119 $$
2120 CALL p1.p1();
2121 @a
2122 11
2123 CALL p1.p1();
2124 @a
2125 12
2126 SELECT p1.f1();
2127 p1.f1()
2128 13
2129 SELECT p1.f1();
2130 p1.f1()
2131 14
2132 # sp-cache-invalidate
2133 SELECT p1.f1();
2134 p1.f1()
2135 11
2136 DROP PACKAGE p1;
2137 DROP TABLE t1;
2138 #
2139 # A package with an initialization section producing an error
2140 #
2141 CREATE PACKAGE p1 AS
2142 PROCEDURE p1;
2143 FUNCTION f1 RETURN TEXT;
2144 END;
2145 $$
2146 CREATE PACKAGE BODY p1 AS
2147 PROCEDURE p1 AS BEGIN SELECT 'This is p1' AS msg; END;
2148 FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is f1'; END;
2149 BEGIN
2150 SELECT 1 FROM t1 INTO @a;
2151 END;
2152 $$
2153 CALL p1.p1();
2154 ERROR 42S02: Table 'test.t1' doesn't exist
2155 SELECT p1.f1();
2156 ERROR 42S02: Table 'test.t1' doesn't exist
2157 # sp-cache-invalidate
2158 SELECT p1.f1();
2159 ERROR 42S02: Table 'test.t1' doesn't exist
2160 CALL p1.p1();
2161 ERROR 42S02: Table 'test.t1' doesn't exist
2162 SELECT p1.f1();
2163 ERROR 42S02: Table 'test.t1' doesn't exist
2164 CREATE TABLE t1 (a INT) AS SELECT 1;
2165 CALL p1.p1();
2166 msg
2167 This is p1
2168 # sp-cache-invalidate
2169 SELECT p1.f1();
2170 p1.f1()
2171 This is f1
2172 # sp-cache-invalidate
2173 CALL p1.p1();
2174 msg
2175 This is p1
2176 DROP TABLE t1;
2177 DROP PACKAGE p1;
2178 #
2179 # A package with SF-unsafe statements in the initialization section
2180 #
2181 CREATE PACKAGE p1 AS
2182 PROCEDURE p1;
2183 FUNCTION f1 RETURN TEXT;
2184 END;
2185 $$
2186 CREATE PACKAGE BODY p1 AS
2187 PROCEDURE p1 AS BEGIN SELECT 'This is p1' AS msg; END;
2188 FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is f1'; END;
2189 BEGIN
2190 CREATE TABLE IF NOT EXISTS t1 (a INT);
2191 DROP TABLE IF EXISTS t1;
2192 END;
2193 $$
2194 CALL p1.p1();
2195 msg
2196 This is p1
2197 SELECT p1.f1();
2198 p1.f1()
2199 This is f1
2200 # sp-cache-invalidate
2201 SELECT p1.f1();
2202 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
2203 CALL p1.p1();
2204 msg
2205 This is p1
2206 SELECT p1.f1();
2207 p1.f1()
2208 This is f1
2209 DROP PACKAGE p1;
2210 #
2211 # MDEV-13139 Package-wide variables in CREATE PACKAGE
2212 #
2213 CREATE PACKAGE p1 AS
2214 PROCEDURE p1;
2215 FUNCTION f1 RETURN INT;
2216 END;
2217 $$
2218 CREATE PACKAGE BODY p1 AS
2219 a INT;
2220 a INT;
2221 PROCEDURE p1 AS
2222 BEGIN
2223 CREATE VIEW v1 AS SELECT a;
2224 END;
2225 END;
2226 $$
2227 ERROR 42000: Duplicate variable: a
2228 CREATE PACKAGE BODY p1 AS
2229 a INT;
2230 PROCEDURE p1 AS
2231 BEGIN
2232 NULL;
2233 END;
2234 b INT; -- Variables cannot go after routine definitions
2235 END;
2236 $$
2237 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 'b INT; -- Variables cannot go after routine definitions
2238 END' at line 7
2239 CREATE PACKAGE BODY p1 AS
2240 a INT;
2241 PROCEDURE p1 AS
2242 BEGIN
2243 CREATE VIEW v1 AS SELECT a;
2244 END;
2245 END;
2246 $$
2247 ERROR HY000: View's SELECT contains a variable or parameter
2248 CREATE PACKAGE BODY p1 AS
2249 a INT:=NULL;
2250 PROCEDURE p1 AS
2251 BEGIN
2252 SELECT a;
2253 a:=COALESCE(a,0)+100;
2254 SET a=a+1;
2255 END;
2256 FUNCTION f1 RETURN INT AS
2257 BEGIN
2258 RETURN a;
2259 END;
2260 END;
2261 $$
2262 CALL p1.p1;
2263 a
2264 NULL
2265 CALL p1.p1;
2266 a
2267 101
2268 CALL p1.p1;
2269 a
2270 202
2271 SELECT p1.f1();
2272 p1.f1()
2273 303
2274 DROP PACKAGE p1;
2275 #
2276 # One package variable with a default value
2277 #
2278 CREATE PACKAGE p1 AS
2279 PROCEDURE p1;
2280 FUNCTION f1 RETURN INT;
2281 END;
2282 $$
2283 CREATE PACKAGE BODY p1 AS
2284 a INT:=10;
2285 PROCEDURE p1 AS BEGIN a:=a+1; SELECT a; END;
2286 FUNCTION f1 RETURN INT AS BEGIN a:=a+1; RETURN a; END;
2287 END;
2288 $$
2289 CALL p1.p1();
2290 a
2291 11
2292 CALL p1.p1();
2293 a
2294 12
2295 SELECT p1.f1();
2296 p1.f1()
2297 13
2298 SELECT p1.f1();
2299 p1.f1()
2300 14
2301 # sp-cache-invalidate
2302 SELECT p1.f1();
2303 p1.f1()
2304 11
2305 CALL p1.p1();
2306 a
2307 12
2308 SELECT p1.f1();
2309 p1.f1()
2310 13
2311 CALL p1.p1();
2312 a
2313 14
2314 DROP PACKAGE p1;
2315 CREATE PACKAGE p1 AS
2316 PROCEDURE p1;
2317 FUNCTION f1 RETURN INT;
2318 END;
2319 $$
2320 CREATE PACKAGE BODY p1 AS
2321 a ROW (a INT, b TEXT):=ROW(10,'bbb');
2322 PROCEDURE p1 AS
2323 BEGIN
2324 a.a:= a.a+1;
2325 a.b:= a.b || 'B';
2326 SELECT a.a, a.b;
2327 END;
2328 FUNCTION f1 RETURN INT AS BEGIN a.a:= a.a+1; RETURN a.a; END;
2329 END;
2330 $$
2331 CALL p1.p1();
2332 a.a a.b
2333 11 bbbB
2334 CALL p1.p1();
2335 a.a a.b
2336 12 bbbBB
2337 SELECT p1.f1();
2338 p1.f1()
2339 13
2340 SELECT p1.f1();
2341 p1.f1()
2342 14
2343 # sp-cache-invalidate
2344 SELECT p1.f1();
2345 p1.f1()
2346 11
2347 CALL p1.p1();
2348 a.a a.b
2349 12 bbbB
2350 SELECT p1.f1();
2351 p1.f1()
2352 13
2353 CALL p1.p1();
2354 a.a a.b
2355 14 bbbBB
2356 DROP PACKAGE p1;
2357 CREATE TABLE t1 (a INT);
2358 CREATE PACKAGE p1 AS
2359 PROCEDURE p1;
2360 FUNCTION f1 RETURN INT;
2361 END;
2362 $$
2363 CREATE PACKAGE BODY p1 AS
2364 a t1.a%TYPE:=10;
2365 PROCEDURE p1 AS BEGIN a:=a+1; SELECT a; END;
2366 FUNCTION f1 RETURN INT AS BEGIN a:=a+1; RETURN a; END;
2367 END;
2368 $$
2369 CALL p1.p1();
2370 a
2371 11
2372 CALL p1.p1();
2373 a
2374 12
2375 SELECT p1.f1();
2376 p1.f1()
2377 13
2378 SELECT p1.f1();
2379 p1.f1()
2380 14
2381 # sp-cache-invalidate
2382 SELECT p1.f1();
2383 p1.f1()
2384 11
2385 CALL p1.p1();
2386 a
2387 12
2388 SELECT p1.f1();
2389 p1.f1()
2390 13
2391 CALL p1.p1();
2392 a
2393 14
2394 DROP PACKAGE p1;
2395 DROP TABLE t1;
2396 CREATE TABLE t1 (a INT, b TEXT);
2397 CREATE PACKAGE p1 AS
2398 PROCEDURE p1;
2399 FUNCTION f1 RETURN INT;
2400 END;
2401 $$
2402 CREATE PACKAGE BODY p1 AS
2403 a t1%ROWTYPE:=ROW(10,'bbb');
2404 PROCEDURE p1 AS
2405 BEGIN
2406 a.a:= a.a+1;
2407 a.b:= a.b || 'B';
2408 SELECT a.a, a.b;
2409 END;
2410 FUNCTION f1 RETURN INT AS BEGIN a.a:= a.a+1; RETURN a.a; END;
2411 END;
2412 $$
2413 CALL p1.p1();
2414 a.a a.b
2415 11 bbbB
2416 CALL p1.p1();
2417 a.a a.b
2418 12 bbbBB
2419 SELECT p1.f1();
2420 p1.f1()
2421 13
2422 SELECT p1.f1();
2423 p1.f1()
2424 14
2425 # sp-cache-invalidate
2426 SELECT p1.f1();
2427 p1.f1()
2428 11
2429 CALL p1.p1();
2430 a.a a.b
2431 12 bbbB
2432 SELECT p1.f1();
2433 p1.f1()
2434 13
2435 CALL p1.p1();
2436 a.a a.b
2437 14 bbbBB
2438 DROP PACKAGE p1;
2439 DROP TABLE t1;
2440 #
2441 # One package variable, set in the package initialization section
2442 #
2443 CREATE PACKAGE p1 AS
2444 PROCEDURE p1;
2445 FUNCTION f1 RETURN INT;
2446 END;
2447 $$
2448 CREATE PACKAGE BODY p1 AS
2449 a INT;
2450 PROCEDURE p1 AS BEGIN a:=a+1; SELECT a; END;
2451 FUNCTION f1 RETURN INT AS BEGIN a:=a+1; RETURN a; END;
2452 BEGIN
2453 a:=10;
2454 END;
2455 $$
2456 CALL p1.p1();
2457 a
2458 11
2459 CALL p1.p1();
2460 a
2461 12
2462 SELECT p1.f1();
2463 p1.f1()
2464 13
2465 SELECT p1.f1();
2466 p1.f1()
2467 14
2468 # sp-cache-invalidate
2469 SELECT p1.f1();
2470 p1.f1()
2471 11
2472 CALL p1.p1();
2473 a
2474 12
2475 SELECT p1.f1();
2476 p1.f1()
2477 13
2478 CALL p1.p1();
2479 a
2480 14
2481 DROP PACKAGE p1;
2482 #
2483 # A package with an initialization section,
2484 # loading table data into a package variable
2485 #
2486 CREATE TABLE t1 AS SELECT 10 AS a;
2487 CREATE PACKAGE p1 AS
2488 PROCEDURE p1;
2489 FUNCTION f1 RETURN INT;
2490 END;
2491 $$
2492 CREATE PACKAGE BODY p1 AS
2493 a INT;
2494 PROCEDURE p1 AS BEGIN SET a=a+1; SELECT a; END;
2495 FUNCTION f1 RETURN INT AS BEGIN SET a=a+1; RETURN a; END;
2496 BEGIN
2497 a:=(SELECT MAX(t1.a) FROM t1);
2498 END;
2499 $$
2500 CALL p1.p1();
2501 a
2502 11
2503 CALL p1.p1();
2504 a
2505 12
2506 SELECT p1.f1();
2507 p1.f1()
2508 13
2509 SELECT p1.f1();
2510 p1.f1()
2511 14
2512 # sp-cache-invalidate
2513 SELECT p1.f1();
2514 p1.f1()
2515 11
2516 DROP PACKAGE p1;
2517 DROP TABLE t1;
2518 #
2519 # Package variables and XPath
2520 #
2521 CREATE PACKAGE p1 AS
2522 FUNCTION f1 RETURN TEXT;
2523 END;
2524 $$
2525 CREATE PACKAGE BODY p1 AS
2526 i INT:=0;
2527 xml TEXT:= '<a><b>b1</b><b>b2</b><b>b3</b></a>';
2528 FUNCTION f1 RETURN TEXT AS
2529 BEGIN
2530 SET i=i+1;
2531 RETURN ExtractValue(xml, '/a/b[$i]');
2532 END;
2533 END;
2534 $$
2535 SELECT p1.f1();
2536 p1.f1()
2537 b1
2538 SELECT p1.f1();
2539 p1.f1()
2540 b2
2541 SELECT p1.f1();
2542 p1.f1()
2543 b3
2544 DROP PACKAGE p1;
2545 #
2546 # Package variables as OUT routine parameter
2547 #
2548 CREATE PACKAGE p1 AS
2549 PROCEDURE p1;
2550 END;
2551 $$
2552 CREATE PACKAGE BODY p1 AS
2553 a INT;
2554 b INT;
2555 c INT:=10;
2556 PROCEDURE p2(a OUT INT) AS
2557 BEGIN
2558 a:=c;
2559 c:=c+1;
2560 END;
2561 PROCEDURE p1 AS
2562 BEGIN
2563 CALL p2(b);
2564 SELECT a,b;
2565 END;
2566 BEGIN
2567 CALL p2(a);
2568 END;
2569 $$
2570 CALL p1.p1;
2571 a b
2572 10 11
2573 DROP PACKAGE p1;
2574 CREATE PACKAGE p1 AS
2575 PROCEDURE p1;
2576 END;
2577 $$
2578 CREATE PACKAGE BODY p1 AS
2579 a ROW(a INT, b TEXT);
2580 b ROW(a INT, b TEXT);
2581 c ROW(a INT, b TEXT):=ROW(1,'b');
2582 PROCEDURE p2(x OUT ROW(a INT,b TEXT)) AS
2583 BEGIN
2584 x:=c;
2585 x.a:=c.a+100;
2586 x.b:=c.b||'X';
2587 c.a:=c.a+1;
2588 c.b:=c.b||'B';
2589 END;
2590 PROCEDURE p1 AS
2591 BEGIN
2592 CALL p2(b);
2593 SELECT a.a,a.b,b.a,b.b;
2594 END;
2595 BEGIN
2596 CALL p2(a);
2597 END;
2598 $$
2599 CALL p1.p1;
2600 a.a a.b b.a b.b
2601 101 bX 102 bBX
2602 DROP PACKAGE p1;
2603 CREATE TABLE t1 (a INT,b TEXT);
2604 CREATE PACKAGE p1 AS
2605 PROCEDURE p1;
2606 END;
2607 $$
2608 CREATE PACKAGE BODY p1 AS
2609 a t1%ROWTYPE;
2610 b t1%ROWTYPE;
2611 c t1%ROWTYPE:=ROW(1,'b');
2612 PROCEDURE p2(x OUT t1%ROWTYPE) AS
2613 BEGIN
2614 x:=c;
2615 x.a:=c.a+100;
2616 x.b:=c.b||'X';
2617 c.a:=c.a+1;
2618 c.b:=c.b||'B';
2619 END;
2620 PROCEDURE p1 AS
2621 BEGIN
2622 CALL p2(b);
2623 SELECT a.a,a.b,b.a,b.b;
2624 END;
2625 BEGIN
2626 CALL p2(a);
2627 END;
2628 $$
2629 CALL p1.p1;
2630 a.a a.b b.a b.b
2631 101 bX 102 bBX
2632 DROP PACKAGE p1;
2633 DROP TABLE t1;
2634 #
2635 # Package variable fields as OUT routine parameters
2636 #
2637 CREATE TABLE t1 (a INT,b TEXT);
2638 CREATE PACKAGE p1 AS
2639 PROCEDURE p1;
2640 END;
2641 $$
2642 CREATE PACKAGE BODY p1 AS
2643 a t1%ROWTYPE;
2644 x t1%ROWTYPE:=ROW(10,'b');
2645 PROCEDURE p2(a OUT INT,b OUT TEXT) AS
2646 BEGIN
2647 a:=x.a;
2648 b:=x.b;
2649 x.a:=x.a+1;
2650 x.b:=x.b||'B';
2651 END;
2652 PROCEDURE p1 AS
2653 BEGIN
2654 CALL p2(a.a, a.b);
2655 SELECT a.a,a.b;
2656 END;
2657 BEGIN
2658 CALL p2(a.a, a.b);
2659 SELECT a.a, a.b;
2660 END;
2661 $$
2662 CALL p1.p1;
2663 a.a a.b
2664 10 b
2665 a.a a.b
2666 11 bB
2667 DROP PACKAGE p1;
2668 DROP TABLE t1;
2669 #
2670 # Package variables as SELECT INTO targets
2671 #
2672 CREATE PACKAGE p1 AS
2673 PROCEDURE p1;
2674 END;
2675 $$
2676 CREATE PACKAGE BODY p1 AS
2677 a INT;
2678 b INT;
2679 PROCEDURE p1 AS
2680 BEGIN
2681 SELECT 2 INTO b;
2682 SELECT a,b;
2683 END;
2684 BEGIN
2685 SELECT 1 INTO a;
2686 END;
2687 $$
2688 CALL p1.p1;
2689 a b
2690 1 2
2691 DROP PACKAGE p1;
2692 CREATE TABLE t1 (a INT, b TEXT);
2693 INSERT INTO t1 VALUES (10,'b');
2694 CREATE PACKAGE p1 AS
2695 PROCEDURE p1;
2696 END;
2697 $$
2698 CREATE PACKAGE BODY p1 AS
2699 a t1%ROWTYPE;
2700 b t1%ROWTYPE;
2701 PROCEDURE p1 AS
2702 BEGIN
2703 SELECT * FROM t1 INTO a;
2704 SELECT a.a,a.b;
2705 END;
2706 BEGIN
2707 SELECT * FROM t1 INTO b;
2708 SELECT b.a, b.b;
2709 END;
2710 $$
2711 CALL p1.p1;
2712 b.a b.b
2713 10 b
2714 a.a a.b
2715 10 b
2716 DROP PACKAGE p1;
2717 DROP TABLE t1;
2718 #
2719 # Package variable fields as SELECT INTO targets
2720 #
2721 CREATE PACKAGE p1 AS
2722 PROCEDURE p1;
2723 END;
2724 $$
2725 CREATE PACKAGE BODY p1 AS
2726 a ROW(a INT, b TEXT);
2727 b ROW(a INT, b TEXT);
2728 PROCEDURE p1 AS
2729 BEGIN
2730 SELECT 20,'x2' INTO b.a,b.b;
2731 SELECT a.a,a.b,b.a,b.b;
2732 END;
2733 BEGIN
2734 SELECT 10,'x1' INTO a.a,a.b;
2735 END;
2736 $$
2737 CALL p1.p1;
2738 a.a a.b b.a b.b
2739 10 x1 20 x2
2740 DROP PACKAGE p1;
2741 #
2742 # Recursive package procedure calls
2743 # Makes sure that the non-top sp_head instances created by
2744 # sp_clone_and_link_routine() correctly reproduce the package context:
2745 # package variables, package routines.
2746 #
2747 CREATE PACKAGE p1 AS
2748 PROCEDURE p1(c INT);
2749 END p1;
2750 $$
2751 CREATE PACKAGE BODY p1 AS
2752 pv1 INT:=10;
2753 FUNCTION f1 RETURN INT AS BEGIN RETURN pv1+100; END;
2754 PROCEDURE p1(c INT) AS
2755 BEGIN
2756 SELECT c, pv1, f1();
2757 IF c>0 THEN
2758 pv1:=pv1+1;
2759 CALL p1(c-1);
2760 END IF;
2761 END;
2762 END;
2763 $$
2764 SET max_sp_recursion_depth=5;
2765 CALL p1.p1(5);
2766 c pv1 f1()
2767 5 10 110
2768 c pv1 f1()
2769 4 11 111
2770 c pv1 f1()
2771 3 12 112
2772 c pv1 f1()
2773 2 13 113
2774 c pv1 f1()
2775 1 14 114
2776 c pv1 f1()
2777 0 15 115
2778 SET max_sp_recursion_depth=0;
2779 CALL p1.p1(0);
2780 c pv1 f1()
2781 0 15 115
2782 CALL p1.p1(1);
2783 c pv1 f1()
2784 1 15 115
2785 ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine p1.p1
2786 DROP PACKAGE p1;
2787 #
2788 # Non-reserved keywords as package body variable names
2789 #
2790 CREATE PACKAGE p1 AS
2791 PROCEDURE p1;
2792 END p1;
2793 $$
2794 CREATE PACKAGE BODY p1 AS
2795 ascii INT:=10;
2796 action INT:=20;
2797 PROCEDURE p1 AS
2798 BEGIN
2799 SELECT ascii, action;
2800 END;
2801 BEGIN
2802 ascii := ascii + 1;
2803 action := action + 1;
2804 END;
2805 $$
2806 CALL p1.p1;
2807 ascii action
2808 11 21
2809 DROP PACKAGE p1;
2810 #
2811 # Package routines calling routines of another package
2812 #
2813 CREATE PACKAGE p1 AS
2814 PROCEDURE p1;
2815 FUNCTION f1 RETURN TEXT;
2816 END;
2817 $$
2818 CREATE PACKAGE p2 AS
2819 PROCEDURE p1;
2820 FUNCTION f1 RETURN TEXT;
2821 END;
2822 $$
2823 CREATE PACKAGE BODY p1 AS
2824 PROCEDURE p1 AS
2825 BEGIN
2826 SELECT 'This is p1.p1' AS msg;
2827 END;
2828 FUNCTION f1 RETURN TEXT AS
2829 BEGIN
2830 RETURN 'This is p1.f1';
2831 END;
2832 END;
2833 $$
2834 CREATE PACKAGE BODY p2 AS
2835 PROCEDURE p1 AS
2836 BEGIN
2837 CALL p1.p1;
2838 END;
2839 FUNCTION f1 RETURN TEXT AS
2840 BEGIN
2841 RETURN p1.f1();
2842 END;
2843 END;
2844 $$
2845 CALL p1.p1;
2846 msg
2847 This is p1.p1
2848 CALL p2.p1;
2849 msg
2850 This is p1.p1
2851 SELECT p1.f1(), p2.f1();
2852 p1.f1() p2.f1()
2853 This is p1.f1 This is p1.f1
2854 DROP PACKAGE p2;
2855 DROP PACKAGE p1;
2856 #
2857 # Package names with dot characters
2858 #
2859 CREATE PACKAGE "p1.p1" AS
2860 PROCEDURE p1;
2861 FUNCTION f1 RETURN TEXT;
2862 END;
2863 $$
2864 CREATE PACKAGE BODY "p1.p1" AS
2865 PROCEDURE p1 AS
2866 BEGIN
2867 SELECT 'This is p1' AS msg;
2868 END;
2869 FUNCTION f1 RETURN TEXT AS
2870 BEGIN
2871 RETURN 'This is f1';
2872 END;
2873 END;
2874 $$
2875 CALL "p1.p1"."p1";
2876 msg
2877 This is p1
2878 SELECT "p1.p1"."f1"();
2879 "p1.p1"."f1"()
2880 This is f1
2881 DROP PACKAGE "p1.p1";
2882 #
2883 # MDEV-15070 Crash when doing a CREATE VIEW inside a package routine
2884 #
2885 SET sql_mode=ORACLE;
2886 CREATE OR REPLACE PACKAGE pkg1 AS
2887 PROCEDURE p00();
2888 END;
2889 $$
2890 CREATE OR REPLACE PACKAGE BODY pkg1 AS
2891 PROCEDURE p01() AS
2892 BEGIN
2893 SELECT 'This is p01' AS msg;
2894 END;
2895 PROCEDURE p00() AS
2896 BEGIN
2897 CREATE OR REPLACE VIEW v1 AS SELECT 1;
2898 DROP VIEW v1;
2899 CALL p01();
2900 END;
2901 END;
2902 $$
2903 CALL pkg1.p00;
2904 msg
2905 This is p01
2906 DROP PACKAGE pkg1;
2907 CREATE OR REPLACE TABLE t1 (a INT);
2908 CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=1;
2909 CREATE OR REPLACE PACKAGE pkg1 AS
2910 PROCEDURE p00();
2911 END;
2912 $$
2913 CREATE OR REPLACE PACKAGE BODY pkg1 AS
2914 PROCEDURE p01() AS
2915 BEGIN
2916 SELECT 'This is p01' AS msg;
2917 END;
2918 PROCEDURE p00() AS
2919 BEGIN
2920 DROP TRIGGER tr1;
2921 CALL p01();
2922 END;
2923 END;
2924 $$
2925 CALL pkg1.p00;
2926 msg
2927 This is p01
2928 DROP PACKAGE pkg1;
2929 DROP TABLE t1;
2930 #
2931 # MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure
2932 #
2933 CREATE TABLE t1 (
2934 CTR varchar(2) NOT NULL,
2935 COR varchar(3) NOT NULL,
2936 DATE datetime NOT NULL,
2937 CHAN varchar(4) NOT NULL,
2938 CNO varchar(20) NOT NULL,
2939 JOBN varchar(18) NOT NULL,
2940 C1 varchar(30) DEFAULT NULL,
2941 C2 varchar(30) DEFAULT NULL,
2942 TIME datetime DEFAULT NULL,
2943 AMT decimal(12,2) DEFAULT NULL,
2944 DT datetime NOT NULL,
2945 pk int(11) NOT NULL,
2946 PRIMARY KEY (pk),
2947 KEY Indx1 (JOBN)
2948 );
2949 CREATE PACKAGE xyz IS
2950 PROCEDURE xyz123(ctr IN VARCHAR2,Jn IN VARCHAR2,R OUT VARCHAR2);
2951 END;
2952 $$
2953 CREATE OR REPLACE PACKAGE BODY xyz IS
2954 PROCEDURE xyz123(
2955 ctr IN VARCHAR2,
2956 Jn IN VARCHAR2,
2957 R OUT VARCHAR2)
2958 AS
2959 lS NUMBER(10) :=0;
2960 CURSOR cBPD IS
2961 SELECT CTR, COR, DATE, CHAN, CNO, C1, C2, TIME, AMT
2962 FROM t1 WHERE JOBN=Jn;
2963 BEGIN
2964 FOR lbpd IN cBPD
2965 LOOP
2966 lS:=lS+1;
2967 END LOOP;
2968 EXCEPTION
2969 WHEN OTHERS THEN
2970 BEGIN
2971 SELECT SQLERRM;
2972 END;
2973 END;
2974 END $$
2975 CALL xyz.xyz123(17,18,@R);
2976 DROP PACKAGE xyz;
2977 DROP TABLE t1;
2978 #
2979 # MDEV-28166 sql_mode=ORACLE: fully qualified package function calls do not work: db.pkg.func()
2980 #
2981 SELECT `db `.pkg.func();
2982 ERROR 42000: Incorrect database name 'db '
2983 SELECT db.`pkg `.func();
2984 ERROR 42000: Incorrect routine name 'pkg '
2985 SELECT db.pkg.`func `();
2986 ERROR 42000: Incorrect routine name 'func '
2987 CREATE DATABASE db1;
2988 USE db1;
2989 CREATE PACKAGE pkg1 AS
2990 FUNCTION f1 RETURN TEXT;
2991 FUNCTION f2_db1_pkg1_f1 RETURN TEXT;
2992 FUNCTION f2_pkg1_f1 RETURN TEXT;
2993 FUNCTION f2_f1 RETURN TEXT;
2994 END;
2995 $$
2996 CREATE PACKAGE BODY pkg1
2997 AS
2998 FUNCTION f1 RETURN TEXT IS
2999 BEGIN
3000 RETURN 'This is db1.pkg1.f1';
3001 END;
3002 FUNCTION f2_db1_pkg1_f1 RETURN TEXT IS
3003 BEGIN
3004 RETURN db1.pkg1.f1();
3005 END;
3006 FUNCTION f2_pkg1_f1 RETURN TEXT IS
3007 BEGIN
3008 RETURN pkg1.f1();
3009 END;
3010 FUNCTION f2_f1 RETURN TEXT IS
3011 BEGIN
3012 RETURN f1();
3013 END;
3014 END;
3015 $$
3016 USE db1;
3017 SELECT pkg1.f2_db1_pkg1_f1();
3018 pkg1.f2_db1_pkg1_f1()
3019 This is db1.pkg1.f1
3020 SELECT pkg1.f2_pkg1_f1();
3021 pkg1.f2_pkg1_f1()
3022 This is db1.pkg1.f1
3023 SELECT pkg1.f2_f1();
3024 pkg1.f2_f1()
3025 This is db1.pkg1.f1
3026 SELECT db1.pkg1.f2_db1_pkg1_f1();
3027 db1.pkg1.f2_db1_pkg1_f1()
3028 This is db1.pkg1.f1
3029 SELECT db1.pkg1.f2_pkg1_f1();
3030 db1.pkg1.f2_pkg1_f1()
3031 This is db1.pkg1.f1
3032 SELECT db1.pkg1.f2_f1();
3033 db1.pkg1.f2_f1()
3034 This is db1.pkg1.f1
3035 USE test;
3036 SELECT db1.pkg1.f2_db1_pkg1_f1();
3037 db1.pkg1.f2_db1_pkg1_f1()
3038 This is db1.pkg1.f1
3039 SELECT db1.pkg1.f2_pkg1_f1();
3040 db1.pkg1.f2_pkg1_f1()
3041 This is db1.pkg1.f1
3042 SELECT db1.pkg1.f2_f1();
3043 db1.pkg1.f2_f1()
3044 This is db1.pkg1.f1
3045 DROP DATABASE db1;
3046 CREATE DATABASE db1;
3047 CREATE DATABASE db2;
3048 CREATE PACKAGE db1.pkg1 AS
3049 FUNCTION f1 RETURN TEXT;
3050 END;
3051 $$
3052 CREATE PACKAGE BODY db1.pkg1 AS
3053 FUNCTION f1 RETURN TEXT AS
3054 BEGIN
3055 RETURN 'This is db1.pkg1.f1';
3056 END;
3057 END;
3058 $$
3059 CREATE PACKAGE db2.pkg1 AS
3060 FUNCTION f1 RETURN TEXT;
3061 FUNCTION var1 RETURN TEXT;
3062 FUNCTION var2 RETURN TEXT;
3063 END;
3064 $$
3065 CREATE PACKAGE BODY db2.pkg1 AS
3066 m_var1 TEXT;
3067 m_var2 TEXT;
3068 FUNCTION f1 RETURN TEXT AS
3069 BEGIN
3070 RETURN 'This is db2.pkg1.f1';
3071 END;
3072 FUNCTION var1 RETURN TEXT AS
3073 BEGIN
3074 RETURN m_var1;
3075 END;
3076 FUNCTION var2 RETURN TEXT AS
3077 BEGIN
3078 RETURN m_var2;
3079 END;
3080 BEGIN
3081 m_var1:= db1.pkg1.f1();
3082 m_var2:= db2.pkg1.f1();
3083 END;
3084 $$
3085 SELECT db2.pkg1.var1(), db2.pkg1.var2();
3086 db2.pkg1.var1() db2.pkg1.var2()
3087 This is db1.pkg1.f1 This is db2.pkg1.f1
3088 DROP DATABASE db1;
3089 DROP DATABASE db2;
3090 CREATE PACKAGE pkg1 AS
3091 FUNCTION f1(a TEXT) RETURN TEXT;
3092 END;
3093 $$
3094 CREATE PACKAGE BODY pkg1 AS
3095 FUNCTION f1(a TEXT) RETURN TEXT AS
3096 BEGIN
3097 RETURN a;
3098 END;
3099 END;
3100 $$
3101 SELECT test.pkg1.f1('xxx');
3102 test.pkg1.f1('xxx')
3103 xxx
3104 SELECT test.pkg1.f1('xxx' AS a);
3105 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 'AS a)' at line 1
3106 DROP PACKAGE pkg1;
3107 #
3108 # MDEV-19328 sql_mode=ORACLE: Package function in VIEW
3109 #
3110 SET sql_mode=ORACLE;
3111 CREATE PACKAGE test1 AS
3112 FUNCTION f_test RETURN number;
3113 END test1;
3114 $$
3115 CREATE PACKAGE BODY test1
3116 AS
3117 FUNCTION f_test RETURN NUMBER IS
3118 BEGIN
3119 RETURN 1;
3120 END;
3121 END test1;
3122 $$
3123 SET sql_mode=ORACLE;
3124 CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
3125 SELECT * FROM v_test;
3126 c1
3127 1
3128 SHOW CREATE VIEW v_test;
3129 View v_test
3130 Create View CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL where 1 = "test"."test1"."f_test"()
3131 character_set_client latin1
3132 collation_connection latin1_swedish_ci
3133 SET sql_mode=DEFAULT;
3134 SELECT * FROM v_test;
3135 c1
3136 1
3137 SHOW CREATE VIEW v_test;
3138 View v_test
3139 Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL where 1 = `test`.`test1`.`f_test`()
3140 character_set_client latin1
3141 collation_connection latin1_swedish_ci
3142 DROP VIEW v_test;
3143 SET sql_mode=DEFAULT;
3144 CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
3145 SELECT * FROM v_test;
3146 c1
3147 1
3148 SHOW CREATE VIEW v_test;
3149 View v_test
3150 Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL where 1 = `test`.`test1`.`f_test`()
3151 character_set_client latin1
3152 collation_connection latin1_swedish_ci
3153 SET sql_mode=ORACLE;
3154 SELECT * FROM v_test;
3155 c1
3156 1
3157 SHOW CREATE VIEW v_test;
3158 View v_test
3159 Create View CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL where 1 = "test"."test1"."f_test"()
3160 character_set_client latin1
3161 collation_connection latin1_swedish_ci
3162 DROP VIEW v_test;
3163 SET sql_mode=ORACLE;
3164 CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test();
3165 SELECT * FROM v_test;
3166 c1
3167 1
3168 SHOW CREATE VIEW v_test;
3169 View v_test
3170 Create View CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL where 1 = "test"."test1"."f_test"()
3171 character_set_client latin1
3172 collation_connection latin1_swedish_ci
3173 SET sql_mode=DEFAULT;
3174 SELECT * FROM v_test;
3175 c1
3176 1
3177 SHOW CREATE VIEW v_test;
3178 View v_test
3179 Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL where 1 = `test`.`test1`.`f_test`()
3180 character_set_client latin1
3181 collation_connection latin1_swedish_ci
3182 DROP VIEW v_test;
3183 SET sql_mode=DEFAULT;
3184 CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test();
3185 SELECT * FROM v_test;
3186 c1
3187 1
3188 SHOW CREATE VIEW v_test;
3189 View v_test
3190 Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL where 1 = `test`.`test1`.`f_test`()
3191 character_set_client latin1
3192 collation_connection latin1_swedish_ci
3193 SET sql_mode=ORACLE;
3194 SELECT * FROM v_test;
3195 c1
3196 1
3197 SHOW CREATE VIEW v_test;
3198 View v_test
3199 Create View CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL where 1 = "test"."test1"."f_test"()
3200 character_set_client latin1
3201 collation_connection latin1_swedish_ci
3202 DROP VIEW v_test;
3203 SET sql_mode=ORACLE;
3204 DROP PACKAGE test1;
3205 #
3206 # MDEV-19804 sql_mode=ORACLE: call procedure in packages
3207 #
3208 CALL `db1 `.pkg.p;
3209 ERROR 42000: Incorrect database name 'db1 '
3210 CALL db1.`pkg `.p;
3211 ERROR 42000: Incorrect routine name 'pkg '
3212 CALL db1.pkg.`p `;
3213 ERROR 42000: Incorrect routine name 'p '
3214 SET sql_mode=ORACLE;
3215 CREATE PACKAGE pkg1 as
3216 PROCEDURE p1();
3217 END;
3218 $$
3219 CREATE PACKAGE BODY pkg1 as
3220 PROCEDURE p1() as
3221 BEGIN
3222 SELECT 'test-function' AS c1;
3223 END;
3224 END;
3225 $$
3226 CALL pkg1.p1;
3227 c1
3228 test-function
3229 CALL test.pkg1.p1;
3230 c1
3231 test-function
3232 SET sql_mode=DEFAULT;
3233 CALL test.pkg1.p1;
3234 c1
3235 test-function
3236 SET sql_mode=ORACLE;
3237 BEGIN
3238 CALL pkg1.p1;
3239 CALL test.pkg1.p1;
3240 END
3241 $$
3242 c1
3243 test-function
3244 c1
3245 test-function
3246 BEGIN
3247 pkg1.p1;
3248 test.pkg1.p1;
3249 END
3250 $$
3251 c1
3252 test-function
3253 c1
3254 test-function
3255 DROP PACKAGE pkg1;
3256 CREATE DATABASE db1;
3257 CREATE PACKAGE db1.pkg1 AS
3258 PROCEDURE p1(a OUT TEXT);
3259 END;
3260 $$
3261 CREATE PACKAGE BODY db1.pkg1 AS
3262 PROCEDURE p1(a OUT TEXT) AS
3263 BEGIN
3264 a:= 'This is db1.pkg1.p1';
3265 END;
3266 END;
3267 $$
3268 CREATE DATABASE db2;
3269 CREATE PACKAGE db2.pkg1 AS
3270 FUNCTION var1 RETURN TEXT;
3271 PROCEDURE p1(a OUT TEXT);
3272 PROCEDURE p2_db1_pkg1_p1;
3273 END;
3274 $$
3275 CREATE PACKAGE BODY db2.pkg1 AS
3276 m_var1 TEXT;
3277 FUNCTION var1 RETURN TEXT AS
3278 BEGIN
3279 RETURN m_var1;
3280 END;
3281 PROCEDURE p1(a OUT TEXT) AS
3282 BEGIN
3283 a:= 'This is db2.pkg1.p1';
3284 END;
3285 PROCEDURE p2_db1_pkg1_p1 AS
3286 a TEXT;
3287 BEGIN
3288 db1.pkg1.p1(a);
3289 SELECT a;
3290 END;
3291 BEGIN
3292 db1.pkg1.p1(m_var1);
3293 END;
3294 $$
3295 SELECT db2.pkg1.var1();
3296 db2.pkg1.var1()
3297 This is db1.pkg1.p1
3298 CALL db2.pkg1.p2_db1_pkg1_p1;
3299 a
3300 This is db1.pkg1.p1
3301 DROP DATABASE db1;
3302 DROP DATABASE db2;
3303 #
3304 # MDEV-29370 Functions in packages are slow and seems to ignore deterministic
3305 #
3306 SET SQL_MODE=ORACLE;
3307 CREATE TABLE t1 (c1 CHAR(1));
3308 CREATE FUNCTION f1_deterministic()
3309 RETURN CHAR(1)
3310 DETERMINISTIC
3311 IS
3312 BEGIN
3313 RETURN 'X';
3314 END;
3315 //
3316 CREATE FUNCTION f2_not_deterministic()
3317 RETURN CHAR(1)
3318 IS
3319 BEGIN
3320 RETURN 'X';
3321 END;
3322 //
3323 CREATE PACKAGE pkg1
3324 IS
3325 PROCEDURE t1_populate(numrows INTEGER);
3326 FUNCTION f3_deterministic() RETURN CHAR(1) DETERMINISTIC;
3327 FUNCTION f4_not_deterministic() RETURN CHAR(1);
3328 END;
3329 //
3330 CREATE PACKAGE BODY pkg1
3331 IS
3332 PROCEDURE t1_populate(numrounds INTEGER)
3333 IS
3334 i INTEGER;
3335 BEGIN
3336 INSERT INTO t1 VALUES('Y');
3337 FOR i IN 1..numrounds LOOP
3338 INSERT INTO t1 SELECT * FROM t1;
3339 END LOOP;
3340 END;
3341 FUNCTION f3_deterministic() RETURN CHAR(1) DETERMINISTIC COMMENT 'xxx'
3342 IS
3343 BEGIN
3344 RETURN 'X';
3345 END;
3346 FUNCTION f4_not_deterministic() RETURN CHAR(1)
3347 IS
3348 BEGIN
3349 RETURN 'X';
3350 END;
3351 END;
3352 //
3353 CALL pkg1.t1_populate(3);
3354 EXPLAIN EXTENDED SELECT 'Deterministic function', COUNT(*) FROM t1 WHERE c1 = f1_deterministic();
3355 id select_type table type possible_keys key key_len ref rows filtered Extra
3356 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where
3357 Warnings:
3358 Note 1003 select 'Deterministic function' AS "Deterministic function",count(0) AS "COUNT(*)" from "test"."t1" where "test"."t1"."c1" = <cache>("f1_deterministic"())
3359 EXPLAIN EXTENDED SELECT 'Non-deterministic function', COUNT(*) FROM t1 WHERE c1 = f2_not_deterministic();
3360 id select_type table type possible_keys key key_len ref rows filtered Extra
3361 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where
3362 Warnings:
3363 Note 1003 select 'Non-deterministic function' AS "Non-deterministic function",count(0) AS "COUNT(*)" from "test"."t1" where "test"."t1"."c1" = "f2_not_deterministic"()
3364 EXPLAIN EXTENDED SELECT 'Deterministic package function', COUNT(*) FROM t1 WHERE c1 = pkg1.f3_deterministic();
3365 id select_type table type possible_keys key key_len ref rows filtered Extra
3366 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where
3367 Warnings:
3368 Note 1003 select 'Deterministic package function' AS "Deterministic package function",count(0) AS "COUNT(*)" from "test"."t1" where "test"."t1"."c1" = <cache>("test"."pkg1"."f3_deterministic"())
3369 EXPLAIN EXTENDED SELECT 'Non-deterministic package function', COUNT(*) FROM t1 WHERE c1 = pkg1.f4_not_deterministic();
3370 id select_type table type possible_keys key key_len ref rows filtered Extra
3371 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where
3372 Warnings:
3373 Note 1003 select 'Non-deterministic package function' AS "Non-deterministic package function",count(0) AS "COUNT(*)" from "test"."t1" where "test"."t1"."c1" = "test"."pkg1"."f4_not_deterministic"()
3374 DROP TABLE t1;
3375 DROP FUNCTION f1_deterministic;
3376 DROP FUNCTION f2_not_deterministic;
3377 DROP PACKAGE pkg1;
3378 #
3379 # Start of 11.7 tests
3380 #
3381 #
3382 # MDEV-12252 ROW data type for stored function return values
3383 #
3384 #
3385 # Testing fixed ROW type with package routines
3386 #
3387 CREATE PACKAGE pkg
3388 AS
3389 FUNCTION f1() RETURN ROW(a INT, b VARCHAR(32));
3390 PROCEDURE p1(r ROW(a INT, b VARCHAR(32)));
3391 PROCEDURE p2();
3392 END;
3393 $$
3394 CREATE PACKAGE BODY pkg
3395 AS
3396 FUNCTION f1() RETURN ROW(a INT, b VARCHAR(32)) AS
3397 BEGIN
3398 RETURN ROW(1,'b1');
3399 END;
3400 PROCEDURE p1(r ROW(a INT, b VARCHAR(32))) AS
3401 BEGIN
3402 SELECT r.a, r.b;
3403 END;
3404 PROCEDURE p2() AS
3405 BEGIN
3406 CALL p1(f1());
3407 END;
3408 END;
3409 $$
3410 CALL pkg.p1(pkg.f1());
3411 r.a r.b
3412 1 b1
3413 CALL pkg.p2;
3414 r.a r.b
3415 1 b1
3416 DROP PACKAGE pkg;
3417 #
3418 # Testing table%ROWTYPE with package routines
3419 #
3420 CREATE TABLE t1 (a INT, b VARCHAR(32));
3421 INSERT INTO t1 VALUES (1,'b1');
3422 CREATE PACKAGE pkg
3423 AS
3424 FUNCTION f1 RETURN t1%ROWTYPE;
3425 PROCEDURE p1(r t1%ROWTYPE);
3426 PROCEDURE p2;
3427 END;
3428 /
3429 CREATE PACKAGE BODY pkg
3430 AS
3431 FUNCTION f1 RETURN t1%ROWTYPE AS
3432 r t1%ROWTYPE;
3433 BEGIN
3434 SELECT * INTO r FROM t1;
3435 RETURN r;
3436 END;
3437 PROCEDURE p1(r t1%ROWTYPE) AS
3438 BEGIN
3439 SELECT r.a || ' ' || r.b;
3440 END;
3441 PROCEDURE p2 AS
3442 BEGIN
3443 p1(f1());
3444 END;
3445 END;
3446 /
3447 CALL pkg.p1(pkg.f1());
3448 r.a || ' ' || r.b
3449 1 b1
3450 CALL pkg.p2;
3451 r.a || ' ' || r.b
3452 1 b1
3453 DROP PACKAGE pkg;
3454 DROP TABLE t1;
3455 #
3456 # End of 11.7 tests
3457 #