Debian Codegraph @

mysql-test/suite/engines/funcs/t/sf_alter.test

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t1,t2,t3,t4;
3 DROP FUNCTION IF EXISTS sf1;
4 --enable_warnings
5 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
6 CREATE TABLE t2 (i INTEGER NULL);
7 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
8 CREATE TABLE t4 (i INTEGER NULL);
9 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
10 INSERT INTO t2 VALUES(1),(2),(3);
11 DELIMITER //;
12 CREATE FUNCTION sf1() RETURNS INTEGER
13 BEGIN
14 DECLARE done INT DEFAULT 0;
15 DECLARE a CHAR(16);
16 DECLARE b,c INT;
17 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
18 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
19 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
20 OPEN cur1;
21 OPEN cur2;
22 REPEAT
23 FETCH cur1 INTO a, b;
24 FETCH cur2 INTO c;
25 IF NOT done THEN
26 IF b < c THEN
27 INSERT INTO t3 VALUES (a,b);
28 ELSE
29 INSERT INTO t3 VALUES (a,c);
30 END IF;
31 END IF;
32 UNTIL done END REPEAT;
33 CLOSE cur1;
34 CLOSE cur2;
35 RETURN 0;
36 END//
37 DELIMITER ;//
38 ALTER FUNCTION sf1 #DET# ;
39 SELECT sf1();
40 SELECT * FROM t1 ORDER BY id;
41 SELECT * FROM t2 ORDER BY i;
42 SELECT * FROM t3 ORDER BY id;
43 INSERT INTO t4 VALUES(sf1());
44 SELECT * FROM t1 ORDER BY id;
45 SELECT * FROM t2 ORDER BY i;
46 SELECT * FROM t3 ORDER BY id;
47 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
48 SELECT * FROM t1 ORDER BY id;
49 SELECT * FROM t2 ORDER BY i;
50 SELECT * FROM t3 ORDER BY id;
51 DELETE FROM t4 WHERE i = sf1() + 1;
52 SELECT * FROM t1 ORDER BY id;
53 SELECT * FROM t2 ORDER BY i;
54 SELECT * FROM t3 ORDER BY id;
55 DROP FUNCTION sf1;
56 DROP TABLE t1,t2,t3,t4;
57 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
58 CREATE TABLE t2 (i INTEGER NULL);
59 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
60 CREATE TABLE t4 (i INTEGER NULL);
61 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
62 INSERT INTO t2 VALUES(1),(2),(3);
63 DELIMITER //;
64 CREATE FUNCTION sf1() RETURNS INTEGER
65 BEGIN
66 DECLARE done INT DEFAULT 0;
67 DECLARE a CHAR(16);
68 DECLARE b,c INT;
69 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
70 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
71 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
72 OPEN cur1;
73 OPEN cur2;
74 REPEAT
75 FETCH cur1 INTO a, b;
76 FETCH cur2 INTO c;
77 IF NOT done THEN
78 IF b < c THEN
79 INSERT INTO t3 VALUES (a,b);
80 ELSE
81 INSERT INTO t3 VALUES (a,c);
82 END IF;
83 END IF;
84 UNTIL done END REPEAT;
85 CLOSE cur1;
86 CLOSE cur2;
87 RETURN 0;
88 END//
89 DELIMITER ;//
90 ALTER FUNCTION sf1 #DET# CONTAINS SQL ;
91 SELECT sf1();
92 SELECT * FROM t1 ORDER BY id;
93 SELECT * FROM t2 ORDER BY i;
94 SELECT * FROM t3 ORDER BY id;
95 INSERT INTO t4 VALUES(sf1());
96 SELECT * FROM t1 ORDER BY id;
97 SELECT * FROM t2 ORDER BY i;
98 SELECT * FROM t3 ORDER BY id;
99 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
100 SELECT * FROM t1 ORDER BY id;
101 SELECT * FROM t2 ORDER BY i;
102 SELECT * FROM t3 ORDER BY id;
103 DELETE FROM t4 WHERE i = sf1() + 1;
104 SELECT * FROM t1 ORDER BY id;
105 SELECT * FROM t2 ORDER BY i;
106 SELECT * FROM t3 ORDER BY id;
107 DROP FUNCTION sf1;
108 DROP TABLE t1,t2,t3,t4;
109 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
110 CREATE TABLE t2 (i INTEGER NULL);
111 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
112 CREATE TABLE t4 (i INTEGER NULL);
113 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
114 INSERT INTO t2 VALUES(1),(2),(3);
115 DELIMITER //;
116 CREATE FUNCTION sf1() RETURNS INTEGER
117 BEGIN
118 DECLARE done INT DEFAULT 0;
119 DECLARE a CHAR(16);
120 DECLARE b,c INT;
121 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
122 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
123 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
124 OPEN cur1;
125 OPEN cur2;
126 REPEAT
127 FETCH cur1 INTO a, b;
128 FETCH cur2 INTO c;
129 IF NOT done THEN
130 IF b < c THEN
131 INSERT INTO t3 VALUES (a,b);
132 ELSE
133 INSERT INTO t3 VALUES (a,c);
134 END IF;
135 END IF;
136 UNTIL done END REPEAT;
137 CLOSE cur1;
138 CLOSE cur2;
139 RETURN 0;
140 END//
141 DELIMITER ;//
142 ALTER FUNCTION sf1 #DET# NO SQL ;
143 SELECT sf1();
144 SELECT * FROM t1 ORDER BY id;
145 SELECT * FROM t2 ORDER BY i;
146 SELECT * FROM t3 ORDER BY id;
147 INSERT INTO t4 VALUES(sf1());
148 SELECT * FROM t1 ORDER BY id;
149 SELECT * FROM t2 ORDER BY i;
150 SELECT * FROM t3 ORDER BY id;
151 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
152 SELECT * FROM t1 ORDER BY id;
153 SELECT * FROM t2 ORDER BY i;
154 SELECT * FROM t3 ORDER BY id;
155 DELETE FROM t4 WHERE i = sf1() + 1;
156 SELECT * FROM t1 ORDER BY id;
157 SELECT * FROM t2 ORDER BY i;
158 SELECT * FROM t3 ORDER BY id;
159 DROP FUNCTION sf1;
160 DROP TABLE t1,t2,t3,t4;
161 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
162 CREATE TABLE t2 (i INTEGER NULL);
163 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
164 CREATE TABLE t4 (i INTEGER NULL);
165 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
166 INSERT INTO t2 VALUES(1),(2),(3);
167 DELIMITER //;
168 CREATE FUNCTION sf1() RETURNS INTEGER
169 BEGIN
170 DECLARE done INT DEFAULT 0;
171 DECLARE a CHAR(16);
172 DECLARE b,c INT;
173 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
174 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
175 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
176 OPEN cur1;
177 OPEN cur2;
178 REPEAT
179 FETCH cur1 INTO a, b;
180 FETCH cur2 INTO c;
181 IF NOT done THEN
182 IF b < c THEN
183 INSERT INTO t3 VALUES (a,b);
184 ELSE
185 INSERT INTO t3 VALUES (a,c);
186 END IF;
187 END IF;
188 UNTIL done END REPEAT;
189 CLOSE cur1;
190 CLOSE cur2;
191 RETURN 0;
192 END//
193 DELIMITER ;//
194 ALTER FUNCTION sf1 #DET# READS SQL DATA ;
195 SELECT sf1();
196 SELECT * FROM t1 ORDER BY id;
197 SELECT * FROM t2 ORDER BY i;
198 SELECT * FROM t3 ORDER BY id;
199 INSERT INTO t4 VALUES(sf1());
200 SELECT * FROM t1 ORDER BY id;
201 SELECT * FROM t2 ORDER BY i;
202 SELECT * FROM t3 ORDER BY id;
203 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
204 SELECT * FROM t1 ORDER BY id;
205 SELECT * FROM t2 ORDER BY i;
206 SELECT * FROM t3 ORDER BY id;
207 DELETE FROM t4 WHERE i = sf1() + 1;
208 SELECT * FROM t1 ORDER BY id;
209 SELECT * FROM t2 ORDER BY i;
210 SELECT * FROM t3 ORDER BY id;
211 DROP FUNCTION sf1;
212 DROP TABLE t1,t2,t3,t4;
213 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
214 CREATE TABLE t2 (i INTEGER NULL);
215 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
216 CREATE TABLE t4 (i INTEGER NULL);
217 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
218 INSERT INTO t2 VALUES(1),(2),(3);
219 DELIMITER //;
220 CREATE FUNCTION sf1() RETURNS INTEGER
221 BEGIN
222 DECLARE done INT DEFAULT 0;
223 DECLARE a CHAR(16);
224 DECLARE b,c INT;
225 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
226 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
227 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
228 OPEN cur1;
229 OPEN cur2;
230 REPEAT
231 FETCH cur1 INTO a, b;
232 FETCH cur2 INTO c;
233 IF NOT done THEN
234 IF b < c THEN
235 INSERT INTO t3 VALUES (a,b);
236 ELSE
237 INSERT INTO t3 VALUES (a,c);
238 END IF;
239 END IF;
240 UNTIL done END REPEAT;
241 CLOSE cur1;
242 CLOSE cur2;
243 RETURN 0;
244 END//
245 DELIMITER ;//
246 ALTER FUNCTION sf1 #DET# MODIFIES SQL DATA ;
247 SELECT sf1();
248 SELECT * FROM t1 ORDER BY id;
249 SELECT * FROM t2 ORDER BY i;
250 SELECT * FROM t3 ORDER BY id;
251 INSERT INTO t4 VALUES(sf1());
252 SELECT * FROM t1 ORDER BY id;
253 SELECT * FROM t2 ORDER BY i;
254 SELECT * FROM t3 ORDER BY id;
255 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
256 SELECT * FROM t1 ORDER BY id;
257 SELECT * FROM t2 ORDER BY i;
258 SELECT * FROM t3 ORDER BY id;
259 DELETE FROM t4 WHERE i = sf1() + 1;
260 SELECT * FROM t1 ORDER BY id;
261 SELECT * FROM t2 ORDER BY i;
262 SELECT * FROM t3 ORDER BY id;
263 DROP FUNCTION sf1;
264 DROP TABLE t1,t2,t3,t4;
265 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
266 CREATE TABLE t2 (i INTEGER NULL);
267 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
268 CREATE TABLE t4 (i INTEGER NULL);
269 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
270 INSERT INTO t2 VALUES(1),(2),(3);
271 DELIMITER //;
272 CREATE FUNCTION sf1() RETURNS INTEGER
273 BEGIN
274 DECLARE done INT DEFAULT 0;
275 DECLARE a CHAR(16);
276 DECLARE b,c INT;
277 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
278 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
279 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
280 OPEN cur1;
281 OPEN cur2;
282 REPEAT
283 FETCH cur1 INTO a, b;
284 FETCH cur2 INTO c;
285 IF NOT done THEN
286 IF b < c THEN
287 INSERT INTO t3 VALUES (a,b);
288 ELSE
289 INSERT INTO t3 VALUES (a,c);
290 END IF;
291 END IF;
292 UNTIL done END REPEAT;
293 CLOSE cur1;
294 CLOSE cur2;
295 RETURN 0;
296 END//
297 DELIMITER ;//
298 ALTER FUNCTION sf1 #DET# COMMENT 'comment' ;
299 SELECT sf1();
300 SELECT * FROM t1 ORDER BY id;
301 SELECT * FROM t2 ORDER BY i;
302 SELECT * FROM t3 ORDER BY id;
303 INSERT INTO t4 VALUES(sf1());
304 SELECT * FROM t1 ORDER BY id;
305 SELECT * FROM t2 ORDER BY i;
306 SELECT * FROM t3 ORDER BY id;
307 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
308 SELECT * FROM t1 ORDER BY id;
309 SELECT * FROM t2 ORDER BY i;
310 SELECT * FROM t3 ORDER BY id;
311 DELETE FROM t4 WHERE i = sf1() + 1;
312 SELECT * FROM t1 ORDER BY id;
313 SELECT * FROM t2 ORDER BY i;
314 SELECT * FROM t3 ORDER BY id;
315 DROP FUNCTION sf1;
316 DROP TABLE t1,t2,t3,t4;
317 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
318 CREATE TABLE t2 (i INTEGER NULL);
319 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
320 CREATE TABLE t4 (i INTEGER NULL);
321 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
322 INSERT INTO t2 VALUES(1),(2),(3);
323 DELIMITER //;
324 CREATE FUNCTION sf1() RETURNS INTEGER
325 BEGIN
326 DECLARE done INT DEFAULT 0;
327 DECLARE a CHAR(16);
328 DECLARE b,c INT;
329 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
330 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
331 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
332 OPEN cur1;
333 OPEN cur2;
334 REPEAT
335 FETCH cur1 INTO a, b;
336 FETCH cur2 INTO c;
337 IF NOT done THEN
338 IF b < c THEN
339 INSERT INTO t3 VALUES (a,b);
340 ELSE
341 INSERT INTO t3 VALUES (a,c);
342 END IF;
343 END IF;
344 UNTIL done END REPEAT;
345 CLOSE cur1;
346 CLOSE cur2;
347 RETURN 0;
348 END//
349 DELIMITER ;//
350 ALTER FUNCTION sf1 #DET# CONTAINS SQL COMMENT 'comment' ;
351 SELECT sf1();
352 SELECT * FROM t1 ORDER BY id;
353 SELECT * FROM t2 ORDER BY i;
354 SELECT * FROM t3 ORDER BY id;
355 INSERT INTO t4 VALUES(sf1());
356 SELECT * FROM t1 ORDER BY id;
357 SELECT * FROM t2 ORDER BY i;
358 SELECT * FROM t3 ORDER BY id;
359 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
360 SELECT * FROM t1 ORDER BY id;
361 SELECT * FROM t2 ORDER BY i;
362 SELECT * FROM t3 ORDER BY id;
363 DELETE FROM t4 WHERE i = sf1() + 1;
364 SELECT * FROM t1 ORDER BY id;
365 SELECT * FROM t2 ORDER BY i;
366 SELECT * FROM t3 ORDER BY id;
367 DROP FUNCTION sf1;
368 DROP TABLE t1,t2,t3,t4;
369 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
370 CREATE TABLE t2 (i INTEGER NULL);
371 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
372 CREATE TABLE t4 (i INTEGER NULL);
373 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
374 INSERT INTO t2 VALUES(1),(2),(3);
375 DELIMITER //;
376 CREATE FUNCTION sf1() RETURNS INTEGER
377 BEGIN
378 DECLARE done INT DEFAULT 0;
379 DECLARE a CHAR(16);
380 DECLARE b,c INT;
381 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
382 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
383 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
384 OPEN cur1;
385 OPEN cur2;
386 REPEAT
387 FETCH cur1 INTO a, b;
388 FETCH cur2 INTO c;
389 IF NOT done THEN
390 IF b < c THEN
391 INSERT INTO t3 VALUES (a,b);
392 ELSE
393 INSERT INTO t3 VALUES (a,c);
394 END IF;
395 END IF;
396 UNTIL done END REPEAT;
397 CLOSE cur1;
398 CLOSE cur2;
399 RETURN 0;
400 END//
401 DELIMITER ;//
402 ALTER FUNCTION sf1 #DET# NO SQL COMMENT 'comment' ;
403 SELECT sf1();
404 SELECT * FROM t1 ORDER BY id;
405 SELECT * FROM t2 ORDER BY i;
406 SELECT * FROM t3 ORDER BY id;
407 INSERT INTO t4 VALUES(sf1());
408 SELECT * FROM t1 ORDER BY id;
409 SELECT * FROM t2 ORDER BY i;
410 SELECT * FROM t3 ORDER BY id;
411 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
412 SELECT * FROM t1 ORDER BY id;
413 SELECT * FROM t2 ORDER BY i;
414 SELECT * FROM t3 ORDER BY id;
415 DELETE FROM t4 WHERE i = sf1() + 1;
416 SELECT * FROM t1 ORDER BY id;
417 SELECT * FROM t2 ORDER BY i;
418 SELECT * FROM t3 ORDER BY id;
419 DROP FUNCTION sf1;
420 DROP TABLE t1,t2,t3,t4;
421 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
422 CREATE TABLE t2 (i INTEGER NULL);
423 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
424 CREATE TABLE t4 (i INTEGER NULL);
425 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
426 INSERT INTO t2 VALUES(1),(2),(3);
427 DELIMITER //;
428 CREATE FUNCTION sf1() RETURNS INTEGER
429 BEGIN
430 DECLARE done INT DEFAULT 0;
431 DECLARE a CHAR(16);
432 DECLARE b,c INT;
433 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
434 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
435 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
436 OPEN cur1;
437 OPEN cur2;
438 REPEAT
439 FETCH cur1 INTO a, b;
440 FETCH cur2 INTO c;
441 IF NOT done THEN
442 IF b < c THEN
443 INSERT INTO t3 VALUES (a,b);
444 ELSE
445 INSERT INTO t3 VALUES (a,c);
446 END IF;
447 END IF;
448 UNTIL done END REPEAT;
449 CLOSE cur1;
450 CLOSE cur2;
451 RETURN 0;
452 END//
453 DELIMITER ;//
454 ALTER FUNCTION sf1 #DET# READS SQL DATA COMMENT 'comment' ;
455 SELECT sf1();
456 SELECT * FROM t1 ORDER BY id;
457 SELECT * FROM t2 ORDER BY i;
458 SELECT * FROM t3 ORDER BY id;
459 INSERT INTO t4 VALUES(sf1());
460 SELECT * FROM t1 ORDER BY id;
461 SELECT * FROM t2 ORDER BY i;
462 SELECT * FROM t3 ORDER BY id;
463 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
464 SELECT * FROM t1 ORDER BY id;
465 SELECT * FROM t2 ORDER BY i;
466 SELECT * FROM t3 ORDER BY id;
467 DELETE FROM t4 WHERE i = sf1() + 1;
468 SELECT * FROM t1 ORDER BY id;
469 SELECT * FROM t2 ORDER BY i;
470 SELECT * FROM t3 ORDER BY id;
471 DROP FUNCTION sf1;
472 DROP TABLE t1,t2,t3,t4;
473 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
474 CREATE TABLE t2 (i INTEGER NULL);
475 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
476 CREATE TABLE t4 (i INTEGER NULL);
477 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
478 INSERT INTO t2 VALUES(1),(2),(3);
479 DELIMITER //;
480 CREATE FUNCTION sf1() RETURNS INTEGER
481 BEGIN
482 DECLARE done INT DEFAULT 0;
483 DECLARE a CHAR(16);
484 DECLARE b,c INT;
485 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
486 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
487 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
488 OPEN cur1;
489 OPEN cur2;
490 REPEAT
491 FETCH cur1 INTO a, b;
492 FETCH cur2 INTO c;
493 IF NOT done THEN
494 IF b < c THEN
495 INSERT INTO t3 VALUES (a,b);
496 ELSE
497 INSERT INTO t3 VALUES (a,c);
498 END IF;
499 END IF;
500 UNTIL done END REPEAT;
501 CLOSE cur1;
502 CLOSE cur2;
503 RETURN 0;
504 END//
505 DELIMITER ;//
506 ALTER FUNCTION sf1 #DET# MODIFIES SQL DATA COMMENT 'comment' ;
507 SELECT sf1();
508 SELECT * FROM t1 ORDER BY id;
509 SELECT * FROM t2 ORDER BY i;
510 SELECT * FROM t3 ORDER BY id;
511 INSERT INTO t4 VALUES(sf1());
512 SELECT * FROM t1 ORDER BY id;
513 SELECT * FROM t2 ORDER BY i;
514 SELECT * FROM t3 ORDER BY id;
515 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
516 SELECT * FROM t1 ORDER BY id;
517 SELECT * FROM t2 ORDER BY i;
518 SELECT * FROM t3 ORDER BY id;
519 DELETE FROM t4 WHERE i = sf1() + 1;
520 SELECT * FROM t1 ORDER BY id;
521 SELECT * FROM t2 ORDER BY i;
522 SELECT * FROM t3 ORDER BY id;
523 DROP FUNCTION sf1;
524 DROP TABLE t1,t2,t3,t4;
525 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
526 CREATE TABLE t2 (i INTEGER NULL);
527 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
528 CREATE TABLE t4 (i INTEGER NULL);
529 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
530 INSERT INTO t2 VALUES(1),(2),(3);
531 DELIMITER //;
532 CREATE FUNCTION sf1() RETURNS INTEGER
533 BEGIN
534 DECLARE done INT DEFAULT 0;
535 DECLARE a CHAR(16);
536 DECLARE b,c INT;
537 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
538 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
539 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
540 OPEN cur1;
541 OPEN cur2;
542 REPEAT
543 FETCH cur1 INTO a, b;
544 FETCH cur2 INTO c;
545 IF NOT done THEN
546 IF b < c THEN
547 INSERT INTO t3 VALUES (a,b);
548 ELSE
549 INSERT INTO t3 VALUES (a,c);
550 END IF;
551 END IF;
552 UNTIL done END REPEAT;
553 CLOSE cur1;
554 CLOSE cur2;
555 RETURN 0;
556 END//
557 DELIMITER ;//
558 ALTER FUNCTION sf1 LANGUAGE SQL #DET# ;
559 SELECT sf1();
560 SELECT * FROM t1 ORDER BY id;
561 SELECT * FROM t2 ORDER BY i;
562 SELECT * FROM t3 ORDER BY id;
563 INSERT INTO t4 VALUES(sf1());
564 SELECT * FROM t1 ORDER BY id;
565 SELECT * FROM t2 ORDER BY i;
566 SELECT * FROM t3 ORDER BY id;
567 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
568 SELECT * FROM t1 ORDER BY id;
569 SELECT * FROM t2 ORDER BY i;
570 SELECT * FROM t3 ORDER BY id;
571 DELETE FROM t4 WHERE i = sf1() + 1;
572 SELECT * FROM t1 ORDER BY id;
573 SELECT * FROM t2 ORDER BY i;
574 SELECT * FROM t3 ORDER BY id;
575 DROP FUNCTION sf1;
576 DROP TABLE t1,t2,t3,t4;
577 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
578 CREATE TABLE t2 (i INTEGER NULL);
579 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
580 CREATE TABLE t4 (i INTEGER NULL);
581 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
582 INSERT INTO t2 VALUES(1),(2),(3);
583 DELIMITER //;
584 CREATE FUNCTION sf1() RETURNS INTEGER
585 BEGIN
586 DECLARE done INT DEFAULT 0;
587 DECLARE a CHAR(16);
588 DECLARE b,c INT;
589 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
590 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
591 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
592 OPEN cur1;
593 OPEN cur2;
594 REPEAT
595 FETCH cur1 INTO a, b;
596 FETCH cur2 INTO c;
597 IF NOT done THEN
598 IF b < c THEN
599 INSERT INTO t3 VALUES (a,b);
600 ELSE
601 INSERT INTO t3 VALUES (a,c);
602 END IF;
603 END IF;
604 UNTIL done END REPEAT;
605 CLOSE cur1;
606 CLOSE cur2;
607 RETURN 0;
608 END//
609 DELIMITER ;//
610 ALTER FUNCTION sf1 LANGUAGE SQL #DET# CONTAINS SQL ;
611 SELECT sf1();
612 SELECT * FROM t1 ORDER BY id;
613 SELECT * FROM t2 ORDER BY i;
614 SELECT * FROM t3 ORDER BY id;
615 INSERT INTO t4 VALUES(sf1());
616 SELECT * FROM t1 ORDER BY id;
617 SELECT * FROM t2 ORDER BY i;
618 SELECT * FROM t3 ORDER BY id;
619 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
620 SELECT * FROM t1 ORDER BY id;
621 SELECT * FROM t2 ORDER BY i;
622 SELECT * FROM t3 ORDER BY id;
623 DELETE FROM t4 WHERE i = sf1() + 1;
624 SELECT * FROM t1 ORDER BY id;
625 SELECT * FROM t2 ORDER BY i;
626 SELECT * FROM t3 ORDER BY id;
627 DROP FUNCTION sf1;
628 DROP TABLE t1,t2,t3,t4;
629 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
630 CREATE TABLE t2 (i INTEGER NULL);
631 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
632 CREATE TABLE t4 (i INTEGER NULL);
633 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
634 INSERT INTO t2 VALUES(1),(2),(3);
635 DELIMITER //;
636 CREATE FUNCTION sf1() RETURNS INTEGER
637 BEGIN
638 DECLARE done INT DEFAULT 0;
639 DECLARE a CHAR(16);
640 DECLARE b,c INT;
641 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
642 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
643 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
644 OPEN cur1;
645 OPEN cur2;
646 REPEAT
647 FETCH cur1 INTO a, b;
648 FETCH cur2 INTO c;
649 IF NOT done THEN
650 IF b < c THEN
651 INSERT INTO t3 VALUES (a,b);
652 ELSE
653 INSERT INTO t3 VALUES (a,c);
654 END IF;
655 END IF;
656 UNTIL done END REPEAT;
657 CLOSE cur1;
658 CLOSE cur2;
659 RETURN 0;
660 END//
661 DELIMITER ;//
662 ALTER FUNCTION sf1 LANGUAGE SQL #DET# NO SQL ;
663 SELECT sf1();
664 SELECT * FROM t1 ORDER BY id;
665 SELECT * FROM t2 ORDER BY i;
666 SELECT * FROM t3 ORDER BY id;
667 INSERT INTO t4 VALUES(sf1());
668 SELECT * FROM t1 ORDER BY id;
669 SELECT * FROM t2 ORDER BY i;
670 SELECT * FROM t3 ORDER BY id;
671 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
672 SELECT * FROM t1 ORDER BY id;
673 SELECT * FROM t2 ORDER BY i;
674 SELECT * FROM t3 ORDER BY id;
675 DELETE FROM t4 WHERE i = sf1() + 1;
676 SELECT * FROM t1 ORDER BY id;
677 SELECT * FROM t2 ORDER BY i;
678 SELECT * FROM t3 ORDER BY id;
679 DROP FUNCTION sf1;
680 DROP TABLE t1,t2,t3,t4;
681 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
682 CREATE TABLE t2 (i INTEGER NULL);
683 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
684 CREATE TABLE t4 (i INTEGER NULL);
685 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
686 INSERT INTO t2 VALUES(1),(2),(3);
687 DELIMITER //;
688 CREATE FUNCTION sf1() RETURNS INTEGER
689 BEGIN
690 DECLARE done INT DEFAULT 0;
691 DECLARE a CHAR(16);
692 DECLARE b,c INT;
693 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
694 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
695 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
696 OPEN cur1;
697 OPEN cur2;
698 REPEAT
699 FETCH cur1 INTO a, b;
700 FETCH cur2 INTO c;
701 IF NOT done THEN
702 IF b < c THEN
703 INSERT INTO t3 VALUES (a,b);
704 ELSE
705 INSERT INTO t3 VALUES (a,c);
706 END IF;
707 END IF;
708 UNTIL done END REPEAT;
709 CLOSE cur1;
710 CLOSE cur2;
711 RETURN 0;
712 END//
713 DELIMITER ;//
714 ALTER FUNCTION sf1 LANGUAGE SQL #DET# READS SQL DATA ;
715 SELECT sf1();
716 SELECT * FROM t1 ORDER BY id;
717 SELECT * FROM t2 ORDER BY i;
718 SELECT * FROM t3 ORDER BY id;
719 INSERT INTO t4 VALUES(sf1());
720 SELECT * FROM t1 ORDER BY id;
721 SELECT * FROM t2 ORDER BY i;
722 SELECT * FROM t3 ORDER BY id;
723 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
724 SELECT * FROM t1 ORDER BY id;
725 SELECT * FROM t2 ORDER BY i;
726 SELECT * FROM t3 ORDER BY id;
727 DELETE FROM t4 WHERE i = sf1() + 1;
728 SELECT * FROM t1 ORDER BY id;
729 SELECT * FROM t2 ORDER BY i;
730 SELECT * FROM t3 ORDER BY id;
731 DROP FUNCTION sf1;
732 DROP TABLE t1,t2,t3,t4;
733 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
734 CREATE TABLE t2 (i INTEGER NULL);
735 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
736 CREATE TABLE t4 (i INTEGER NULL);
737 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
738 INSERT INTO t2 VALUES(1),(2),(3);
739 DELIMITER //;
740 CREATE FUNCTION sf1() RETURNS INTEGER
741 BEGIN
742 DECLARE done INT DEFAULT 0;
743 DECLARE a CHAR(16);
744 DECLARE b,c INT;
745 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
746 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
747 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
748 OPEN cur1;
749 OPEN cur2;
750 REPEAT
751 FETCH cur1 INTO a, b;
752 FETCH cur2 INTO c;
753 IF NOT done THEN
754 IF b < c THEN
755 INSERT INTO t3 VALUES (a,b);
756 ELSE
757 INSERT INTO t3 VALUES (a,c);
758 END IF;
759 END IF;
760 UNTIL done END REPEAT;
761 CLOSE cur1;
762 CLOSE cur2;
763 RETURN 0;
764 END//
765 DELIMITER ;//
766 ALTER FUNCTION sf1 LANGUAGE SQL #DET# MODIFIES SQL DATA ;
767 SELECT sf1();
768 SELECT * FROM t1 ORDER BY id;
769 SELECT * FROM t2 ORDER BY i;
770 SELECT * FROM t3 ORDER BY id;
771 INSERT INTO t4 VALUES(sf1());
772 SELECT * FROM t1 ORDER BY id;
773 SELECT * FROM t2 ORDER BY i;
774 SELECT * FROM t3 ORDER BY id;
775 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
776 SELECT * FROM t1 ORDER BY id;
777 SELECT * FROM t2 ORDER BY i;
778 SELECT * FROM t3 ORDER BY id;
779 DELETE FROM t4 WHERE i = sf1() + 1;
780 SELECT * FROM t1 ORDER BY id;
781 SELECT * FROM t2 ORDER BY i;
782 SELECT * FROM t3 ORDER BY id;
783 DROP FUNCTION sf1;
784 DROP TABLE t1,t2,t3,t4;
785 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
786 CREATE TABLE t2 (i INTEGER NULL);
787 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
788 CREATE TABLE t4 (i INTEGER NULL);
789 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
790 INSERT INTO t2 VALUES(1),(2),(3);
791 DELIMITER //;
792 CREATE FUNCTION sf1() RETURNS INTEGER
793 BEGIN
794 DECLARE done INT DEFAULT 0;
795 DECLARE a CHAR(16);
796 DECLARE b,c INT;
797 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
798 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
799 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
800 OPEN cur1;
801 OPEN cur2;
802 REPEAT
803 FETCH cur1 INTO a, b;
804 FETCH cur2 INTO c;
805 IF NOT done THEN
806 IF b < c THEN
807 INSERT INTO t3 VALUES (a,b);
808 ELSE
809 INSERT INTO t3 VALUES (a,c);
810 END IF;
811 END IF;
812 UNTIL done END REPEAT;
813 CLOSE cur1;
814 CLOSE cur2;
815 RETURN 0;
816 END//
817 DELIMITER ;//
818 ALTER FUNCTION sf1 LANGUAGE SQL #DET# COMMENT 'comment' ;
819 SELECT sf1();
820 SELECT * FROM t1 ORDER BY id;
821 SELECT * FROM t2 ORDER BY i;
822 SELECT * FROM t3 ORDER BY id;
823 INSERT INTO t4 VALUES(sf1());
824 SELECT * FROM t1 ORDER BY id;
825 SELECT * FROM t2 ORDER BY i;
826 SELECT * FROM t3 ORDER BY id;
827 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
828 SELECT * FROM t1 ORDER BY id;
829 SELECT * FROM t2 ORDER BY i;
830 SELECT * FROM t3 ORDER BY id;
831 DELETE FROM t4 WHERE i = sf1() + 1;
832 SELECT * FROM t1 ORDER BY id;
833 SELECT * FROM t2 ORDER BY i;
834 SELECT * FROM t3 ORDER BY id;
835 DROP FUNCTION sf1;
836 DROP TABLE t1,t2,t3,t4;
837 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
838 CREATE TABLE t2 (i INTEGER NULL);
839 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
840 CREATE TABLE t4 (i INTEGER NULL);
841 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
842 INSERT INTO t2 VALUES(1),(2),(3);
843 DELIMITER //;
844 CREATE FUNCTION sf1() RETURNS INTEGER
845 BEGIN
846 DECLARE done INT DEFAULT 0;
847 DECLARE a CHAR(16);
848 DECLARE b,c INT;
849 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
850 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
851 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
852 OPEN cur1;
853 OPEN cur2;
854 REPEAT
855 FETCH cur1 INTO a, b;
856 FETCH cur2 INTO c;
857 IF NOT done THEN
858 IF b < c THEN
859 INSERT INTO t3 VALUES (a,b);
860 ELSE
861 INSERT INTO t3 VALUES (a,c);
862 END IF;
863 END IF;
864 UNTIL done END REPEAT;
865 CLOSE cur1;
866 CLOSE cur2;
867 RETURN 0;
868 END//
869 DELIMITER ;//
870 ALTER FUNCTION sf1 LANGUAGE SQL #DET# CONTAINS SQL COMMENT 'comment' ;
871 SELECT sf1();
872 SELECT * FROM t1 ORDER BY id;
873 SELECT * FROM t2 ORDER BY i;
874 SELECT * FROM t3 ORDER BY id;
875 INSERT INTO t4 VALUES(sf1());
876 SELECT * FROM t1 ORDER BY id;
877 SELECT * FROM t2 ORDER BY i;
878 SELECT * FROM t3 ORDER BY id;
879 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
880 SELECT * FROM t1 ORDER BY id;
881 SELECT * FROM t2 ORDER BY i;
882 SELECT * FROM t3 ORDER BY id;
883 DELETE FROM t4 WHERE i = sf1() + 1;
884 SELECT * FROM t1 ORDER BY id;
885 SELECT * FROM t2 ORDER BY i;
886 SELECT * FROM t3 ORDER BY id;
887 DROP FUNCTION sf1;
888 DROP TABLE t1,t2,t3,t4;
889 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
890 CREATE TABLE t2 (i INTEGER NULL);
891 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
892 CREATE TABLE t4 (i INTEGER NULL);
893 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
894 INSERT INTO t2 VALUES(1),(2),(3);
895 DELIMITER //;
896 CREATE FUNCTION sf1() RETURNS INTEGER
897 BEGIN
898 DECLARE done INT DEFAULT 0;
899 DECLARE a CHAR(16);
900 DECLARE b,c INT;
901 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
902 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
903 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
904 OPEN cur1;
905 OPEN cur2;
906 REPEAT
907 FETCH cur1 INTO a, b;
908 FETCH cur2 INTO c;
909 IF NOT done THEN
910 IF b < c THEN
911 INSERT INTO t3 VALUES (a,b);
912 ELSE
913 INSERT INTO t3 VALUES (a,c);
914 END IF;
915 END IF;
916 UNTIL done END REPEAT;
917 CLOSE cur1;
918 CLOSE cur2;
919 RETURN 0;
920 END//
921 DELIMITER ;//
922 ALTER FUNCTION sf1 LANGUAGE SQL #DET# NO SQL COMMENT 'comment' ;
923 SELECT sf1();
924 SELECT * FROM t1 ORDER BY id;
925 SELECT * FROM t2 ORDER BY i;
926 SELECT * FROM t3 ORDER BY id;
927 INSERT INTO t4 VALUES(sf1());
928 SELECT * FROM t1 ORDER BY id;
929 SELECT * FROM t2 ORDER BY i;
930 SELECT * FROM t3 ORDER BY id;
931 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
932 SELECT * FROM t1 ORDER BY id;
933 SELECT * FROM t2 ORDER BY i;
934 SELECT * FROM t3 ORDER BY id;
935 DELETE FROM t4 WHERE i = sf1() + 1;
936 SELECT * FROM t1 ORDER BY id;
937 SELECT * FROM t2 ORDER BY i;
938 SELECT * FROM t3 ORDER BY id;
939 DROP FUNCTION sf1;
940 DROP TABLE t1,t2,t3,t4;
941 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
942 CREATE TABLE t2 (i INTEGER NULL);
943 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
944 CREATE TABLE t4 (i INTEGER NULL);
945 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
946 INSERT INTO t2 VALUES(1),(2),(3);
947 DELIMITER //;
948 CREATE FUNCTION sf1() RETURNS INTEGER
949 BEGIN
950 DECLARE done INT DEFAULT 0;
951 DECLARE a CHAR(16);
952 DECLARE b,c INT;
953 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
954 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
955 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
956 OPEN cur1;
957 OPEN cur2;
958 REPEAT
959 FETCH cur1 INTO a, b;
960 FETCH cur2 INTO c;
961 IF NOT done THEN
962 IF b < c THEN
963 INSERT INTO t3 VALUES (a,b);
964 ELSE
965 INSERT INTO t3 VALUES (a,c);
966 END IF;
967 END IF;
968 UNTIL done END REPEAT;
969 CLOSE cur1;
970 CLOSE cur2;
971 RETURN 0;
972 END//
973 DELIMITER ;//
974 ALTER FUNCTION sf1 LANGUAGE SQL #DET# READS SQL DATA COMMENT 'comment' ;
975 SELECT sf1();
976 SELECT * FROM t1 ORDER BY id;
977 SELECT * FROM t2 ORDER BY i;
978 SELECT * FROM t3 ORDER BY id;
979 INSERT INTO t4 VALUES(sf1());
980 SELECT * FROM t1 ORDER BY id;
981 SELECT * FROM t2 ORDER BY i;
982 SELECT * FROM t3 ORDER BY id;
983 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
984 SELECT * FROM t1 ORDER BY id;
985 SELECT * FROM t2 ORDER BY i;
986 SELECT * FROM t3 ORDER BY id;
987 DELETE FROM t4 WHERE i = sf1() + 1;
988 SELECT * FROM t1 ORDER BY id;
989 SELECT * FROM t2 ORDER BY i;
990 SELECT * FROM t3 ORDER BY id;
991 DROP FUNCTION sf1;
992 DROP TABLE t1,t2,t3,t4;
993 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
994 CREATE TABLE t2 (i INTEGER NULL);
995 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
996 CREATE TABLE t4 (i INTEGER NULL);
997 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
998 INSERT INTO t2 VALUES(1),(2),(3);
999 DELIMITER //;
1000 CREATE FUNCTION sf1() RETURNS INTEGER
1001 BEGIN
1002 DECLARE done INT DEFAULT 0;
1003 DECLARE a CHAR(16);
1004 DECLARE b,c INT;
1005 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1006 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1007 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1008 OPEN cur1;
1009 OPEN cur2;
1010 REPEAT
1011 FETCH cur1 INTO a, b;
1012 FETCH cur2 INTO c;
1013 IF NOT done THEN
1014 IF b < c THEN
1015 INSERT INTO t3 VALUES (a,b);
1016 ELSE
1017 INSERT INTO t3 VALUES (a,c);
1018 END IF;
1019 END IF;
1020 UNTIL done END REPEAT;
1021 CLOSE cur1;
1022 CLOSE cur2;
1023 RETURN 0;
1024 END//
1025 DELIMITER ;//
1026 ALTER FUNCTION sf1 LANGUAGE SQL #DET# MODIFIES SQL DATA COMMENT 'comment' ;
1027 SELECT sf1();
1028 SELECT * FROM t1 ORDER BY id;
1029 SELECT * FROM t2 ORDER BY i;
1030 SELECT * FROM t3 ORDER BY id;
1031 INSERT INTO t4 VALUES(sf1());
1032 SELECT * FROM t1 ORDER BY id;
1033 SELECT * FROM t2 ORDER BY i;
1034 SELECT * FROM t3 ORDER BY id;
1035 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1036 SELECT * FROM t1 ORDER BY id;
1037 SELECT * FROM t2 ORDER BY i;
1038 SELECT * FROM t3 ORDER BY id;
1039 DELETE FROM t4 WHERE i = sf1() + 1;
1040 SELECT * FROM t1 ORDER BY id;
1041 SELECT * FROM t2 ORDER BY i;
1042 SELECT * FROM t3 ORDER BY id;
1043 DROP FUNCTION sf1;
1044 DROP TABLE t1,t2,t3,t4;
1045 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1046 CREATE TABLE t2 (i INTEGER NULL);
1047 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1048 CREATE TABLE t4 (i INTEGER NULL);
1049 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1050 INSERT INTO t2 VALUES(1),(2),(3);
1051 DELIMITER //;
1052 CREATE FUNCTION sf1() RETURNS INTEGER
1053 BEGIN
1054 DECLARE done INT DEFAULT 0;
1055 DECLARE a CHAR(16);
1056 DECLARE b,c INT;
1057 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1058 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1059 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1060 OPEN cur1;
1061 OPEN cur2;
1062 REPEAT
1063 FETCH cur1 INTO a, b;
1064 FETCH cur2 INTO c;
1065 IF NOT done THEN
1066 IF b < c THEN
1067 INSERT INTO t3 VALUES (a,b);
1068 ELSE
1069 INSERT INTO t3 VALUES (a,c);
1070 END IF;
1071 END IF;
1072 UNTIL done END REPEAT;
1073 CLOSE cur1;
1074 CLOSE cur2;
1075 RETURN 0;
1076 END//
1077 DELIMITER ;//
1078 ALTER FUNCTION sf1 #DET# SQL SECURITY INVOKER ;
1079 SELECT sf1();
1080 SELECT * FROM t1 ORDER BY id;
1081 SELECT * FROM t2 ORDER BY i;
1082 SELECT * FROM t3 ORDER BY id;
1083 INSERT INTO t4 VALUES(sf1());
1084 SELECT * FROM t1 ORDER BY id;
1085 SELECT * FROM t2 ORDER BY i;
1086 SELECT * FROM t3 ORDER BY id;
1087 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1088 SELECT * FROM t1 ORDER BY id;
1089 SELECT * FROM t2 ORDER BY i;
1090 SELECT * FROM t3 ORDER BY id;
1091 DELETE FROM t4 WHERE i = sf1() + 1;
1092 SELECT * FROM t1 ORDER BY id;
1093 SELECT * FROM t2 ORDER BY i;
1094 SELECT * FROM t3 ORDER BY id;
1095 DROP FUNCTION sf1;
1096 DROP TABLE t1,t2,t3,t4;
1097 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1098 CREATE TABLE t2 (i INTEGER NULL);
1099 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1100 CREATE TABLE t4 (i INTEGER NULL);
1101 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1102 INSERT INTO t2 VALUES(1),(2),(3);
1103 DELIMITER //;
1104 CREATE FUNCTION sf1() RETURNS INTEGER
1105 BEGIN
1106 DECLARE done INT DEFAULT 0;
1107 DECLARE a CHAR(16);
1108 DECLARE b,c INT;
1109 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1110 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1111 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1112 OPEN cur1;
1113 OPEN cur2;
1114 REPEAT
1115 FETCH cur1 INTO a, b;
1116 FETCH cur2 INTO c;
1117 IF NOT done THEN
1118 IF b < c THEN
1119 INSERT INTO t3 VALUES (a,b);
1120 ELSE
1121 INSERT INTO t3 VALUES (a,c);
1122 END IF;
1123 END IF;
1124 UNTIL done END REPEAT;
1125 CLOSE cur1;
1126 CLOSE cur2;
1127 RETURN 0;
1128 END//
1129 DELIMITER ;//
1130 ALTER FUNCTION sf1 #DET# CONTAINS SQL SQL SECURITY INVOKER ;
1131 SELECT sf1();
1132 SELECT * FROM t1 ORDER BY id;
1133 SELECT * FROM t2 ORDER BY i;
1134 SELECT * FROM t3 ORDER BY id;
1135 INSERT INTO t4 VALUES(sf1());
1136 SELECT * FROM t1 ORDER BY id;
1137 SELECT * FROM t2 ORDER BY i;
1138 SELECT * FROM t3 ORDER BY id;
1139 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1140 SELECT * FROM t1 ORDER BY id;
1141 SELECT * FROM t2 ORDER BY i;
1142 SELECT * FROM t3 ORDER BY id;
1143 DELETE FROM t4 WHERE i = sf1() + 1;
1144 SELECT * FROM t1 ORDER BY id;
1145 SELECT * FROM t2 ORDER BY i;
1146 SELECT * FROM t3 ORDER BY id;
1147 DROP FUNCTION sf1;
1148 DROP TABLE t1,t2,t3,t4;
1149 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1150 CREATE TABLE t2 (i INTEGER NULL);
1151 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1152 CREATE TABLE t4 (i INTEGER NULL);
1153 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1154 INSERT INTO t2 VALUES(1),(2),(3);
1155 DELIMITER //;
1156 CREATE FUNCTION sf1() RETURNS INTEGER
1157 BEGIN
1158 DECLARE done INT DEFAULT 0;
1159 DECLARE a CHAR(16);
1160 DECLARE b,c INT;
1161 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1162 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1163 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1164 OPEN cur1;
1165 OPEN cur2;
1166 REPEAT
1167 FETCH cur1 INTO a, b;
1168 FETCH cur2 INTO c;
1169 IF NOT done THEN
1170 IF b < c THEN
1171 INSERT INTO t3 VALUES (a,b);
1172 ELSE
1173 INSERT INTO t3 VALUES (a,c);
1174 END IF;
1175 END IF;
1176 UNTIL done END REPEAT;
1177 CLOSE cur1;
1178 CLOSE cur2;
1179 RETURN 0;
1180 END//
1181 DELIMITER ;//
1182 ALTER FUNCTION sf1 #DET# NO SQL SQL SECURITY INVOKER ;
1183 SELECT sf1();
1184 SELECT * FROM t1 ORDER BY id;
1185 SELECT * FROM t2 ORDER BY i;
1186 SELECT * FROM t3 ORDER BY id;
1187 INSERT INTO t4 VALUES(sf1());
1188 SELECT * FROM t1 ORDER BY id;
1189 SELECT * FROM t2 ORDER BY i;
1190 SELECT * FROM t3 ORDER BY id;
1191 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1192 SELECT * FROM t1 ORDER BY id;
1193 SELECT * FROM t2 ORDER BY i;
1194 SELECT * FROM t3 ORDER BY id;
1195 DELETE FROM t4 WHERE i = sf1() + 1;
1196 SELECT * FROM t1 ORDER BY id;
1197 SELECT * FROM t2 ORDER BY i;
1198 SELECT * FROM t3 ORDER BY id;
1199 DROP FUNCTION sf1;
1200 DROP TABLE t1,t2,t3,t4;
1201 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1202 CREATE TABLE t2 (i INTEGER NULL);
1203 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1204 CREATE TABLE t4 (i INTEGER NULL);
1205 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1206 INSERT INTO t2 VALUES(1),(2),(3);
1207 DELIMITER //;
1208 CREATE FUNCTION sf1() RETURNS INTEGER
1209 BEGIN
1210 DECLARE done INT DEFAULT 0;
1211 DECLARE a CHAR(16);
1212 DECLARE b,c INT;
1213 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1214 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1215 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1216 OPEN cur1;
1217 OPEN cur2;
1218 REPEAT
1219 FETCH cur1 INTO a, b;
1220 FETCH cur2 INTO c;
1221 IF NOT done THEN
1222 IF b < c THEN
1223 INSERT INTO t3 VALUES (a,b);
1224 ELSE
1225 INSERT INTO t3 VALUES (a,c);
1226 END IF;
1227 END IF;
1228 UNTIL done END REPEAT;
1229 CLOSE cur1;
1230 CLOSE cur2;
1231 RETURN 0;
1232 END//
1233 DELIMITER ;//
1234 ALTER FUNCTION sf1 #DET# READS SQL DATA SQL SECURITY INVOKER ;
1235 SELECT sf1();
1236 SELECT * FROM t1 ORDER BY id;
1237 SELECT * FROM t2 ORDER BY i;
1238 SELECT * FROM t3 ORDER BY id;
1239 INSERT INTO t4 VALUES(sf1());
1240 SELECT * FROM t1 ORDER BY id;
1241 SELECT * FROM t2 ORDER BY i;
1242 SELECT * FROM t3 ORDER BY id;
1243 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1244 SELECT * FROM t1 ORDER BY id;
1245 SELECT * FROM t2 ORDER BY i;
1246 SELECT * FROM t3 ORDER BY id;
1247 DELETE FROM t4 WHERE i = sf1() + 1;
1248 SELECT * FROM t1 ORDER BY id;
1249 SELECT * FROM t2 ORDER BY i;
1250 SELECT * FROM t3 ORDER BY id;
1251 DROP FUNCTION sf1;
1252 DROP TABLE t1,t2,t3,t4;
1253 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1254 CREATE TABLE t2 (i INTEGER NULL);
1255 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1256 CREATE TABLE t4 (i INTEGER NULL);
1257 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1258 INSERT INTO t2 VALUES(1),(2),(3);
1259 DELIMITER //;
1260 CREATE FUNCTION sf1() RETURNS INTEGER
1261 BEGIN
1262 DECLARE done INT DEFAULT 0;
1263 DECLARE a CHAR(16);
1264 DECLARE b,c INT;
1265 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1266 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1267 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1268 OPEN cur1;
1269 OPEN cur2;
1270 REPEAT
1271 FETCH cur1 INTO a, b;
1272 FETCH cur2 INTO c;
1273 IF NOT done THEN
1274 IF b < c THEN
1275 INSERT INTO t3 VALUES (a,b);
1276 ELSE
1277 INSERT INTO t3 VALUES (a,c);
1278 END IF;
1279 END IF;
1280 UNTIL done END REPEAT;
1281 CLOSE cur1;
1282 CLOSE cur2;
1283 RETURN 0;
1284 END//
1285 DELIMITER ;//
1286 ALTER FUNCTION sf1 #DET# MODIFIES SQL DATA SQL SECURITY INVOKER ;
1287 SELECT sf1();
1288 SELECT * FROM t1 ORDER BY id;
1289 SELECT * FROM t2 ORDER BY i;
1290 SELECT * FROM t3 ORDER BY id;
1291 INSERT INTO t4 VALUES(sf1());
1292 SELECT * FROM t1 ORDER BY id;
1293 SELECT * FROM t2 ORDER BY i;
1294 SELECT * FROM t3 ORDER BY id;
1295 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1296 SELECT * FROM t1 ORDER BY id;
1297 SELECT * FROM t2 ORDER BY i;
1298 SELECT * FROM t3 ORDER BY id;
1299 DELETE FROM t4 WHERE i = sf1() + 1;
1300 SELECT * FROM t1 ORDER BY id;
1301 SELECT * FROM t2 ORDER BY i;
1302 SELECT * FROM t3 ORDER BY id;
1303 DROP FUNCTION sf1;
1304 DROP TABLE t1,t2,t3,t4;
1305 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1306 CREATE TABLE t2 (i INTEGER NULL);
1307 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1308 CREATE TABLE t4 (i INTEGER NULL);
1309 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1310 INSERT INTO t2 VALUES(1),(2),(3);
1311 DELIMITER //;
1312 CREATE FUNCTION sf1() RETURNS INTEGER
1313 BEGIN
1314 DECLARE done INT DEFAULT 0;
1315 DECLARE a CHAR(16);
1316 DECLARE b,c INT;
1317 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1318 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1319 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1320 OPEN cur1;
1321 OPEN cur2;
1322 REPEAT
1323 FETCH cur1 INTO a, b;
1324 FETCH cur2 INTO c;
1325 IF NOT done THEN
1326 IF b < c THEN
1327 INSERT INTO t3 VALUES (a,b);
1328 ELSE
1329 INSERT INTO t3 VALUES (a,c);
1330 END IF;
1331 END IF;
1332 UNTIL done END REPEAT;
1333 CLOSE cur1;
1334 CLOSE cur2;
1335 RETURN 0;
1336 END//
1337 DELIMITER ;//
1338 ALTER FUNCTION sf1 #DET# SQL SECURITY INVOKER COMMENT 'comment' ;
1339 SELECT sf1();
1340 SELECT * FROM t1 ORDER BY id;
1341 SELECT * FROM t2 ORDER BY i;
1342 SELECT * FROM t3 ORDER BY id;
1343 INSERT INTO t4 VALUES(sf1());
1344 SELECT * FROM t1 ORDER BY id;
1345 SELECT * FROM t2 ORDER BY i;
1346 SELECT * FROM t3 ORDER BY id;
1347 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1348 SELECT * FROM t1 ORDER BY id;
1349 SELECT * FROM t2 ORDER BY i;
1350 SELECT * FROM t3 ORDER BY id;
1351 DELETE FROM t4 WHERE i = sf1() + 1;
1352 SELECT * FROM t1 ORDER BY id;
1353 SELECT * FROM t2 ORDER BY i;
1354 SELECT * FROM t3 ORDER BY id;
1355 DROP FUNCTION sf1;
1356 DROP TABLE t1,t2,t3,t4;
1357 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1358 CREATE TABLE t2 (i INTEGER NULL);
1359 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1360 CREATE TABLE t4 (i INTEGER NULL);
1361 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1362 INSERT INTO t2 VALUES(1),(2),(3);
1363 DELIMITER //;
1364 CREATE FUNCTION sf1() RETURNS INTEGER
1365 BEGIN
1366 DECLARE done INT DEFAULT 0;
1367 DECLARE a CHAR(16);
1368 DECLARE b,c INT;
1369 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1370 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1371 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1372 OPEN cur1;
1373 OPEN cur2;
1374 REPEAT
1375 FETCH cur1 INTO a, b;
1376 FETCH cur2 INTO c;
1377 IF NOT done THEN
1378 IF b < c THEN
1379 INSERT INTO t3 VALUES (a,b);
1380 ELSE
1381 INSERT INTO t3 VALUES (a,c);
1382 END IF;
1383 END IF;
1384 UNTIL done END REPEAT;
1385 CLOSE cur1;
1386 CLOSE cur2;
1387 RETURN 0;
1388 END//
1389 DELIMITER ;//
1390 ALTER FUNCTION sf1 #DET# CONTAINS SQL SQL SECURITY INVOKER COMMENT 'comment' ;
1391 SELECT sf1();
1392 SELECT * FROM t1 ORDER BY id;
1393 SELECT * FROM t2 ORDER BY i;
1394 SELECT * FROM t3 ORDER BY id;
1395 INSERT INTO t4 VALUES(sf1());
1396 SELECT * FROM t1 ORDER BY id;
1397 SELECT * FROM t2 ORDER BY i;
1398 SELECT * FROM t3 ORDER BY id;
1399 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1400 SELECT * FROM t1 ORDER BY id;
1401 SELECT * FROM t2 ORDER BY i;
1402 SELECT * FROM t3 ORDER BY id;
1403 DELETE FROM t4 WHERE i = sf1() + 1;
1404 SELECT * FROM t1 ORDER BY id;
1405 SELECT * FROM t2 ORDER BY i;
1406 SELECT * FROM t3 ORDER BY id;
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 DELIMITER //;
1416 CREATE FUNCTION sf1() RETURNS INTEGER
1417 BEGIN
1418 DECLARE done INT DEFAULT 0;
1419 DECLARE a CHAR(16);
1420 DECLARE b,c INT;
1421 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1422 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1423 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1424 OPEN cur1;
1425 OPEN cur2;
1426 REPEAT
1427 FETCH cur1 INTO a, b;
1428 FETCH cur2 INTO c;
1429 IF NOT done THEN
1430 IF b < c THEN
1431 INSERT INTO t3 VALUES (a,b);
1432 ELSE
1433 INSERT INTO t3 VALUES (a,c);
1434 END IF;
1435 END IF;
1436 UNTIL done END REPEAT;
1437 CLOSE cur1;
1438 CLOSE cur2;
1439 RETURN 0;
1440 END//
1441 DELIMITER ;//
1442 ALTER FUNCTION sf1 #DET# NO SQL SQL SECURITY INVOKER COMMENT 'comment' ;
1443 SELECT sf1();
1444 SELECT * FROM t1 ORDER BY id;
1445 SELECT * FROM t2 ORDER BY i;
1446 SELECT * FROM t3 ORDER BY id;
1447 INSERT INTO t4 VALUES(sf1());
1448 SELECT * FROM t1 ORDER BY id;
1449 SELECT * FROM t2 ORDER BY i;
1450 SELECT * FROM t3 ORDER BY id;
1451 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1452 SELECT * FROM t1 ORDER BY id;
1453 SELECT * FROM t2 ORDER BY i;
1454 SELECT * FROM t3 ORDER BY id;
1455 DELETE FROM t4 WHERE i = sf1() + 1;
1456 SELECT * FROM t1 ORDER BY id;
1457 SELECT * FROM t2 ORDER BY i;
1458 SELECT * FROM t3 ORDER BY id;
1459 DROP FUNCTION sf1;
1460 DROP TABLE t1,t2,t3,t4;
1461 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1462 CREATE TABLE t2 (i INTEGER NULL);
1463 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1464 CREATE TABLE t4 (i INTEGER NULL);
1465 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1466 INSERT INTO t2 VALUES(1),(2),(3);
1467 DELIMITER //;
1468 CREATE FUNCTION sf1() RETURNS INTEGER
1469 BEGIN
1470 DECLARE done INT DEFAULT 0;
1471 DECLARE a CHAR(16);
1472 DECLARE b,c INT;
1473 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1474 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1475 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1476 OPEN cur1;
1477 OPEN cur2;
1478 REPEAT
1479 FETCH cur1 INTO a, b;
1480 FETCH cur2 INTO c;
1481 IF NOT done THEN
1482 IF b < c THEN
1483 INSERT INTO t3 VALUES (a,b);
1484 ELSE
1485 INSERT INTO t3 VALUES (a,c);
1486 END IF;
1487 END IF;
1488 UNTIL done END REPEAT;
1489 CLOSE cur1;
1490 CLOSE cur2;
1491 RETURN 0;
1492 END//
1493 DELIMITER ;//
1494 ALTER FUNCTION sf1 #DET# READS SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
1495 SELECT sf1();
1496 SELECT * FROM t1 ORDER BY id;
1497 SELECT * FROM t2 ORDER BY i;
1498 SELECT * FROM t3 ORDER BY id;
1499 INSERT INTO t4 VALUES(sf1());
1500 SELECT * FROM t1 ORDER BY id;
1501 SELECT * FROM t2 ORDER BY i;
1502 SELECT * FROM t3 ORDER BY id;
1503 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1504 SELECT * FROM t1 ORDER BY id;
1505 SELECT * FROM t2 ORDER BY i;
1506 SELECT * FROM t3 ORDER BY id;
1507 DELETE FROM t4 WHERE i = sf1() + 1;
1508 SELECT * FROM t1 ORDER BY id;
1509 SELECT * FROM t2 ORDER BY i;
1510 SELECT * FROM t3 ORDER BY id;
1511 DROP FUNCTION sf1;
1512 DROP TABLE t1,t2,t3,t4;
1513 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1514 CREATE TABLE t2 (i INTEGER NULL);
1515 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1516 CREATE TABLE t4 (i INTEGER NULL);
1517 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1518 INSERT INTO t2 VALUES(1),(2),(3);
1519 DELIMITER //;
1520 CREATE FUNCTION sf1() RETURNS INTEGER
1521 BEGIN
1522 DECLARE done INT DEFAULT 0;
1523 DECLARE a CHAR(16);
1524 DECLARE b,c INT;
1525 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1526 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1527 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1528 OPEN cur1;
1529 OPEN cur2;
1530 REPEAT
1531 FETCH cur1 INTO a, b;
1532 FETCH cur2 INTO c;
1533 IF NOT done THEN
1534 IF b < c THEN
1535 INSERT INTO t3 VALUES (a,b);
1536 ELSE
1537 INSERT INTO t3 VALUES (a,c);
1538 END IF;
1539 END IF;
1540 UNTIL done END REPEAT;
1541 CLOSE cur1;
1542 CLOSE cur2;
1543 RETURN 0;
1544 END//
1545 DELIMITER ;//
1546 ALTER FUNCTION sf1 #DET# MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
1547 SELECT sf1();
1548 SELECT * FROM t1 ORDER BY id;
1549 SELECT * FROM t2 ORDER BY i;
1550 SELECT * FROM t3 ORDER BY id;
1551 INSERT INTO t4 VALUES(sf1());
1552 SELECT * FROM t1 ORDER BY id;
1553 SELECT * FROM t2 ORDER BY i;
1554 SELECT * FROM t3 ORDER BY id;
1555 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1556 SELECT * FROM t1 ORDER BY id;
1557 SELECT * FROM t2 ORDER BY i;
1558 SELECT * FROM t3 ORDER BY id;
1559 DELETE FROM t4 WHERE i = sf1() + 1;
1560 SELECT * FROM t1 ORDER BY id;
1561 SELECT * FROM t2 ORDER BY i;
1562 SELECT * FROM t3 ORDER BY id;
1563 DROP FUNCTION sf1;
1564 DROP TABLE t1,t2,t3,t4;
1565 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1566 CREATE TABLE t2 (i INTEGER NULL);
1567 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1568 CREATE TABLE t4 (i INTEGER NULL);
1569 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1570 INSERT INTO t2 VALUES(1),(2),(3);
1571 DELIMITER //;
1572 CREATE FUNCTION sf1() RETURNS INTEGER
1573 BEGIN
1574 DECLARE done INT DEFAULT 0;
1575 DECLARE a CHAR(16);
1576 DECLARE b,c INT;
1577 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1578 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1579 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1580 OPEN cur1;
1581 OPEN cur2;
1582 REPEAT
1583 FETCH cur1 INTO a, b;
1584 FETCH cur2 INTO c;
1585 IF NOT done THEN
1586 IF b < c THEN
1587 INSERT INTO t3 VALUES (a,b);
1588 ELSE
1589 INSERT INTO t3 VALUES (a,c);
1590 END IF;
1591 END IF;
1592 UNTIL done END REPEAT;
1593 CLOSE cur1;
1594 CLOSE cur2;
1595 RETURN 0;
1596 END//
1597 DELIMITER ;//
1598 ALTER FUNCTION sf1 LANGUAGE SQL #DET# SQL SECURITY INVOKER ;
1599 SELECT sf1();
1600 SELECT * FROM t1 ORDER BY id;
1601 SELECT * FROM t2 ORDER BY i;
1602 SELECT * FROM t3 ORDER BY id;
1603 INSERT INTO t4 VALUES(sf1());
1604 SELECT * FROM t1 ORDER BY id;
1605 SELECT * FROM t2 ORDER BY i;
1606 SELECT * FROM t3 ORDER BY id;
1607 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1608 SELECT * FROM t1 ORDER BY id;
1609 SELECT * FROM t2 ORDER BY i;
1610 SELECT * FROM t3 ORDER BY id;
1611 DELETE FROM t4 WHERE i = sf1() + 1;
1612 SELECT * FROM t1 ORDER BY id;
1613 SELECT * FROM t2 ORDER BY i;
1614 SELECT * FROM t3 ORDER BY id;
1615 DROP FUNCTION sf1;
1616 DROP TABLE t1,t2,t3,t4;
1617 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1618 CREATE TABLE t2 (i INTEGER NULL);
1619 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1620 CREATE TABLE t4 (i INTEGER NULL);
1621 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1622 INSERT INTO t2 VALUES(1),(2),(3);
1623 DELIMITER //;
1624 CREATE FUNCTION sf1() RETURNS INTEGER
1625 BEGIN
1626 DECLARE done INT DEFAULT 0;
1627 DECLARE a CHAR(16);
1628 DECLARE b,c INT;
1629 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1630 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1631 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1632 OPEN cur1;
1633 OPEN cur2;
1634 REPEAT
1635 FETCH cur1 INTO a, b;
1636 FETCH cur2 INTO c;
1637 IF NOT done THEN
1638 IF b < c THEN
1639 INSERT INTO t3 VALUES (a,b);
1640 ELSE
1641 INSERT INTO t3 VALUES (a,c);
1642 END IF;
1643 END IF;
1644 UNTIL done END REPEAT;
1645 CLOSE cur1;
1646 CLOSE cur2;
1647 RETURN 0;
1648 END//
1649 DELIMITER ;//
1650 ALTER FUNCTION sf1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY INVOKER ;
1651 SELECT sf1();
1652 SELECT * FROM t1 ORDER BY id;
1653 SELECT * FROM t2 ORDER BY i;
1654 SELECT * FROM t3 ORDER BY id;
1655 INSERT INTO t4 VALUES(sf1());
1656 SELECT * FROM t1 ORDER BY id;
1657 SELECT * FROM t2 ORDER BY i;
1658 SELECT * FROM t3 ORDER BY id;
1659 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1660 SELECT * FROM t1 ORDER BY id;
1661 SELECT * FROM t2 ORDER BY i;
1662 SELECT * FROM t3 ORDER BY id;
1663 DELETE FROM t4 WHERE i = sf1() + 1;
1664 SELECT * FROM t1 ORDER BY id;
1665 SELECT * FROM t2 ORDER BY i;
1666 SELECT * FROM t3 ORDER BY id;
1667 DROP FUNCTION sf1;
1668 DROP TABLE t1,t2,t3,t4;
1669 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1670 CREATE TABLE t2 (i INTEGER NULL);
1671 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1672 CREATE TABLE t4 (i INTEGER NULL);
1673 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1674 INSERT INTO t2 VALUES(1),(2),(3);
1675 DELIMITER //;
1676 CREATE FUNCTION sf1() RETURNS INTEGER
1677 BEGIN
1678 DECLARE done INT DEFAULT 0;
1679 DECLARE a CHAR(16);
1680 DECLARE b,c INT;
1681 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1682 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1683 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1684 OPEN cur1;
1685 OPEN cur2;
1686 REPEAT
1687 FETCH cur1 INTO a, b;
1688 FETCH cur2 INTO c;
1689 IF NOT done THEN
1690 IF b < c THEN
1691 INSERT INTO t3 VALUES (a,b);
1692 ELSE
1693 INSERT INTO t3 VALUES (a,c);
1694 END IF;
1695 END IF;
1696 UNTIL done END REPEAT;
1697 CLOSE cur1;
1698 CLOSE cur2;
1699 RETURN 0;
1700 END//
1701 DELIMITER ;//
1702 ALTER FUNCTION sf1 LANGUAGE SQL #DET# NO SQL SQL SECURITY INVOKER ;
1703 SELECT sf1();
1704 SELECT * FROM t1 ORDER BY id;
1705 SELECT * FROM t2 ORDER BY i;
1706 SELECT * FROM t3 ORDER BY id;
1707 INSERT INTO t4 VALUES(sf1());
1708 SELECT * FROM t1 ORDER BY id;
1709 SELECT * FROM t2 ORDER BY i;
1710 SELECT * FROM t3 ORDER BY id;
1711 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1712 SELECT * FROM t1 ORDER BY id;
1713 SELECT * FROM t2 ORDER BY i;
1714 SELECT * FROM t3 ORDER BY id;
1715 DELETE FROM t4 WHERE i = sf1() + 1;
1716 SELECT * FROM t1 ORDER BY id;
1717 SELECT * FROM t2 ORDER BY i;
1718 SELECT * FROM t3 ORDER BY id;
1719 DROP FUNCTION sf1;
1720 DROP TABLE t1,t2,t3,t4;
1721 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1722 CREATE TABLE t2 (i INTEGER NULL);
1723 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1724 CREATE TABLE t4 (i INTEGER NULL);
1725 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1726 INSERT INTO t2 VALUES(1),(2),(3);
1727 DELIMITER //;
1728 CREATE FUNCTION sf1() RETURNS INTEGER
1729 BEGIN
1730 DECLARE done INT DEFAULT 0;
1731 DECLARE a CHAR(16);
1732 DECLARE b,c INT;
1733 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1734 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1735 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1736 OPEN cur1;
1737 OPEN cur2;
1738 REPEAT
1739 FETCH cur1 INTO a, b;
1740 FETCH cur2 INTO c;
1741 IF NOT done THEN
1742 IF b < c THEN
1743 INSERT INTO t3 VALUES (a,b);
1744 ELSE
1745 INSERT INTO t3 VALUES (a,c);
1746 END IF;
1747 END IF;
1748 UNTIL done END REPEAT;
1749 CLOSE cur1;
1750 CLOSE cur2;
1751 RETURN 0;
1752 END//
1753 DELIMITER ;//
1754 ALTER FUNCTION sf1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY INVOKER ;
1755 SELECT sf1();
1756 SELECT * FROM t1 ORDER BY id;
1757 SELECT * FROM t2 ORDER BY i;
1758 SELECT * FROM t3 ORDER BY id;
1759 INSERT INTO t4 VALUES(sf1());
1760 SELECT * FROM t1 ORDER BY id;
1761 SELECT * FROM t2 ORDER BY i;
1762 SELECT * FROM t3 ORDER BY id;
1763 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1764 SELECT * FROM t1 ORDER BY id;
1765 SELECT * FROM t2 ORDER BY i;
1766 SELECT * FROM t3 ORDER BY id;
1767 DELETE FROM t4 WHERE i = sf1() + 1;
1768 SELECT * FROM t1 ORDER BY id;
1769 SELECT * FROM t2 ORDER BY i;
1770 SELECT * FROM t3 ORDER BY id;
1771 DROP FUNCTION sf1;
1772 DROP TABLE t1,t2,t3,t4;
1773 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1774 CREATE TABLE t2 (i INTEGER NULL);
1775 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1776 CREATE TABLE t4 (i INTEGER NULL);
1777 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1778 INSERT INTO t2 VALUES(1),(2),(3);
1779 DELIMITER //;
1780 CREATE FUNCTION sf1() RETURNS INTEGER
1781 BEGIN
1782 DECLARE done INT DEFAULT 0;
1783 DECLARE a CHAR(16);
1784 DECLARE b,c INT;
1785 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1786 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1787 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1788 OPEN cur1;
1789 OPEN cur2;
1790 REPEAT
1791 FETCH cur1 INTO a, b;
1792 FETCH cur2 INTO c;
1793 IF NOT done THEN
1794 IF b < c THEN
1795 INSERT INTO t3 VALUES (a,b);
1796 ELSE
1797 INSERT INTO t3 VALUES (a,c);
1798 END IF;
1799 END IF;
1800 UNTIL done END REPEAT;
1801 CLOSE cur1;
1802 CLOSE cur2;
1803 RETURN 0;
1804 END//
1805 DELIMITER ;//
1806 ALTER FUNCTION sf1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY INVOKER ;
1807 SELECT sf1();
1808 SELECT * FROM t1 ORDER BY id;
1809 SELECT * FROM t2 ORDER BY i;
1810 SELECT * FROM t3 ORDER BY id;
1811 INSERT INTO t4 VALUES(sf1());
1812 SELECT * FROM t1 ORDER BY id;
1813 SELECT * FROM t2 ORDER BY i;
1814 SELECT * FROM t3 ORDER BY id;
1815 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1816 SELECT * FROM t1 ORDER BY id;
1817 SELECT * FROM t2 ORDER BY i;
1818 SELECT * FROM t3 ORDER BY id;
1819 DELETE FROM t4 WHERE i = sf1() + 1;
1820 SELECT * FROM t1 ORDER BY id;
1821 SELECT * FROM t2 ORDER BY i;
1822 SELECT * FROM t3 ORDER BY id;
1823 DROP FUNCTION sf1;
1824 DROP TABLE t1,t2,t3,t4;
1825 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1826 CREATE TABLE t2 (i INTEGER NULL);
1827 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1828 CREATE TABLE t4 (i INTEGER NULL);
1829 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1830 INSERT INTO t2 VALUES(1),(2),(3);
1831 DELIMITER //;
1832 CREATE FUNCTION sf1() RETURNS INTEGER
1833 BEGIN
1834 DECLARE done INT DEFAULT 0;
1835 DECLARE a CHAR(16);
1836 DECLARE b,c INT;
1837 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1838 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1839 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1840 OPEN cur1;
1841 OPEN cur2;
1842 REPEAT
1843 FETCH cur1 INTO a, b;
1844 FETCH cur2 INTO c;
1845 IF NOT done THEN
1846 IF b < c THEN
1847 INSERT INTO t3 VALUES (a,b);
1848 ELSE
1849 INSERT INTO t3 VALUES (a,c);
1850 END IF;
1851 END IF;
1852 UNTIL done END REPEAT;
1853 CLOSE cur1;
1854 CLOSE cur2;
1855 RETURN 0;
1856 END//
1857 DELIMITER ;//
1858 ALTER FUNCTION sf1 LANGUAGE SQL #DET# SQL SECURITY INVOKER COMMENT 'comment' ;
1859 SELECT sf1();
1860 SELECT * FROM t1 ORDER BY id;
1861 SELECT * FROM t2 ORDER BY i;
1862 SELECT * FROM t3 ORDER BY id;
1863 INSERT INTO t4 VALUES(sf1());
1864 SELECT * FROM t1 ORDER BY id;
1865 SELECT * FROM t2 ORDER BY i;
1866 SELECT * FROM t3 ORDER BY id;
1867 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1868 SELECT * FROM t1 ORDER BY id;
1869 SELECT * FROM t2 ORDER BY i;
1870 SELECT * FROM t3 ORDER BY id;
1871 DELETE FROM t4 WHERE i = sf1() + 1;
1872 SELECT * FROM t1 ORDER BY id;
1873 SELECT * FROM t2 ORDER BY i;
1874 SELECT * FROM t3 ORDER BY id;
1875 DROP FUNCTION sf1;
1876 DROP TABLE t1,t2,t3,t4;
1877 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1878 CREATE TABLE t2 (i INTEGER NULL);
1879 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1880 CREATE TABLE t4 (i INTEGER NULL);
1881 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1882 INSERT INTO t2 VALUES(1),(2),(3);
1883 DELIMITER //;
1884 CREATE FUNCTION sf1() RETURNS INTEGER
1885 BEGIN
1886 DECLARE done INT DEFAULT 0;
1887 DECLARE a CHAR(16);
1888 DECLARE b,c INT;
1889 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1890 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1891 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1892 OPEN cur1;
1893 OPEN cur2;
1894 REPEAT
1895 FETCH cur1 INTO a, b;
1896 FETCH cur2 INTO c;
1897 IF NOT done THEN
1898 IF b < c THEN
1899 INSERT INTO t3 VALUES (a,b);
1900 ELSE
1901 INSERT INTO t3 VALUES (a,c);
1902 END IF;
1903 END IF;
1904 UNTIL done END REPEAT;
1905 CLOSE cur1;
1906 CLOSE cur2;
1907 RETURN 0;
1908 END//
1909 DELIMITER ;//
1910 ALTER FUNCTION sf1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY INVOKER COMMENT 'comment' ;
1911 SELECT sf1();
1912 SELECT * FROM t1 ORDER BY id;
1913 SELECT * FROM t2 ORDER BY i;
1914 SELECT * FROM t3 ORDER BY id;
1915 INSERT INTO t4 VALUES(sf1());
1916 SELECT * FROM t1 ORDER BY id;
1917 SELECT * FROM t2 ORDER BY i;
1918 SELECT * FROM t3 ORDER BY id;
1919 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1920 SELECT * FROM t1 ORDER BY id;
1921 SELECT * FROM t2 ORDER BY i;
1922 SELECT * FROM t3 ORDER BY id;
1923 DELETE FROM t4 WHERE i = sf1() + 1;
1924 SELECT * FROM t1 ORDER BY id;
1925 SELECT * FROM t2 ORDER BY i;
1926 SELECT * FROM t3 ORDER BY id;
1927 DROP FUNCTION sf1;
1928 DROP TABLE t1,t2,t3,t4;
1929 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1930 CREATE TABLE t2 (i INTEGER NULL);
1931 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1932 CREATE TABLE t4 (i INTEGER NULL);
1933 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1934 INSERT INTO t2 VALUES(1),(2),(3);
1935 DELIMITER //;
1936 CREATE FUNCTION sf1() RETURNS INTEGER
1937 BEGIN
1938 DECLARE done INT DEFAULT 0;
1939 DECLARE a CHAR(16);
1940 DECLARE b,c INT;
1941 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1942 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1943 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1944 OPEN cur1;
1945 OPEN cur2;
1946 REPEAT
1947 FETCH cur1 INTO a, b;
1948 FETCH cur2 INTO c;
1949 IF NOT done THEN
1950 IF b < c THEN
1951 INSERT INTO t3 VALUES (a,b);
1952 ELSE
1953 INSERT INTO t3 VALUES (a,c);
1954 END IF;
1955 END IF;
1956 UNTIL done END REPEAT;
1957 CLOSE cur1;
1958 CLOSE cur2;
1959 RETURN 0;
1960 END//
1961 DELIMITER ;//
1962 ALTER FUNCTION sf1 LANGUAGE SQL #DET# NO SQL SQL SECURITY INVOKER COMMENT 'comment' ;
1963 SELECT sf1();
1964 SELECT * FROM t1 ORDER BY id;
1965 SELECT * FROM t2 ORDER BY i;
1966 SELECT * FROM t3 ORDER BY id;
1967 INSERT INTO t4 VALUES(sf1());
1968 SELECT * FROM t1 ORDER BY id;
1969 SELECT * FROM t2 ORDER BY i;
1970 SELECT * FROM t3 ORDER BY id;
1971 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
1972 SELECT * FROM t1 ORDER BY id;
1973 SELECT * FROM t2 ORDER BY i;
1974 SELECT * FROM t3 ORDER BY id;
1975 DELETE FROM t4 WHERE i = sf1() + 1;
1976 SELECT * FROM t1 ORDER BY id;
1977 SELECT * FROM t2 ORDER BY i;
1978 SELECT * FROM t3 ORDER BY id;
1979 DROP FUNCTION sf1;
1980 DROP TABLE t1,t2,t3,t4;
1981 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
1982 CREATE TABLE t2 (i INTEGER NULL);
1983 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
1984 CREATE TABLE t4 (i INTEGER NULL);
1985 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
1986 INSERT INTO t2 VALUES(1),(2),(3);
1987 DELIMITER //;
1988 CREATE FUNCTION sf1() RETURNS INTEGER
1989 BEGIN
1990 DECLARE done INT DEFAULT 0;
1991 DECLARE a CHAR(16);
1992 DECLARE b,c INT;
1993 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
1994 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
1995 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
1996 OPEN cur1;
1997 OPEN cur2;
1998 REPEAT
1999 FETCH cur1 INTO a, b;
2000 FETCH cur2 INTO c;
2001 IF NOT done THEN
2002 IF b < c THEN
2003 INSERT INTO t3 VALUES (a,b);
2004 ELSE
2005 INSERT INTO t3 VALUES (a,c);
2006 END IF;
2007 END IF;
2008 UNTIL done END REPEAT;
2009 CLOSE cur1;
2010 CLOSE cur2;
2011 RETURN 0;
2012 END//
2013 DELIMITER ;//
2014 ALTER FUNCTION sf1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
2015 SELECT sf1();
2016 SELECT * FROM t1 ORDER BY id;
2017 SELECT * FROM t2 ORDER BY i;
2018 SELECT * FROM t3 ORDER BY id;
2019 INSERT INTO t4 VALUES(sf1());
2020 SELECT * FROM t1 ORDER BY id;
2021 SELECT * FROM t2 ORDER BY i;
2022 SELECT * FROM t3 ORDER BY id;
2023 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2024 SELECT * FROM t1 ORDER BY id;
2025 SELECT * FROM t2 ORDER BY i;
2026 SELECT * FROM t3 ORDER BY id;
2027 DELETE FROM t4 WHERE i = sf1() + 1;
2028 SELECT * FROM t1 ORDER BY id;
2029 SELECT * FROM t2 ORDER BY i;
2030 SELECT * FROM t3 ORDER BY id;
2031 DROP FUNCTION sf1;
2032 DROP TABLE t1,t2,t3,t4;
2033 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2034 CREATE TABLE t2 (i INTEGER NULL);
2035 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2036 CREATE TABLE t4 (i INTEGER NULL);
2037 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2038 INSERT INTO t2 VALUES(1),(2),(3);
2039 DELIMITER //;
2040 CREATE FUNCTION sf1() RETURNS INTEGER
2041 BEGIN
2042 DECLARE done INT DEFAULT 0;
2043 DECLARE a CHAR(16);
2044 DECLARE b,c INT;
2045 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2046 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2047 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2048 OPEN cur1;
2049 OPEN cur2;
2050 REPEAT
2051 FETCH cur1 INTO a, b;
2052 FETCH cur2 INTO c;
2053 IF NOT done THEN
2054 IF b < c THEN
2055 INSERT INTO t3 VALUES (a,b);
2056 ELSE
2057 INSERT INTO t3 VALUES (a,c);
2058 END IF;
2059 END IF;
2060 UNTIL done END REPEAT;
2061 CLOSE cur1;
2062 CLOSE cur2;
2063 RETURN 0;
2064 END//
2065 DELIMITER ;//
2066 ALTER FUNCTION sf1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT 'comment' ;
2067 SELECT sf1();
2068 SELECT * FROM t1 ORDER BY id;
2069 SELECT * FROM t2 ORDER BY i;
2070 SELECT * FROM t3 ORDER BY id;
2071 INSERT INTO t4 VALUES(sf1());
2072 SELECT * FROM t1 ORDER BY id;
2073 SELECT * FROM t2 ORDER BY i;
2074 SELECT * FROM t3 ORDER BY id;
2075 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2076 SELECT * FROM t1 ORDER BY id;
2077 SELECT * FROM t2 ORDER BY i;
2078 SELECT * FROM t3 ORDER BY id;
2079 DELETE FROM t4 WHERE i = sf1() + 1;
2080 SELECT * FROM t1 ORDER BY id;
2081 SELECT * FROM t2 ORDER BY i;
2082 SELECT * FROM t3 ORDER BY id;
2083 DROP FUNCTION sf1;
2084 DROP TABLE t1,t2,t3,t4;
2085 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2086 CREATE TABLE t2 (i INTEGER NULL);
2087 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2088 CREATE TABLE t4 (i INTEGER NULL);
2089 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2090 INSERT INTO t2 VALUES(1),(2),(3);
2091 DELIMITER //;
2092 CREATE FUNCTION sf1() RETURNS INTEGER
2093 BEGIN
2094 DECLARE done INT DEFAULT 0;
2095 DECLARE a CHAR(16);
2096 DECLARE b,c INT;
2097 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2098 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2099 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2100 OPEN cur1;
2101 OPEN cur2;
2102 REPEAT
2103 FETCH cur1 INTO a, b;
2104 FETCH cur2 INTO c;
2105 IF NOT done THEN
2106 IF b < c THEN
2107 INSERT INTO t3 VALUES (a,b);
2108 ELSE
2109 INSERT INTO t3 VALUES (a,c);
2110 END IF;
2111 END IF;
2112 UNTIL done END REPEAT;
2113 CLOSE cur1;
2114 CLOSE cur2;
2115 RETURN 0;
2116 END//
2117 DELIMITER ;//
2118 ALTER FUNCTION sf1 #DET# SQL SECURITY DEFINER ;
2119 SELECT sf1();
2120 SELECT * FROM t1 ORDER BY id;
2121 SELECT * FROM t2 ORDER BY i;
2122 SELECT * FROM t3 ORDER BY id;
2123 INSERT INTO t4 VALUES(sf1());
2124 SELECT * FROM t1 ORDER BY id;
2125 SELECT * FROM t2 ORDER BY i;
2126 SELECT * FROM t3 ORDER BY id;
2127 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2128 SELECT * FROM t1 ORDER BY id;
2129 SELECT * FROM t2 ORDER BY i;
2130 SELECT * FROM t3 ORDER BY id;
2131 DELETE FROM t4 WHERE i = sf1() + 1;
2132 SELECT * FROM t1 ORDER BY id;
2133 SELECT * FROM t2 ORDER BY i;
2134 SELECT * FROM t3 ORDER BY id;
2135 DROP FUNCTION sf1;
2136 DROP TABLE t1,t2,t3,t4;
2137 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2138 CREATE TABLE t2 (i INTEGER NULL);
2139 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2140 CREATE TABLE t4 (i INTEGER NULL);
2141 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2142 INSERT INTO t2 VALUES(1),(2),(3);
2143 DELIMITER //;
2144 CREATE FUNCTION sf1() RETURNS INTEGER
2145 BEGIN
2146 DECLARE done INT DEFAULT 0;
2147 DECLARE a CHAR(16);
2148 DECLARE b,c INT;
2149 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2150 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2151 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2152 OPEN cur1;
2153 OPEN cur2;
2154 REPEAT
2155 FETCH cur1 INTO a, b;
2156 FETCH cur2 INTO c;
2157 IF NOT done THEN
2158 IF b < c THEN
2159 INSERT INTO t3 VALUES (a,b);
2160 ELSE
2161 INSERT INTO t3 VALUES (a,c);
2162 END IF;
2163 END IF;
2164 UNTIL done END REPEAT;
2165 CLOSE cur1;
2166 CLOSE cur2;
2167 RETURN 0;
2168 END//
2169 DELIMITER ;//
2170 ALTER FUNCTION sf1 #DET# CONTAINS SQL SQL SECURITY DEFINER ;
2171 SELECT sf1();
2172 SELECT * FROM t1 ORDER BY id;
2173 SELECT * FROM t2 ORDER BY i;
2174 SELECT * FROM t3 ORDER BY id;
2175 INSERT INTO t4 VALUES(sf1());
2176 SELECT * FROM t1 ORDER BY id;
2177 SELECT * FROM t2 ORDER BY i;
2178 SELECT * FROM t3 ORDER BY id;
2179 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2180 SELECT * FROM t1 ORDER BY id;
2181 SELECT * FROM t2 ORDER BY i;
2182 SELECT * FROM t3 ORDER BY id;
2183 DELETE FROM t4 WHERE i = sf1() + 1;
2184 SELECT * FROM t1 ORDER BY id;
2185 SELECT * FROM t2 ORDER BY i;
2186 SELECT * FROM t3 ORDER BY id;
2187 DROP FUNCTION sf1;
2188 DROP TABLE t1,t2,t3,t4;
2189 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2190 CREATE TABLE t2 (i INTEGER NULL);
2191 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2192 CREATE TABLE t4 (i INTEGER NULL);
2193 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2194 INSERT INTO t2 VALUES(1),(2),(3);
2195 DELIMITER //;
2196 CREATE FUNCTION sf1() RETURNS INTEGER
2197 BEGIN
2198 DECLARE done INT DEFAULT 0;
2199 DECLARE a CHAR(16);
2200 DECLARE b,c INT;
2201 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2202 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2203 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2204 OPEN cur1;
2205 OPEN cur2;
2206 REPEAT
2207 FETCH cur1 INTO a, b;
2208 FETCH cur2 INTO c;
2209 IF NOT done THEN
2210 IF b < c THEN
2211 INSERT INTO t3 VALUES (a,b);
2212 ELSE
2213 INSERT INTO t3 VALUES (a,c);
2214 END IF;
2215 END IF;
2216 UNTIL done END REPEAT;
2217 CLOSE cur1;
2218 CLOSE cur2;
2219 RETURN 0;
2220 END//
2221 DELIMITER ;//
2222 ALTER FUNCTION sf1 #DET# NO SQL SQL SECURITY DEFINER ;
2223 SELECT sf1();
2224 SELECT * FROM t1 ORDER BY id;
2225 SELECT * FROM t2 ORDER BY i;
2226 SELECT * FROM t3 ORDER BY id;
2227 INSERT INTO t4 VALUES(sf1());
2228 SELECT * FROM t1 ORDER BY id;
2229 SELECT * FROM t2 ORDER BY i;
2230 SELECT * FROM t3 ORDER BY id;
2231 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2232 SELECT * FROM t1 ORDER BY id;
2233 SELECT * FROM t2 ORDER BY i;
2234 SELECT * FROM t3 ORDER BY id;
2235 DELETE FROM t4 WHERE i = sf1() + 1;
2236 SELECT * FROM t1 ORDER BY id;
2237 SELECT * FROM t2 ORDER BY i;
2238 SELECT * FROM t3 ORDER BY id;
2239 DROP FUNCTION sf1;
2240 DROP TABLE t1,t2,t3,t4;
2241 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2242 CREATE TABLE t2 (i INTEGER NULL);
2243 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2244 CREATE TABLE t4 (i INTEGER NULL);
2245 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2246 INSERT INTO t2 VALUES(1),(2),(3);
2247 DELIMITER //;
2248 CREATE FUNCTION sf1() RETURNS INTEGER
2249 BEGIN
2250 DECLARE done INT DEFAULT 0;
2251 DECLARE a CHAR(16);
2252 DECLARE b,c INT;
2253 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2254 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2255 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2256 OPEN cur1;
2257 OPEN cur2;
2258 REPEAT
2259 FETCH cur1 INTO a, b;
2260 FETCH cur2 INTO c;
2261 IF NOT done THEN
2262 IF b < c THEN
2263 INSERT INTO t3 VALUES (a,b);
2264 ELSE
2265 INSERT INTO t3 VALUES (a,c);
2266 END IF;
2267 END IF;
2268 UNTIL done END REPEAT;
2269 CLOSE cur1;
2270 CLOSE cur2;
2271 RETURN 0;
2272 END//
2273 DELIMITER ;//
2274 ALTER FUNCTION sf1 #DET# READS SQL DATA SQL SECURITY DEFINER ;
2275 SELECT sf1();
2276 SELECT * FROM t1 ORDER BY id;
2277 SELECT * FROM t2 ORDER BY i;
2278 SELECT * FROM t3 ORDER BY id;
2279 INSERT INTO t4 VALUES(sf1());
2280 SELECT * FROM t1 ORDER BY id;
2281 SELECT * FROM t2 ORDER BY i;
2282 SELECT * FROM t3 ORDER BY id;
2283 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2284 SELECT * FROM t1 ORDER BY id;
2285 SELECT * FROM t2 ORDER BY i;
2286 SELECT * FROM t3 ORDER BY id;
2287 DELETE FROM t4 WHERE i = sf1() + 1;
2288 SELECT * FROM t1 ORDER BY id;
2289 SELECT * FROM t2 ORDER BY i;
2290 SELECT * FROM t3 ORDER BY id;
2291 DROP FUNCTION sf1;
2292 DROP TABLE t1,t2,t3,t4;
2293 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2294 CREATE TABLE t2 (i INTEGER NULL);
2295 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2296 CREATE TABLE t4 (i INTEGER NULL);
2297 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2298 INSERT INTO t2 VALUES(1),(2),(3);
2299 DELIMITER //;
2300 CREATE FUNCTION sf1() RETURNS INTEGER
2301 BEGIN
2302 DECLARE done INT DEFAULT 0;
2303 DECLARE a CHAR(16);
2304 DECLARE b,c INT;
2305 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2306 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2307 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2308 OPEN cur1;
2309 OPEN cur2;
2310 REPEAT
2311 FETCH cur1 INTO a, b;
2312 FETCH cur2 INTO c;
2313 IF NOT done THEN
2314 IF b < c THEN
2315 INSERT INTO t3 VALUES (a,b);
2316 ELSE
2317 INSERT INTO t3 VALUES (a,c);
2318 END IF;
2319 END IF;
2320 UNTIL done END REPEAT;
2321 CLOSE cur1;
2322 CLOSE cur2;
2323 RETURN 0;
2324 END//
2325 DELIMITER ;//
2326 ALTER FUNCTION sf1 #DET# MODIFIES SQL DATA SQL SECURITY DEFINER ;
2327 SELECT sf1();
2328 SELECT * FROM t1 ORDER BY id;
2329 SELECT * FROM t2 ORDER BY i;
2330 SELECT * FROM t3 ORDER BY id;
2331 INSERT INTO t4 VALUES(sf1());
2332 SELECT * FROM t1 ORDER BY id;
2333 SELECT * FROM t2 ORDER BY i;
2334 SELECT * FROM t3 ORDER BY id;
2335 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2336 SELECT * FROM t1 ORDER BY id;
2337 SELECT * FROM t2 ORDER BY i;
2338 SELECT * FROM t3 ORDER BY id;
2339 DELETE FROM t4 WHERE i = sf1() + 1;
2340 SELECT * FROM t1 ORDER BY id;
2341 SELECT * FROM t2 ORDER BY i;
2342 SELECT * FROM t3 ORDER BY id;
2343 DROP FUNCTION sf1;
2344 DROP TABLE t1,t2,t3,t4;
2345 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2346 CREATE TABLE t2 (i INTEGER NULL);
2347 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2348 CREATE TABLE t4 (i INTEGER NULL);
2349 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2350 INSERT INTO t2 VALUES(1),(2),(3);
2351 DELIMITER //;
2352 CREATE FUNCTION sf1() RETURNS INTEGER
2353 BEGIN
2354 DECLARE done INT DEFAULT 0;
2355 DECLARE a CHAR(16);
2356 DECLARE b,c INT;
2357 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2358 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2359 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2360 OPEN cur1;
2361 OPEN cur2;
2362 REPEAT
2363 FETCH cur1 INTO a, b;
2364 FETCH cur2 INTO c;
2365 IF NOT done THEN
2366 IF b < c THEN
2367 INSERT INTO t3 VALUES (a,b);
2368 ELSE
2369 INSERT INTO t3 VALUES (a,c);
2370 END IF;
2371 END IF;
2372 UNTIL done END REPEAT;
2373 CLOSE cur1;
2374 CLOSE cur2;
2375 RETURN 0;
2376 END//
2377 DELIMITER ;//
2378 ALTER FUNCTION sf1 #DET# SQL SECURITY DEFINER COMMENT 'comment' ;
2379 SELECT sf1();
2380 SELECT * FROM t1 ORDER BY id;
2381 SELECT * FROM t2 ORDER BY i;
2382 SELECT * FROM t3 ORDER BY id;
2383 INSERT INTO t4 VALUES(sf1());
2384 SELECT * FROM t1 ORDER BY id;
2385 SELECT * FROM t2 ORDER BY i;
2386 SELECT * FROM t3 ORDER BY id;
2387 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2388 SELECT * FROM t1 ORDER BY id;
2389 SELECT * FROM t2 ORDER BY i;
2390 SELECT * FROM t3 ORDER BY id;
2391 DELETE FROM t4 WHERE i = sf1() + 1;
2392 SELECT * FROM t1 ORDER BY id;
2393 SELECT * FROM t2 ORDER BY i;
2394 SELECT * FROM t3 ORDER BY id;
2395 DROP FUNCTION sf1;
2396 DROP TABLE t1,t2,t3,t4;
2397 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2398 CREATE TABLE t2 (i INTEGER NULL);
2399 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2400 CREATE TABLE t4 (i INTEGER NULL);
2401 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2402 INSERT INTO t2 VALUES(1),(2),(3);
2403 DELIMITER //;
2404 CREATE FUNCTION sf1() RETURNS INTEGER
2405 BEGIN
2406 DECLARE done INT DEFAULT 0;
2407 DECLARE a CHAR(16);
2408 DECLARE b,c INT;
2409 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2410 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2411 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2412 OPEN cur1;
2413 OPEN cur2;
2414 REPEAT
2415 FETCH cur1 INTO a, b;
2416 FETCH cur2 INTO c;
2417 IF NOT done THEN
2418 IF b < c THEN
2419 INSERT INTO t3 VALUES (a,b);
2420 ELSE
2421 INSERT INTO t3 VALUES (a,c);
2422 END IF;
2423 END IF;
2424 UNTIL done END REPEAT;
2425 CLOSE cur1;
2426 CLOSE cur2;
2427 RETURN 0;
2428 END//
2429 DELIMITER ;//
2430 ALTER FUNCTION sf1 #DET# CONTAINS SQL SQL SECURITY DEFINER COMMENT 'comment' ;
2431 SELECT sf1();
2432 SELECT * FROM t1 ORDER BY id;
2433 SELECT * FROM t2 ORDER BY i;
2434 SELECT * FROM t3 ORDER BY id;
2435 INSERT INTO t4 VALUES(sf1());
2436 SELECT * FROM t1 ORDER BY id;
2437 SELECT * FROM t2 ORDER BY i;
2438 SELECT * FROM t3 ORDER BY id;
2439 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2440 SELECT * FROM t1 ORDER BY id;
2441 SELECT * FROM t2 ORDER BY i;
2442 SELECT * FROM t3 ORDER BY id;
2443 DELETE FROM t4 WHERE i = sf1() + 1;
2444 SELECT * FROM t1 ORDER BY id;
2445 SELECT * FROM t2 ORDER BY i;
2446 SELECT * FROM t3 ORDER BY id;
2447 DROP FUNCTION sf1;
2448 DROP TABLE t1,t2,t3,t4;
2449 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2450 CREATE TABLE t2 (i INTEGER NULL);
2451 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2452 CREATE TABLE t4 (i INTEGER NULL);
2453 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2454 INSERT INTO t2 VALUES(1),(2),(3);
2455 DELIMITER //;
2456 CREATE FUNCTION sf1() RETURNS INTEGER
2457 BEGIN
2458 DECLARE done INT DEFAULT 0;
2459 DECLARE a CHAR(16);
2460 DECLARE b,c INT;
2461 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2462 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2463 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2464 OPEN cur1;
2465 OPEN cur2;
2466 REPEAT
2467 FETCH cur1 INTO a, b;
2468 FETCH cur2 INTO c;
2469 IF NOT done THEN
2470 IF b < c THEN
2471 INSERT INTO t3 VALUES (a,b);
2472 ELSE
2473 INSERT INTO t3 VALUES (a,c);
2474 END IF;
2475 END IF;
2476 UNTIL done END REPEAT;
2477 CLOSE cur1;
2478 CLOSE cur2;
2479 RETURN 0;
2480 END//
2481 DELIMITER ;//
2482 ALTER FUNCTION sf1 #DET# NO SQL SQL SECURITY DEFINER COMMENT 'comment' ;
2483 SELECT sf1();
2484 SELECT * FROM t1 ORDER BY id;
2485 SELECT * FROM t2 ORDER BY i;
2486 SELECT * FROM t3 ORDER BY id;
2487 INSERT INTO t4 VALUES(sf1());
2488 SELECT * FROM t1 ORDER BY id;
2489 SELECT * FROM t2 ORDER BY i;
2490 SELECT * FROM t3 ORDER BY id;
2491 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2492 SELECT * FROM t1 ORDER BY id;
2493 SELECT * FROM t2 ORDER BY i;
2494 SELECT * FROM t3 ORDER BY id;
2495 DELETE FROM t4 WHERE i = sf1() + 1;
2496 SELECT * FROM t1 ORDER BY id;
2497 SELECT * FROM t2 ORDER BY i;
2498 SELECT * FROM t3 ORDER BY id;
2499 DROP FUNCTION sf1;
2500 DROP TABLE t1,t2,t3,t4;
2501 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2502 CREATE TABLE t2 (i INTEGER NULL);
2503 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2504 CREATE TABLE t4 (i INTEGER NULL);
2505 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2506 INSERT INTO t2 VALUES(1),(2),(3);
2507 DELIMITER //;
2508 CREATE FUNCTION sf1() RETURNS INTEGER
2509 BEGIN
2510 DECLARE done INT DEFAULT 0;
2511 DECLARE a CHAR(16);
2512 DECLARE b,c INT;
2513 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2514 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2515 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2516 OPEN cur1;
2517 OPEN cur2;
2518 REPEAT
2519 FETCH cur1 INTO a, b;
2520 FETCH cur2 INTO c;
2521 IF NOT done THEN
2522 IF b < c THEN
2523 INSERT INTO t3 VALUES (a,b);
2524 ELSE
2525 INSERT INTO t3 VALUES (a,c);
2526 END IF;
2527 END IF;
2528 UNTIL done END REPEAT;
2529 CLOSE cur1;
2530 CLOSE cur2;
2531 RETURN 0;
2532 END//
2533 DELIMITER ;//
2534 ALTER FUNCTION sf1 #DET# READS SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
2535 SELECT sf1();
2536 SELECT * FROM t1 ORDER BY id;
2537 SELECT * FROM t2 ORDER BY i;
2538 SELECT * FROM t3 ORDER BY id;
2539 INSERT INTO t4 VALUES(sf1());
2540 SELECT * FROM t1 ORDER BY id;
2541 SELECT * FROM t2 ORDER BY i;
2542 SELECT * FROM t3 ORDER BY id;
2543 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2544 SELECT * FROM t1 ORDER BY id;
2545 SELECT * FROM t2 ORDER BY i;
2546 SELECT * FROM t3 ORDER BY id;
2547 DELETE FROM t4 WHERE i = sf1() + 1;
2548 SELECT * FROM t1 ORDER BY id;
2549 SELECT * FROM t2 ORDER BY i;
2550 SELECT * FROM t3 ORDER BY id;
2551 DROP FUNCTION sf1;
2552 DROP TABLE t1,t2,t3,t4;
2553 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2554 CREATE TABLE t2 (i INTEGER NULL);
2555 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2556 CREATE TABLE t4 (i INTEGER NULL);
2557 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2558 INSERT INTO t2 VALUES(1),(2),(3);
2559 DELIMITER //;
2560 CREATE FUNCTION sf1() RETURNS INTEGER
2561 BEGIN
2562 DECLARE done INT DEFAULT 0;
2563 DECLARE a CHAR(16);
2564 DECLARE b,c INT;
2565 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2566 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2567 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2568 OPEN cur1;
2569 OPEN cur2;
2570 REPEAT
2571 FETCH cur1 INTO a, b;
2572 FETCH cur2 INTO c;
2573 IF NOT done THEN
2574 IF b < c THEN
2575 INSERT INTO t3 VALUES (a,b);
2576 ELSE
2577 INSERT INTO t3 VALUES (a,c);
2578 END IF;
2579 END IF;
2580 UNTIL done END REPEAT;
2581 CLOSE cur1;
2582 CLOSE cur2;
2583 RETURN 0;
2584 END//
2585 DELIMITER ;//
2586 ALTER FUNCTION sf1 #DET# MODIFIES SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
2587 SELECT sf1();
2588 SELECT * FROM t1 ORDER BY id;
2589 SELECT * FROM t2 ORDER BY i;
2590 SELECT * FROM t3 ORDER BY id;
2591 INSERT INTO t4 VALUES(sf1());
2592 SELECT * FROM t1 ORDER BY id;
2593 SELECT * FROM t2 ORDER BY i;
2594 SELECT * FROM t3 ORDER BY id;
2595 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2596 SELECT * FROM t1 ORDER BY id;
2597 SELECT * FROM t2 ORDER BY i;
2598 SELECT * FROM t3 ORDER BY id;
2599 DELETE FROM t4 WHERE i = sf1() + 1;
2600 SELECT * FROM t1 ORDER BY id;
2601 SELECT * FROM t2 ORDER BY i;
2602 SELECT * FROM t3 ORDER BY id;
2603 DROP FUNCTION sf1;
2604 DROP TABLE t1,t2,t3,t4;
2605 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2606 CREATE TABLE t2 (i INTEGER NULL);
2607 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2608 CREATE TABLE t4 (i INTEGER NULL);
2609 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2610 INSERT INTO t2 VALUES(1),(2),(3);
2611 DELIMITER //;
2612 CREATE FUNCTION sf1() RETURNS INTEGER
2613 BEGIN
2614 DECLARE done INT DEFAULT 0;
2615 DECLARE a CHAR(16);
2616 DECLARE b,c INT;
2617 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2618 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2619 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2620 OPEN cur1;
2621 OPEN cur2;
2622 REPEAT
2623 FETCH cur1 INTO a, b;
2624 FETCH cur2 INTO c;
2625 IF NOT done THEN
2626 IF b < c THEN
2627 INSERT INTO t3 VALUES (a,b);
2628 ELSE
2629 INSERT INTO t3 VALUES (a,c);
2630 END IF;
2631 END IF;
2632 UNTIL done END REPEAT;
2633 CLOSE cur1;
2634 CLOSE cur2;
2635 RETURN 0;
2636 END//
2637 DELIMITER ;//
2638 ALTER FUNCTION sf1 LANGUAGE SQL #DET# SQL SECURITY DEFINER ;
2639 SELECT sf1();
2640 SELECT * FROM t1 ORDER BY id;
2641 SELECT * FROM t2 ORDER BY i;
2642 SELECT * FROM t3 ORDER BY id;
2643 INSERT INTO t4 VALUES(sf1());
2644 SELECT * FROM t1 ORDER BY id;
2645 SELECT * FROM t2 ORDER BY i;
2646 SELECT * FROM t3 ORDER BY id;
2647 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2648 SELECT * FROM t1 ORDER BY id;
2649 SELECT * FROM t2 ORDER BY i;
2650 SELECT * FROM t3 ORDER BY id;
2651 DELETE FROM t4 WHERE i = sf1() + 1;
2652 SELECT * FROM t1 ORDER BY id;
2653 SELECT * FROM t2 ORDER BY i;
2654 SELECT * FROM t3 ORDER BY id;
2655 DROP FUNCTION sf1;
2656 DROP TABLE t1,t2,t3,t4;
2657 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2658 CREATE TABLE t2 (i INTEGER NULL);
2659 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2660 CREATE TABLE t4 (i INTEGER NULL);
2661 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2662 INSERT INTO t2 VALUES(1),(2),(3);
2663 DELIMITER //;
2664 CREATE FUNCTION sf1() RETURNS INTEGER
2665 BEGIN
2666 DECLARE done INT DEFAULT 0;
2667 DECLARE a CHAR(16);
2668 DECLARE b,c INT;
2669 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2670 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2671 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2672 OPEN cur1;
2673 OPEN cur2;
2674 REPEAT
2675 FETCH cur1 INTO a, b;
2676 FETCH cur2 INTO c;
2677 IF NOT done THEN
2678 IF b < c THEN
2679 INSERT INTO t3 VALUES (a,b);
2680 ELSE
2681 INSERT INTO t3 VALUES (a,c);
2682 END IF;
2683 END IF;
2684 UNTIL done END REPEAT;
2685 CLOSE cur1;
2686 CLOSE cur2;
2687 RETURN 0;
2688 END//
2689 DELIMITER ;//
2690 ALTER FUNCTION sf1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY DEFINER ;
2691 SELECT sf1();
2692 SELECT * FROM t1 ORDER BY id;
2693 SELECT * FROM t2 ORDER BY i;
2694 SELECT * FROM t3 ORDER BY id;
2695 INSERT INTO t4 VALUES(sf1());
2696 SELECT * FROM t1 ORDER BY id;
2697 SELECT * FROM t2 ORDER BY i;
2698 SELECT * FROM t3 ORDER BY id;
2699 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2700 SELECT * FROM t1 ORDER BY id;
2701 SELECT * FROM t2 ORDER BY i;
2702 SELECT * FROM t3 ORDER BY id;
2703 DELETE FROM t4 WHERE i = sf1() + 1;
2704 SELECT * FROM t1 ORDER BY id;
2705 SELECT * FROM t2 ORDER BY i;
2706 SELECT * FROM t3 ORDER BY id;
2707 DROP FUNCTION sf1;
2708 DROP TABLE t1,t2,t3,t4;
2709 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2710 CREATE TABLE t2 (i INTEGER NULL);
2711 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2712 CREATE TABLE t4 (i INTEGER NULL);
2713 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2714 INSERT INTO t2 VALUES(1),(2),(3);
2715 DELIMITER //;
2716 CREATE FUNCTION sf1() RETURNS INTEGER
2717 BEGIN
2718 DECLARE done INT DEFAULT 0;
2719 DECLARE a CHAR(16);
2720 DECLARE b,c INT;
2721 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2722 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2723 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2724 OPEN cur1;
2725 OPEN cur2;
2726 REPEAT
2727 FETCH cur1 INTO a, b;
2728 FETCH cur2 INTO c;
2729 IF NOT done THEN
2730 IF b < c THEN
2731 INSERT INTO t3 VALUES (a,b);
2732 ELSE
2733 INSERT INTO t3 VALUES (a,c);
2734 END IF;
2735 END IF;
2736 UNTIL done END REPEAT;
2737 CLOSE cur1;
2738 CLOSE cur2;
2739 RETURN 0;
2740 END//
2741 DELIMITER ;//
2742 ALTER FUNCTION sf1 LANGUAGE SQL #DET# NO SQL SQL SECURITY DEFINER ;
2743 SELECT sf1();
2744 SELECT * FROM t1 ORDER BY id;
2745 SELECT * FROM t2 ORDER BY i;
2746 SELECT * FROM t3 ORDER BY id;
2747 INSERT INTO t4 VALUES(sf1());
2748 SELECT * FROM t1 ORDER BY id;
2749 SELECT * FROM t2 ORDER BY i;
2750 SELECT * FROM t3 ORDER BY id;
2751 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2752 SELECT * FROM t1 ORDER BY id;
2753 SELECT * FROM t2 ORDER BY i;
2754 SELECT * FROM t3 ORDER BY id;
2755 DELETE FROM t4 WHERE i = sf1() + 1;
2756 SELECT * FROM t1 ORDER BY id;
2757 SELECT * FROM t2 ORDER BY i;
2758 SELECT * FROM t3 ORDER BY id;
2759 DROP FUNCTION sf1;
2760 DROP TABLE t1,t2,t3,t4;
2761 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2762 CREATE TABLE t2 (i INTEGER NULL);
2763 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2764 CREATE TABLE t4 (i INTEGER NULL);
2765 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2766 INSERT INTO t2 VALUES(1),(2),(3);
2767 DELIMITER //;
2768 CREATE FUNCTION sf1() RETURNS INTEGER
2769 BEGIN
2770 DECLARE done INT DEFAULT 0;
2771 DECLARE a CHAR(16);
2772 DECLARE b,c INT;
2773 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2774 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2775 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2776 OPEN cur1;
2777 OPEN cur2;
2778 REPEAT
2779 FETCH cur1 INTO a, b;
2780 FETCH cur2 INTO c;
2781 IF NOT done THEN
2782 IF b < c THEN
2783 INSERT INTO t3 VALUES (a,b);
2784 ELSE
2785 INSERT INTO t3 VALUES (a,c);
2786 END IF;
2787 END IF;
2788 UNTIL done END REPEAT;
2789 CLOSE cur1;
2790 CLOSE cur2;
2791 RETURN 0;
2792 END//
2793 DELIMITER ;//
2794 ALTER FUNCTION sf1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY DEFINER ;
2795 SELECT sf1();
2796 SELECT * FROM t1 ORDER BY id;
2797 SELECT * FROM t2 ORDER BY i;
2798 SELECT * FROM t3 ORDER BY id;
2799 INSERT INTO t4 VALUES(sf1());
2800 SELECT * FROM t1 ORDER BY id;
2801 SELECT * FROM t2 ORDER BY i;
2802 SELECT * FROM t3 ORDER BY id;
2803 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2804 SELECT * FROM t1 ORDER BY id;
2805 SELECT * FROM t2 ORDER BY i;
2806 SELECT * FROM t3 ORDER BY id;
2807 DELETE FROM t4 WHERE i = sf1() + 1;
2808 SELECT * FROM t1 ORDER BY id;
2809 SELECT * FROM t2 ORDER BY i;
2810 SELECT * FROM t3 ORDER BY id;
2811 DROP FUNCTION sf1;
2812 DROP TABLE t1,t2,t3,t4;
2813 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2814 CREATE TABLE t2 (i INTEGER NULL);
2815 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2816 CREATE TABLE t4 (i INTEGER NULL);
2817 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2818 INSERT INTO t2 VALUES(1),(2),(3);
2819 DELIMITER //;
2820 CREATE FUNCTION sf1() RETURNS INTEGER
2821 BEGIN
2822 DECLARE done INT DEFAULT 0;
2823 DECLARE a CHAR(16);
2824 DECLARE b,c INT;
2825 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2826 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2827 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2828 OPEN cur1;
2829 OPEN cur2;
2830 REPEAT
2831 FETCH cur1 INTO a, b;
2832 FETCH cur2 INTO c;
2833 IF NOT done THEN
2834 IF b < c THEN
2835 INSERT INTO t3 VALUES (a,b);
2836 ELSE
2837 INSERT INTO t3 VALUES (a,c);
2838 END IF;
2839 END IF;
2840 UNTIL done END REPEAT;
2841 CLOSE cur1;
2842 CLOSE cur2;
2843 RETURN 0;
2844 END//
2845 DELIMITER ;//
2846 ALTER FUNCTION sf1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY DEFINER ;
2847 SELECT sf1();
2848 SELECT * FROM t1 ORDER BY id;
2849 SELECT * FROM t2 ORDER BY i;
2850 SELECT * FROM t3 ORDER BY id;
2851 INSERT INTO t4 VALUES(sf1());
2852 SELECT * FROM t1 ORDER BY id;
2853 SELECT * FROM t2 ORDER BY i;
2854 SELECT * FROM t3 ORDER BY id;
2855 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2856 SELECT * FROM t1 ORDER BY id;
2857 SELECT * FROM t2 ORDER BY i;
2858 SELECT * FROM t3 ORDER BY id;
2859 DELETE FROM t4 WHERE i = sf1() + 1;
2860 SELECT * FROM t1 ORDER BY id;
2861 SELECT * FROM t2 ORDER BY i;
2862 SELECT * FROM t3 ORDER BY id;
2863 DROP FUNCTION sf1;
2864 DROP TABLE t1,t2,t3,t4;
2865 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2866 CREATE TABLE t2 (i INTEGER NULL);
2867 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2868 CREATE TABLE t4 (i INTEGER NULL);
2869 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2870 INSERT INTO t2 VALUES(1),(2),(3);
2871 DELIMITER //;
2872 CREATE FUNCTION sf1() RETURNS INTEGER
2873 BEGIN
2874 DECLARE done INT DEFAULT 0;
2875 DECLARE a CHAR(16);
2876 DECLARE b,c INT;
2877 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2878 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2879 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2880 OPEN cur1;
2881 OPEN cur2;
2882 REPEAT
2883 FETCH cur1 INTO a, b;
2884 FETCH cur2 INTO c;
2885 IF NOT done THEN
2886 IF b < c THEN
2887 INSERT INTO t3 VALUES (a,b);
2888 ELSE
2889 INSERT INTO t3 VALUES (a,c);
2890 END IF;
2891 END IF;
2892 UNTIL done END REPEAT;
2893 CLOSE cur1;
2894 CLOSE cur2;
2895 RETURN 0;
2896 END//
2897 DELIMITER ;//
2898 ALTER FUNCTION sf1 LANGUAGE SQL #DET# SQL SECURITY DEFINER COMMENT 'comment' ;
2899 SELECT sf1();
2900 SELECT * FROM t1 ORDER BY id;
2901 SELECT * FROM t2 ORDER BY i;
2902 SELECT * FROM t3 ORDER BY id;
2903 INSERT INTO t4 VALUES(sf1());
2904 SELECT * FROM t1 ORDER BY id;
2905 SELECT * FROM t2 ORDER BY i;
2906 SELECT * FROM t3 ORDER BY id;
2907 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2908 SELECT * FROM t1 ORDER BY id;
2909 SELECT * FROM t2 ORDER BY i;
2910 SELECT * FROM t3 ORDER BY id;
2911 DELETE FROM t4 WHERE i = sf1() + 1;
2912 SELECT * FROM t1 ORDER BY id;
2913 SELECT * FROM t2 ORDER BY i;
2914 SELECT * FROM t3 ORDER BY id;
2915 DROP FUNCTION sf1;
2916 DROP TABLE t1,t2,t3,t4;
2917 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2918 CREATE TABLE t2 (i INTEGER NULL);
2919 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2920 CREATE TABLE t4 (i INTEGER NULL);
2921 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2922 INSERT INTO t2 VALUES(1),(2),(3);
2923 DELIMITER //;
2924 CREATE FUNCTION sf1() RETURNS INTEGER
2925 BEGIN
2926 DECLARE done INT DEFAULT 0;
2927 DECLARE a CHAR(16);
2928 DECLARE b,c INT;
2929 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2930 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2931 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2932 OPEN cur1;
2933 OPEN cur2;
2934 REPEAT
2935 FETCH cur1 INTO a, b;
2936 FETCH cur2 INTO c;
2937 IF NOT done THEN
2938 IF b < c THEN
2939 INSERT INTO t3 VALUES (a,b);
2940 ELSE
2941 INSERT INTO t3 VALUES (a,c);
2942 END IF;
2943 END IF;
2944 UNTIL done END REPEAT;
2945 CLOSE cur1;
2946 CLOSE cur2;
2947 RETURN 0;
2948 END//
2949 DELIMITER ;//
2950 ALTER FUNCTION sf1 LANGUAGE SQL #DET# CONTAINS SQL SQL SECURITY DEFINER COMMENT 'comment' ;
2951 SELECT sf1();
2952 SELECT * FROM t1 ORDER BY id;
2953 SELECT * FROM t2 ORDER BY i;
2954 SELECT * FROM t3 ORDER BY id;
2955 INSERT INTO t4 VALUES(sf1());
2956 SELECT * FROM t1 ORDER BY id;
2957 SELECT * FROM t2 ORDER BY i;
2958 SELECT * FROM t3 ORDER BY id;
2959 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
2960 SELECT * FROM t1 ORDER BY id;
2961 SELECT * FROM t2 ORDER BY i;
2962 SELECT * FROM t3 ORDER BY id;
2963 DELETE FROM t4 WHERE i = sf1() + 1;
2964 SELECT * FROM t1 ORDER BY id;
2965 SELECT * FROM t2 ORDER BY i;
2966 SELECT * FROM t3 ORDER BY id;
2967 DROP FUNCTION sf1;
2968 DROP TABLE t1,t2,t3,t4;
2969 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
2970 CREATE TABLE t2 (i INTEGER NULL);
2971 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
2972 CREATE TABLE t4 (i INTEGER NULL);
2973 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
2974 INSERT INTO t2 VALUES(1),(2),(3);
2975 DELIMITER //;
2976 CREATE FUNCTION sf1() RETURNS INTEGER
2977 BEGIN
2978 DECLARE done INT DEFAULT 0;
2979 DECLARE a CHAR(16);
2980 DECLARE b,c INT;
2981 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
2982 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
2983 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
2984 OPEN cur1;
2985 OPEN cur2;
2986 REPEAT
2987 FETCH cur1 INTO a, b;
2988 FETCH cur2 INTO c;
2989 IF NOT done THEN
2990 IF b < c THEN
2991 INSERT INTO t3 VALUES (a,b);
2992 ELSE
2993 INSERT INTO t3 VALUES (a,c);
2994 END IF;
2995 END IF;
2996 UNTIL done END REPEAT;
2997 CLOSE cur1;
2998 CLOSE cur2;
2999 RETURN 0;
3000 END//
3001 DELIMITER ;//
3002 ALTER FUNCTION sf1 LANGUAGE SQL #DET# NO SQL SQL SECURITY DEFINER COMMENT 'comment' ;
3003 SELECT sf1();
3004 SELECT * FROM t1 ORDER BY id;
3005 SELECT * FROM t2 ORDER BY i;
3006 SELECT * FROM t3 ORDER BY id;
3007 INSERT INTO t4 VALUES(sf1());
3008 SELECT * FROM t1 ORDER BY id;
3009 SELECT * FROM t2 ORDER BY i;
3010 SELECT * FROM t3 ORDER BY id;
3011 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
3012 SELECT * FROM t1 ORDER BY id;
3013 SELECT * FROM t2 ORDER BY i;
3014 SELECT * FROM t3 ORDER BY id;
3015 DELETE FROM t4 WHERE i = sf1() + 1;
3016 SELECT * FROM t1 ORDER BY id;
3017 SELECT * FROM t2 ORDER BY i;
3018 SELECT * FROM t3 ORDER BY id;
3019 DROP FUNCTION sf1;
3020 DROP TABLE t1,t2,t3,t4;
3021 CREATE TABLE t1 (id INTEGER NULL , data INTEGER NULL, KEY(id));
3022 CREATE TABLE t2 (i INTEGER NULL);
3023 CREATE TABLE t3 (id INTEGER NULL, data INTEGER NULL, KEY(id));
3024 CREATE TABLE t4 (i INTEGER NULL);
3025 INSERT INTO t1 VALUES(1,1),(2,1),(3,4),(4,5);
3026 INSERT INTO t2 VALUES(1),(2),(3);
3027 DELIMITER //;
3028 CREATE FUNCTION sf1() RETURNS INTEGER
3029 BEGIN
3030 DECLARE done INT DEFAULT 0;
3031 DECLARE a CHAR(16);
3032 DECLARE b,c INT;
3033 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
3034 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
3035 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
3036 OPEN cur1;
3037 OPEN cur2;
3038 REPEAT
3039 FETCH cur1 INTO a, b;
3040 FETCH cur2 INTO c;
3041 IF NOT done THEN
3042 IF b < c THEN
3043 INSERT INTO t3 VALUES (a,b);
3044 ELSE
3045 INSERT INTO t3 VALUES (a,c);
3046 END IF;
3047 END IF;
3048 UNTIL done END REPEAT;
3049 CLOSE cur1;
3050 CLOSE cur2;
3051 RETURN 0;
3052 END//
3053 DELIMITER ;//
3054 ALTER FUNCTION sf1 LANGUAGE SQL #DET# READS SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
3055 SELECT sf1();
3056 SELECT * FROM t1 ORDER BY id;
3057 SELECT * FROM t2 ORDER BY i;
3058 SELECT * FROM t3 ORDER BY id;
3059 INSERT INTO t4 VALUES(sf1());
3060 SELECT * FROM t1 ORDER BY id;
3061 SELECT * FROM t2 ORDER BY i;
3062 SELECT * FROM t3 ORDER BY id;
3063 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
3064 SELECT * FROM t1 ORDER BY id;
3065 SELECT * FROM t2 ORDER BY i;
3066 SELECT * FROM t3 ORDER BY id;
3067 DELETE FROM t4 WHERE i = sf1() + 1;
3068 SELECT * FROM t1 ORDER BY id;
3069 SELECT * FROM t2 ORDER BY i;
3070 SELECT * FROM t3 ORDER BY id;
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 DELIMITER //;
3080 CREATE FUNCTION sf1() RETURNS INTEGER
3081 BEGIN
3082 DECLARE done INT DEFAULT 0;
3083 DECLARE a CHAR(16);
3084 DECLARE b,c INT;
3085 DECLARE cur1 CURSOR FOR SELECT id,data FROM t1;
3086 DECLARE cur2 CURSOR FOR SELECT i FROM t2;
3087 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
3088 OPEN cur1;
3089 OPEN cur2;
3090 REPEAT
3091 FETCH cur1 INTO a, b;
3092 FETCH cur2 INTO c;
3093 IF NOT done THEN
3094 IF b < c THEN
3095 INSERT INTO t3 VALUES (a,b);
3096 ELSE
3097 INSERT INTO t3 VALUES (a,c);
3098 END IF;
3099 END IF;
3100 UNTIL done END REPEAT;
3101 CLOSE cur1;
3102 CLOSE cur2;
3103 RETURN 0;
3104 END//
3105 DELIMITER ;//
3106 ALTER FUNCTION sf1 LANGUAGE SQL #DET# MODIFIES SQL DATA SQL SECURITY DEFINER COMMENT 'comment' ;
3107 SELECT sf1();
3108 SELECT * FROM t1 ORDER BY id;
3109 SELECT * FROM t2 ORDER BY i;
3110 SELECT * FROM t3 ORDER BY id;
3111 INSERT INTO t4 VALUES(sf1());
3112 SELECT * FROM t1 ORDER BY id;
3113 SELECT * FROM t2 ORDER BY i;
3114 SELECT * FROM t3 ORDER BY id;
3115 UPDATE t4 SET i = sf1() + 1 WHERE i = sf1();
3116 SELECT * FROM t1 ORDER BY id;
3117 SELECT * FROM t2 ORDER BY i;
3118 SELECT * FROM t3 ORDER BY id;
3119 DELETE FROM t4 WHERE i = sf1() + 1;
3120 SELECT * FROM t1 ORDER BY id;
3121 SELECT * FROM t2 ORDER BY i;
3122 SELECT * FROM t3 ORDER BY id;
3123 DROP FUNCTION sf1;
3124 DROP TABLE t1,t2,t3,t4;
3125