Debian Codegraph @

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

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