Debian Codegraph @

mysql-test/suite/engines/funcs/r/sf_alter.result

raw
1 DROP TABLE IF EXISTS t1,t2,t3,t4;
2 DROP FUNCTION IF EXISTS sf1;
3 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
4 CREATE TABLE t2 (i INTEGER NULL);
5 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
6 CREATE TABLE t4 (i INTEGER NULL);
7 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
8 INSERT INTO t2 VALUES(1),(2),(3);
9 CREATE FUNCTION sf1() RETURNS INTEGER
10 BEGIN
11 DECLARE done INT DEFAULT 0;
12 DECLARE a CHAR(16);
13 DECLARE b,c INT;
14 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
15 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
16 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
17 OPEN cur1;
18 OPEN cur2;
19 REPEAT
20 FETCH cur1 INTO a, b;
21 FETCH cur2 INTO c;
22 IF NOT done THEN
23 IF b < c THEN
24 INSERT INTO t3 VALUES (a,b);
25 ELSE
26 INSERT INTO t3 VALUES (a,c);
27 END IF;
28 END IF;
29 UNTIL done END REPEAT;
30 CLOSE cur1;
31 CLOSE cur2;
32 RETURN 0;
33 END//
34 ALTER FUNCTION sf1 #DET# ;
35 SELECT sf1();
36 sf1()
37 0
38 SELECT * FROM t1 ORDER BY id;
39 id data
40 1 1
41 2 1
42 3 4
43 4 5
44 SELECT * FROM t2 ORDER BY i;
45 i
46 1
47 2
48 3
49 SELECT * FROM t3 ORDER BY id;
50 id data
51 1 1
52 2 1
53 3 3
54 INSERT INTO t4 VALUES(sf1());
55 SELECT * FROM t1 ORDER BY id;
56 id data
57 1 1
58 2 1
59 3 4
60 4 5
61 SELECT * FROM t2 ORDER BY i;
62 i
63 1
64 2
65 3
66 SELECT * FROM t3 ORDER BY id;
67 id data
68 1 1
69 1 1
70 2 1
71 2 1
72 3 3
73 3 3
74 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
75 SELECT * FROM t1 ORDER BY id;
76 id data
77 1 1
78 2 1
79 3 4
80 4 5
81 SELECT * FROM t2 ORDER BY i;
82 i
83 1
84 2
85 3
86 SELECT * FROM t3 ORDER BY id;
87 id data
88 1 1
89 1 1
90 1 1
91 1 1
92 2 1
93 2 1
94 2 1
95 2 1
96 3 3
97 3 3
98 3 3
99 3 3
100 DELETE FROM t4 WHERE i = sf1() + 1;
101 SELECT * FROM t1 ORDER BY id;
102 id data
103 1 1
104 2 1
105 3 4
106 4 5
107 SELECT * FROM t2 ORDER BY i;
108 i
109 1
110 2
111 3
112 SELECT * FROM t3 ORDER BY id;
113 id data
114 1 1
115 1 1
116 1 1
117 1 1
118 1 1
119 2 1
120 2 1
121 2 1
122 2 1
123 2 1
124 3 3
125 3 3
126 3 3
127 3 3
128 3 3
129 DROP FUNCTION sf1;
130 DROP TABLE t1,t2,t3,t4;
131 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
132 CREATE TABLE t2 (i INTEGER NULL);
133 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
134 CREATE TABLE t4 (i INTEGER NULL);
135 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
136 INSERT INTO t2 VALUES(1),(2),(3);
137 CREATE FUNCTION sf1() RETURNS INTEGER
138 BEGIN
139 DECLARE done INT DEFAULT 0;
140 DECLARE a CHAR(16);
141 DECLARE b,c INT;
142 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
143 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
144 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
145 OPEN cur1;
146 OPEN cur2;
147 REPEAT
148 FETCH cur1 INTO a, b;
149 FETCH cur2 INTO c;
150 IF NOT done THEN
151 IF b < c THEN
152 INSERT INTO t3 VALUES (a,b);
153 ELSE
154 INSERT INTO t3 VALUES (a,c);
155 END IF;
156 END IF;
157 UNTIL done END REPEAT;
158 CLOSE cur1;
159 CLOSE cur2;
160 RETURN 0;
161 END//
162 ALTER FUNCTION sf1 #DET# CONTAINS SQL ;
163 SELECT sf1();
164 sf1()
165 0
166 SELECT * FROM t1 ORDER BY id;
167 id data
168 1 1
169 2 1
170 3 4
171 4 5
172 SELECT * FROM t2 ORDER BY i;
173 i
174 1
175 2
176 3
177 SELECT * FROM t3 ORDER BY id;
178 id data
179 1 1
180 2 1
181 3 3
182 INSERT INTO t4 VALUES(sf1());
183 SELECT * FROM t1 ORDER BY id;
184 id data
185 1 1
186 2 1
187 3 4
188 4 5
189 SELECT * FROM t2 ORDER BY i;
190 i
191 1
192 2
193 3
194 SELECT * FROM t3 ORDER BY id;
195 id data
196 1 1
197 1 1
198 2 1
199 2 1
200 3 3
201 3 3
202 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
203 SELECT * FROM t1 ORDER BY id;
204 id data
205 1 1
206 2 1
207 3 4
208 4 5
209 SELECT * FROM t2 ORDER BY i;
210 i
211 1
212 2
213 3
214 SELECT * FROM t3 ORDER BY id;
215 id data
216 1 1
217 1 1
218 1 1
219 1 1
220 2 1
221 2 1
222 2 1
223 2 1
224 3 3
225 3 3
226 3 3
227 3 3
228 DELETE FROM t4 WHERE i = sf1() + 1;
229 SELECT * FROM t1 ORDER BY id;
230 id data
231 1 1
232 2 1
233 3 4
234 4 5
235 SELECT * FROM t2 ORDER BY i;
236 i
237 1
238 2
239 3
240 SELECT * FROM t3 ORDER BY id;
241 id data
242 1 1
243 1 1
244 1 1
245 1 1
246 1 1
247 2 1
248 2 1
249 2 1
250 2 1
251 2 1
252 3 3
253 3 3
254 3 3
255 3 3
256 3 3
257 DROP FUNCTION sf1;
258 DROP TABLE t1,t2,t3,t4;
259 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
260 CREATE TABLE t2 (i INTEGER NULL);
261 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
262 CREATE TABLE t4 (i INTEGER NULL);
263 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
264 INSERT INTO t2 VALUES(1),(2),(3);
265 CREATE FUNCTION sf1() RETURNS INTEGER
266 BEGIN
267 DECLARE done INT DEFAULT 0;
268 DECLARE a CHAR(16);
269 DECLARE b,c INT;
270 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
271 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
272 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
273 OPEN cur1;
274 OPEN cur2;
275 REPEAT
276 FETCH cur1 INTO a, b;
277 FETCH cur2 INTO c;
278 IF NOT done THEN
279 IF b < c THEN
280 INSERT INTO t3 VALUES (a,b);
281 ELSE
282 INSERT INTO t3 VALUES (a,c);
283 END IF;
284 END IF;
285 UNTIL done END REPEAT;
286 CLOSE cur1;
287 CLOSE cur2;
288 RETURN 0;
289 END//
290 ALTER FUNCTION sf1 #DET# NO SQL ;
291 SELECT sf1();
292 sf1()
293 0
294 SELECT * FROM t1 ORDER BY id;
295 id data
296 1 1
297 2 1
298 3 4
299 4 5
300 SELECT * FROM t2 ORDER BY i;
301 i
302 1
303 2
304 3
305 SELECT * FROM t3 ORDER BY id;
306 id data
307 1 1
308 2 1
309 3 3
310 INSERT INTO t4 VALUES(sf1());
311 SELECT * FROM t1 ORDER BY id;
312 id data
313 1 1
314 2 1
315 3 4
316 4 5
317 SELECT * FROM t2 ORDER BY i;
318 i
319 1
320 2
321 3
322 SELECT * FROM t3 ORDER BY id;
323 id data
324 1 1
325 1 1
326 2 1
327 2 1
328 3 3
329 3 3
330 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
331 SELECT * FROM t1 ORDER BY id;
332 id data
333 1 1
334 2 1
335 3 4
336 4 5
337 SELECT * FROM t2 ORDER BY i;
338 i
339 1
340 2
341 3
342 SELECT * FROM t3 ORDER BY id;
343 id data
344 1 1
345 1 1
346 1 1
347 1 1
348 2 1
349 2 1
350 2 1
351 2 1
352 3 3
353 3 3
354 3 3
355 3 3
356 DELETE FROM t4 WHERE i = sf1() + 1;
357 SELECT * FROM t1 ORDER BY id;
358 id data
359 1 1
360 2 1
361 3 4
362 4 5
363 SELECT * FROM t2 ORDER BY i;
364 i
365 1
366 2
367 3
368 SELECT * FROM t3 ORDER BY id;
369 id data
370 1 1
371 1 1
372 1 1
373 1 1
374 1 1
375 2 1
376 2 1
377 2 1
378 2 1
379 2 1
380 3 3
381 3 3
382 3 3
383 3 3
384 3 3
385 DROP FUNCTION sf1;
386 DROP TABLE t1,t2,t3,t4;
387 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
388 CREATE TABLE t2 (i INTEGER NULL);
389 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
390 CREATE TABLE t4 (i INTEGER NULL);
391 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
392 INSERT INTO t2 VALUES(1),(2),(3);
393 CREATE FUNCTION sf1() RETURNS INTEGER
394 BEGIN
395 DECLARE done INT DEFAULT 0;
396 DECLARE a CHAR(16);
397 DECLARE b,c INT;
398 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
399 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
400 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
401 OPEN cur1;
402 OPEN cur2;
403 REPEAT
404 FETCH cur1 INTO a, b;
405 FETCH cur2 INTO c;
406 IF NOT done THEN
407 IF b < c THEN
408 INSERT INTO t3 VALUES (a,b);
409 ELSE
410 INSERT INTO t3 VALUES (a,c);
411 END IF;
412 END IF;
413 UNTIL done END REPEAT;
414 CLOSE cur1;
415 CLOSE cur2;
416 RETURN 0;
417 END//
418 ALTER FUNCTION sf1 #DET# READS SQL DATA ;
419 SELECT sf1();
420 sf1()
421 0
422 SELECT * FROM t1 ORDER BY id;
423 id data
424 1 1
425 2 1
426 3 4
427 4 5
428 SELECT * FROM t2 ORDER BY i;
429 i
430 1
431 2
432 3
433 SELECT * FROM t3 ORDER BY id;
434 id data
435 1 1
436 2 1
437 3 3
438 INSERT INTO t4 VALUES(sf1());
439 SELECT * FROM t1 ORDER BY id;
440 id data
441 1 1
442 2 1
443 3 4
444 4 5
445 SELECT * FROM t2 ORDER BY i;
446 i
447 1
448 2
449 3
450 SELECT * FROM t3 ORDER BY id;
451 id data
452 1 1
453 1 1
454 2 1
455 2 1
456 3 3
457 3 3
458 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
459 SELECT * FROM t1 ORDER BY id;
460 id data
461 1 1
462 2 1
463 3 4
464 4 5
465 SELECT * FROM t2 ORDER BY i;
466 i
467 1
468 2
469 3
470 SELECT * FROM t3 ORDER BY id;
471 id data
472 1 1
473 1 1
474 1 1
475 1 1
476 2 1
477 2 1
478 2 1
479 2 1
480 3 3
481 3 3
482 3 3
483 3 3
484 DELETE FROM t4 WHERE i = sf1() + 1;
485 SELECT * FROM t1 ORDER BY id;
486 id data
487 1 1
488 2 1
489 3 4
490 4 5
491 SELECT * FROM t2 ORDER BY i;
492 i
493 1
494 2
495 3
496 SELECT * FROM t3 ORDER BY id;
497 id data
498 1 1
499 1 1
500 1 1
501 1 1
502 1 1
503 2 1
504 2 1
505 2 1
506 2 1
507 2 1
508 3 3
509 3 3
510 3 3
511 3 3
512 3 3
513 DROP FUNCTION sf1;
514 DROP TABLE t1,t2,t3,t4;
515 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
516 CREATE TABLE t2 (i INTEGER NULL);
517 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
518 CREATE TABLE t4 (i INTEGER NULL);
519 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
520 INSERT INTO t2 VALUES(1),(2),(3);
521 CREATE FUNCTION sf1() RETURNS INTEGER
522 BEGIN
523 DECLARE done INT DEFAULT 0;
524 DECLARE a CHAR(16);
525 DECLARE b,c INT;
526 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
527 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
528 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
529 OPEN cur1;
530 OPEN cur2;
531 REPEAT
532 FETCH cur1 INTO a, b;
533 FETCH cur2 INTO c;
534 IF NOT done THEN
535 IF b < c THEN
536 INSERT INTO t3 VALUES (a,b);
537 ELSE
538 INSERT INTO t3 VALUES (a,c);
539 END IF;
540 END IF;
541 UNTIL done END REPEAT;
542 CLOSE cur1;
543 CLOSE cur2;
544 RETURN 0;
545 END//
546 ALTER FUNCTION sf1 #DET# MODIFIES SQL DATA ;
547 SELECT sf1();
548 sf1()
549 0
550 SELECT * FROM t1 ORDER BY id;
551 id data
552 1 1
553 2 1
554 3 4
555 4 5
556 SELECT * FROM t2 ORDER BY i;
557 i
558 1
559 2
560 3
561 SELECT * FROM t3 ORDER BY id;
562 id data
563 1 1
564 2 1
565 3 3
566 INSERT INTO t4 VALUES(sf1());
567 SELECT * FROM t1 ORDER BY id;
568 id data
569 1 1
570 2 1
571 3 4
572 4 5
573 SELECT * FROM t2 ORDER BY i;
574 i
575 1
576 2
577 3
578 SELECT * FROM t3 ORDER BY id;
579 id data
580 1 1
581 1 1
582 2 1
583 2 1
584 3 3
585 3 3
586 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
587 SELECT * FROM t1 ORDER BY id;
588 id data
589 1 1
590 2 1
591 3 4
592 4 5
593 SELECT * FROM t2 ORDER BY i;
594 i
595 1
596 2
597 3
598 SELECT * FROM t3 ORDER BY id;
599 id data
600 1 1
601 1 1
602 1 1
603 1 1
604 2 1
605 2 1
606 2 1
607 2 1
608 3 3
609 3 3
610 3 3
611 3 3
612 DELETE FROM t4 WHERE i = sf1() + 1;
613 SELECT * FROM t1 ORDER BY id;
614 id data
615 1 1
616 2 1
617 3 4
618 4 5
619 SELECT * FROM t2 ORDER BY i;
620 i
621 1
622 2
623 3
624 SELECT * FROM t3 ORDER BY id;
625 id data
626 1 1
627 1 1
628 1 1
629 1 1
630 1 1
631 2 1
632 2 1
633 2 1
634 2 1
635 2 1
636 3 3
637 3 3
638 3 3
639 3 3
640 3 3
641 DROP FUNCTION sf1;
642 DROP TABLE t1,t2,t3,t4;
643 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
644 CREATE TABLE t2 (i INTEGER NULL);
645 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
646 CREATE TABLE t4 (i INTEGER NULL);
647 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
648 INSERT INTO t2 VALUES(1),(2),(3);
649 CREATE FUNCTION sf1() RETURNS INTEGER
650 BEGIN
651 DECLARE done INT DEFAULT 0;
652 DECLARE a CHAR(16);
653 DECLARE b,c INT;
654 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
655 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
656 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
657 OPEN cur1;
658 OPEN cur2;
659 REPEAT
660 FETCH cur1 INTO a, b;
661 FETCH cur2 INTO c;
662 IF NOT done THEN
663 IF b < c THEN
664 INSERT INTO t3 VALUES (a,b);
665 ELSE
666 INSERT INTO t3 VALUES (a,c);
667 END IF;
668 END IF;
669 UNTIL done END REPEAT;
670 CLOSE cur1;
671 CLOSE cur2;
672 RETURN 0;
673 END//
674 ALTER FUNCTION sf1 #DET# COMMENT 'comment' ;
675 SELECT sf1();
676 sf1()
677 0
678 SELECT * FROM t1 ORDER BY id;
679 id data
680 1 1
681 2 1
682 3 4
683 4 5
684 SELECT * FROM t2 ORDER BY i;
685 i
686 1
687 2
688 3
689 SELECT * FROM t3 ORDER BY id;
690 id data
691 1 1
692 2 1
693 3 3
694 INSERT INTO t4 VALUES(sf1());
695 SELECT * FROM t1 ORDER BY id;
696 id data
697 1 1
698 2 1
699 3 4
700 4 5
701 SELECT * FROM t2 ORDER BY i;
702 i
703 1
704 2
705 3
706 SELECT * FROM t3 ORDER BY id;
707 id data
708 1 1
709 1 1
710 2 1
711 2 1
712 3 3
713 3 3
714 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
715 SELECT * FROM t1 ORDER BY id;
716 id data
717 1 1
718 2 1
719 3 4
720 4 5
721 SELECT * FROM t2 ORDER BY i;
722 i
723 1
724 2
725 3
726 SELECT * FROM t3 ORDER BY id;
727 id data
728 1 1
729 1 1
730 1 1
731 1 1
732 2 1
733 2 1
734 2 1
735 2 1
736 3 3
737 3 3
738 3 3
739 3 3
740 DELETE FROM t4 WHERE i = sf1() + 1;
741 SELECT * FROM t1 ORDER BY id;
742 id data
743 1 1
744 2 1
745 3 4
746 4 5
747 SELECT * FROM t2 ORDER BY i;
748 i
749 1
750 2
751 3
752 SELECT * FROM t3 ORDER BY id;
753 id data
754 1 1
755 1 1
756 1 1
757 1 1
758 1 1
759 2 1
760 2 1
761 2 1
762 2 1
763 2 1
764 3 3
765 3 3
766 3 3
767 3 3
768 3 3
769 DROP FUNCTION sf1;
770 DROP TABLE t1,t2,t3,t4;
771 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
772 CREATE TABLE t2 (i INTEGER NULL);
773 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
774 CREATE TABLE t4 (i INTEGER NULL);
775 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
776 INSERT INTO t2 VALUES(1),(2),(3);
777 CREATE FUNCTION sf1() RETURNS INTEGER
778 BEGIN
779 DECLARE done INT DEFAULT 0;
780 DECLARE a CHAR(16);
781 DECLARE b,c INT;
782 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
783 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
784 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
785 OPEN cur1;
786 OPEN cur2;
787 REPEAT
788 FETCH cur1 INTO a, b;
789 FETCH cur2 INTO c;
790 IF NOT done THEN
791 IF b < c THEN
792 INSERT INTO t3 VALUES (a,b);
793 ELSE
794 INSERT INTO t3 VALUES (a,c);
795 END IF;
796 END IF;
797 UNTIL done END REPEAT;
798 CLOSE cur1;
799 CLOSE cur2;
800 RETURN 0;
801 END//
802 ALTER FUNCTION sf1 #DET# CONTAINS SQL COMMENT 'comment' ;
803 SELECT sf1();
804 sf1()
805 0
806 SELECT * FROM t1 ORDER BY id;
807 id data
808 1 1
809 2 1
810 3 4
811 4 5
812 SELECT * FROM t2 ORDER BY i;
813 i
814 1
815 2
816 3
817 SELECT * FROM t3 ORDER BY id;
818 id data
819 1 1
820 2 1
821 3 3
822 INSERT INTO t4 VALUES(sf1());
823 SELECT * FROM t1 ORDER BY id;
824 id data
825 1 1
826 2 1
827 3 4
828 4 5
829 SELECT * FROM t2 ORDER BY i;
830 i
831 1
832 2
833 3
834 SELECT * FROM t3 ORDER BY id;
835 id data
836 1 1
837 1 1
838 2 1
839 2 1
840 3 3
841 3 3
842 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
843 SELECT * FROM t1 ORDER BY id;
844 id data
845 1 1
846 2 1
847 3 4
848 4 5
849 SELECT * FROM t2 ORDER BY i;
850 i
851 1
852 2
853 3
854 SELECT * FROM t3 ORDER BY id;
855 id data
856 1 1
857 1 1
858 1 1
859 1 1
860 2 1
861 2 1
862 2 1
863 2 1
864 3 3
865 3 3
866 3 3
867 3 3
868 DELETE FROM t4 WHERE i = sf1() + 1;
869 SELECT * FROM t1 ORDER BY id;
870 id data
871 1 1
872 2 1
873 3 4
874 4 5
875 SELECT * FROM t2 ORDER BY i;
876 i
877 1
878 2
879 3
880 SELECT * FROM t3 ORDER BY id;
881 id data
882 1 1
883 1 1
884 1 1
885 1 1
886 1 1
887 2 1
888 2 1
889 2 1
890 2 1
891 2 1
892 3 3
893 3 3
894 3 3
895 3 3
896 3 3
897 DROP FUNCTION sf1;
898 DROP TABLE t1,t2,t3,t4;
899 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
900 CREATE TABLE t2 (i INTEGER NULL);
901 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
902 CREATE TABLE t4 (i INTEGER NULL);
903 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
904 INSERT INTO t2 VALUES(1),(2),(3);
905 CREATE FUNCTION sf1() RETURNS INTEGER
906 BEGIN
907 DECLARE done INT DEFAULT 0;
908 DECLARE a CHAR(16);
909 DECLARE b,c INT;
910 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
911 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
912 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
913 OPEN cur1;
914 OPEN cur2;
915 REPEAT
916 FETCH cur1 INTO a, b;
917 FETCH cur2 INTO c;
918 IF NOT done THEN
919 IF b < c THEN
920 INSERT INTO t3 VALUES (a,b);
921 ELSE
922 INSERT INTO t3 VALUES (a,c);
923 END IF;
924 END IF;
925 UNTIL done END REPEAT;
926 CLOSE cur1;
927 CLOSE cur2;
928 RETURN 0;
929 END//
930 ALTER FUNCTION sf1 #DET# NO SQL COMMENT 'comment' ;
931 SELECT sf1();
932 sf1()
933 0
934 SELECT * FROM t1 ORDER BY id;
935 id data
936 1 1
937 2 1
938 3 4
939 4 5
940 SELECT * FROM t2 ORDER BY i;
941 i
942 1
943 2
944 3
945 SELECT * FROM t3 ORDER BY id;
946 id data
947 1 1
948 2 1
949 3 3
950 INSERT INTO t4 VALUES(sf1());
951 SELECT * FROM t1 ORDER BY id;
952 id data
953 1 1
954 2 1
955 3 4
956 4 5
957 SELECT * FROM t2 ORDER BY i;
958 i
959 1
960 2
961 3
962 SELECT * FROM t3 ORDER BY id;
963 id data
964 1 1
965 1 1
966 2 1
967 2 1
968 3 3
969 3 3
970 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
971 SELECT * FROM t1 ORDER BY id;
972 id data
973 1 1
974 2 1
975 3 4
976 4 5
977 SELECT * FROM t2 ORDER BY i;
978 i
979 1
980 2
981 3
982 SELECT * FROM t3 ORDER BY id;
983 id data
984 1 1
985 1 1
986 1 1
987 1 1
988 2 1
989 2 1
990 2 1
991 2 1
992 3 3
993 3 3
994 3 3
995 3 3
996 DELETE FROM t4 WHERE i = sf1() + 1;
997 SELECT * FROM t1 ORDER BY id;
998 id data
999 1 1
1000 2 1
1001 3 4
1002 4 5
1003 SELECT * FROM t2 ORDER BY i;
1004 i
1005 1
1006 2
1007 3
1008 SELECT * FROM t3 ORDER BY id;
1009 id data
1010 1 1
1011 1 1
1012 1 1
1013 1 1
1014 1 1
1015 2 1
1016 2 1
1017 2 1
1018 2 1
1019 2 1
1020 3 3
1021 3 3
1022 3 3
1023 3 3
1024 3 3
1025 DROP FUNCTION sf1;
1026 DROP TABLE t1,t2,t3,t4;
1027 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1028 CREATE TABLE t2 (i INTEGER NULL);
1029 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1030 CREATE TABLE t4 (i INTEGER NULL);
1031 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1032 INSERT INTO t2 VALUES(1),(2),(3);
1033 CREATE FUNCTION sf1() RETURNS INTEGER
1034 BEGIN
1035 DECLARE done INT DEFAULT 0;
1036 DECLARE a CHAR(16);
1037 DECLARE b,c INT;
1038 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1039 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1040 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1041 OPEN cur1;
1042 OPEN cur2;
1043 REPEAT
1044 FETCH cur1 INTO a, b;
1045 FETCH cur2 INTO c;
1046 IF NOT done THEN
1047 IF b < c THEN
1048 INSERT INTO t3 VALUES (a,b);
1049 ELSE
1050 INSERT INTO t3 VALUES (a,c);
1051 END IF;
1052 END IF;
1053 UNTIL done END REPEAT;
1054 CLOSE cur1;
1055 CLOSE cur2;
1056 RETURN 0;
1057 END//
1058 ALTER FUNCTION sf1 #DET# READS SQL DATA COMMENT 'comment' ;
1059 SELECT sf1();
1060 sf1()
1061 0
1062 SELECT * FROM t1 ORDER BY id;
1063 id data
1064 1 1
1065 2 1
1066 3 4
1067 4 5
1068 SELECT * FROM t2 ORDER BY i;
1069 i
1070 1
1071 2
1072 3
1073 SELECT * FROM t3 ORDER BY id;
1074 id data
1075 1 1
1076 2 1
1077 3 3
1078 INSERT INTO t4 VALUES(sf1());
1079 SELECT * FROM t1 ORDER BY id;
1080 id data
1081 1 1
1082 2 1
1083 3 4
1084 4 5
1085 SELECT * FROM t2 ORDER BY i;
1086 i
1087 1
1088 2
1089 3
1090 SELECT * FROM t3 ORDER BY id;
1091 id data
1092 1 1
1093 1 1
1094 2 1
1095 2 1
1096 3 3
1097 3 3
1098 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1099 SELECT * FROM t1 ORDER BY id;
1100 id data
1101 1 1
1102 2 1
1103 3 4
1104 4 5
1105 SELECT * FROM t2 ORDER BY i;
1106 i
1107 1
1108 2
1109 3
1110 SELECT * FROM t3 ORDER BY id;
1111 id data
1112 1 1
1113 1 1
1114 1 1
1115 1 1
1116 2 1
1117 2 1
1118 2 1
1119 2 1
1120 3 3
1121 3 3
1122 3 3
1123 3 3
1124 DELETE FROM t4 WHERE i = sf1() + 1;
1125 SELECT * FROM t1 ORDER BY id;
1126 id data
1127 1 1
1128 2 1
1129 3 4
1130 4 5
1131 SELECT * FROM t2 ORDER BY i;
1132 i
1133 1
1134 2
1135 3
1136 SELECT * FROM t3 ORDER BY id;
1137 id data
1138 1 1
1139 1 1
1140 1 1
1141 1 1
1142 1 1
1143 2 1
1144 2 1
1145 2 1
1146 2 1
1147 2 1
1148 3 3
1149 3 3
1150 3 3
1151 3 3
1152 3 3
1153 DROP FUNCTION sf1;
1154 DROP TABLE t1,t2,t3,t4;
1155 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1156 CREATE TABLE t2 (i INTEGER NULL);
1157 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1158 CREATE TABLE t4 (i INTEGER NULL);
1159 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1160 INSERT INTO t2 VALUES(1),(2),(3);
1161 CREATE FUNCTION sf1() RETURNS INTEGER
1162 BEGIN
1163 DECLARE done INT DEFAULT 0;
1164 DECLARE a CHAR(16);
1165 DECLARE b,c INT;
1166 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1167 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1168 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1169 OPEN cur1;
1170 OPEN cur2;
1171 REPEAT
1172 FETCH cur1 INTO a, b;
1173 FETCH cur2 INTO c;
1174 IF NOT done THEN
1175 IF b < c THEN
1176 INSERT INTO t3 VALUES (a,b);
1177 ELSE
1178 INSERT INTO t3 VALUES (a,c);
1179 END IF;
1180 END IF;
1181 UNTIL done END REPEAT;
1182 CLOSE cur1;
1183 CLOSE cur2;
1184 RETURN 0;
1185 END//
1186 ALTER FUNCTION sf1 #DET# MODIFIES SQL DATA COMMENT 'comment' ;
1187 SELECT sf1();
1188 sf1()
1189 0
1190 SELECT * FROM t1 ORDER BY id;
1191 id data
1192 1 1
1193 2 1
1194 3 4
1195 4 5
1196 SELECT * FROM t2 ORDER BY i;
1197 i
1198 1
1199 2
1200 3
1201 SELECT * FROM t3 ORDER BY id;
1202 id data
1203 1 1
1204 2 1
1205 3 3
1206 INSERT INTO t4 VALUES(sf1());
1207 SELECT * FROM t1 ORDER BY id;
1208 id data
1209 1 1
1210 2 1
1211 3 4
1212 4 5
1213 SELECT * FROM t2 ORDER BY i;
1214 i
1215 1
1216 2
1217 3
1218 SELECT * FROM t3 ORDER BY id;
1219 id data
1220 1 1
1221 1 1
1222 2 1
1223 2 1
1224 3 3
1225 3 3
1226 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1227 SELECT * FROM t1 ORDER BY id;
1228 id data
1229 1 1
1230 2 1
1231 3 4
1232 4 5
1233 SELECT * FROM t2 ORDER BY i;
1234 i
1235 1
1236 2
1237 3
1238 SELECT * FROM t3 ORDER BY id;
1239 id data
1240 1 1
1241 1 1
1242 1 1
1243 1 1
1244 2 1
1245 2 1
1246 2 1
1247 2 1
1248 3 3
1249 3 3
1250 3 3
1251 3 3
1252 DELETE FROM t4 WHERE i = sf1() + 1;
1253 SELECT * FROM t1 ORDER BY id;
1254 id data
1255 1 1
1256 2 1
1257 3 4
1258 4 5
1259 SELECT * FROM t2 ORDER BY i;
1260 i
1261 1
1262 2
1263 3
1264 SELECT * FROM t3 ORDER BY id;
1265 id data
1266 1 1
1267 1 1
1268 1 1
1269 1 1
1270 1 1
1271 2 1
1272 2 1
1273 2 1
1274 2 1
1275 2 1
1276 3 3
1277 3 3
1278 3 3
1279 3 3
1280 3 3
1281 DROP FUNCTION sf1;
1282 DROP TABLE t1,t2,t3,t4;
1283 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1284 CREATE TABLE t2 (i INTEGER NULL);
1285 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1286 CREATE TABLE t4 (i INTEGER NULL);
1287 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1288 INSERT INTO t2 VALUES(1),(2),(3);
1289 CREATE FUNCTION sf1() RETURNS INTEGER
1290 BEGIN
1291 DECLARE done INT DEFAULT 0;
1292 DECLARE a CHAR(16);
1293 DECLARE b,c INT;
1294 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1295 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1296 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1297 OPEN cur1;
1298 OPEN cur2;
1299 REPEAT
1300 FETCH cur1 INTO a, b;
1301 FETCH cur2 INTO c;
1302 IF NOT done THEN
1303 IF b < c THEN
1304 INSERT INTO t3 VALUES (a,b);
1305 ELSE
1306 INSERT INTO t3 VALUES (a,c);
1307 END IF;
1308 END IF;
1309 UNTIL done END REPEAT;
1310 CLOSE cur1;
1311 CLOSE cur2;
1312 RETURN 0;
1313 END//
1314 ALTER FUNCTION sf1 LANGUAGE SQL #DET# ;
1315 SELECT sf1();
1316 sf1()
1317 0
1318 SELECT * FROM t1 ORDER BY id;
1319 id data
1320 1 1
1321 2 1
1322 3 4
1323 4 5
1324 SELECT * FROM t2 ORDER BY i;
1325 i
1326 1
1327 2
1328 3
1329 SELECT * FROM t3 ORDER BY id;
1330 id data
1331 1 1
1332 2 1
1333 3 3
1334 INSERT INTO t4 VALUES(sf1());
1335 SELECT * FROM t1 ORDER BY id;
1336 id data
1337 1 1
1338 2 1
1339 3 4
1340 4 5
1341 SELECT * FROM t2 ORDER BY i;
1342 i
1343 1
1344 2
1345 3
1346 SELECT * FROM t3 ORDER BY id;
1347 id data
1348 1 1
1349 1 1
1350 2 1
1351 2 1
1352 3 3
1353 3 3
1354 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1355 SELECT * FROM t1 ORDER BY id;
1356 id data
1357 1 1
1358 2 1
1359 3 4
1360 4 5
1361 SELECT * FROM t2 ORDER BY i;
1362 i
1363 1
1364 2
1365 3
1366 SELECT * FROM t3 ORDER BY id;
1367 id data
1368 1 1
1369 1 1
1370 1 1
1371 1 1
1372 2 1
1373 2 1
1374 2 1
1375 2 1
1376 3 3
1377 3 3
1378 3 3
1379 3 3
1380 DELETE FROM t4 WHERE i = sf1() + 1;
1381 SELECT * FROM t1 ORDER BY id;
1382 id data
1383 1 1
1384 2 1
1385 3 4
1386 4 5
1387 SELECT * FROM t2 ORDER BY i;
1388 i
1389 1
1390 2
1391 3
1392 SELECT * FROM t3 ORDER BY id;
1393 id data
1394 1 1
1395 1 1
1396 1 1
1397 1 1
1398 1 1
1399 2 1
1400 2 1
1401 2 1
1402 2 1
1403 2 1
1404 3 3
1405 3 3
1406 3 3
1407 3 3
1408 3 3
1409 DROP FUNCTION sf1;
1410 DROP TABLE t1,t2,t3,t4;
1411 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1412 CREATE TABLE t2 (i INTEGER NULL);
1413 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1414 CREATE TABLE t4 (i INTEGER NULL);
1415 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1416 INSERT INTO t2 VALUES(1),(2),(3);
1417 CREATE FUNCTION sf1() RETURNS INTEGER
1418 BEGIN
1419 DECLARE done INT DEFAULT 0;
1420 DECLARE a CHAR(16);
1421 DECLARE b,c INT;
1422 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1423 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1424 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1425 OPEN cur1;
1426 OPEN cur2;
1427 REPEAT
1428 FETCH cur1 INTO a, b;
1429 FETCH cur2 INTO c;
1430 IF NOT done THEN
1431 IF b < c THEN
1432 INSERT INTO t3 VALUES (a,b);
1433 ELSE
1434 INSERT INTO t3 VALUES (a,c);
1435 END IF;
1436 END IF;
1437 UNTIL done END REPEAT;
1438 CLOSE cur1;
1439 CLOSE cur2;
1440 RETURN 0;
1441 END//
1442 ALTER FUNCTION sf1 LANGUAGE SQL #DET# CONTAINS SQL ;
1443 SELECT sf1();
1444 sf1()
1445 0
1446 SELECT * FROM t1 ORDER BY id;
1447 id data
1448 1 1
1449 2 1
1450 3 4
1451 4 5
1452 SELECT * FROM t2 ORDER BY i;
1453 i
1454 1
1455 2
1456 3
1457 SELECT * FROM t3 ORDER BY id;
1458 id data
1459 1 1
1460 2 1
1461 3 3
1462 INSERT INTO t4 VALUES(sf1());
1463 SELECT * FROM t1 ORDER BY id;
1464 id data
1465 1 1
1466 2 1
1467 3 4
1468 4 5
1469 SELECT * FROM t2 ORDER BY i;
1470 i
1471 1
1472 2
1473 3
1474 SELECT * FROM t3 ORDER BY id;
1475 id data
1476 1 1
1477 1 1
1478 2 1
1479 2 1
1480 3 3
1481 3 3
1482 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1483 SELECT * FROM t1 ORDER BY id;
1484 id data
1485 1 1
1486 2 1
1487 3 4
1488 4 5
1489 SELECT * FROM t2 ORDER BY i;
1490 i
1491 1
1492 2
1493 3
1494 SELECT * FROM t3 ORDER BY id;
1495 id data
1496 1 1
1497 1 1
1498 1 1
1499 1 1
1500 2 1
1501 2 1
1502 2 1
1503 2 1
1504 3 3
1505 3 3
1506 3 3
1507 3 3
1508 DELETE FROM t4 WHERE i = sf1() + 1;
1509 SELECT * FROM t1 ORDER BY id;
1510 id data
1511 1 1
1512 2 1
1513 3 4
1514 4 5
1515 SELECT * FROM t2 ORDER BY i;
1516 i
1517 1
1518 2
1519 3
1520 SELECT * FROM t3 ORDER BY id;
1521 id data
1522 1 1
1523 1 1
1524 1 1
1525 1 1
1526 1 1
1527 2 1
1528 2 1
1529 2 1
1530 2 1
1531 2 1
1532 3 3
1533 3 3
1534 3 3
1535 3 3
1536 3 3
1537 DROP FUNCTION sf1;
1538 DROP TABLE t1,t2,t3,t4;
1539 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1540 CREATE TABLE t2 (i INTEGER NULL);
1541 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1542 CREATE TABLE t4 (i INTEGER NULL);
1543 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1544 INSERT INTO t2 VALUES(1),(2),(3);
1545 CREATE FUNCTION sf1() RETURNS INTEGER
1546 BEGIN
1547 DECLARE done INT DEFAULT 0;
1548 DECLARE a CHAR(16);
1549 DECLARE b,c INT;
1550 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1551 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1552 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1553 OPEN cur1;
1554 OPEN cur2;
1555 REPEAT
1556 FETCH cur1 INTO a, b;
1557 FETCH cur2 INTO c;
1558 IF NOT done THEN
1559 IF b < c THEN
1560 INSERT INTO t3 VALUES (a,b);
1561 ELSE
1562 INSERT INTO t3 VALUES (a,c);
1563 END IF;
1564 END IF;
1565 UNTIL done END REPEAT;
1566 CLOSE cur1;
1567 CLOSE cur2;
1568 RETURN 0;
1569 END//
1570 ALTER FUNCTION sf1 LANGUAGE SQL #DET# NO SQL ;
1571 SELECT sf1();
1572 sf1()
1573 0
1574 SELECT * FROM t1 ORDER BY id;
1575 id data
1576 1 1
1577 2 1
1578 3 4
1579 4 5
1580 SELECT * FROM t2 ORDER BY i;
1581 i
1582 1
1583 2
1584 3
1585 SELECT * FROM t3 ORDER BY id;
1586 id data
1587 1 1
1588 2 1
1589 3 3
1590 INSERT INTO t4 VALUES(sf1());
1591 SELECT * FROM t1 ORDER BY id;
1592 id data
1593 1 1
1594 2 1
1595 3 4
1596 4 5
1597 SELECT * FROM t2 ORDER BY i;
1598 i
1599 1
1600 2
1601 3
1602 SELECT * FROM t3 ORDER BY id;
1603 id data
1604 1 1
1605 1 1
1606 2 1
1607 2 1
1608 3 3
1609 3 3
1610 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1611 SELECT * FROM t1 ORDER BY id;
1612 id data
1613 1 1
1614 2 1
1615 3 4
1616 4 5
1617 SELECT * FROM t2 ORDER BY i;
1618 i
1619 1
1620 2
1621 3
1622 SELECT * FROM t3 ORDER BY id;
1623 id data
1624 1 1
1625 1 1
1626 1 1
1627 1 1
1628 2 1
1629 2 1
1630 2 1
1631 2 1
1632 3 3
1633 3 3
1634 3 3
1635 3 3
1636 DELETE FROM t4 WHERE i = sf1() + 1;
1637 SELECT * FROM t1 ORDER BY id;
1638 id data
1639 1 1
1640 2 1
1641 3 4
1642 4 5
1643 SELECT * FROM t2 ORDER BY i;
1644 i
1645 1
1646 2
1647 3
1648 SELECT * FROM t3 ORDER BY id;
1649 id data
1650 1 1
1651 1 1
1652 1 1
1653 1 1
1654 1 1
1655 2 1
1656 2 1
1657 2 1
1658 2 1
1659 2 1
1660 3 3
1661 3 3
1662 3 3
1663 3 3
1664 3 3
1665 DROP FUNCTION sf1;
1666 DROP TABLE t1,t2,t3,t4;
1667 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1668 CREATE TABLE t2 (i INTEGER NULL);
1669 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1670 CREATE TABLE t4 (i INTEGER NULL);
1671 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1672 INSERT INTO t2 VALUES(1),(2),(3);
1673 CREATE FUNCTION sf1() RETURNS INTEGER
1674 BEGIN
1675 DECLARE done INT DEFAULT 0;
1676 DECLARE a CHAR(16);
1677 DECLARE b,c INT;
1678 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1679 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1680 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1681 OPEN cur1;
1682 OPEN cur2;
1683 REPEAT
1684 FETCH cur1 INTO a, b;
1685 FETCH cur2 INTO c;
1686 IF NOT done THEN
1687 IF b < c THEN
1688 INSERT INTO t3 VALUES (a,b);
1689 ELSE
1690 INSERT INTO t3 VALUES (a,c);
1691 END IF;
1692 END IF;
1693 UNTIL done END REPEAT;
1694 CLOSE cur1;
1695 CLOSE cur2;
1696 RETURN 0;
1697 END//
1698 ALTER FUNCTION sf1 LANGUAGE SQL #DET# READS SQL DATA ;
1699 SELECT sf1();
1700 sf1()
1701 0
1702 SELECT * FROM t1 ORDER BY id;
1703 id data
1704 1 1
1705 2 1
1706 3 4
1707 4 5
1708 SELECT * FROM t2 ORDER BY i;
1709 i
1710 1
1711 2
1712 3
1713 SELECT * FROM t3 ORDER BY id;
1714 id data
1715 1 1
1716 2 1
1717 3 3
1718 INSERT INTO t4 VALUES(sf1());
1719 SELECT * FROM t1 ORDER BY id;
1720 id data
1721 1 1
1722 2 1
1723 3 4
1724 4 5
1725 SELECT * FROM t2 ORDER BY i;
1726 i
1727 1
1728 2
1729 3
1730 SELECT * FROM t3 ORDER BY id;
1731 id data
1732 1 1
1733 1 1
1734 2 1
1735 2 1
1736 3 3
1737 3 3
1738 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1739 SELECT * FROM t1 ORDER BY id;
1740 id data
1741 1 1
1742 2 1
1743 3 4
1744 4 5
1745 SELECT * FROM t2 ORDER BY i;
1746 i
1747 1
1748 2
1749 3
1750 SELECT * FROM t3 ORDER BY id;
1751 id data
1752 1 1
1753 1 1
1754 1 1
1755 1 1
1756 2 1
1757 2 1
1758 2 1
1759 2 1
1760 3 3
1761 3 3
1762 3 3
1763 3 3
1764 DELETE FROM t4 WHERE i = sf1() + 1;
1765 SELECT * FROM t1 ORDER BY id;
1766 id data
1767 1 1
1768 2 1
1769 3 4
1770 4 5
1771 SELECT * FROM t2 ORDER BY i;
1772 i
1773 1
1774 2
1775 3
1776 SELECT * FROM t3 ORDER BY id;
1777 id data
1778 1 1
1779 1 1
1780 1 1
1781 1 1
1782 1 1
1783 2 1
1784 2 1
1785 2 1
1786 2 1
1787 2 1
1788 3 3
1789 3 3
1790 3 3
1791 3 3
1792 3 3
1793 DROP FUNCTION sf1;
1794 DROP TABLE t1,t2,t3,t4;
1795 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1796 CREATE TABLE t2 (i INTEGER NULL);
1797 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1798 CREATE TABLE t4 (i INTEGER NULL);
1799 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1800 INSERT INTO t2 VALUES(1),(2),(3);
1801 CREATE FUNCTION sf1() RETURNS INTEGER
1802 BEGIN
1803 DECLARE done INT DEFAULT 0;
1804 DECLARE a CHAR(16);
1805 DECLARE b,c INT;
1806 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1807 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1808 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1809 OPEN cur1;
1810 OPEN cur2;
1811 REPEAT
1812 FETCH cur1 INTO a, b;
1813 FETCH cur2 INTO c;
1814 IF NOT done THEN
1815 IF b < c THEN
1816 INSERT INTO t3 VALUES (a,b);
1817 ELSE
1818 INSERT INTO t3 VALUES (a,c);
1819 END IF;
1820 END IF;
1821 UNTIL done END REPEAT;
1822 CLOSE cur1;
1823 CLOSE cur2;
1824 RETURN 0;
1825 END//
1826 ALTER FUNCTION sf1 LANGUAGE SQL #DET# MODIFIES SQL DATA ;
1827 SELECT sf1();
1828 sf1()
1829 0
1830 SELECT * FROM t1 ORDER BY id;
1831 id data
1832 1 1
1833 2 1
1834 3 4
1835 4 5
1836 SELECT * FROM t2 ORDER BY i;
1837 i
1838 1
1839 2
1840 3
1841 SELECT * FROM t3 ORDER BY id;
1842 id data
1843 1 1
1844 2 1
1845 3 3
1846 INSERT INTO t4 VALUES(sf1());
1847 SELECT * FROM t1 ORDER BY id;
1848 id data
1849 1 1
1850 2 1
1851 3 4
1852 4 5
1853 SELECT * FROM t2 ORDER BY i;
1854 i
1855 1
1856 2
1857 3
1858 SELECT * FROM t3 ORDER BY id;
1859 id data
1860 1 1
1861 1 1
1862 2 1
1863 2 1
1864 3 3
1865 3 3
1866 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1867 SELECT * FROM t1 ORDER BY id;
1868 id data
1869 1 1
1870 2 1
1871 3 4
1872 4 5
1873 SELECT * FROM t2 ORDER BY i;
1874 i
1875 1
1876 2
1877 3
1878 SELECT * FROM t3 ORDER BY id;
1879 id data
1880 1 1
1881 1 1
1882 1 1
1883 1 1
1884 2 1
1885 2 1
1886 2 1
1887 2 1
1888 3 3
1889 3 3
1890 3 3
1891 3 3
1892 DELETE FROM t4 WHERE i = sf1() + 1;
1893 SELECT * FROM t1 ORDER BY id;
1894 id data
1895 1 1
1896 2 1
1897 3 4
1898 4 5
1899 SELECT * FROM t2 ORDER BY i;
1900 i
1901 1
1902 2
1903 3
1904 SELECT * FROM t3 ORDER BY id;
1905 id data
1906 1 1
1907 1 1
1908 1 1
1909 1 1
1910 1 1
1911 2 1
1912 2 1
1913 2 1
1914 2 1
1915 2 1
1916 3 3
1917 3 3
1918 3 3
1919 3 3
1920 3 3
1921 DROP FUNCTION sf1;
1922 DROP TABLE t1,t2,t3,t4;
1923 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1924 CREATE TABLE t2 (i INTEGER NULL);
1925 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1926 CREATE TABLE t4 (i INTEGER NULL);
1927 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1928 INSERT INTO t2 VALUES(1),(2),(3);
1929 CREATE FUNCTION sf1() RETURNS INTEGER
1930 BEGIN
1931 DECLARE done INT DEFAULT 0;
1932 DECLARE a CHAR(16);
1933 DECLARE b,c INT;
1934 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1935 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1936 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1937 OPEN cur1;
1938 OPEN cur2;
1939 REPEAT
1940 FETCH cur1 INTO a, b;
1941 FETCH cur2 INTO c;
1942 IF NOT done THEN
1943 IF b < c THEN
1944 INSERT INTO t3 VALUES (a,b);
1945 ELSE
1946 INSERT INTO t3 VALUES (a,c);
1947 END IF;
1948 END IF;
1949 UNTIL done END REPEAT;
1950 CLOSE cur1;
1951 CLOSE cur2;
1952 RETURN 0;
1953 END//
1954 ALTER FUNCTION sf1 LANGUAGE SQL #DET# COMMENT 'comment' ;
1955 SELECT sf1();
1956 sf1()
1957 0
1958 SELECT * FROM t1 ORDER BY id;
1959 id data
1960 1 1
1961 2 1
1962 3 4
1963 4 5
1964 SELECT * FROM t2 ORDER BY i;
1965 i
1966 1
1967 2
1968 3
1969 SELECT * FROM t3 ORDER BY id;
1970 id data
1971 1 1
1972 2 1
1973 3 3
1974 INSERT INTO t4 VALUES(sf1());
1975 SELECT * FROM t1 ORDER BY id;
1976 id data
1977 1 1
1978 2 1
1979 3 4
1980 4 5
1981 SELECT * FROM t2 ORDER BY i;
1982 i
1983 1
1984 2
1985 3
1986 SELECT * FROM t3 ORDER BY id;
1987 id data
1988 1 1
1989 1 1
1990 2 1
1991 2 1
1992 3 3
1993 3 3
1994 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1995 SELECT * FROM t1 ORDER BY id;
1996 id data
1997 1 1
1998 2 1
1999 3 4
2000 4 5
2001 SELECT * FROM t2 ORDER BY i;
2002 i
2003 1
2004 2
2005 3
2006 SELECT * FROM t3 ORDER BY id;
2007 id data
2008 1 1
2009 1 1
2010 1 1
2011 1 1
2012 2 1
2013 2 1
2014 2 1
2015 2 1
2016 3 3
2017 3 3
2018 3 3
2019 3 3
2020 DELETE FROM t4 WHERE i = sf1() + 1;
2021 SELECT * FROM t1 ORDER BY id;
2022 id data
2023 1 1
2024 2 1
2025 3 4
2026 4 5
2027 SELECT * FROM t2 ORDER BY i;
2028 i
2029 1
2030 2
2031 3
2032 SELECT * FROM t3 ORDER BY id;
2033 id data
2034 1 1
2035 1 1
2036 1 1
2037 1 1
2038 1 1
2039 2 1
2040 2 1
2041 2 1
2042 2 1
2043 2 1
2044 3 3
2045 3 3
2046 3 3
2047 3 3
2048 3 3
2049 DROP FUNCTION sf1;
2050 DROP TABLE t1,t2,t3,t4;
2051 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2052 CREATE TABLE t2 (i INTEGER NULL);
2053 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2054 CREATE TABLE t4 (i INTEGER NULL);
2055 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2056 INSERT INTO t2 VALUES(1),(2),(3);
2057 CREATE FUNCTION sf1() RETURNS INTEGER
2058 BEGIN
2059 DECLARE done INT DEFAULT 0;
2060 DECLARE a CHAR(16);
2061 DECLARE b,c INT;
2062 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2063 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2064 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2065 OPEN cur1;
2066 OPEN cur2;
2067 REPEAT
2068 FETCH cur1 INTO a, b;
2069 FETCH cur2 INTO c;
2070 IF NOT done THEN
2071 IF b < c THEN
2072 INSERT INTO t3 VALUES (a,b);
2073 ELSE
2074 INSERT INTO t3 VALUES (a,c);
2075 END IF;
2076 END IF;
2077 UNTIL done END REPEAT;
2078 CLOSE cur1;
2079 CLOSE cur2;
2080 RETURN 0;
2081 END//
2082 ALTER FUNCTION sf1 LANGUAGE SQL #DET# CONTAINS SQL COMMENT 'comment' ;
2083 SELECT sf1();
2084 sf1()
2085 0
2086 SELECT * FROM t1 ORDER BY id;
2087 id data
2088 1 1
2089 2 1
2090 3 4
2091 4 5
2092 SELECT * FROM t2 ORDER BY i;
2093 i
2094 1
2095 2
2096 3
2097 SELECT * FROM t3 ORDER BY id;
2098 id data
2099 1 1
2100 2 1
2101 3 3
2102 INSERT INTO t4 VALUES(sf1());
2103 SELECT * FROM t1 ORDER BY id;
2104 id data
2105 1 1
2106 2 1
2107 3 4
2108 4 5
2109 SELECT * FROM t2 ORDER BY i;
2110 i
2111 1
2112 2
2113 3
2114 SELECT * FROM t3 ORDER BY id;
2115 id data
2116 1 1
2117 1 1
2118 2 1
2119 2 1
2120 3 3
2121 3 3
2122 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2123 SELECT * FROM t1 ORDER BY id;
2124 id data
2125 1 1
2126 2 1
2127 3 4
2128 4 5
2129 SELECT * FROM t2 ORDER BY i;
2130 i
2131 1
2132 2
2133 3
2134 SELECT * FROM t3 ORDER BY id;
2135 id data
2136 1 1
2137 1 1
2138 1 1
2139 1 1
2140 2 1
2141 2 1
2142 2 1
2143 2 1
2144 3 3
2145 3 3
2146 3 3
2147 3 3
2148 DELETE FROM t4 WHERE i = sf1() + 1;
2149 SELECT * FROM t1 ORDER BY id;
2150 id data
2151 1 1
2152 2 1
2153 3 4
2154 4 5
2155 SELECT * FROM t2 ORDER BY i;
2156 i
2157 1
2158 2
2159 3
2160 SELECT * FROM t3 ORDER BY id;
2161 id data
2162 1 1
2163 1 1
2164 1 1
2165 1 1
2166 1 1
2167 2 1
2168 2 1
2169 2 1
2170 2 1
2171 2 1
2172 3 3
2173 3 3
2174 3 3
2175 3 3
2176 3 3
2177 DROP FUNCTION sf1;
2178 DROP TABLE t1,t2,t3,t4;
2179 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2180 CREATE TABLE t2 (i INTEGER NULL);
2181 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2182 CREATE TABLE t4 (i INTEGER NULL);
2183 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2184 INSERT INTO t2 VALUES(1),(2),(3);
2185 CREATE FUNCTION sf1() RETURNS INTEGER
2186 BEGIN
2187 DECLARE done INT DEFAULT 0;
2188 DECLARE a CHAR(16);
2189 DECLARE b,c INT;
2190 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2191 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2192 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2193 OPEN cur1;
2194 OPEN cur2;
2195 REPEAT
2196 FETCH cur1 INTO a, b;
2197 FETCH cur2 INTO c;
2198 IF NOT done THEN
2199 IF b < c THEN
2200 INSERT INTO t3 VALUES (a,b);
2201 ELSE
2202 INSERT INTO t3 VALUES (a,c);
2203 END IF;
2204 END IF;
2205 UNTIL done END REPEAT;
2206 CLOSE cur1;
2207 CLOSE cur2;
2208 RETURN 0;
2209 END//
2210 ALTER FUNCTION sf1 LANGUAGE SQL #DET# NO SQL COMMENT 'comment' ;
2211 SELECT sf1();
2212 sf1()
2213 0
2214 SELECT * FROM t1 ORDER BY id;
2215 id data
2216 1 1
2217 2 1
2218 3 4
2219 4 5
2220 SELECT * FROM t2 ORDER BY i;
2221 i
2222 1
2223 2
2224 3
2225 SELECT * FROM t3 ORDER BY id;
2226 id data
2227 1 1
2228 2 1
2229 3 3
2230 INSERT INTO t4 VALUES(sf1());
2231 SELECT * FROM t1 ORDER BY id;
2232 id data
2233 1 1
2234 2 1
2235 3 4
2236 4 5
2237 SELECT * FROM t2 ORDER BY i;
2238 i
2239 1
2240 2
2241 3
2242 SELECT * FROM t3 ORDER BY id;
2243 id data
2244 1 1
2245 1 1
2246 2 1
2247 2 1
2248 3 3
2249 3 3
2250 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2251 SELECT * FROM t1 ORDER BY id;
2252 id data
2253 1 1
2254 2 1
2255 3 4
2256 4 5
2257 SELECT * FROM t2 ORDER BY i;
2258 i
2259 1
2260 2
2261 3
2262 SELECT * FROM t3 ORDER BY id;
2263 id data
2264 1 1
2265 1 1
2266 1 1
2267 1 1
2268 2 1
2269 2 1
2270 2 1
2271 2 1
2272 3 3
2273 3 3
2274 3 3
2275 3 3
2276 DELETE FROM t4 WHERE i = sf1() + 1;
2277 SELECT * FROM t1 ORDER BY id;
2278 id data
2279 1 1
2280 2 1
2281 3 4
2282 4 5
2283 SELECT * FROM t2 ORDER BY i;
2284 i
2285 1
2286 2
2287 3
2288 SELECT * FROM t3 ORDER BY id;
2289 id data
2290 1 1
2291 1 1
2292 1 1
2293 1 1
2294 1 1
2295 2 1
2296 2 1
2297 2 1
2298 2 1
2299 2 1
2300 3 3
2301 3 3
2302 3 3
2303 3 3
2304 3 3
2305 DROP FUNCTION sf1;
2306 DROP TABLE t1,t2,t3,t4;
2307 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2308 CREATE TABLE t2 (i INTEGER NULL);
2309 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2310 CREATE TABLE t4 (i INTEGER NULL);
2311 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2312 INSERT INTO t2 VALUES(1),(2),(3);
2313 CREATE FUNCTION sf1() RETURNS INTEGER
2314 BEGIN
2315 DECLARE done INT DEFAULT 0;
2316 DECLARE a CHAR(16);
2317 DECLARE b,c INT;
2318 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2319 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2320 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2321 OPEN cur1;
2322 OPEN cur2;
2323 REPEAT
2324 FETCH cur1 INTO a, b;
2325 FETCH cur2 INTO c;
2326 IF NOT done THEN
2327 IF b < c THEN
2328 INSERT INTO t3 VALUES (a,b);
2329 ELSE
2330 INSERT INTO t3 VALUES (a,c);
2331 END IF;
2332 END IF;
2333 UNTIL done END REPEAT;
2334 CLOSE cur1;
2335 CLOSE cur2;
2336 RETURN 0;
2337 END//
2338 ALTER FUNCTION sf1 LANGUAGE SQL #DET# READS SQL DATA COMMENT 'comment' ;
2339 SELECT sf1();
2340 sf1()
2341 0
2342 SELECT * FROM t1 ORDER BY id;
2343 id data
2344 1 1
2345 2 1
2346 3 4
2347 4 5
2348 SELECT * FROM t2 ORDER BY i;
2349 i
2350 1
2351 2
2352 3
2353 SELECT * FROM t3 ORDER BY id;
2354 id data
2355 1 1
2356 2 1
2357 3 3
2358 INSERT INTO t4 VALUES(sf1());
2359 SELECT * FROM t1 ORDER BY id;
2360 id data
2361 1 1
2362 2 1
2363 3 4
2364 4 5
2365 SELECT * FROM t2 ORDER BY i;
2366 i
2367 1
2368 2
2369 3
2370 SELECT * FROM t3 ORDER BY id;
2371 id data
2372 1 1
2373 1 1
2374 2 1
2375 2 1
2376 3 3
2377 3 3
2378 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2379 SELECT * FROM t1 ORDER BY id;
2380 id data
2381 1 1
2382 2 1
2383 3 4
2384 4 5
2385 SELECT * FROM t2 ORDER BY i;
2386 i
2387 1
2388 2
2389 3
2390 SELECT * FROM t3 ORDER BY id;
2391 id data
2392 1 1
2393 1 1
2394 1 1
2395 1 1
2396 2 1
2397 2 1
2398 2 1
2399 2 1
2400 3 3
2401 3 3
2402 3 3
2403 3 3
2404 DELETE FROM t4 WHERE i = sf1() + 1;
2405 SELECT * FROM t1 ORDER BY id;
2406 id data
2407 1 1
2408 2 1
2409 3 4
2410 4 5
2411 SELECT * FROM t2 ORDER BY i;
2412 i
2413 1
2414 2
2415 3
2416 SELECT * FROM t3 ORDER BY id;
2417 id data
2418 1 1
2419 1 1
2420 1 1
2421 1 1
2422 1 1
2423 2 1
2424 2 1
2425 2 1
2426 2 1
2427 2 1
2428 3 3
2429 3 3
2430 3 3
2431 3 3
2432 3 3
2433 DROP FUNCTION sf1;
2434 DROP TABLE t1,t2,t3,t4;
2435 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2436 CREATE TABLE t2 (i INTEGER NULL);
2437 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2438 CREATE TABLE t4 (i INTEGER NULL);
2439 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2440 INSERT INTO t2 VALUES(1),(2),(3);
2441 CREATE FUNCTION sf1() RETURNS INTEGER
2442 BEGIN
2443 DECLARE done INT DEFAULT 0;
2444 DECLARE a CHAR(16);
2445 DECLARE b,c INT;
2446 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2447 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2448 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2449 OPEN cur1;
2450 OPEN cur2;
2451 REPEAT
2452 FETCH cur1 INTO a, b;
2453 FETCH cur2 INTO c;
2454 IF NOT done THEN
2455 IF b < c THEN
2456 INSERT INTO t3 VALUES (a,b);
2457 ELSE
2458 INSERT INTO t3 VALUES (a,c);
2459 END IF;
2460 END IF;
2461 UNTIL done END REPEAT;
2462 CLOSE cur1;
2463 CLOSE cur2;
2464 RETURN 0;
2465 END//
2466 ALTER FUNCTION sf1 LANGUAGE SQL #DET# MODIFIES SQL DATA COMMENT 'comment' ;
2467 SELECT sf1();
2468 sf1()
2469 0
2470 SELECT * FROM t1 ORDER BY id;
2471 id data
2472 1 1
2473 2 1
2474 3 4
2475 4 5
2476 SELECT * FROM t2 ORDER BY i;
2477 i
2478 1
2479 2
2480 3
2481 SELECT * FROM t3 ORDER BY id;
2482 id data
2483 1 1
2484 2 1
2485 3 3
2486 INSERT INTO t4 VALUES(sf1());
2487 SELECT * FROM t1 ORDER BY id;
2488 id data
2489 1 1
2490 2 1
2491 3 4
2492 4 5
2493 SELECT * FROM t2 ORDER BY i;
2494 i
2495 1
2496 2
2497 3
2498 SELECT * FROM t3 ORDER BY id;
2499 id data
2500 1 1
2501 1 1
2502 2 1
2503 2 1
2504 3 3
2505 3 3
2506 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2507 SELECT * FROM t1 ORDER BY id;
2508 id data
2509 1 1
2510 2 1
2511 3 4
2512 4 5
2513 SELECT * FROM t2 ORDER BY i;
2514 i
2515 1
2516 2
2517 3
2518 SELECT * FROM t3 ORDER BY id;
2519 id data
2520 1 1
2521 1 1
2522 1 1
2523 1 1
2524 2 1
2525 2 1
2526 2 1
2527 2 1
2528 3 3
2529 3 3
2530 3 3
2531 3 3
2532 DELETE FROM t4 WHERE i = sf1() + 1;
2533 SELECT * FROM t1 ORDER BY id;
2534 id data
2535 1 1
2536 2 1
2537 3 4
2538 4 5
2539 SELECT * FROM t2 ORDER BY i;
2540 i
2541 1
2542 2
2543 3
2544 SELECT * FROM t3 ORDER BY id;
2545 id data
2546 1 1
2547 1 1
2548 1 1
2549 1 1
2550 1 1
2551 2 1
2552 2 1
2553 2 1
2554 2 1
2555 2 1
2556 3 3
2557 3 3
2558 3 3
2559 3 3
2560 3 3
2561 DROP FUNCTION sf1;
2562 DROP TABLE t1,t2,t3,t4;
2563 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2564 CREATE TABLE t2 (i INTEGER NULL);
2565 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2566 CREATE TABLE t4 (i INTEGER NULL);
2567 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2568 INSERT INTO t2 VALUES(1),(2),(3);
2569 CREATE FUNCTION sf1() RETURNS INTEGER
2570 BEGIN
2571 DECLARE done INT DEFAULT 0;
2572 DECLARE a CHAR(16);
2573 DECLARE b,c INT;
2574 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2575 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2576 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2577 OPEN cur1;
2578 OPEN cur2;
2579 REPEAT
2580 FETCH cur1 INTO a, b;
2581 FETCH cur2 INTO c;
2582 IF NOT done THEN
2583 IF b < c THEN
2584 INSERT INTO t3 VALUES (a,b);
2585 ELSE
2586 INSERT INTO t3 VALUES (a,c);
2587 END IF;
2588 END IF;
2589 UNTIL done END REPEAT;
2590 CLOSE cur1;
2591 CLOSE cur2;
2592 RETURN 0;
2593 END//
2594 ALTER FUNCTION sf1 #DET# SQL SECURITY INVOKER ;
2595 SELECT sf1();
2596 sf1()
2597 0
2598 SELECT * FROM t1 ORDER BY id;
2599 id data
2600 1 1
2601 2 1
2602 3 4
2603 4 5
2604 SELECT * FROM t2 ORDER BY i;
2605 i
2606 1
2607 2
2608 3
2609 SELECT * FROM t3 ORDER BY id;
2610 id data
2611 1 1
2612 2 1
2613 3 3
2614 INSERT INTO t4 VALUES(sf1());
2615 SELECT * FROM t1 ORDER BY id;
2616 id data
2617 1 1
2618 2 1
2619 3 4
2620 4 5
2621 SELECT * FROM t2 ORDER BY i;
2622 i
2623 1
2624 2
2625 3
2626 SELECT * FROM t3 ORDER BY id;
2627 id data
2628 1 1
2629 1 1
2630 2 1
2631 2 1
2632 3 3
2633 3 3
2634 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2635 SELECT * FROM t1 ORDER BY id;
2636 id data
2637 1 1
2638 2 1
2639 3 4
2640 4 5
2641 SELECT * FROM t2 ORDER BY i;
2642 i
2643 1
2644 2
2645 3
2646 SELECT * FROM t3 ORDER BY id;
2647 id data
2648 1 1
2649 1 1
2650 1 1
2651 1 1
2652 2 1
2653 2 1
2654 2 1
2655 2 1
2656 3 3
2657 3 3
2658 3 3
2659 3 3
2660 DELETE FROM t4 WHERE i = sf1() + 1;
2661 SELECT * FROM t1 ORDER BY id;
2662 id data
2663 1 1
2664 2 1
2665 3 4
2666 4 5
2667 SELECT * FROM t2 ORDER BY i;
2668 i
2669 1
2670 2
2671 3
2672 SELECT * FROM t3 ORDER BY id;
2673 id data
2674 1 1
2675 1 1
2676 1 1
2677 1 1
2678 1 1
2679 2 1
2680 2 1
2681 2 1
2682 2 1
2683 2 1
2684 3 3
2685 3 3
2686 3 3
2687 3 3
2688 3 3
2689 DROP FUNCTION sf1;
2690 DROP TABLE t1,t2,t3,t4;
2691 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2692 CREATE TABLE t2 (i INTEGER NULL);
2693 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2694 CREATE TABLE t4 (i INTEGER NULL);
2695 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2696 INSERT INTO t2 VALUES(1),(2),(3);
2697 CREATE FUNCTION sf1() RETURNS INTEGER
2698 BEGIN
2699 DECLARE done INT DEFAULT 0;
2700 DECLARE a CHAR(16);
2701 DECLARE b,c INT;
2702 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2703 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2704 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2705 OPEN cur1;
2706 OPEN cur2;
2707 REPEAT
2708 FETCH cur1 INTO a, b;
2709 FETCH cur2 INTO c;
2710 IF NOT done THEN
2711 IF b < c THEN
2712 INSERT INTO t3 VALUES (a,b);
2713 ELSE
2714 INSERT INTO t3 VALUES (a,c);
2715 END IF;
2716 END IF;
2717 UNTIL done END REPEAT;
2718 CLOSE cur1;
2719 CLOSE cur2;
2720 RETURN 0;
2721 END//
2722 ALTER FUNCTION sf1 #DET# CONTAINS SQL SQL SECURITY INVOKER ;
2723 SELECT sf1();
2724 sf1()
2725 0
2726 SELECT * FROM t1 ORDER BY id;
2727 id data
2728 1 1
2729 2 1
2730 3 4
2731 4 5
2732 SELECT * FROM t2 ORDER BY i;
2733 i
2734 1
2735 2
2736 3
2737 SELECT * FROM t3 ORDER BY id;
2738 id data
2739 1 1
2740 2 1
2741 3 3
2742 INSERT INTO t4 VALUES(sf1());
2743 SELECT * FROM t1 ORDER BY id;
2744 id data
2745 1 1
2746 2 1
2747 3 4
2748 4 5
2749 SELECT * FROM t2 ORDER BY i;
2750 i
2751 1
2752 2
2753 3
2754 SELECT * FROM t3 ORDER BY id;
2755 id data
2756 1 1
2757 1 1
2758 2 1
2759 2 1
2760 3 3
2761 3 3
2762 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2763 SELECT * FROM t1 ORDER BY id;
2764 id data
2765 1 1
2766 2 1
2767 3 4
2768 4 5
2769 SELECT * FROM t2 ORDER BY i;
2770 i
2771 1
2772 2
2773 3
2774 SELECT * FROM t3 ORDER BY id;
2775 id data
2776 1 1
2777 1 1
2778 1 1
2779 1 1
2780 2 1
2781 2 1
2782 2 1
2783 2 1
2784 3 3
2785 3 3
2786 3 3
2787 3 3
2788 DELETE FROM t4 WHERE i = sf1() + 1;
2789 SELECT * FROM t1 ORDER BY id;
2790 id data
2791 1 1
2792 2 1
2793 3 4
2794 4 5
2795 SELECT * FROM t2 ORDER BY i;
2796 i
2797 1
2798 2
2799 3
2800 SELECT * FROM t3 ORDER BY id;
2801 id data
2802 1 1
2803 1 1
2804 1 1
2805 1 1
2806 1 1
2807 2 1
2808 2 1
2809 2 1
2810 2 1
2811 2 1
2812 3 3
2813 3 3
2814 3 3
2815 3 3
2816 3 3
2817 DROP FUNCTION sf1;
2818 DROP TABLE t1,t2,t3,t4;
2819 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2820 CREATE TABLE t2 (i INTEGER NULL);
2821 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2822 CREATE TABLE t4 (i INTEGER NULL);
2823 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2824 INSERT INTO t2 VALUES(1),(2),(3);
2825 CREATE FUNCTION sf1() RETURNS INTEGER
2826 BEGIN
2827 DECLARE done INT DEFAULT 0;
2828 DECLARE a CHAR(16);
2829 DECLARE b,c INT;
2830 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2831 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2832 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2833 OPEN cur1;
2834 OPEN cur2;
2835 REPEAT
2836 FETCH cur1 INTO a, b;
2837 FETCH cur2 INTO c;
2838 IF NOT done THEN
2839 IF b < c THEN
2840 INSERT INTO t3 VALUES (a,b);
2841 ELSE
2842 INSERT INTO t3 VALUES (a,c);
2843 END IF;
2844 END IF;
2845 UNTIL done END REPEAT;
2846 CLOSE cur1;
2847 CLOSE cur2;
2848 RETURN 0;
2849 END//
2850 ALTER FUNCTION sf1 #DET# NO SQL SQL SECURITY INVOKER ;
2851 SELECT sf1();
2852 sf1()
2853 0
2854 SELECT * FROM t1 ORDER BY id;
2855 id data
2856 1 1
2857 2 1
2858 3 4
2859 4 5
2860 SELECT * FROM t2 ORDER BY i;
2861 i
2862 1
2863 2
2864 3
2865 SELECT * FROM t3 ORDER BY id;
2866 id data
2867 1 1
2868 2 1
2869 3 3
2870 INSERT INTO t4 VALUES(sf1());
2871 SELECT * FROM t1 ORDER BY id;
2872 id data
2873 1 1
2874 2 1
2875 3 4
2876 4 5
2877 SELECT * FROM t2 ORDER BY i;
2878 i
2879 1
2880 2
2881 3
2882 SELECT * FROM t3 ORDER BY id;
2883 id data
2884 1 1
2885 1 1
2886 2 1
2887 2 1
2888 3 3
2889 3 3
2890 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2891 SELECT * FROM t1 ORDER BY id;
2892 id data
2893 1 1
2894 2 1
2895 3 4
2896 4 5
2897 SELECT * FROM t2 ORDER BY i;
2898 i
2899 1
2900 2
2901 3
2902 SELECT * FROM t3 ORDER BY id;
2903 id data
2904 1 1
2905 1 1
2906 1 1
2907 1 1
2908 2 1
2909 2 1
2910 2 1
2911 2 1
2912 3 3
2913 3 3
2914 3 3
2915 3 3
2916 DELETE FROM t4 WHERE i = sf1() + 1;
2917 SELECT * FROM t1 ORDER BY id;
2918 id data
2919 1 1
2920 2 1
2921 3 4
2922 4 5
2923 SELECT * FROM t2 ORDER BY i;
2924 i
2925 1
2926 2
2927 3
2928 SELECT * FROM t3 ORDER BY id;
2929 id data
2930 1 1
2931 1 1
2932 1 1
2933 1 1
2934 1 1
2935 2 1
2936 2 1
2937 2 1
2938 2 1
2939 2 1
2940 3 3
2941 3 3
2942 3 3
2943 3 3
2944 3 3
2945 DROP FUNCTION sf1;
2946 DROP TABLE t1,t2,t3,t4;
2947 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2948 CREATE TABLE t2 (i INTEGER NULL);
2949 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2950 CREATE TABLE t4 (i INTEGER NULL);
2951 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2952 INSERT INTO t2 VALUES(1),(2),(3);
2953 CREATE FUNCTION sf1() RETURNS INTEGER
2954 BEGIN
2955 DECLARE done INT DEFAULT 0;
2956 DECLARE a CHAR(16);
2957 DECLARE b,c INT;
2958 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2959 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2960 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2961 OPEN cur1;
2962 OPEN cur2;
2963 REPEAT
2964 FETCH cur1 INTO a, b;
2965 FETCH cur2 INTO c;
2966 IF NOT done THEN
2967 IF b < c THEN
2968 INSERT INTO t3 VALUES (a,b);
2969 ELSE
2970 INSERT INTO t3 VALUES (a,c);
2971 END IF;
2972 END IF;
2973 UNTIL done END REPEAT;
2974 CLOSE cur1;
2975 CLOSE cur2;
2976 RETURN 0;
2977 END//
2978 ALTER FUNCTION sf1 #DET# READS SQL DATA SQL SECURITY INVOKER ;
2979 SELECT sf1();
2980 sf1()
2981 0
2982 SELECT * FROM t1 ORDER BY id;
2983 id data
2984 1 1
2985 2 1
2986 3 4
2987 4 5
2988 SELECT * FROM t2 ORDER BY i;
2989 i
2990 1
2991 2
2992 3
2993 SELECT * FROM t3 ORDER BY id;
2994 id data
2995 1 1
2996 2 1
2997 3 3
2998 INSERT INTO t4 VALUES(sf1());
2999 SELECT * FROM t1 ORDER BY id;
3000 id data
3001 1 1
3002 2 1
3003 3 4
3004 4 5
3005 SELECT * FROM t2 ORDER BY i;
3006 i
3007 1
3008 2
3009 3
3010 SELECT * FROM t3 ORDER BY id;
3011 id data
3012 1 1
3013 1 1
3014 2 1
3015 2 1
3016 3 3
3017 3 3
3018 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
3019 SELECT * FROM t1 ORDER BY id;
3020 id data
3021 1 1
3022 2 1
3023 3 4
3024 4 5
3025 SELECT * FROM t2 ORDER BY i;
3026 i
3027 1
3028 2
3029 3
3030 SELECT * FROM t3 ORDER BY id;
3031 id data
3032 1 1
3033 1 1
3034 1 1
3035 1 1
3036 2 1
3037 2 1
3038 2 1
3039 2 1
3040 3 3
3041 3 3
3042 3 3
3043 3 3
3044 DELETE FROM t4 WHERE i = sf1() + 1;
3045 SELECT * FROM t1 ORDER BY id;
3046 id data
3047 1 1
3048 2 1
3049 3 4
3050 4 5
3051 SELECT * FROM t2 ORDER BY i;
3052 i
3053 1
3054 2
3055 3
3056 SELECT * FROM t3 ORDER BY id;
3057 id data
3058 1 1
3059 1 1
3060 1 1
3061 1 1
3062 1 1
3063 2 1
3064 2 1
3065 2 1
3066 2 1
3067 2 1
3068 3 3
3069 3 3
3070 3 3
3071 3 3
3072 3 3
3073 DROP FUNCTION sf1;
3074 DROP TABLE t1,t2,t3,t4;
3075 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
3076 CREATE TABLE t2 (i INTEGER NULL);
3077 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
3078 CREATE TABLE t4 (i INTEGER NULL);
3079 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
3080 INSERT INTO t2 VALUES(1),(2),(3);
3081 CREATE FUNCTION sf1() RETURNS INTEGER
3082 BEGIN
3083 DECLARE done INT DEFAULT 0;
3084 DECLARE a CHAR(16);
3085 DECLARE b,c INT;
3086 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
3087 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
3088 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
3089 OPEN cur1;
3090 OPEN cur2;
3091 REPEAT
3092 FETCH cur1 INTO a, b;
3093 FETCH cur2 INTO c;
3094 IF NOT done THEN
3095 IF b < c THEN
3096 INSERT INTO t3 VALUES (a,b);
3097 ELSE
3098 INSERT INTO t3 VALUES (a,c);
3099 END IF;
3100 END IF;
3101 UNTIL done END REPEAT;
3102 CLOSE cur1;
3103 CLOSE cur2;
3104 RETURN 0;
3105 END//
3106 ALTER FUNCTION sf1 #DET# MODIFIES SQL DATA SQL SECURITY INVOKER ;
3107 SELECT sf1();
3108 sf1()
3109 0
3110 SELECT * FROM t1 ORDER BY id;
3111 id data
3112 1 1
3113 2 1
3114 3 4
3115 4 5
3116 SELECT * FROM t2 ORDER BY i;
3117 i
3118 1
3119 2
3120 3
3121 SELECT * FROM t3 ORDER BY id;
3122 id data
3123 1 1
3124 2 1
3125 3 3
3126 INSERT INTO t4 VALUES(sf1());
3127 SELECT * FROM t1 ORDER BY id;
3128 id data
3129 1 1
3130 2 1
3131 3 4
3132 4 5
3133 SELECT * FROM t2 ORDER BY i;
3134 i
3135 1
3136 2
3137 3
3138 SELECT * FROM t3 ORDER BY id;
3139 id data
3140 1 1
3141 1 1
3142 2 1
3143 2 1
3144 3 3
3145 3 3
3146 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
3147 SELECT * FROM t1 ORDER BY id;
3148 id data
3149 1 1
3150 2 1
3151 3 4
3152 4 5
3153 SELECT * FROM t2 ORDER BY i;
3154 i
3155 1
3156 2
3157 3
3158 SELECT * FROM t3 ORDER BY id;
3159 id data
3160 1 1
3161 1 1
3162 1 1
3163 1 1
3164 2 1
3165 2 1
3166 2 1
3167 2 1
3168 3 3
3169 3 3
3170 3 3
3171 3 3
3172 DELETE FROM t4 WHERE i = sf1() + 1;
3173 SELECT * FROM t1 ORDER BY id;
3174 id data
3175 1 1
3176 2 1
3177 3 4
3178 4 5
3179 SELECT * FROM t2 ORDER BY i;
3180 i
3181 1
3182 2
3183 3
3184 SELECT * FROM t3 ORDER BY id;
3185 id data
3186 1 1
3187 1 1
3188 1 1
3189 1 1
3190 1 1
3191 2 1
3192 2 1
3193 2 1
3194 2 1
3195 2 1
3196 3 3
3197 3 3
3198 3 3
3199 3 3
3200 3 3
3201 DROP FUNCTION sf1;
3202 DROP TABLE t1,t2,t3,t4;
3203 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
3204 CREATE TABLE t2 (i INTEGER NULL);
3205 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
3206 CREATE TABLE t4 (i INTEGER NULL);
3207 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
3208 INSERT INTO t2 VALUES(1),(2),(3);
3209 CREATE FUNCTION sf1() RETURNS INTEGER
3210 BEGIN
3211 DECLARE done INT DEFAULT 0;
3212 DECLARE a CHAR(16);
3213 DECLARE b,c INT;
3214 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
3215 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
3216 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
3217 OPEN cur1;
3218 OPEN cur2;
3219 REPEAT
3220 FETCH cur1 INTO a, b;
3221 FETCH cur2 INTO c;
3222 IF NOT done THEN
3223 IF b < c THEN
3224 INSERT INTO t3 VALUES (a,b);
3225 ELSE
3226 INSERT INTO t3 VALUES (a,c);
3227 END IF;
3228 END IF;
3229 UNTIL done END REPEAT;
3230 CLOSE cur1;
3231 CLOSE cur2;
3232 RETURN 0;
3233 END//
3234 ALTER FUNCTION sf1 #DET# SQL SECURITY INVOKER COMMENT 'comment' ;
3235 SELECT sf1();
3236 sf1()
3237 0
3238 SELECT * FROM t1 ORDER BY id;
3239 id data
3240 1 1
3241 2 1
3242 3 4
3243 4 5
3244 SELECT * FROM t2 ORDER BY i;
3245 i
3246 1
3247 2
3248 3
3249 SELECT * FROM t3 ORDER BY id;
3250 id data
3251 1 1
3252 2 1
3253 3 3
3254 INSERT INTO t4 VALUES(sf1());
3255 SELECT * FROM t1 ORDER BY id;
3256 id data
3257 1 1
3258 2 1
3259 3 4
3260 4 5
3261 SELECT * FROM t2 ORDER BY i;
3262 i
3263 1
3264 2
3265 3
3266 SELECT * FROM t3 ORDER BY id;
3267 id data
3268 1 1
3269 1 1
3270 2 1
3271 2 1
3272 3 3
3273 3 3
3274 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
3275 SELECT * FROM t1 ORDER BY id;
3276 id data
3277 1 1
3278 2 1
3279 3 4
3280 4 5
3281 SELECT * FROM t2 ORDER BY i;
3282 i
3283 1
3284 2
3285 3
3286 SELECT * FROM t3 ORDER BY id;
3287 id data
3288 1 1
3289 1 1
3290 1 1
3291 1 1
3292 2 1
3293 2 1
3294 2 1
3295 2 1
3296 3 3
3297 3 3
3298 3 3
3299 3 3
3300 DELETE FROM t4 WHERE i = sf1() + 1;
3301 SELECT * FROM t1 ORDER BY id;
3302 id data
3303 1 1
3304 2 1
3305 3 4
3306 4 5
3307 SELECT * FROM t2 ORDER BY i;
3308 i
3309 1
3310 2
3311 3
3312 SELECT * FROM t3 ORDER BY id;
3313 id data
3314 1 1
3315 1 1
3316 1 1
3317 1 1
3318 1 1
3319 2 1
3320 2 1
3321 2 1
3322 2 1
3323 2 1
3324 3 3
3325 3 3
3326 3 3
3327 3 3
3328 3 3
3329 DROP FUNCTION sf1;
3330 DROP TABLE t1,t2,t3,t4;
3331 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
3332 CREATE TABLE t2 (i INTEGER NULL);
3333 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
3334 CREATE TABLE t4 (i INTEGER NULL);
3335 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
3336 INSERT INTO t2 VALUES(1),(2),(3);
3337 CREATE FUNCTION sf1() RETURNS INTEGER
3338 BEGIN
3339 DECLARE done INT DEFAULT 0;
3340 DECLARE a CHAR(16);
3341 DECLARE b,c INT;
3342 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
3343 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
3344 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
3345 OPEN cur1;
3346 OPEN cur2;
3347 REPEAT
3348 FETCH cur1 INTO a, b;
3349 FETCH cur2 INTO c;
3350 IF NOT done THEN
3351 IF b < c THEN
3352 INSERT INTO t3 VALUES (a,b);
3353 ELSE
3354 INSERT INTO t3 VALUES (a,c);
3355 END IF;
3356 END IF;
3357 UNTIL done END REPEAT;
3358 CLOSE cur1;
3359 CLOSE cur2;
3360 RETURN 0;
3361 END//
3362 ALTER FUNCTION sf1 #DET# CONTAINS SQL SQL SECURITY INVOKER COMMENT 'comment' ;
3363 SELECT sf1();
3364 sf1()
3365 0
3366 SELECT * FROM t1 ORDER BY id;
3367 id data
3368 1 1
3369 2 1
3370 3 4
3371 4 5
3372 SELECT * FROM t2 ORDER BY i;
3373 i
3374 1
3375 2
3376 3
3377 SELECT * FROM t3 ORDER BY id;
3378 id data
3379 1 1
3380 2 1
3381 3 3
3382 INSERT INTO t4 VALUES(sf1());
3383 SELECT * FROM t1 ORDER BY id;
3384 id data
3385 1 1
3386 2 1
3387 3 4
3388 4 5
3389 SELECT * FROM t2 ORDER BY i;
3390 i
3391 1
3392 2
3393 3
3394 SELECT * FROM t3 ORDER BY id;
3395 id data
3396 1 1
3397 1 1
3398 2 1
3399 2 1
3400 3 3
3401 3 3
3402 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
3403 SELECT * FROM t1 ORDER BY id;
3404 id data
3405 1 1
3406 2 1
3407 3 4
3408 4 5
3409 SELECT * FROM t2 ORDER BY i;
3410 i
3411 1
3412 2
3413 3
3414 SELECT * FROM t3 ORDER BY id;
3415 id data
3416 1 1
3417 1 1
3418 1 1
3419 1 1
3420 2 1
3421 2 1
3422 2 1
3423 2 1
3424 3 3
3425 3 3
3426 3 3
3427 3 3
3428 DELETE FROM t4 WHERE i = sf1() + 1;
3429 SELECT * FROM t1 ORDER BY id;
3430 id data
3431 1 1
3432 2 1
3433 3 4
3434 4 5
3435 SELECT * FROM t2 ORDER BY i;
3436 i
3437 1
3438 2
3439 3
3440 SELECT * FROM t3 ORDER BY id;
3441 id data
3442 1 1
3443 1 1
3444 1 1
3445 1 1
3446 1 1
3447 2 1
3448 2 1
3449 2 1
3450 2 1
3451 2 1
3452 3 3
3453 3 3
3454 3 3
3455 3 3
3456 3 3
3457 DROP FUNCTION sf1;
3458 DROP TABLE t1,t2,t3,t4;
3459 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
3460 CREATE TABLE t2 (i INTEGER NULL);
3461 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
3462 CREATE TABLE t4 (i INTEGER NULL);
3463 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
3464 INSERT INTO t2 VALUES(1),(2),(3);
3465 CREATE FUNCTION sf1() RETURNS INTEGER
3466 BEGIN
3467 DECLARE done INT DEFAULT 0;
3468 DECLARE a CHAR(16);
3469 DECLARE b,c INT;
3470 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
3471 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
3472 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
3473 OPEN cur1;
3474 OPEN cur2;
3475 REPEAT
3476 FETCH cur1 INTO a, b;
3477 FETCH cur2 INTO c;
3478 IF NOT done THEN
3479 IF b < c THEN
3480 INSERT INTO t3 VALUES (a,b);
3481 ELSE
3482 INSERT INTO t3 VALUES (a,c);
3483 END IF;
3484 END IF;
3485 UNTIL done END REPEAT;
3486 CLOSE cur1;
3487 CLOSE cur2;
3488 RETURN 0;
3489 END//
3490 ALTER FUNCTION sf1 #DET# NO SQL SQL SECURITY INVOKER COMMENT 'comment' ;
3491 SELECT sf1();
3492 sf1()
3493 0
3494 SELECT * FROM t1 ORDER BY id;
3495 id data
3496 1 1
3497 2 1
3498 3 4
3499 4 5
3500 SELECT * FROM t2 ORDER BY i;
3501 i
3502 1
3503 2
3504 3
3505 SELECT * FROM t3 ORDER BY id;
3506 id data
3507 1 1
3508 2 1
3509 3 3
3510 INSERT INTO t4 VALUES(sf1());
3511 SELECT * FROM t1 ORDER BY id;
3512 id data
3513 1 1
3514 2 1
3515 3 4
3516 4 5
3517 SELECT * FROM t2 ORDER BY i;
3518 i
3519 1
3520 2
3521 3
3522 SELECT * FROM t3 ORDER BY id;
3523 id data
3524 1 1
3525 1 1
3526 2 1
3527 2 1
3528 3 3
3529 3 3
3530 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
3531 SELECT * FROM t1 ORDER BY id;
3532 id data
3533 1 1
3534 2 1
3535 3 4
3536 4 5
3537 SELECT * FROM t2 ORDER BY i;
3538 i
3539 1
3540 2
3541 3
3542 SELECT * FROM t3 ORDER BY id;
3543 id data
3544 1 1
3545 1 1
3546 1 1
3547 1 1
3548 2 1
3549 2 1
3550 2 1
3551 2 1
3552 3 3
3553 3 3
3554 3 3
3555 3 3
3556 DELETE FROM t4 WHERE i = sf1() + 1;
3557 SELECT * FROM t1 ORDER BY id;
3558 id data
3559 1 1
3560 2 1
3561 3 4
3562 4 5
3563 SELECT * FROM t2 ORDER BY i;
3564 i
3565 1
3566 2
3567 3
3568 SELECT * FROM t3 ORDER BY id;
3569 id data
3570 1 1
3571 1 1
3572 1 1
3573 1 1
3574 1 1
3575 2 1
3576 2 1
3577 2 1
3578 2 1
3579 2 1
3580 3 3
3581 3 3
3582 3 3
3583 3 3
3584 3 3
3585 DROP FUNCTION sf1;
3586 DROP TABLE t1,t2,t3,t4;
3587 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
3588 CREATE TABLE t2 (i INTEGER NULL);
3589 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
3590 CREATE TABLE t4 (i INTEGER NULL);
3591 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
3592 INSERT INTO t2 VALUES(1),(2),(3);
3593 CREATE FUNCTION sf1() RETURNS INTEGER
3594 BEGIN
3595 DECLARE done INT DEFAULT 0;
3596 DECLARE a CHAR(16);
3597 DECLARE b,c INT;
3598 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
3599 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
3600 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
3601 OPEN cur1;
3602 OPEN cur2;
3603 REPEAT
3604 FETCH cur1 INTO a, b;
3605 FETCH cur2 INTO c;
3606 IF NOT done THEN
3607 IF b < c THEN
3608 INSERT INTO t3 VALUES (a,b);
3609 ELSE
3610 INSERT INTO t3 VALUES (a,c);
3611 END IF;
3612 END IF;
3613 UNTIL done END REPEAT;
3614 CLOSE cur1;
3615 CLOSE cur2;
3616 RETURN 0;
3617 END//
3618 ALTER FUNCTION sf1 #DET# READS SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
3619 SELECT sf1();
3620 sf1()
3621 0
3622 SELECT * FROM t1 ORDER BY id;
3623 id data
3624 1 1
3625 2 1
3626 3 4
3627 4 5
3628 SELECT * FROM t2 ORDER BY i;
3629 i
3630 1
3631 2
3632 3
3633 SELECT * FROM t3 ORDER BY id;
3634 id data
3635 1 1
3636 2 1
3637 3 3
3638 INSERT INTO t4 VALUES(sf1());
3639 SELECT * FROM t1 ORDER BY id;
3640 id data
3641 1 1
3642 2 1
3643 3 4
3644 4 5
3645 SELECT * FROM t2 ORDER BY i;
3646 i
3647 1
3648 2
3649 3
3650 SELECT * FROM t3 ORDER BY id;
3651 id data
3652 1 1
3653 1 1
3654 2 1
3655 2 1
3656 3 3
3657 3 3
3658 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
3659 SELECT * FROM t1 ORDER BY id;
3660 id data
3661 1 1
3662 2 1
3663 3 4
3664 4 5
3665 SELECT * FROM t2 ORDER BY i;
3666 i
3667 1
3668 2
3669 3
3670 SELECT * FROM t3 ORDER BY id;
3671 id data
3672 1 1
3673 1 1
3674 1 1
3675 1 1
3676 2 1
3677 2 1
3678 2 1
3679 2 1
3680 3 3
3681 3 3
3682 3 3
3683 3 3
3684 DELETE FROM t4 WHERE i = sf1() + 1;
3685 SELECT * FROM t1 ORDER BY id;
3686 id data
3687 1 1
3688 2 1
3689 3 4
3690 4 5
3691 SELECT * FROM t2 ORDER BY i;
3692 i
3693 1
3694 2
3695 3
3696 SELECT * FROM t3 ORDER BY id;
3697 id data
3698 1 1
3699 1 1
3700 1 1
3701 1 1
3702 1 1
3703 2 1
3704 2 1
3705 2 1
3706 2 1
3707 2 1
3708 3 3
3709 3 3
3710 3 3
3711 3 3
3712 3 3
3713 DROP FUNCTION sf1;
3714 DROP TABLE t1,t2,t3,t4;
3715 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
3716 CREATE TABLE t2 (i INTEGER NULL);
3717 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
3718 CREATE TABLE t4 (i INTEGER NULL);
3719 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
3720 INSERT INTO t2 VALUES(1),(2),(3);
3721 CREATE FUNCTION sf1() RETURNS INTEGER
3722 BEGIN
3723 DECLARE done INT DEFAULT 0;
3724 DECLARE a CHAR(16);
3725 DECLARE b,c INT;
3726 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
3727 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
3728 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
3729 OPEN cur1;
3730 OPEN cur2;
3731 REPEAT
3732 FETCH cur1 INTO a, b;
3733 FETCH cur2 INTO c;
3734 IF NOT done THEN
3735 IF b < c THEN
3736 INSERT INTO t3 VALUES (a,b);
3737 ELSE
3738 INSERT INTO t3 VALUES (a,c);
3739 END IF;
3740 END IF;
3741 UNTIL done END REPEAT;
3742 CLOSE cur1;
3743 CLOSE cur2;
3744 RETURN 0;
3745 END//
3746 ALTER FUNCTION sf1 #DET# MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
3747 SELECT sf1();
3748 sf1()
3749 0
3750 SELECT * FROM t1 ORDER BY id;
3751 id data
3752 1 1
3753 2 1
3754 3 4
3755 4 5
3756 SELECT * FROM t2 ORDER BY i;
3757 i
3758 1
3759 2
3760 3
3761 SELECT * FROM t3 ORDER BY id;
3762 id data
3763 1 1
3764 2 1
3765 3 3
3766 INSERT INTO t4 VALUES(sf1());
3767 SELECT * FROM t1 ORDER BY id;
3768 id data
3769 1 1
3770 2 1
3771 3 4
3772 4 5
3773 SELECT * FROM t2 ORDER BY i;
3774 i
3775 1
3776 2
3777 3
3778 SELECT * FROM t3 ORDER BY id;
3779 id data
3780 1 1
3781 1 1
3782 2 1
3783 2 1
3784 3 3
3785 3 3
3786 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
3787 SELECT * FROM t1 ORDER BY id;
3788 id data
3789 1 1
3790 2 1
3791 3 4
3792 4 5
3793 SELECT * FROM t2 ORDER BY i;
3794 i
3795 1
3796 2
3797 3
3798 SELECT * FROM t3 ORDER BY id;
3799 id data
3800 1 1
3801 1 1
3802 1 1
3803 1 1
3804 2 1
3805 2 1
3806 2 1
3807 2 1
3808 3 3
3809 3 3
3810 3 3
3811 3 3
3812 DELETE FROM t4 WHERE i = sf1() + 1;
3813 SELECT * FROM t1 ORDER BY id;
3814 id data
3815 1 1
3816 2 1
3817 3 4
3818 4 5
3819 SELECT * FROM t2 ORDER BY i;
3820 i
3821 1
3822 2
3823 3
3824 SELECT * FROM t3 ORDER BY id;
3825 id data
3826 1 1
3827 1 1
3828 1 1
3829 1 1
3830 1 1
3831 2 1
3832 2 1
3833 2 1
3834 2 1
3835 2 1
3836 3 3
3837 3 3
3838 3 3
3839 3 3
3840 3 3
3841 DROP FUNCTION sf1;
3842 DROP TABLE t1,t2,t3,t4;
3843 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
3844 CREATE TABLE t2 (i INTEGER NULL);
3845 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
3846 CREATE TABLE t4 (i INTEGER NULL);
3847 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
3848 INSERT INTO t2 VALUES(1),(2),(3);
3849 CREATE FUNCTION sf1() RETURNS INTEGER
3850 BEGIN
3851 DECLARE done INT DEFAULT 0;
3852 DECLARE a CHAR(16);
3853 DECLARE b,c INT;
3854 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
3855 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
3856 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
3857 OPEN cur1;
3858 OPEN cur2;
3859 REPEAT
3860 FETCH cur1 INTO a, b;
3861 FETCH cur2 INTO c;
3862 IF NOT done THEN
3863 IF b < c THEN
3864 INSERT INTO t3 VALUES (a,b);
3865 ELSE
3866 INSERT INTO t3 VALUES (a,c);
3867 END IF;
3868 END IF;
3869 UNTIL done END REPEAT;
3870 CLOSE cur1;
3871 CLOSE cur2;
3872 RETURN 0;
3873 END//
3874 ALTER FUNCTION sf1 LANGUAGE SQL #DET# SQL SECURITY INVOKER ;
3875 SELECT sf1();
3876 sf1()
3877 0
3878 SELECT * FROM t1 ORDER BY id;
3879 id data
3880 1 1
3881 2 1
3882 3 4
3883 4 5
3884 SELECT * FROM t2 ORDER BY i;
3885 i
3886 1
3887 2
3888 3
3889 SELECT * FROM t3 ORDER BY id;
3890 id data
3891 1 1
3892 2 1
3893 3 3
3894 INSERT INTO t4 VALUES(sf1());
3895 SELECT * FROM t1 ORDER BY id;
3896 id data
3897 1 1
3898 2 1
3899 3 4
3900 4 5
3901 SELECT * FROM t2 ORDER BY i;
3902 i
3903 1
3904 2
3905 3
3906 SELECT * FROM t3 ORDER BY id;
3907 id data
3908 1 1
3909 1 1
3910 2 1
3911 2 1
3912 3 3
3913 3 3
3914 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
3915 SELECT * FROM t1 ORDER BY id;
3916 id data
3917 1 1
3918 2 1
3919 3 4
3920 4 5
3921 SELECT * FROM t2 ORDER BY i;
3922 i
3923 1
3924 2
3925 3
3926 SELECT * FROM t3 ORDER BY id;
3927 id data
3928 1 1
3929 1 1
3930 1 1
3931 1 1
3932 2 1
3933 2 1
3934 2 1
3935 2 1
3936 3 3
3937 3 3
3938 3 3
3939 3 3
3940 DELETE FROM t4 WHERE i = sf1() + 1;
3941 SELECT * FROM t1 ORDER BY id;
3942 id data
3943 1 1
3944 2 1
3945 3 4
3946 4 5
3947 SELECT * FROM t2 ORDER BY i;
3948 i
3949 1
3950 2
3951 3
3952 SELECT * FROM t3 ORDER BY id;
3953 id data
3954 1 1
3955 1 1
3956 1 1
3957 1 1
3958 1 1
3959 2 1
3960 2 1
3961 2 1
3962 2 1
3963 2 1
3964 3 3
3965 3 3
3966 3 3
3967 3 3
3968 3 3
3969 DROP FUNCTION sf1;
3970 DROP TABLE t1,t2,t3,t4;
3971 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
3972 CREATE TABLE t2 (i INTEGER NULL);
3973 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
3974 CREATE TABLE t4 (i INTEGER NULL);
3975 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
3976 INSERT INTO t2 VALUES(1),(2),(3);
3977 CREATE FUNCTION sf1() RETURNS INTEGER
3978 BEGIN
3979 DECLARE done INT DEFAULT 0;
3980 DECLARE a CHAR(16);
3981 DECLARE b,c INT;
3982 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
3983 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
3984 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
3985 OPEN cur1;
3986 OPEN cur2;
3987 REPEAT
3988 FETCH cur1 INTO a, b;
3989 FETCH cur2 INTO c;
3990 IF NOT done THEN
3991 IF b < c THEN
3992 INSERT INTO t3 VALUES (a,b);
3993 ELSE
3994 INSERT INTO t3 VALUES (a,c);
3995 END IF;
3996 END IF;
3997 UNTIL done END REPEAT;
3998 CLOSE cur1;
3999 CLOSE cur2;
4000 RETURN 0;
4001 END//
4002 ALTER FUNCTION sf1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY INVOKER ;
4003 SELECT sf1();
4004 sf1()
4005 0
4006 SELECT * FROM t1 ORDER BY id;
4007 id data
4008 1 1
4009 2 1
4010 3 4
4011 4 5
4012 SELECT * FROM t2 ORDER BY i;
4013 i
4014 1
4015 2
4016 3
4017 SELECT * FROM t3 ORDER BY id;
4018 id data
4019 1 1
4020 2 1
4021 3 3
4022 INSERT INTO t4 VALUES(sf1());
4023 SELECT * FROM t1 ORDER BY id;
4024 id data
4025 1 1
4026 2 1
4027 3 4
4028 4 5
4029 SELECT * FROM t2 ORDER BY i;
4030 i
4031 1
4032 2
4033 3
4034 SELECT * FROM t3 ORDER BY id;
4035 id data
4036 1 1
4037 1 1
4038 2 1
4039 2 1
4040 3 3
4041 3 3
4042 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
4043 SELECT * FROM t1 ORDER BY id;
4044 id data
4045 1 1
4046 2 1
4047 3 4
4048 4 5
4049 SELECT * FROM t2 ORDER BY i;
4050 i
4051 1
4052 2
4053 3
4054 SELECT * FROM t3 ORDER BY id;
4055 id data
4056 1 1
4057 1 1
4058 1 1
4059 1 1
4060 2 1
4061 2 1
4062 2 1
4063 2 1
4064 3 3
4065 3 3
4066 3 3
4067 3 3
4068 DELETE FROM t4 WHERE i = sf1() + 1;
4069 SELECT * FROM t1 ORDER BY id;
4070 id data
4071 1 1
4072 2 1
4073 3 4
4074 4 5
4075 SELECT * FROM t2 ORDER BY i;
4076 i
4077 1
4078 2
4079 3
4080 SELECT * FROM t3 ORDER BY id;
4081 id data
4082 1 1
4083 1 1
4084 1 1
4085 1 1
4086 1 1
4087 2 1
4088 2 1
4089 2 1
4090 2 1
4091 2 1
4092 3 3
4093 3 3
4094 3 3
4095 3 3
4096 3 3
4097 DROP FUNCTION sf1;
4098 DROP TABLE t1,t2,t3,t4;
4099 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
4100 CREATE TABLE t2 (i INTEGER NULL);
4101 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
4102 CREATE TABLE t4 (i INTEGER NULL);
4103 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
4104 INSERT INTO t2 VALUES(1),(2),(3);
4105 CREATE FUNCTION sf1() RETURNS INTEGER
4106 BEGIN
4107 DECLARE done INT DEFAULT 0;
4108 DECLARE a CHAR(16);
4109 DECLARE b,c INT;
4110 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
4111 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
4112 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
4113 OPEN cur1;
4114 OPEN cur2;
4115 REPEAT
4116 FETCH cur1 INTO a, b;
4117 FETCH cur2 INTO c;
4118 IF NOT done THEN
4119 IF b < c THEN
4120 INSERT INTO t3 VALUES (a,b);
4121 ELSE
4122 INSERT INTO t3 VALUES (a,c);
4123 END IF;
4124 END IF;
4125 UNTIL done END REPEAT;
4126 CLOSE cur1;
4127 CLOSE cur2;
4128 RETURN 0;
4129 END//
4130 ALTER FUNCTION sf1 LANGUAGE SQL #DET# NO SQL SQL SECURITY INVOKER ;
4131 SELECT sf1();
4132 sf1()
4133 0
4134 SELECT * FROM t1 ORDER BY id;
4135 id data
4136 1 1
4137 2 1
4138 3 4
4139 4 5
4140 SELECT * FROM t2 ORDER BY i;
4141 i
4142 1
4143 2
4144 3
4145 SELECT * FROM t3 ORDER BY id;
4146 id data
4147 1 1
4148 2 1
4149 3 3
4150 INSERT INTO t4 VALUES(sf1());
4151 SELECT * FROM t1 ORDER BY id;
4152 id data
4153 1 1
4154 2 1
4155 3 4
4156 4 5
4157 SELECT * FROM t2 ORDER BY i;
4158 i
4159 1
4160 2
4161 3
4162 SELECT * FROM t3 ORDER BY id;
4163 id data
4164 1 1
4165 1 1
4166 2 1
4167 2 1
4168 3 3
4169 3 3
4170 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
4171 SELECT * FROM t1 ORDER BY id;
4172 id data
4173 1 1
4174 2 1
4175 3 4
4176 4 5
4177 SELECT * FROM t2 ORDER BY i;
4178 i
4179 1
4180 2
4181 3
4182 SELECT * FROM t3 ORDER BY id;
4183 id data
4184 1 1
4185 1 1
4186 1 1
4187 1 1
4188 2 1
4189 2 1
4190 2 1
4191 2 1
4192 3 3
4193 3 3
4194 3 3
4195 3 3
4196 DELETE FROM t4 WHERE i = sf1() + 1;
4197 SELECT * FROM t1 ORDER BY id;
4198 id data
4199 1 1
4200 2 1
4201 3 4
4202 4 5
4203 SELECT * FROM t2 ORDER BY i;
4204 i
4205 1
4206 2
4207 3
4208 SELECT * FROM t3 ORDER BY id;
4209 id data
4210 1 1
4211 1 1
4212 1 1
4213 1 1
4214 1 1
4215 2 1
4216 2 1
4217 2 1
4218 2 1
4219 2 1
4220 3 3
4221 3 3
4222 3 3
4223 3 3
4224 3 3
4225 DROP FUNCTION sf1;
4226 DROP TABLE t1,t2,t3,t4;
4227 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
4228 CREATE TABLE t2 (i INTEGER NULL);
4229 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
4230 CREATE TABLE t4 (i INTEGER NULL);
4231 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
4232 INSERT INTO t2 VALUES(1),(2),(3);
4233 CREATE FUNCTION sf1() RETURNS INTEGER
4234 BEGIN
4235 DECLARE done INT DEFAULT 0;
4236 DECLARE a CHAR(16);
4237 DECLARE b,c INT;
4238 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
4239 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
4240 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
4241 OPEN cur1;
4242 OPEN cur2;
4243 REPEAT
4244 FETCH cur1 INTO a, b;
4245 FETCH cur2 INTO c;
4246 IF NOT done THEN
4247 IF b < c THEN
4248 INSERT INTO t3 VALUES (a,b);
4249 ELSE
4250 INSERT INTO t3 VALUES (a,c);
4251 END IF;
4252 END IF;
4253 UNTIL done END REPEAT;
4254 CLOSE cur1;
4255 CLOSE cur2;
4256 RETURN 0;
4257 END//
4258 ALTER FUNCTION sf1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY INVOKER ;
4259 SELECT sf1();
4260 sf1()
4261 0
4262 SELECT * FROM t1 ORDER BY id;
4263 id data
4264 1 1
4265 2 1
4266 3 4
4267 4 5
4268 SELECT * FROM t2 ORDER BY i;
4269 i
4270 1
4271 2
4272 3
4273 SELECT * FROM t3 ORDER BY id;
4274 id data
4275 1 1
4276 2 1
4277 3 3
4278 INSERT INTO t4 VALUES(sf1());
4279 SELECT * FROM t1 ORDER BY id;
4280 id data
4281 1 1
4282 2 1
4283 3 4
4284 4 5
4285 SELECT * FROM t2 ORDER BY i;
4286 i
4287 1
4288 2
4289 3
4290 SELECT * FROM t3 ORDER BY id;
4291 id data
4292 1 1
4293 1 1
4294 2 1
4295 2 1
4296 3 3
4297 3 3
4298 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
4299 SELECT * FROM t1 ORDER BY id;
4300 id data
4301 1 1
4302 2 1
4303 3 4
4304 4 5
4305 SELECT * FROM t2 ORDER BY i;
4306 i
4307 1
4308 2
4309 3
4310 SELECT * FROM t3 ORDER BY id;
4311 id data
4312 1 1
4313 1 1
4314 1 1
4315 1 1
4316 2 1
4317 2 1
4318 2 1
4319 2 1
4320 3 3
4321 3 3
4322 3 3
4323 3 3
4324 DELETE FROM t4 WHERE i = sf1() + 1;
4325 SELECT * FROM t1 ORDER BY id;
4326 id data
4327 1 1
4328 2 1
4329 3 4
4330 4 5
4331 SELECT * FROM t2 ORDER BY i;
4332 i
4333 1
4334 2
4335 3
4336 SELECT * FROM t3 ORDER BY id;
4337 id data
4338 1 1
4339 1 1
4340 1 1
4341 1 1
4342 1 1
4343 2 1
4344 2 1
4345 2 1
4346 2 1
4347 2 1
4348 3 3
4349 3 3
4350 3 3
4351 3 3
4352 3 3
4353 DROP FUNCTION sf1;
4354 DROP TABLE t1,t2,t3,t4;
4355 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
4356 CREATE TABLE t2 (i INTEGER NULL);
4357 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
4358 CREATE TABLE t4 (i INTEGER NULL);
4359 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
4360 INSERT INTO t2 VALUES(1),(2),(3);
4361 CREATE FUNCTION sf1() RETURNS INTEGER
4362 BEGIN
4363 DECLARE done INT DEFAULT 0;
4364 DECLARE a CHAR(16);
4365 DECLARE b,c INT;
4366 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
4367 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
4368 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
4369 OPEN cur1;
4370 OPEN cur2;
4371 REPEAT
4372 FETCH cur1 INTO a, b;
4373 FETCH cur2 INTO c;
4374 IF NOT done THEN
4375 IF b < c THEN
4376 INSERT INTO t3 VALUES (a,b);
4377 ELSE
4378 INSERT INTO t3 VALUES (a,c);
4379 END IF;
4380 END IF;
4381 UNTIL done END REPEAT;
4382 CLOSE cur1;
4383 CLOSE cur2;
4384 RETURN 0;
4385 END//
4386 ALTER FUNCTION sf1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY INVOKER ;
4387 SELECT sf1();
4388 sf1()
4389 0
4390 SELECT * FROM t1 ORDER BY id;
4391 id data
4392 1 1
4393 2 1
4394 3 4
4395 4 5
4396 SELECT * FROM t2 ORDER BY i;
4397 i
4398 1
4399 2
4400 3
4401 SELECT * FROM t3 ORDER BY id;
4402 id data
4403 1 1
4404 2 1
4405 3 3
4406 INSERT INTO t4 VALUES(sf1());
4407 SELECT * FROM t1 ORDER BY id;
4408 id data
4409 1 1
4410 2 1
4411 3 4
4412 4 5
4413 SELECT * FROM t2 ORDER BY i;
4414 i
4415 1
4416 2
4417 3
4418 SELECT * FROM t3 ORDER BY id;
4419 id data
4420 1 1
4421 1 1
4422 2 1
4423 2 1
4424 3 3
4425 3 3
4426 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
4427 SELECT * FROM t1 ORDER BY id;
4428 id data
4429 1 1
4430 2 1
4431 3 4
4432 4 5
4433 SELECT * FROM t2 ORDER BY i;
4434 i
4435 1
4436 2
4437 3
4438 SELECT * FROM t3 ORDER BY id;
4439 id data
4440 1 1
4441 1 1
4442 1 1
4443 1 1
4444 2 1
4445 2 1
4446 2 1
4447 2 1
4448 3 3
4449 3 3
4450 3 3
4451 3 3
4452 DELETE FROM t4 WHERE i = sf1() + 1;
4453 SELECT * FROM t1 ORDER BY id;
4454 id data
4455 1 1
4456 2 1
4457 3 4
4458 4 5
4459 SELECT * FROM t2 ORDER BY i;
4460 i
4461 1
4462 2
4463 3
4464 SELECT * FROM t3 ORDER BY id;
4465 id data
4466 1 1
4467 1 1
4468 1 1
4469 1 1
4470 1 1
4471 2 1
4472 2 1
4473 2 1
4474 2 1
4475 2 1
4476 3 3
4477 3 3
4478 3 3
4479 3 3
4480 3 3
4481 DROP FUNCTION sf1;
4482 DROP TABLE t1,t2,t3,t4;
4483 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
4484 CREATE TABLE t2 (i INTEGER NULL);
4485 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
4486 CREATE TABLE t4 (i INTEGER NULL);
4487 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
4488 INSERT INTO t2 VALUES(1),(2),(3);
4489 CREATE FUNCTION sf1() RETURNS INTEGER
4490 BEGIN
4491 DECLARE done INT DEFAULT 0;
4492 DECLARE a CHAR(16);
4493 DECLARE b,c INT;
4494 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
4495 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
4496 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
4497 OPEN cur1;
4498 OPEN cur2;
4499 REPEAT
4500 FETCH cur1 INTO a, b;
4501 FETCH cur2 INTO c;
4502 IF NOT done THEN
4503 IF b < c THEN
4504 INSERT INTO t3 VALUES (a,b);
4505 ELSE
4506 INSERT INTO t3 VALUES (a,c);
4507 END IF;
4508 END IF;
4509 UNTIL done END REPEAT;
4510 CLOSE cur1;
4511 CLOSE cur2;
4512 RETURN 0;
4513 END//
4514 ALTER FUNCTION sf1 LANGUAGE SQL #DET# SQL SECURITY INVOKER COMMENT 'comment' ;
4515 SELECT sf1();
4516 sf1()
4517 0
4518 SELECT * FROM t1 ORDER BY id;
4519 id data
4520 1 1
4521 2 1
4522 3 4
4523 4 5
4524 SELECT * FROM t2 ORDER BY i;
4525 i
4526 1
4527 2
4528 3
4529 SELECT * FROM t3 ORDER BY id;
4530 id data
4531 1 1
4532 2 1
4533 3 3
4534 INSERT INTO t4 VALUES(sf1());
4535 SELECT * FROM t1 ORDER BY id;
4536 id data
4537 1 1
4538 2 1
4539 3 4
4540 4 5
4541 SELECT * FROM t2 ORDER BY i;
4542 i
4543 1
4544 2
4545 3
4546 SELECT * FROM t3 ORDER BY id;
4547 id data
4548 1 1
4549 1 1
4550 2 1
4551 2 1
4552 3 3
4553 3 3
4554 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
4555 SELECT * FROM t1 ORDER BY id;
4556 id data
4557 1 1
4558 2 1
4559 3 4
4560 4 5
4561 SELECT * FROM t2 ORDER BY i;
4562 i
4563 1
4564 2
4565 3
4566 SELECT * FROM t3 ORDER BY id;
4567 id data
4568 1 1
4569 1 1
4570 1 1
4571 1 1
4572 2 1
4573 2 1
4574 2 1
4575 2 1
4576 3 3
4577 3 3
4578 3 3
4579 3 3
4580 DELETE FROM t4 WHERE i = sf1() + 1;
4581 SELECT * FROM t1 ORDER BY id;
4582 id data
4583 1 1
4584 2 1
4585 3 4
4586 4 5
4587 SELECT * FROM t2 ORDER BY i;
4588 i
4589 1
4590 2
4591 3
4592 SELECT * FROM t3 ORDER BY id;
4593 id data
4594 1 1
4595 1 1
4596 1 1
4597 1 1
4598 1 1
4599 2 1
4600 2 1
4601 2 1
4602 2 1
4603 2 1
4604 3 3
4605 3 3
4606 3 3
4607 3 3
4608 3 3
4609 DROP FUNCTION sf1;
4610 DROP TABLE t1,t2,t3,t4;
4611 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
4612 CREATE TABLE t2 (i INTEGER NULL);
4613 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
4614 CREATE TABLE t4 (i INTEGER NULL);
4615 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
4616 INSERT INTO t2 VALUES(1),(2),(3);
4617 CREATE FUNCTION sf1() RETURNS INTEGER
4618 BEGIN
4619 DECLARE done INT DEFAULT 0;
4620 DECLARE a CHAR(16);
4621 DECLARE b,c INT;
4622 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
4623 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
4624 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
4625 OPEN cur1;
4626 OPEN cur2;
4627 REPEAT
4628 FETCH cur1 INTO a, b;
4629 FETCH cur2 INTO c;
4630 IF NOT done THEN
4631 IF b < c THEN
4632 INSERT INTO t3 VALUES (a,b);
4633 ELSE
4634 INSERT INTO t3 VALUES (a,c);
4635 END IF;
4636 END IF;
4637 UNTIL done END REPEAT;
4638 CLOSE cur1;
4639 CLOSE cur2;
4640 RETURN 0;
4641 END//
4642 ALTER FUNCTION sf1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY INVOKER COMMENT 'comment' ;
4643 SELECT sf1();
4644 sf1()
4645 0
4646 SELECT * FROM t1 ORDER BY id;
4647 id data
4648 1 1
4649 2 1
4650 3 4
4651 4 5
4652 SELECT * FROM t2 ORDER BY i;
4653 i
4654 1
4655 2
4656 3
4657 SELECT * FROM t3 ORDER BY id;
4658 id data
4659 1 1
4660 2 1
4661 3 3
4662 INSERT INTO t4 VALUES(sf1());
4663 SELECT * FROM t1 ORDER BY id;
4664 id data
4665 1 1
4666 2 1
4667 3 4
4668 4 5
4669 SELECT * FROM t2 ORDER BY i;
4670 i
4671 1
4672 2
4673 3
4674 SELECT * FROM t3 ORDER BY id;
4675 id data
4676 1 1
4677 1 1
4678 2 1
4679 2 1
4680 3 3
4681 3 3
4682 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
4683 SELECT * FROM t1 ORDER BY id;
4684 id data
4685 1 1
4686 2 1
4687 3 4
4688 4 5
4689 SELECT * FROM t2 ORDER BY i;
4690 i
4691 1
4692 2
4693 3
4694 SELECT * FROM t3 ORDER BY id;
4695 id data
4696 1 1
4697 1 1
4698 1 1
4699 1 1
4700 2 1
4701 2 1
4702 2 1
4703 2 1
4704 3 3
4705 3 3
4706 3 3
4707 3 3
4708 DELETE FROM t4 WHERE i = sf1() + 1;
4709 SELECT * FROM t1 ORDER BY id;
4710 id data
4711 1 1
4712 2 1
4713 3 4
4714 4 5
4715 SELECT * FROM t2 ORDER BY i;
4716 i
4717 1
4718 2
4719 3
4720 SELECT * FROM t3 ORDER BY id;
4721 id data
4722 1 1
4723 1 1
4724 1 1
4725 1 1
4726 1 1
4727 2 1
4728 2 1
4729 2 1
4730 2 1
4731 2 1
4732 3 3
4733 3 3
4734 3 3
4735 3 3
4736 3 3
4737 DROP FUNCTION sf1;
4738 DROP TABLE t1,t2,t3,t4;
4739 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
4740 CREATE TABLE t2 (i INTEGER NULL);
4741 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
4742 CREATE TABLE t4 (i INTEGER NULL);
4743 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
4744 INSERT INTO t2 VALUES(1),(2),(3);
4745 CREATE FUNCTION sf1() RETURNS INTEGER
4746 BEGIN
4747 DECLARE done INT DEFAULT 0;
4748 DECLARE a CHAR(16);
4749 DECLARE b,c INT;
4750 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
4751 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
4752 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
4753 OPEN cur1;
4754 OPEN cur2;
4755 REPEAT
4756 FETCH cur1 INTO a, b;
4757 FETCH cur2 INTO c;
4758 IF NOT done THEN
4759 IF b < c THEN
4760 INSERT INTO t3 VALUES (a,b);
4761 ELSE
4762 INSERT INTO t3 VALUES (a,c);
4763 END IF;
4764 END IF;
4765 UNTIL done END REPEAT;
4766 CLOSE cur1;
4767 CLOSE cur2;
4768 RETURN 0;
4769 END//
4770 ALTER FUNCTION sf1 LANGUAGE SQL #DET# NO SQL SQL SECURITY INVOKER COMMENT 'comment' ;
4771 SELECT sf1();
4772 sf1()
4773 0
4774 SELECT * FROM t1 ORDER BY id;
4775 id data
4776 1 1
4777 2 1
4778 3 4
4779 4 5
4780 SELECT * FROM t2 ORDER BY i;
4781 i
4782 1
4783 2
4784 3
4785 SELECT * FROM t3 ORDER BY id;
4786 id data
4787 1 1
4788 2 1
4789 3 3
4790 INSERT INTO t4 VALUES(sf1());
4791 SELECT * FROM t1 ORDER BY id;
4792 id data
4793 1 1
4794 2 1
4795 3 4
4796 4 5
4797 SELECT * FROM t2 ORDER BY i;
4798 i
4799 1
4800 2
4801 3
4802 SELECT * FROM t3 ORDER BY id;
4803 id data
4804 1 1
4805 1 1
4806 2 1
4807 2 1
4808 3 3
4809 3 3
4810 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
4811 SELECT * FROM t1 ORDER BY id;
4812 id data
4813 1 1
4814 2 1
4815 3 4
4816 4 5
4817 SELECT * FROM t2 ORDER BY i;
4818 i
4819 1
4820 2
4821 3
4822 SELECT * FROM t3 ORDER BY id;
4823 id data
4824 1 1
4825 1 1
4826 1 1
4827 1 1
4828 2 1
4829 2 1
4830 2 1
4831 2 1
4832 3 3
4833 3 3
4834 3 3
4835 3 3
4836 DELETE FROM t4 WHERE i = sf1() + 1;
4837 SELECT * FROM t1 ORDER BY id;
4838 id data
4839 1 1
4840 2 1
4841 3 4
4842 4 5
4843 SELECT * FROM t2 ORDER BY i;
4844 i
4845 1
4846 2
4847 3
4848 SELECT * FROM t3 ORDER BY id;
4849 id data
4850 1 1
4851 1 1
4852 1 1
4853 1 1
4854 1 1
4855 2 1
4856 2 1
4857 2 1
4858 2 1
4859 2 1
4860 3 3
4861 3 3
4862 3 3
4863 3 3
4864 3 3
4865 DROP FUNCTION sf1;
4866 DROP TABLE t1,t2,t3,t4;
4867 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
4868 CREATE TABLE t2 (i INTEGER NULL);
4869 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
4870 CREATE TABLE t4 (i INTEGER NULL);
4871 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
4872 INSERT INTO t2 VALUES(1),(2),(3);
4873 CREATE FUNCTION sf1() RETURNS INTEGER
4874 BEGIN
4875 DECLARE done INT DEFAULT 0;
4876 DECLARE a CHAR(16);
4877 DECLARE b,c INT;
4878 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
4879 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
4880 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
4881 OPEN cur1;
4882 OPEN cur2;
4883 REPEAT
4884 FETCH cur1 INTO a, b;
4885 FETCH cur2 INTO c;
4886 IF NOT done THEN
4887 IF b < c THEN
4888 INSERT INTO t3 VALUES (a,b);
4889 ELSE
4890 INSERT INTO t3 VALUES (a,c);
4891 END IF;
4892 END IF;
4893 UNTIL done END REPEAT;
4894 CLOSE cur1;
4895 CLOSE cur2;
4896 RETURN 0;
4897 END//
4898 ALTER FUNCTION sf1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
4899 SELECT sf1();
4900 sf1()
4901 0
4902 SELECT * FROM t1 ORDER BY id;
4903 id data
4904 1 1
4905 2 1
4906 3 4
4907 4 5
4908 SELECT * FROM t2 ORDER BY i;
4909 i
4910 1
4911 2
4912 3
4913 SELECT * FROM t3 ORDER BY id;
4914 id data
4915 1 1
4916 2 1
4917 3 3
4918 INSERT INTO t4 VALUES(sf1());
4919 SELECT * FROM t1 ORDER BY id;
4920 id data
4921 1 1
4922 2 1
4923 3 4
4924 4 5
4925 SELECT * FROM t2 ORDER BY i;
4926 i
4927 1
4928 2
4929 3
4930 SELECT * FROM t3 ORDER BY id;
4931 id data
4932 1 1
4933 1 1
4934 2 1
4935 2 1
4936 3 3
4937 3 3
4938 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
4939 SELECT * FROM t1 ORDER BY id;
4940 id data
4941 1 1
4942 2 1
4943 3 4
4944 4 5
4945 SELECT * FROM t2 ORDER BY i;
4946 i
4947 1
4948 2
4949 3
4950 SELECT * FROM t3 ORDER BY id;
4951 id data
4952 1 1
4953 1 1
4954 1 1
4955 1 1
4956 2 1
4957 2 1
4958 2 1
4959 2 1
4960 3 3
4961 3 3
4962 3 3
4963 3 3
4964 DELETE FROM t4 WHERE i = sf1() + 1;
4965 SELECT * FROM t1 ORDER BY id;
4966 id data
4967 1 1
4968 2 1
4969 3 4
4970 4 5
4971 SELECT * FROM t2 ORDER BY i;
4972 i
4973 1
4974 2
4975 3
4976 SELECT * FROM t3 ORDER BY id;
4977 id data
4978 1 1
4979 1 1
4980 1 1
4981 1 1
4982 1 1
4983 2 1
4984 2 1
4985 2 1
4986 2 1
4987 2 1
4988 3 3
4989 3 3
4990 3 3
4991 3 3
4992 3 3
4993 DROP FUNCTION sf1;
4994 DROP TABLE t1,t2,t3,t4;
4995 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
4996 CREATE TABLE t2 (i INTEGER NULL);
4997 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
4998 CREATE TABLE t4 (i INTEGER NULL);
4999 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
5000 INSERT INTO t2 VALUES(1),(2),(3);
5001 CREATE FUNCTION sf1() RETURNS INTEGER
5002 BEGIN
5003 DECLARE done INT DEFAULT 0;
5004 DECLARE a CHAR(16);
5005 DECLARE b,c INT;
5006 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
5007 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
5008 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
5009 OPEN cur1;
5010 OPEN cur2;
5011 REPEAT
5012 FETCH cur1 INTO a, b;
5013 FETCH cur2 INTO c;
5014 IF NOT done THEN
5015 IF b < c THEN
5016 INSERT INTO t3 VALUES (a,b);
5017 ELSE
5018 INSERT INTO t3 VALUES (a,c);
5019 END IF;
5020 END IF;
5021 UNTIL done END REPEAT;
5022 CLOSE cur1;
5023 CLOSE cur2;
5024 RETURN 0;
5025 END//
5026 ALTER FUNCTION sf1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
5027 SELECT sf1();
5028 sf1()
5029 0
5030 SELECT * FROM t1 ORDER BY id;
5031 id data
5032 1 1
5033 2 1
5034 3 4
5035 4 5
5036 SELECT * FROM t2 ORDER BY i;
5037 i
5038 1
5039 2
5040 3
5041 SELECT * FROM t3 ORDER BY id;
5042 id data
5043 1 1
5044 2 1
5045 3 3
5046 INSERT INTO t4 VALUES(sf1());
5047 SELECT * FROM t1 ORDER BY id;
5048 id data
5049 1 1
5050 2 1
5051 3 4
5052 4 5
5053 SELECT * FROM t2 ORDER BY i;
5054 i
5055 1
5056 2
5057 3
5058 SELECT * FROM t3 ORDER BY id;
5059 id data
5060 1 1
5061 1 1
5062 2 1
5063 2 1
5064 3 3
5065 3 3
5066 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
5067 SELECT * FROM t1 ORDER BY id;
5068 id data
5069 1 1
5070 2 1
5071 3 4
5072 4 5
5073 SELECT * FROM t2 ORDER BY i;
5074 i
5075 1
5076 2
5077 3
5078 SELECT * FROM t3 ORDER BY id;
5079 id data
5080 1 1
5081 1 1
5082 1 1
5083 1 1
5084 2 1
5085 2 1
5086 2 1
5087 2 1
5088 3 3
5089 3 3
5090 3 3
5091 3 3
5092 DELETE FROM t4 WHERE i = sf1() + 1;
5093 SELECT * FROM t1 ORDER BY id;
5094 id data
5095 1 1
5096 2 1
5097 3 4
5098 4 5
5099 SELECT * FROM t2 ORDER BY i;
5100 i
5101 1
5102 2
5103 3
5104 SELECT * FROM t3 ORDER BY id;
5105 id data
5106 1 1
5107 1 1
5108 1 1
5109 1 1
5110 1 1
5111 2 1
5112 2 1
5113 2 1
5114 2 1
5115 2 1
5116 3 3
5117 3 3
5118 3 3
5119 3 3
5120 3 3
5121 DROP FUNCTION sf1;
5122 DROP TABLE t1,t2,t3,t4;
5123 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
5124 CREATE TABLE t2 (i INTEGER NULL);
5125 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
5126 CREATE TABLE t4 (i INTEGER NULL);
5127 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
5128 INSERT INTO t2 VALUES(1),(2),(3);
5129 CREATE FUNCTION sf1() RETURNS INTEGER
5130 BEGIN
5131 DECLARE done INT DEFAULT 0;
5132 DECLARE a CHAR(16);
5133 DECLARE b,c INT;
5134 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
5135 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
5136 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
5137 OPEN cur1;
5138 OPEN cur2;
5139 REPEAT
5140 FETCH cur1 INTO a, b;
5141 FETCH cur2 INTO c;
5142 IF NOT done THEN
5143 IF b < c THEN
5144 INSERT INTO t3 VALUES (a,b);
5145 ELSE
5146 INSERT INTO t3 VALUES (a,c);
5147 END IF;
5148 END IF;
5149 UNTIL done END REPEAT;
5150 CLOSE cur1;
5151 CLOSE cur2;
5152 RETURN 0;
5153 END//
5154 ALTER FUNCTION sf1 #DET# SQL SECURITY DEFINER ;
5155 SELECT sf1();
5156 sf1()
5157 0
5158 SELECT * FROM t1 ORDER BY id;
5159 id data
5160 1 1
5161 2 1
5162 3 4
5163 4 5
5164 SELECT * FROM t2 ORDER BY i;
5165 i
5166 1
5167 2
5168 3
5169 SELECT * FROM t3 ORDER BY id;
5170 id data
5171 1 1
5172 2 1
5173 3 3
5174 INSERT INTO t4 VALUES(sf1());
5175 SELECT * FROM t1 ORDER BY id;
5176 id data
5177 1 1
5178 2 1
5179 3 4
5180 4 5
5181 SELECT * FROM t2 ORDER BY i;
5182 i
5183 1
5184 2
5185 3
5186 SELECT * FROM t3 ORDER BY id;
5187 id data
5188 1 1
5189 1 1
5190 2 1
5191 2 1
5192 3 3
5193 3 3
5194 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
5195 SELECT * FROM t1 ORDER BY id;
5196 id data
5197 1 1
5198 2 1
5199 3 4
5200 4 5
5201 SELECT * FROM t2 ORDER BY i;
5202 i
5203 1
5204 2
5205 3
5206 SELECT * FROM t3 ORDER BY id;
5207 id data
5208 1 1
5209 1 1
5210 1 1
5211 1 1
5212 2 1
5213 2 1
5214 2 1
5215 2 1
5216 3 3
5217 3 3
5218 3 3
5219 3 3
5220 DELETE FROM t4 WHERE i = sf1() + 1;
5221 SELECT * FROM t1 ORDER BY id;
5222 id data
5223 1 1
5224 2 1
5225 3 4
5226 4 5
5227 SELECT * FROM t2 ORDER BY i;
5228 i
5229 1
5230 2
5231 3
5232 SELECT * FROM t3 ORDER BY id;
5233 id data
5234 1 1
5235 1 1
5236 1 1
5237 1 1
5238 1 1
5239 2 1
5240 2 1
5241 2 1
5242 2 1
5243 2 1
5244 3 3
5245 3 3
5246 3 3
5247 3 3
5248 3 3
5249 DROP FUNCTION sf1;
5250 DROP TABLE t1,t2,t3,t4;
5251 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
5252 CREATE TABLE t2 (i INTEGER NULL);
5253 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
5254 CREATE TABLE t4 (i INTEGER NULL);
5255 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
5256 INSERT INTO t2 VALUES(1),(2),(3);
5257 CREATE FUNCTION sf1() RETURNS INTEGER
5258 BEGIN
5259 DECLARE done INT DEFAULT 0;
5260 DECLARE a CHAR(16);
5261 DECLARE b,c INT;
5262 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
5263 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
5264 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
5265 OPEN cur1;
5266 OPEN cur2;
5267 REPEAT
5268 FETCH cur1 INTO a, b;
5269 FETCH cur2 INTO c;
5270 IF NOT done THEN
5271 IF b < c THEN
5272 INSERT INTO t3 VALUES (a,b);
5273 ELSE
5274 INSERT INTO t3 VALUES (a,c);
5275 END IF;
5276 END IF;
5277 UNTIL done END REPEAT;
5278 CLOSE cur1;
5279 CLOSE cur2;
5280 RETURN 0;
5281 END//
5282 ALTER FUNCTION sf1 #DET# CONTAINS SQL SQL SECURITY DEFINER ;
5283 SELECT sf1();
5284 sf1()
5285 0
5286 SELECT * FROM t1 ORDER BY id;
5287 id data
5288 1 1
5289 2 1
5290 3 4
5291 4 5
5292 SELECT * FROM t2 ORDER BY i;
5293 i
5294 1
5295 2
5296 3
5297 SELECT * FROM t3 ORDER BY id;
5298 id data
5299 1 1
5300 2 1
5301 3 3
5302 INSERT INTO t4 VALUES(sf1());
5303 SELECT * FROM t1 ORDER BY id;
5304 id data
5305 1 1
5306 2 1
5307 3 4
5308 4 5
5309 SELECT * FROM t2 ORDER BY i;
5310 i
5311 1
5312 2
5313 3
5314 SELECT * FROM t3 ORDER BY id;
5315 id data
5316 1 1
5317 1 1
5318 2 1
5319 2 1
5320 3 3
5321 3 3
5322 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
5323 SELECT * FROM t1 ORDER BY id;
5324 id data
5325 1 1
5326 2 1
5327 3 4
5328 4 5
5329 SELECT * FROM t2 ORDER BY i;
5330 i
5331 1
5332 2
5333 3
5334 SELECT * FROM t3 ORDER BY id;
5335 id data
5336 1 1
5337 1 1
5338 1 1
5339 1 1
5340 2 1
5341 2 1
5342 2 1
5343 2 1
5344 3 3
5345 3 3
5346 3 3
5347 3 3
5348 DELETE FROM t4 WHERE i = sf1() + 1;
5349 SELECT * FROM t1 ORDER BY id;
5350 id data
5351 1 1
5352 2 1
5353 3 4
5354 4 5
5355 SELECT * FROM t2 ORDER BY i;
5356 i
5357 1
5358 2
5359 3
5360 SELECT * FROM t3 ORDER BY id;
5361 id data
5362 1 1
5363 1 1
5364 1 1
5365 1 1
5366 1 1
5367 2 1
5368 2 1
5369 2 1
5370 2 1
5371 2 1
5372 3 3
5373 3 3
5374 3 3
5375 3 3
5376 3 3
5377 DROP FUNCTION sf1;
5378 DROP TABLE t1,t2,t3,t4;
5379 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
5380 CREATE TABLE t2 (i INTEGER NULL);
5381 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
5382 CREATE TABLE t4 (i INTEGER NULL);
5383 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
5384 INSERT INTO t2 VALUES(1),(2),(3);
5385 CREATE FUNCTION sf1() RETURNS INTEGER
5386 BEGIN
5387 DECLARE done INT DEFAULT 0;
5388 DECLARE a CHAR(16);
5389 DECLARE b,c INT;
5390 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
5391 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
5392 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
5393 OPEN cur1;
5394 OPEN cur2;
5395 REPEAT
5396 FETCH cur1 INTO a, b;
5397 FETCH cur2 INTO c;
5398 IF NOT done THEN
5399 IF b < c THEN
5400 INSERT INTO t3 VALUES (a,b);
5401 ELSE
5402 INSERT INTO t3 VALUES (a,c);
5403 END IF;
5404 END IF;
5405 UNTIL done END REPEAT;
5406 CLOSE cur1;
5407 CLOSE cur2;
5408 RETURN 0;
5409 END//
5410 ALTER FUNCTION sf1 #DET# NO SQL SQL SECURITY DEFINER ;
5411 SELECT sf1();
5412 sf1()
5413 0
5414 SELECT * FROM t1 ORDER BY id;
5415 id data
5416 1 1
5417 2 1
5418 3 4
5419 4 5
5420 SELECT * FROM t2 ORDER BY i;
5421 i
5422 1
5423 2
5424 3
5425 SELECT * FROM t3 ORDER BY id;
5426 id data
5427 1 1
5428 2 1
5429 3 3
5430 INSERT INTO t4 VALUES(sf1());
5431 SELECT * FROM t1 ORDER BY id;
5432 id data
5433 1 1
5434 2 1
5435 3 4
5436 4 5
5437 SELECT * FROM t2 ORDER BY i;
5438 i
5439 1
5440 2
5441 3
5442 SELECT * FROM t3 ORDER BY id;
5443 id data
5444 1 1
5445 1 1
5446 2 1
5447 2 1
5448 3 3
5449 3 3
5450 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
5451 SELECT * FROM t1 ORDER BY id;
5452 id data
5453 1 1
5454 2 1
5455 3 4
5456 4 5
5457 SELECT * FROM t2 ORDER BY i;
5458 i
5459 1
5460 2
5461 3
5462 SELECT * FROM t3 ORDER BY id;
5463 id data
5464 1 1
5465 1 1
5466 1 1
5467 1 1
5468 2 1
5469 2 1
5470 2 1
5471 2 1
5472 3 3
5473 3 3
5474 3 3
5475 3 3
5476 DELETE FROM t4 WHERE i = sf1() + 1;
5477 SELECT * FROM t1 ORDER BY id;
5478 id data
5479 1 1
5480 2 1
5481 3 4
5482 4 5
5483 SELECT * FROM t2 ORDER BY i;
5484 i
5485 1
5486 2
5487 3
5488 SELECT * FROM t3 ORDER BY id;
5489 id data
5490 1 1
5491 1 1
5492 1 1
5493 1 1
5494 1 1
5495 2 1
5496 2 1
5497 2 1
5498 2 1
5499 2 1
5500 3 3
5501 3 3
5502 3 3
5503 3 3
5504 3 3
5505 DROP FUNCTION sf1;
5506 DROP TABLE t1,t2,t3,t4;
5507 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
5508 CREATE TABLE t2 (i INTEGER NULL);
5509 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
5510 CREATE TABLE t4 (i INTEGER NULL);
5511 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
5512 INSERT INTO t2 VALUES(1),(2),(3);
5513 CREATE FUNCTION sf1() RETURNS INTEGER
5514 BEGIN
5515 DECLARE done INT DEFAULT 0;
5516 DECLARE a CHAR(16);
5517 DECLARE b,c INT;
5518 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
5519 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
5520 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
5521 OPEN cur1;
5522 OPEN cur2;
5523 REPEAT
5524 FETCH cur1 INTO a, b;
5525 FETCH cur2 INTO c;
5526 IF NOT done THEN
5527 IF b < c THEN
5528 INSERT INTO t3 VALUES (a,b);
5529 ELSE
5530 INSERT INTO t3 VALUES (a,c);
5531 END IF;
5532 END IF;
5533 UNTIL done END REPEAT;
5534 CLOSE cur1;
5535 CLOSE cur2;
5536 RETURN 0;
5537 END//
5538 ALTER FUNCTION sf1 #DET# READS SQL DATA SQL SECURITY DEFINER ;
5539 SELECT sf1();
5540 sf1()
5541 0
5542 SELECT * FROM t1 ORDER BY id;
5543 id data
5544 1 1
5545 2 1
5546 3 4
5547 4 5
5548 SELECT * FROM t2 ORDER BY i;
5549 i
5550 1
5551 2
5552 3
5553 SELECT * FROM t3 ORDER BY id;
5554 id data
5555 1 1
5556 2 1
5557 3 3
5558 INSERT INTO t4 VALUES(sf1());
5559 SELECT * FROM t1 ORDER BY id;
5560 id data
5561 1 1
5562 2 1
5563 3 4
5564 4 5
5565 SELECT * FROM t2 ORDER BY i;
5566 i
5567 1
5568 2
5569 3
5570 SELECT * FROM t3 ORDER BY id;
5571 id data
5572 1 1
5573 1 1
5574 2 1
5575 2 1
5576 3 3
5577 3 3
5578 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
5579 SELECT * FROM t1 ORDER BY id;
5580 id data
5581 1 1
5582 2 1
5583 3 4
5584 4 5
5585 SELECT * FROM t2 ORDER BY i;
5586 i
5587 1
5588 2
5589 3
5590 SELECT * FROM t3 ORDER BY id;
5591 id data
5592 1 1
5593 1 1
5594 1 1
5595 1 1
5596 2 1
5597 2 1
5598 2 1
5599 2 1
5600 3 3
5601 3 3
5602 3 3
5603 3 3
5604 DELETE FROM t4 WHERE i = sf1() + 1;
5605 SELECT * FROM t1 ORDER BY id;
5606 id data
5607 1 1
5608 2 1
5609 3 4
5610 4 5
5611 SELECT * FROM t2 ORDER BY i;
5612 i
5613 1
5614 2
5615 3
5616 SELECT * FROM t3 ORDER BY id;
5617 id data
5618 1 1
5619 1 1
5620 1 1
5621 1 1
5622 1 1
5623 2 1
5624 2 1
5625 2 1
5626 2 1
5627 2 1
5628 3 3
5629 3 3
5630 3 3
5631 3 3
5632 3 3
5633 DROP FUNCTION sf1;
5634 DROP TABLE t1,t2,t3,t4;
5635 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
5636 CREATE TABLE t2 (i INTEGER NULL);
5637 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
5638 CREATE TABLE t4 (i INTEGER NULL);
5639 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
5640 INSERT INTO t2 VALUES(1),(2),(3);
5641 CREATE FUNCTION sf1() RETURNS INTEGER
5642 BEGIN
5643 DECLARE done INT DEFAULT 0;
5644 DECLARE a CHAR(16);
5645 DECLARE b,c INT;
5646 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
5647 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
5648 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
5649 OPEN cur1;
5650 OPEN cur2;
5651 REPEAT
5652 FETCH cur1 INTO a, b;
5653 FETCH cur2 INTO c;
5654 IF NOT done THEN
5655 IF b < c THEN
5656 INSERT INTO t3 VALUES (a,b);
5657 ELSE
5658 INSERT INTO t3 VALUES (a,c);
5659 END IF;
5660 END IF;
5661 UNTIL done END REPEAT;
5662 CLOSE cur1;
5663 CLOSE cur2;
5664 RETURN 0;
5665 END//
5666 ALTER FUNCTION sf1 #DET# MODIFIES SQL DATA SQL SECURITY DEFINER ;
5667 SELECT sf1();
5668 sf1()
5669 0
5670 SELECT * FROM t1 ORDER BY id;
5671 id data
5672 1 1
5673 2 1
5674 3 4
5675 4 5
5676 SELECT * FROM t2 ORDER BY i;
5677 i
5678 1
5679 2
5680 3
5681 SELECT * FROM t3 ORDER BY id;
5682 id data
5683 1 1
5684 2 1
5685 3 3
5686 INSERT INTO t4 VALUES(sf1());
5687 SELECT * FROM t1 ORDER BY id;
5688 id data
5689 1 1
5690 2 1
5691 3 4
5692 4 5
5693 SELECT * FROM t2 ORDER BY i;
5694 i
5695 1
5696 2
5697 3
5698 SELECT * FROM t3 ORDER BY id;
5699 id data
5700 1 1
5701 1 1
5702 2 1
5703 2 1
5704 3 3
5705 3 3
5706 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
5707 SELECT * FROM t1 ORDER BY id;
5708 id data
5709 1 1
5710 2 1
5711 3 4
5712 4 5
5713 SELECT * FROM t2 ORDER BY i;
5714 i
5715 1
5716 2
5717 3
5718 SELECT * FROM t3 ORDER BY id;
5719 id data
5720 1 1
5721 1 1
5722 1 1
5723 1 1
5724 2 1
5725 2 1
5726 2 1
5727 2 1
5728 3 3
5729 3 3
5730 3 3
5731 3 3
5732 DELETE FROM t4 WHERE i = sf1() + 1;
5733 SELECT * FROM t1 ORDER BY id;
5734 id data
5735 1 1
5736 2 1
5737 3 4
5738 4 5
5739 SELECT * FROM t2 ORDER BY i;
5740 i
5741 1
5742 2
5743 3
5744 SELECT * FROM t3 ORDER BY id;
5745 id data
5746 1 1
5747 1 1
5748 1 1
5749 1 1
5750 1 1
5751 2 1
5752 2 1
5753 2 1
5754 2 1
5755 2 1
5756 3 3
5757 3 3
5758 3 3
5759 3 3
5760 3 3
5761 DROP FUNCTION sf1;
5762 DROP TABLE t1,t2,t3,t4;
5763 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
5764 CREATE TABLE t2 (i INTEGER NULL);
5765 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
5766 CREATE TABLE t4 (i INTEGER NULL);
5767 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
5768 INSERT INTO t2 VALUES(1),(2),(3);
5769 CREATE FUNCTION sf1() RETURNS INTEGER
5770 BEGIN
5771 DECLARE done INT DEFAULT 0;
5772 DECLARE a CHAR(16);
5773 DECLARE b,c INT;
5774 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
5775 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
5776 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
5777 OPEN cur1;
5778 OPEN cur2;
5779 REPEAT
5780 FETCH cur1 INTO a, b;
5781 FETCH cur2 INTO c;
5782 IF NOT done THEN
5783 IF b < c THEN
5784 INSERT INTO t3 VALUES (a,b);
5785 ELSE
5786 INSERT INTO t3 VALUES (a,c);
5787 END IF;
5788 END IF;
5789 UNTIL done END REPEAT;
5790 CLOSE cur1;
5791 CLOSE cur2;
5792 RETURN 0;
5793 END//
5794 ALTER FUNCTION sf1 #DET# SQL SECURITY DEFINER COMMENT 'comment' ;
5795 SELECT sf1();
5796 sf1()
5797 0
5798 SELECT * FROM t1 ORDER BY id;
5799 id data
5800 1 1
5801 2 1
5802 3 4
5803 4 5
5804 SELECT * FROM t2 ORDER BY i;
5805 i
5806 1
5807 2
5808 3
5809 SELECT * FROM t3 ORDER BY id;
5810 id data
5811 1 1
5812 2 1
5813 3 3
5814 INSERT INTO t4 VALUES(sf1());
5815 SELECT * FROM t1 ORDER BY id;
5816 id data
5817 1 1
5818 2 1
5819 3 4
5820 4 5
5821 SELECT * FROM t2 ORDER BY i;
5822 i
5823 1
5824 2
5825 3
5826 SELECT * FROM t3 ORDER BY id;
5827 id data
5828 1 1
5829 1 1
5830 2 1
5831 2 1
5832 3 3
5833 3 3
5834 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
5835 SELECT * FROM t1 ORDER BY id;
5836 id data
5837 1 1
5838 2 1
5839 3 4
5840 4 5
5841 SELECT * FROM t2 ORDER BY i;
5842 i
5843 1
5844 2
5845 3
5846 SELECT * FROM t3 ORDER BY id;
5847 id data
5848 1 1
5849 1 1
5850 1 1
5851 1 1
5852 2 1
5853 2 1
5854 2 1
5855 2 1
5856 3 3
5857 3 3
5858 3 3
5859 3 3
5860 DELETE FROM t4 WHERE i = sf1() + 1;
5861 SELECT * FROM t1 ORDER BY id;
5862 id data
5863 1 1
5864 2 1
5865 3 4
5866 4 5
5867 SELECT * FROM t2 ORDER BY i;
5868 i
5869 1
5870 2
5871 3
5872 SELECT * FROM t3 ORDER BY id;
5873 id data
5874 1 1
5875 1 1
5876 1 1
5877 1 1
5878 1 1
5879 2 1
5880 2 1
5881 2 1
5882 2 1
5883 2 1
5884 3 3
5885 3 3
5886 3 3
5887 3 3
5888 3 3
5889 DROP FUNCTION sf1;
5890 DROP TABLE t1,t2,t3,t4;
5891 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
5892 CREATE TABLE t2 (i INTEGER NULL);
5893 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
5894 CREATE TABLE t4 (i INTEGER NULL);
5895 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
5896 INSERT INTO t2 VALUES(1),(2),(3);
5897 CREATE FUNCTION sf1() RETURNS INTEGER
5898 BEGIN
5899 DECLARE done INT DEFAULT 0;
5900 DECLARE a CHAR(16);
5901 DECLARE b,c INT;
5902 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
5903 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
5904 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
5905 OPEN cur1;
5906 OPEN cur2;
5907 REPEAT
5908 FETCH cur1 INTO a, b;
5909 FETCH cur2 INTO c;
5910 IF NOT done THEN
5911 IF b < c THEN
5912 INSERT INTO t3 VALUES (a,b);
5913 ELSE
5914 INSERT INTO t3 VALUES (a,c);
5915 END IF;
5916 END IF;
5917 UNTIL done END REPEAT;
5918 CLOSE cur1;
5919 CLOSE cur2;
5920 RETURN 0;
5921 END//
5922 ALTER FUNCTION sf1 #DET# CONTAINS SQL SQL SECURITY DEFINER COMMENT 'comment' ;
5923 SELECT sf1();
5924 sf1()
5925 0
5926 SELECT * FROM t1 ORDER BY id;
5927 id data
5928 1 1
5929 2 1
5930 3 4
5931 4 5
5932 SELECT * FROM t2 ORDER BY i;
5933 i
5934 1
5935 2
5936 3
5937 SELECT * FROM t3 ORDER BY id;
5938 id data
5939 1 1
5940 2 1
5941 3 3
5942 INSERT INTO t4 VALUES(sf1());
5943 SELECT * FROM t1 ORDER BY id;
5944 id data
5945 1 1
5946 2 1
5947 3 4
5948 4 5
5949 SELECT * FROM t2 ORDER BY i;
5950 i
5951 1
5952 2
5953 3
5954 SELECT * FROM t3 ORDER BY id;
5955 id data
5956 1 1
5957 1 1
5958 2 1
5959 2 1
5960 3 3
5961 3 3
5962 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
5963 SELECT * FROM t1 ORDER BY id;
5964 id data
5965 1 1
5966 2 1
5967 3 4
5968 4 5
5969 SELECT * FROM t2 ORDER BY i;
5970 i
5971 1
5972 2
5973 3
5974 SELECT * FROM t3 ORDER BY id;
5975 id data
5976 1 1
5977 1 1
5978 1 1
5979 1 1
5980 2 1
5981 2 1
5982 2 1
5983 2 1
5984 3 3
5985 3 3
5986 3 3
5987 3 3
5988 DELETE FROM t4 WHERE i = sf1() + 1;
5989 SELECT * FROM t1 ORDER BY id;
5990 id data
5991 1 1
5992 2 1
5993 3 4
5994 4 5
5995 SELECT * FROM t2 ORDER BY i;
5996 i
5997 1
5998 2
5999 3
6000 SELECT * FROM t3 ORDER BY id;
6001 id data
6002 1 1
6003 1 1
6004 1 1
6005 1 1
6006 1 1
6007 2 1
6008 2 1
6009 2 1
6010 2 1
6011 2 1
6012 3 3
6013 3 3
6014 3 3
6015 3 3
6016 3 3
6017 DROP FUNCTION sf1;
6018 DROP TABLE t1,t2,t3,t4;
6019 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
6020 CREATE TABLE t2 (i INTEGER NULL);
6021 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
6022 CREATE TABLE t4 (i INTEGER NULL);
6023 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
6024 INSERT INTO t2 VALUES(1),(2),(3);
6025 CREATE FUNCTION sf1() RETURNS INTEGER
6026 BEGIN
6027 DECLARE done INT DEFAULT 0;
6028 DECLARE a CHAR(16);
6029 DECLARE b,c INT;
6030 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
6031 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
6032 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
6033 OPEN cur1;
6034 OPEN cur2;
6035 REPEAT
6036 FETCH cur1 INTO a, b;
6037 FETCH cur2 INTO c;
6038 IF NOT done THEN
6039 IF b < c THEN
6040 INSERT INTO t3 VALUES (a,b);
6041 ELSE
6042 INSERT INTO t3 VALUES (a,c);
6043 END IF;
6044 END IF;
6045 UNTIL done END REPEAT;
6046 CLOSE cur1;
6047 CLOSE cur2;
6048 RETURN 0;
6049 END//
6050 ALTER FUNCTION sf1 #DET# NO SQL SQL SECURITY DEFINER COMMENT 'comment' ;
6051 SELECT sf1();
6052 sf1()
6053 0
6054 SELECT * FROM t1 ORDER BY id;
6055 id data
6056 1 1
6057 2 1
6058 3 4
6059 4 5
6060 SELECT * FROM t2 ORDER BY i;
6061 i
6062 1
6063 2
6064 3
6065 SELECT * FROM t3 ORDER BY id;
6066 id data
6067 1 1
6068 2 1
6069 3 3
6070 INSERT INTO t4 VALUES(sf1());
6071 SELECT * FROM t1 ORDER BY id;
6072 id data
6073 1 1
6074 2 1
6075 3 4
6076 4 5
6077 SELECT * FROM t2 ORDER BY i;
6078 i
6079 1
6080 2
6081 3
6082 SELECT * FROM t3 ORDER BY id;
6083 id data
6084 1 1
6085 1 1
6086 2 1
6087 2 1
6088 3 3
6089 3 3
6090 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
6091 SELECT * FROM t1 ORDER BY id;
6092 id data
6093 1 1
6094 2 1
6095 3 4
6096 4 5
6097 SELECT * FROM t2 ORDER BY i;
6098 i
6099 1
6100 2
6101 3
6102 SELECT * FROM t3 ORDER BY id;
6103 id data
6104 1 1
6105 1 1
6106 1 1
6107 1 1
6108 2 1
6109 2 1
6110 2 1
6111 2 1
6112 3 3
6113 3 3
6114 3 3
6115 3 3
6116 DELETE FROM t4 WHERE i = sf1() + 1;
6117 SELECT * FROM t1 ORDER BY id;
6118 id data
6119 1 1
6120 2 1
6121 3 4
6122 4 5
6123 SELECT * FROM t2 ORDER BY i;
6124 i
6125 1
6126 2
6127 3
6128 SELECT * FROM t3 ORDER BY id;
6129 id data
6130 1 1
6131 1 1
6132 1 1
6133 1 1
6134 1 1
6135 2 1
6136 2 1
6137 2 1
6138 2 1
6139 2 1
6140 3 3
6141 3 3
6142 3 3
6143 3 3
6144 3 3
6145 DROP FUNCTION sf1;
6146 DROP TABLE t1,t2,t3,t4;
6147 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
6148 CREATE TABLE t2 (i INTEGER NULL);
6149 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
6150 CREATE TABLE t4 (i INTEGER NULL);
6151 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
6152 INSERT INTO t2 VALUES(1),(2),(3);
6153 CREATE FUNCTION sf1() RETURNS INTEGER
6154 BEGIN
6155 DECLARE done INT DEFAULT 0;
6156 DECLARE a CHAR(16);
6157 DECLARE b,c INT;
6158 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
6159 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
6160 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
6161 OPEN cur1;
6162 OPEN cur2;
6163 REPEAT
6164 FETCH cur1 INTO a, b;
6165 FETCH cur2 INTO c;
6166 IF NOT done THEN
6167 IF b < c THEN
6168 INSERT INTO t3 VALUES (a,b);
6169 ELSE
6170 INSERT INTO t3 VALUES (a,c);
6171 END IF;
6172 END IF;
6173 UNTIL done END REPEAT;
6174 CLOSE cur1;
6175 CLOSE cur2;
6176 RETURN 0;
6177 END//
6178 ALTER FUNCTION sf1 #DET# READS SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
6179 SELECT sf1();
6180 sf1()
6181 0
6182 SELECT * FROM t1 ORDER BY id;
6183 id data
6184 1 1
6185 2 1
6186 3 4
6187 4 5
6188 SELECT * FROM t2 ORDER BY i;
6189 i
6190 1
6191 2
6192 3
6193 SELECT * FROM t3 ORDER BY id;
6194 id data
6195 1 1
6196 2 1
6197 3 3
6198 INSERT INTO t4 VALUES(sf1());
6199 SELECT * FROM t1 ORDER BY id;
6200 id data
6201 1 1
6202 2 1
6203 3 4
6204 4 5
6205 SELECT * FROM t2 ORDER BY i;
6206 i
6207 1
6208 2
6209 3
6210 SELECT * FROM t3 ORDER BY id;
6211 id data
6212 1 1
6213 1 1
6214 2 1
6215 2 1
6216 3 3
6217 3 3
6218 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
6219 SELECT * FROM t1 ORDER BY id;
6220 id data
6221 1 1
6222 2 1
6223 3 4
6224 4 5
6225 SELECT * FROM t2 ORDER BY i;
6226 i
6227 1
6228 2
6229 3
6230 SELECT * FROM t3 ORDER BY id;
6231 id data
6232 1 1
6233 1 1
6234 1 1
6235 1 1
6236 2 1
6237 2 1
6238 2 1
6239 2 1
6240 3 3
6241 3 3
6242 3 3
6243 3 3
6244 DELETE FROM t4 WHERE i = sf1() + 1;
6245 SELECT * FROM t1 ORDER BY id;
6246 id data
6247 1 1
6248 2 1
6249 3 4
6250 4 5
6251 SELECT * FROM t2 ORDER BY i;
6252 i
6253 1
6254 2
6255 3
6256 SELECT * FROM t3 ORDER BY id;
6257 id data
6258 1 1
6259 1 1
6260 1 1
6261 1 1
6262 1 1
6263 2 1
6264 2 1
6265 2 1
6266 2 1
6267 2 1
6268 3 3
6269 3 3
6270 3 3
6271 3 3
6272 3 3
6273 DROP FUNCTION sf1;
6274 DROP TABLE t1,t2,t3,t4;
6275 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
6276 CREATE TABLE t2 (i INTEGER NULL);
6277 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
6278 CREATE TABLE t4 (i INTEGER NULL);
6279 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
6280 INSERT INTO t2 VALUES(1),(2),(3);
6281 CREATE FUNCTION sf1() RETURNS INTEGER
6282 BEGIN
6283 DECLARE done INT DEFAULT 0;
6284 DECLARE a CHAR(16);
6285 DECLARE b,c INT;
6286 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
6287 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
6288 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
6289 OPEN cur1;
6290 OPEN cur2;
6291 REPEAT
6292 FETCH cur1 INTO a, b;
6293 FETCH cur2 INTO c;
6294 IF NOT done THEN
6295 IF b < c THEN
6296 INSERT INTO t3 VALUES (a,b);
6297 ELSE
6298 INSERT INTO t3 VALUES (a,c);
6299 END IF;
6300 END IF;
6301 UNTIL done END REPEAT;
6302 CLOSE cur1;
6303 CLOSE cur2;
6304 RETURN 0;
6305 END//
6306 ALTER FUNCTION sf1 #DET# MODIFIES SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
6307 SELECT sf1();
6308 sf1()
6309 0
6310 SELECT * FROM t1 ORDER BY id;
6311 id data
6312 1 1
6313 2 1
6314 3 4
6315 4 5
6316 SELECT * FROM t2 ORDER BY i;
6317 i
6318 1
6319 2
6320 3
6321 SELECT * FROM t3 ORDER BY id;
6322 id data
6323 1 1
6324 2 1
6325 3 3
6326 INSERT INTO t4 VALUES(sf1());
6327 SELECT * FROM t1 ORDER BY id;
6328 id data
6329 1 1
6330 2 1
6331 3 4
6332 4 5
6333 SELECT * FROM t2 ORDER BY i;
6334 i
6335 1
6336 2
6337 3
6338 SELECT * FROM t3 ORDER BY id;
6339 id data
6340 1 1
6341 1 1
6342 2 1
6343 2 1
6344 3 3
6345 3 3
6346 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
6347 SELECT * FROM t1 ORDER BY id;
6348 id data
6349 1 1
6350 2 1
6351 3 4
6352 4 5
6353 SELECT * FROM t2 ORDER BY i;
6354 i
6355 1
6356 2
6357 3
6358 SELECT * FROM t3 ORDER BY id;
6359 id data
6360 1 1
6361 1 1
6362 1 1
6363 1 1
6364 2 1
6365 2 1
6366 2 1
6367 2 1
6368 3 3
6369 3 3
6370 3 3
6371 3 3
6372 DELETE FROM t4 WHERE i = sf1() + 1;
6373 SELECT * FROM t1 ORDER BY id;
6374 id data
6375 1 1
6376 2 1
6377 3 4
6378 4 5
6379 SELECT * FROM t2 ORDER BY i;
6380 i
6381 1
6382 2
6383 3
6384 SELECT * FROM t3 ORDER BY id;
6385 id data
6386 1 1
6387 1 1
6388 1 1
6389 1 1
6390 1 1
6391 2 1
6392 2 1
6393 2 1
6394 2 1
6395 2 1
6396 3 3
6397 3 3
6398 3 3
6399 3 3
6400 3 3
6401 DROP FUNCTION sf1;
6402 DROP TABLE t1,t2,t3,t4;
6403 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
6404 CREATE TABLE t2 (i INTEGER NULL);
6405 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
6406 CREATE TABLE t4 (i INTEGER NULL);
6407 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
6408 INSERT INTO t2 VALUES(1),(2),(3);
6409 CREATE FUNCTION sf1() RETURNS INTEGER
6410 BEGIN
6411 DECLARE done INT DEFAULT 0;
6412 DECLARE a CHAR(16);
6413 DECLARE b,c INT;
6414 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
6415 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
6416 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
6417 OPEN cur1;
6418 OPEN cur2;
6419 REPEAT
6420 FETCH cur1 INTO a, b;
6421 FETCH cur2 INTO c;
6422 IF NOT done THEN
6423 IF b < c THEN
6424 INSERT INTO t3 VALUES (a,b);
6425 ELSE
6426 INSERT INTO t3 VALUES (a,c);
6427 END IF;
6428 END IF;
6429 UNTIL done END REPEAT;
6430 CLOSE cur1;
6431 CLOSE cur2;
6432 RETURN 0;
6433 END//
6434 ALTER FUNCTION sf1 LANGUAGE SQL #DET# SQL SECURITY DEFINER ;
6435 SELECT sf1();
6436 sf1()
6437 0
6438 SELECT * FROM t1 ORDER BY id;
6439 id data
6440 1 1
6441 2 1
6442 3 4
6443 4 5
6444 SELECT * FROM t2 ORDER BY i;
6445 i
6446 1
6447 2
6448 3
6449 SELECT * FROM t3 ORDER BY id;
6450 id data
6451 1 1
6452 2 1
6453 3 3
6454 INSERT INTO t4 VALUES(sf1());
6455 SELECT * FROM t1 ORDER BY id;
6456 id data
6457 1 1
6458 2 1
6459 3 4
6460 4 5
6461 SELECT * FROM t2 ORDER BY i;
6462 i
6463 1
6464 2
6465 3
6466 SELECT * FROM t3 ORDER BY id;
6467 id data
6468 1 1
6469 1 1
6470 2 1
6471 2 1
6472 3 3
6473 3 3
6474 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
6475 SELECT * FROM t1 ORDER BY id;
6476 id data
6477 1 1
6478 2 1
6479 3 4
6480 4 5
6481 SELECT * FROM t2 ORDER BY i;
6482 i
6483 1
6484 2
6485 3
6486 SELECT * FROM t3 ORDER BY id;
6487 id data
6488 1 1
6489 1 1
6490 1 1
6491 1 1
6492 2 1
6493 2 1
6494 2 1
6495 2 1
6496 3 3
6497 3 3
6498 3 3
6499 3 3
6500 DELETE FROM t4 WHERE i = sf1() + 1;
6501 SELECT * FROM t1 ORDER BY id;
6502 id data
6503 1 1
6504 2 1
6505 3 4
6506 4 5
6507 SELECT * FROM t2 ORDER BY i;
6508 i
6509 1
6510 2
6511 3
6512 SELECT * FROM t3 ORDER BY id;
6513 id data
6514 1 1
6515 1 1
6516 1 1
6517 1 1
6518 1 1
6519 2 1
6520 2 1
6521 2 1
6522 2 1
6523 2 1
6524 3 3
6525 3 3
6526 3 3
6527 3 3
6528 3 3
6529 DROP FUNCTION sf1;
6530 DROP TABLE t1,t2,t3,t4;
6531 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
6532 CREATE TABLE t2 (i INTEGER NULL);
6533 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
6534 CREATE TABLE t4 (i INTEGER NULL);
6535 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
6536 INSERT INTO t2 VALUES(1),(2),(3);
6537 CREATE FUNCTION sf1() RETURNS INTEGER
6538 BEGIN
6539 DECLARE done INT DEFAULT 0;
6540 DECLARE a CHAR(16);
6541 DECLARE b,c INT;
6542 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
6543 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
6544 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
6545 OPEN cur1;
6546 OPEN cur2;
6547 REPEAT
6548 FETCH cur1 INTO a, b;
6549 FETCH cur2 INTO c;
6550 IF NOT done THEN
6551 IF b < c THEN
6552 INSERT INTO t3 VALUES (a,b);
6553 ELSE
6554 INSERT INTO t3 VALUES (a,c);
6555 END IF;
6556 END IF;
6557 UNTIL done END REPEAT;
6558 CLOSE cur1;
6559 CLOSE cur2;
6560 RETURN 0;
6561 END//
6562 ALTER FUNCTION sf1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY DEFINER ;
6563 SELECT sf1();
6564 sf1()
6565 0
6566 SELECT * FROM t1 ORDER BY id;
6567 id data
6568 1 1
6569 2 1
6570 3 4
6571 4 5
6572 SELECT * FROM t2 ORDER BY i;
6573 i
6574 1
6575 2
6576 3
6577 SELECT * FROM t3 ORDER BY id;
6578 id data
6579 1 1
6580 2 1
6581 3 3
6582 INSERT INTO t4 VALUES(sf1());
6583 SELECT * FROM t1 ORDER BY id;
6584 id data
6585 1 1
6586 2 1
6587 3 4
6588 4 5
6589 SELECT * FROM t2 ORDER BY i;
6590 i
6591 1
6592 2
6593 3
6594 SELECT * FROM t3 ORDER BY id;
6595 id data
6596 1 1
6597 1 1
6598 2 1
6599 2 1
6600 3 3
6601 3 3
6602 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
6603 SELECT * FROM t1 ORDER BY id;
6604 id data
6605 1 1
6606 2 1
6607 3 4
6608 4 5
6609 SELECT * FROM t2 ORDER BY i;
6610 i
6611 1
6612 2
6613 3
6614 SELECT * FROM t3 ORDER BY id;
6615 id data
6616 1 1
6617 1 1
6618 1 1
6619 1 1
6620 2 1
6621 2 1
6622 2 1
6623 2 1
6624 3 3
6625 3 3
6626 3 3
6627 3 3
6628 DELETE FROM t4 WHERE i = sf1() + 1;
6629 SELECT * FROM t1 ORDER BY id;
6630 id data
6631 1 1
6632 2 1
6633 3 4
6634 4 5
6635 SELECT * FROM t2 ORDER BY i;
6636 i
6637 1
6638 2
6639 3
6640 SELECT * FROM t3 ORDER BY id;
6641 id data
6642 1 1
6643 1 1
6644 1 1
6645 1 1
6646 1 1
6647 2 1
6648 2 1
6649 2 1
6650 2 1
6651 2 1
6652 3 3
6653 3 3
6654 3 3
6655 3 3
6656 3 3
6657 DROP FUNCTION sf1;
6658 DROP TABLE t1,t2,t3,t4;
6659 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
6660 CREATE TABLE t2 (i INTEGER NULL);
6661 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
6662 CREATE TABLE t4 (i INTEGER NULL);
6663 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
6664 INSERT INTO t2 VALUES(1),(2),(3);
6665 CREATE FUNCTION sf1() RETURNS INTEGER
6666 BEGIN
6667 DECLARE done INT DEFAULT 0;
6668 DECLARE a CHAR(16);
6669 DECLARE b,c INT;
6670 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
6671 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
6672 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
6673 OPEN cur1;
6674 OPEN cur2;
6675 REPEAT
6676 FETCH cur1 INTO a, b;
6677 FETCH cur2 INTO c;
6678 IF NOT done THEN
6679 IF b < c THEN
6680 INSERT INTO t3 VALUES (a,b);
6681 ELSE
6682 INSERT INTO t3 VALUES (a,c);
6683 END IF;
6684 END IF;
6685 UNTIL done END REPEAT;
6686 CLOSE cur1;
6687 CLOSE cur2;
6688 RETURN 0;
6689 END//
6690 ALTER FUNCTION sf1 LANGUAGE SQL #DET# NO SQL SQL SECURITY DEFINER ;
6691 SELECT sf1();
6692 sf1()
6693 0
6694 SELECT * FROM t1 ORDER BY id;
6695 id data
6696 1 1
6697 2 1
6698 3 4
6699 4 5
6700 SELECT * FROM t2 ORDER BY i;
6701 i
6702 1
6703 2
6704 3
6705 SELECT * FROM t3 ORDER BY id;
6706 id data
6707 1 1
6708 2 1
6709 3 3
6710 INSERT INTO t4 VALUES(sf1());
6711 SELECT * FROM t1 ORDER BY id;
6712 id data
6713 1 1
6714 2 1
6715 3 4
6716 4 5
6717 SELECT * FROM t2 ORDER BY i;
6718 i
6719 1
6720 2
6721 3
6722 SELECT * FROM t3 ORDER BY id;
6723 id data
6724 1 1
6725 1 1
6726 2 1
6727 2 1
6728 3 3
6729 3 3
6730 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
6731 SELECT * FROM t1 ORDER BY id;
6732 id data
6733 1 1
6734 2 1
6735 3 4
6736 4 5
6737 SELECT * FROM t2 ORDER BY i;
6738 i
6739 1
6740 2
6741 3
6742 SELECT * FROM t3 ORDER BY id;
6743 id data
6744 1 1
6745 1 1
6746 1 1
6747 1 1
6748 2 1
6749 2 1
6750 2 1
6751 2 1
6752 3 3
6753 3 3
6754 3 3
6755 3 3
6756 DELETE FROM t4 WHERE i = sf1() + 1;
6757 SELECT * FROM t1 ORDER BY id;
6758 id data
6759 1 1
6760 2 1
6761 3 4
6762 4 5
6763 SELECT * FROM t2 ORDER BY i;
6764 i
6765 1
6766 2
6767 3
6768 SELECT * FROM t3 ORDER BY id;
6769 id data
6770 1 1
6771 1 1
6772 1 1
6773 1 1
6774 1 1
6775 2 1
6776 2 1
6777 2 1
6778 2 1
6779 2 1
6780 3 3
6781 3 3
6782 3 3
6783 3 3
6784 3 3
6785 DROP FUNCTION sf1;
6786 DROP TABLE t1,t2,t3,t4;
6787 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
6788 CREATE TABLE t2 (i INTEGER NULL);
6789 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
6790 CREATE TABLE t4 (i INTEGER NULL);
6791 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
6792 INSERT INTO t2 VALUES(1),(2),(3);
6793 CREATE FUNCTION sf1() RETURNS INTEGER
6794 BEGIN
6795 DECLARE done INT DEFAULT 0;
6796 DECLARE a CHAR(16);
6797 DECLARE b,c INT;
6798 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
6799 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
6800 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
6801 OPEN cur1;
6802 OPEN cur2;
6803 REPEAT
6804 FETCH cur1 INTO a, b;
6805 FETCH cur2 INTO c;
6806 IF NOT done THEN
6807 IF b < c THEN
6808 INSERT INTO t3 VALUES (a,b);
6809 ELSE
6810 INSERT INTO t3 VALUES (a,c);
6811 END IF;
6812 END IF;
6813 UNTIL done END REPEAT;
6814 CLOSE cur1;
6815 CLOSE cur2;
6816 RETURN 0;
6817 END//
6818 ALTER FUNCTION sf1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY DEFINER ;
6819 SELECT sf1();
6820 sf1()
6821 0
6822 SELECT * FROM t1 ORDER BY id;
6823 id data
6824 1 1
6825 2 1
6826 3 4
6827 4 5
6828 SELECT * FROM t2 ORDER BY i;
6829 i
6830 1
6831 2
6832 3
6833 SELECT * FROM t3 ORDER BY id;
6834 id data
6835 1 1
6836 2 1
6837 3 3
6838 INSERT INTO t4 VALUES(sf1());
6839 SELECT * FROM t1 ORDER BY id;
6840 id data
6841 1 1
6842 2 1
6843 3 4
6844 4 5
6845 SELECT * FROM t2 ORDER BY i;
6846 i
6847 1
6848 2
6849 3
6850 SELECT * FROM t3 ORDER BY id;
6851 id data
6852 1 1
6853 1 1
6854 2 1
6855 2 1
6856 3 3
6857 3 3
6858 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
6859 SELECT * FROM t1 ORDER BY id;
6860 id data
6861 1 1
6862 2 1
6863 3 4
6864 4 5
6865 SELECT * FROM t2 ORDER BY i;
6866 i
6867 1
6868 2
6869 3
6870 SELECT * FROM t3 ORDER BY id;
6871 id data
6872 1 1
6873 1 1
6874 1 1
6875 1 1
6876 2 1
6877 2 1
6878 2 1
6879 2 1
6880 3 3
6881 3 3
6882 3 3
6883 3 3
6884 DELETE FROM t4 WHERE i = sf1() + 1;
6885 SELECT * FROM t1 ORDER BY id;
6886 id data
6887 1 1
6888 2 1
6889 3 4
6890 4 5
6891 SELECT * FROM t2 ORDER BY i;
6892 i
6893 1
6894 2
6895 3
6896 SELECT * FROM t3 ORDER BY id;
6897 id data
6898 1 1
6899 1 1
6900 1 1
6901 1 1
6902 1 1
6903 2 1
6904 2 1
6905 2 1
6906 2 1
6907 2 1
6908 3 3
6909 3 3
6910 3 3
6911 3 3
6912 3 3
6913 DROP FUNCTION sf1;
6914 DROP TABLE t1,t2,t3,t4;
6915 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
6916 CREATE TABLE t2 (i INTEGER NULL);
6917 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
6918 CREATE TABLE t4 (i INTEGER NULL);
6919 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
6920 INSERT INTO t2 VALUES(1),(2),(3);
6921 CREATE FUNCTION sf1() RETURNS INTEGER
6922 BEGIN
6923 DECLARE done INT DEFAULT 0;
6924 DECLARE a CHAR(16);
6925 DECLARE b,c INT;
6926 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
6927 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
6928 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
6929 OPEN cur1;
6930 OPEN cur2;
6931 REPEAT
6932 FETCH cur1 INTO a, b;
6933 FETCH cur2 INTO c;
6934 IF NOT done THEN
6935 IF b < c THEN
6936 INSERT INTO t3 VALUES (a,b);
6937 ELSE
6938 INSERT INTO t3 VALUES (a,c);
6939 END IF;
6940 END IF;
6941 UNTIL done END REPEAT;
6942 CLOSE cur1;
6943 CLOSE cur2;
6944 RETURN 0;
6945 END//
6946 ALTER FUNCTION sf1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY DEFINER ;
6947 SELECT sf1();
6948 sf1()
6949 0
6950 SELECT * FROM t1 ORDER BY id;
6951 id data
6952 1 1
6953 2 1
6954 3 4
6955 4 5
6956 SELECT * FROM t2 ORDER BY i;
6957 i
6958 1
6959 2
6960 3
6961 SELECT * FROM t3 ORDER BY id;
6962 id data
6963 1 1
6964 2 1
6965 3 3
6966 INSERT INTO t4 VALUES(sf1());
6967 SELECT * FROM t1 ORDER BY id;
6968 id data
6969 1 1
6970 2 1
6971 3 4
6972 4 5
6973 SELECT * FROM t2 ORDER BY i;
6974 i
6975 1
6976 2
6977 3
6978 SELECT * FROM t3 ORDER BY id;
6979 id data
6980 1 1
6981 1 1
6982 2 1
6983 2 1
6984 3 3
6985 3 3
6986 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
6987 SELECT * FROM t1 ORDER BY id;
6988 id data
6989 1 1
6990 2 1
6991 3 4
6992 4 5
6993 SELECT * FROM t2 ORDER BY i;
6994 i
6995 1
6996 2
6997 3
6998 SELECT * FROM t3 ORDER BY id;
6999 id data
7000 1 1
7001 1 1
7002 1 1
7003 1 1
7004 2 1
7005 2 1
7006 2 1
7007 2 1
7008 3 3
7009 3 3
7010 3 3
7011 3 3
7012 DELETE FROM t4 WHERE i = sf1() + 1;
7013 SELECT * FROM t1 ORDER BY id;
7014 id data
7015 1 1
7016 2 1
7017 3 4
7018 4 5
7019 SELECT * FROM t2 ORDER BY i;
7020 i
7021 1
7022 2
7023 3
7024 SELECT * FROM t3 ORDER BY id;
7025 id data
7026 1 1
7027 1 1
7028 1 1
7029 1 1
7030 1 1
7031 2 1
7032 2 1
7033 2 1
7034 2 1
7035 2 1
7036 3 3
7037 3 3
7038 3 3
7039 3 3
7040 3 3
7041 DROP FUNCTION sf1;
7042 DROP TABLE t1,t2,t3,t4;
7043 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
7044 CREATE TABLE t2 (i INTEGER NULL);
7045 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
7046 CREATE TABLE t4 (i INTEGER NULL);
7047 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
7048 INSERT INTO t2 VALUES(1),(2),(3);
7049 CREATE FUNCTION sf1() RETURNS INTEGER
7050 BEGIN
7051 DECLARE done INT DEFAULT 0;
7052 DECLARE a CHAR(16);
7053 DECLARE b,c INT;
7054 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
7055 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
7056 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
7057 OPEN cur1;
7058 OPEN cur2;
7059 REPEAT
7060 FETCH cur1 INTO a, b;
7061 FETCH cur2 INTO c;
7062 IF NOT done THEN
7063 IF b < c THEN
7064 INSERT INTO t3 VALUES (a,b);
7065 ELSE
7066 INSERT INTO t3 VALUES (a,c);
7067 END IF;
7068 END IF;
7069 UNTIL done END REPEAT;
7070 CLOSE cur1;
7071 CLOSE cur2;
7072 RETURN 0;
7073 END//
7074 ALTER FUNCTION sf1 LANGUAGE SQL #DET# SQL SECURITY DEFINER COMMENT 'comment' ;
7075 SELECT sf1();
7076 sf1()
7077 0
7078 SELECT * FROM t1 ORDER BY id;
7079 id data
7080 1 1
7081 2 1
7082 3 4
7083 4 5
7084 SELECT * FROM t2 ORDER BY i;
7085 i
7086 1
7087 2
7088 3
7089 SELECT * FROM t3 ORDER BY id;
7090 id data
7091 1 1
7092 2 1
7093 3 3
7094 INSERT INTO t4 VALUES(sf1());
7095 SELECT * FROM t1 ORDER BY id;
7096 id data
7097 1 1
7098 2 1
7099 3 4
7100 4 5
7101 SELECT * FROM t2 ORDER BY i;
7102 i
7103 1
7104 2
7105 3
7106 SELECT * FROM t3 ORDER BY id;
7107 id data
7108 1 1
7109 1 1
7110 2 1
7111 2 1
7112 3 3
7113 3 3
7114 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
7115 SELECT * FROM t1 ORDER BY id;
7116 id data
7117 1 1
7118 2 1
7119 3 4
7120 4 5
7121 SELECT * FROM t2 ORDER BY i;
7122 i
7123 1
7124 2
7125 3
7126 SELECT * FROM t3 ORDER BY id;
7127 id data
7128 1 1
7129 1 1
7130 1 1
7131 1 1
7132 2 1
7133 2 1
7134 2 1
7135 2 1
7136 3 3
7137 3 3
7138 3 3
7139 3 3
7140 DELETE FROM t4 WHERE i = sf1() + 1;
7141 SELECT * FROM t1 ORDER BY id;
7142 id data
7143 1 1
7144 2 1
7145 3 4
7146 4 5
7147 SELECT * FROM t2 ORDER BY i;
7148 i
7149 1
7150 2
7151 3
7152 SELECT * FROM t3 ORDER BY id;
7153 id data
7154 1 1
7155 1 1
7156 1 1
7157 1 1
7158 1 1
7159 2 1
7160 2 1
7161 2 1
7162 2 1
7163 2 1
7164 3 3
7165 3 3
7166 3 3
7167 3 3
7168 3 3
7169 DROP FUNCTION sf1;
7170 DROP TABLE t1,t2,t3,t4;
7171 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
7172 CREATE TABLE t2 (i INTEGER NULL);
7173 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
7174 CREATE TABLE t4 (i INTEGER NULL);
7175 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
7176 INSERT INTO t2 VALUES(1),(2),(3);
7177 CREATE FUNCTION sf1() RETURNS INTEGER
7178 BEGIN
7179 DECLARE done INT DEFAULT 0;
7180 DECLARE a CHAR(16);
7181 DECLARE b,c INT;
7182 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
7183 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
7184 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
7185 OPEN cur1;
7186 OPEN cur2;
7187 REPEAT
7188 FETCH cur1 INTO a, b;
7189 FETCH cur2 INTO c;
7190 IF NOT done THEN
7191 IF b < c THEN
7192 INSERT INTO t3 VALUES (a,b);
7193 ELSE
7194 INSERT INTO t3 VALUES (a,c);
7195 END IF;
7196 END IF;
7197 UNTIL done END REPEAT;
7198 CLOSE cur1;
7199 CLOSE cur2;
7200 RETURN 0;
7201 END//
7202 ALTER FUNCTION sf1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY DEFINER COMMENT 'comment' ;
7203 SELECT sf1();
7204 sf1()
7205 0
7206 SELECT * FROM t1 ORDER BY id;
7207 id data
7208 1 1
7209 2 1
7210 3 4
7211 4 5
7212 SELECT * FROM t2 ORDER BY i;
7213 i
7214 1
7215 2
7216 3
7217 SELECT * FROM t3 ORDER BY id;
7218 id data
7219 1 1
7220 2 1
7221 3 3
7222 INSERT INTO t4 VALUES(sf1());
7223 SELECT * FROM t1 ORDER BY id;
7224 id data
7225 1 1
7226 2 1
7227 3 4
7228 4 5
7229 SELECT * FROM t2 ORDER BY i;
7230 i
7231 1
7232 2
7233 3
7234 SELECT * FROM t3 ORDER BY id;
7235 id data
7236 1 1
7237 1 1
7238 2 1
7239 2 1
7240 3 3
7241 3 3
7242 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
7243 SELECT * FROM t1 ORDER BY id;
7244 id data
7245 1 1
7246 2 1
7247 3 4
7248 4 5
7249 SELECT * FROM t2 ORDER BY i;
7250 i
7251 1
7252 2
7253 3
7254 SELECT * FROM t3 ORDER BY id;
7255 id data
7256 1 1
7257 1 1
7258 1 1
7259 1 1
7260 2 1
7261 2 1
7262 2 1
7263 2 1
7264 3 3
7265 3 3
7266 3 3
7267 3 3
7268 DELETE FROM t4 WHERE i = sf1() + 1;
7269 SELECT * FROM t1 ORDER BY id;
7270 id data
7271 1 1
7272 2 1
7273 3 4
7274 4 5
7275 SELECT * FROM t2 ORDER BY i;
7276 i
7277 1
7278 2
7279 3
7280 SELECT * FROM t3 ORDER BY id;
7281 id data
7282 1 1
7283 1 1
7284 1 1
7285 1 1
7286 1 1
7287 2 1
7288 2 1
7289 2 1
7290 2 1
7291 2 1
7292 3 3
7293 3 3
7294 3 3
7295 3 3
7296 3 3
7297 DROP FUNCTION sf1;
7298 DROP TABLE t1,t2,t3,t4;
7299 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
7300 CREATE TABLE t2 (i INTEGER NULL);
7301 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
7302 CREATE TABLE t4 (i INTEGER NULL);
7303 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
7304 INSERT INTO t2 VALUES(1),(2),(3);
7305 CREATE FUNCTION sf1() RETURNS INTEGER
7306 BEGIN
7307 DECLARE done INT DEFAULT 0;
7308 DECLARE a CHAR(16);
7309 DECLARE b,c INT;
7310 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
7311 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
7312 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
7313 OPEN cur1;
7314 OPEN cur2;
7315 REPEAT
7316 FETCH cur1 INTO a, b;
7317 FETCH cur2 INTO c;
7318 IF NOT done THEN
7319 IF b < c THEN
7320 INSERT INTO t3 VALUES (a,b);
7321 ELSE
7322 INSERT INTO t3 VALUES (a,c);
7323 END IF;
7324 END IF;
7325 UNTIL done END REPEAT;
7326 CLOSE cur1;
7327 CLOSE cur2;
7328 RETURN 0;
7329 END//
7330 ALTER FUNCTION sf1 LANGUAGE SQL #DET# NO SQL SQL SECURITY DEFINER COMMENT 'comment' ;
7331 SELECT sf1();
7332 sf1()
7333 0
7334 SELECT * FROM t1 ORDER BY id;
7335 id data
7336 1 1
7337 2 1
7338 3 4
7339 4 5
7340 SELECT * FROM t2 ORDER BY i;
7341 i
7342 1
7343 2
7344 3
7345 SELECT * FROM t3 ORDER BY id;
7346 id data
7347 1 1
7348 2 1
7349 3 3
7350 INSERT INTO t4 VALUES(sf1());
7351 SELECT * FROM t1 ORDER BY id;
7352 id data
7353 1 1
7354 2 1
7355 3 4
7356 4 5
7357 SELECT * FROM t2 ORDER BY i;
7358 i
7359 1
7360 2
7361 3
7362 SELECT * FROM t3 ORDER BY id;
7363 id data
7364 1 1
7365 1 1
7366 2 1
7367 2 1
7368 3 3
7369 3 3
7370 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
7371 SELECT * FROM t1 ORDER BY id;
7372 id data
7373 1 1
7374 2 1
7375 3 4
7376 4 5
7377 SELECT * FROM t2 ORDER BY i;
7378 i
7379 1
7380 2
7381 3
7382 SELECT * FROM t3 ORDER BY id;
7383 id data
7384 1 1
7385 1 1
7386 1 1
7387 1 1
7388 2 1
7389 2 1
7390 2 1
7391 2 1
7392 3 3
7393 3 3
7394 3 3
7395 3 3
7396 DELETE FROM t4 WHERE i = sf1() + 1;
7397 SELECT * FROM t1 ORDER BY id;
7398 id data
7399 1 1
7400 2 1
7401 3 4
7402 4 5
7403 SELECT * FROM t2 ORDER BY i;
7404 i
7405 1
7406 2
7407 3
7408 SELECT * FROM t3 ORDER BY id;
7409 id data
7410 1 1
7411 1 1
7412 1 1
7413 1 1
7414 1 1
7415 2 1
7416 2 1
7417 2 1
7418 2 1
7419 2 1
7420 3 3
7421 3 3
7422 3 3
7423 3 3
7424 3 3
7425 DROP FUNCTION sf1;
7426 DROP TABLE t1,t2,t3,t4;
7427 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
7428 CREATE TABLE t2 (i INTEGER NULL);
7429 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
7430 CREATE TABLE t4 (i INTEGER NULL);
7431 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
7432 INSERT INTO t2 VALUES(1),(2),(3);
7433 CREATE FUNCTION sf1() RETURNS INTEGER
7434 BEGIN
7435 DECLARE done INT DEFAULT 0;
7436 DECLARE a CHAR(16);
7437 DECLARE b,c INT;
7438 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
7439 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
7440 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
7441 OPEN cur1;
7442 OPEN cur2;
7443 REPEAT
7444 FETCH cur1 INTO a, b;
7445 FETCH cur2 INTO c;
7446 IF NOT done THEN
7447 IF b < c THEN
7448 INSERT INTO t3 VALUES (a,b);
7449 ELSE
7450 INSERT INTO t3 VALUES (a,c);
7451 END IF;
7452 END IF;
7453 UNTIL done END REPEAT;
7454 CLOSE cur1;
7455 CLOSE cur2;
7456 RETURN 0;
7457 END//
7458 ALTER FUNCTION sf1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
7459 SELECT sf1();
7460 sf1()
7461 0
7462 SELECT * FROM t1 ORDER BY id;
7463 id data
7464 1 1
7465 2 1
7466 3 4
7467 4 5
7468 SELECT * FROM t2 ORDER BY i;
7469 i
7470 1
7471 2
7472 3
7473 SELECT * FROM t3 ORDER BY id;
7474 id data
7475 1 1
7476 2 1
7477 3 3
7478 INSERT INTO t4 VALUES(sf1());
7479 SELECT * FROM t1 ORDER BY id;
7480 id data
7481 1 1
7482 2 1
7483 3 4
7484 4 5
7485 SELECT * FROM t2 ORDER BY i;
7486 i
7487 1
7488 2
7489 3
7490 SELECT * FROM t3 ORDER BY id;
7491 id data
7492 1 1
7493 1 1
7494 2 1
7495 2 1
7496 3 3
7497 3 3
7498 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
7499 SELECT * FROM t1 ORDER BY id;
7500 id data
7501 1 1
7502 2 1
7503 3 4
7504 4 5
7505 SELECT * FROM t2 ORDER BY i;
7506 i
7507 1
7508 2
7509 3
7510 SELECT * FROM t3 ORDER BY id;
7511 id data
7512 1 1
7513 1 1
7514 1 1
7515 1 1
7516 2 1
7517 2 1
7518 2 1
7519 2 1
7520 3 3
7521 3 3
7522 3 3
7523 3 3
7524 DELETE FROM t4 WHERE i = sf1() + 1;
7525 SELECT * FROM t1 ORDER BY id;
7526 id data
7527 1 1
7528 2 1
7529 3 4
7530 4 5
7531 SELECT * FROM t2 ORDER BY i;
7532 i
7533 1
7534 2
7535 3
7536 SELECT * FROM t3 ORDER BY id;
7537 id data
7538 1 1
7539 1 1
7540 1 1
7541 1 1
7542 1 1
7543 2 1
7544 2 1
7545 2 1
7546 2 1
7547 2 1
7548 3 3
7549 3 3
7550 3 3
7551 3 3
7552 3 3
7553 DROP FUNCTION sf1;
7554 DROP TABLE t1,t2,t3,t4;
7555 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
7556 CREATE TABLE t2 (i INTEGER NULL);
7557 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
7558 CREATE TABLE t4 (i INTEGER NULL);
7559 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
7560 INSERT INTO t2 VALUES(1),(2),(3);
7561 CREATE FUNCTION sf1() RETURNS INTEGER
7562 BEGIN
7563 DECLARE done INT DEFAULT 0;
7564 DECLARE a CHAR(16);
7565 DECLARE b,c INT;
7566 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
7567 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
7568 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
7569 OPEN cur1;
7570 OPEN cur2;
7571 REPEAT
7572 FETCH cur1 INTO a, b;
7573 FETCH cur2 INTO c;
7574 IF NOT done THEN
7575 IF b < c THEN
7576 INSERT INTO t3 VALUES (a,b);
7577 ELSE
7578 INSERT INTO t3 VALUES (a,c);
7579 END IF;
7580 END IF;
7581 UNTIL done END REPEAT;
7582 CLOSE cur1;
7583 CLOSE cur2;
7584 RETURN 0;
7585 END//
7586 ALTER FUNCTION sf1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
7587 SELECT sf1();
7588 sf1()
7589 0
7590 SELECT * FROM t1 ORDER BY id;
7591 id data
7592 1 1
7593 2 1
7594 3 4
7595 4 5
7596 SELECT * FROM t2 ORDER BY i;
7597 i
7598 1
7599 2
7600 3
7601 SELECT * FROM t3 ORDER BY id;
7602 id data
7603 1 1
7604 2 1
7605 3 3
7606 INSERT INTO t4 VALUES(sf1());
7607 SELECT * FROM t1 ORDER BY id;
7608 id data
7609 1 1
7610 2 1
7611 3 4
7612 4 5
7613 SELECT * FROM t2 ORDER BY i;
7614 i
7615 1
7616 2
7617 3
7618 SELECT * FROM t3 ORDER BY id;
7619 id data
7620 1 1
7621 1 1
7622 2 1
7623 2 1
7624 3 3
7625 3 3
7626 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
7627 SELECT * FROM t1 ORDER BY id;
7628 id data
7629 1 1
7630 2 1
7631 3 4
7632 4 5
7633 SELECT * FROM t2 ORDER BY i;
7634 i
7635 1
7636 2
7637 3
7638 SELECT * FROM t3 ORDER BY id;
7639 id data
7640 1 1
7641 1 1
7642 1 1
7643 1 1
7644 2 1
7645 2 1
7646 2 1
7647 2 1
7648 3 3
7649 3 3
7650 3 3
7651 3 3
7652 DELETE FROM t4 WHERE i = sf1() + 1;
7653 SELECT * FROM t1 ORDER BY id;
7654 id data
7655 1 1
7656 2 1
7657 3 4
7658 4 5
7659 SELECT * FROM t2 ORDER BY i;
7660 i
7661 1
7662 2
7663 3
7664 SELECT * FROM t3 ORDER BY id;
7665 id data
7666 1 1
7667 1 1
7668 1 1
7669 1 1
7670 1 1
7671 2 1
7672 2 1
7673 2 1
7674 2 1
7675 2 1
7676 3 3
7677 3 3
7678 3 3
7679 3 3
7680 3 3
7681 DROP FUNCTION sf1;
7682 DROP TABLE t1,t2,t3,t4;