Debian Codegraph @

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

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