summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/sp-code.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/sp-code.test')
-rw-r--r--mysql-test/main/sp-code.test1221
1 files changed, 1221 insertions, 0 deletions
diff --git a/mysql-test/main/sp-code.test b/mysql-test/main/sp-code.test
new file mode 100644
index 00000000..5bb94669
--- /dev/null
+++ b/mysql-test/main/sp-code.test
@@ -0,0 +1,1221 @@
+#
+# Test the debugging feature "show procedure/function code <name>"
+#
+
+-- source include/have_debug.inc
+
+--disable_warnings
+drop procedure if exists empty;
+drop procedure if exists code_sample;
+--enable_warnings
+
+create procedure empty()
+begin
+end;
+show procedure code empty;
+drop procedure empty;
+
+create function almost_empty()
+ returns int
+ return 0;
+show function code almost_empty;
+drop function almost_empty;
+
+delimiter //;
+create procedure code_sample(x int, out err int, out nulls int)
+begin
+ declare count int default 0;
+
+ set nulls = 0;
+ begin
+ declare c cursor for select name from t1;
+ declare exit handler for not found close c;
+
+ open c;
+ loop
+ begin
+ declare n varchar(20);
+ declare continue handler for sqlexception set err=1;
+
+ fetch c into n;
+ if isnull(n) then
+ set nulls = nulls + 1;
+ else
+ set count = count + 1;
+ update t2 set idx = count where name=n;
+ end if;
+ end;
+ end loop;
+ end;
+ select t.name, t.idx from t2 t order by idx asc;
+end//
+delimiter ;//
+show procedure code code_sample;
+drop procedure code_sample;
+
+
+#
+# BUG#15737: Stored procedure optimizer bug with LEAVE
+#
+# This is a much more extensive test case than is strictly needed,
+# but it was kept as is for two reasons:
+# - The bug occurs under some quite special circumstances, so it
+# wasn't trivial to create a smaller test,
+# - There's some value in having another more complex code sample
+# in this test file. This might catch future code generation bugs
+# that doesn't show in behaviour in any obvious way.
+
+--disable_warnings
+drop procedure if exists sudoku_solve;
+--enable_warnings
+
+delimiter //;
+create procedure sudoku_solve(p_naive boolean, p_all boolean)
+ deterministic
+ modifies sql data
+begin
+ drop temporary table if exists sudoku_work, sudoku_schedule;
+
+ create temporary table sudoku_work
+ (
+ row smallint not null,
+ col smallint not null,
+ dig smallint not null,
+ cnt smallint,
+ key using btree (cnt),
+ key using btree (row),
+ key using btree (col),
+ unique key using hash (row,col)
+ );
+
+ create temporary table sudoku_schedule
+ (
+ idx int not null auto_increment primary key,
+ row smallint not null,
+ col smallint not null
+ );
+
+ call sudoku_init();
+
+ if p_naive then
+ update sudoku_work set cnt = 0 where dig = 0;
+ else
+ call sudoku_count();
+ end if;
+ insert into sudoku_schedule (row,col)
+ select row,col from sudoku_work where cnt is not null order by cnt desc;
+
+ begin
+ declare v_scounter bigint default 0;
+ declare v_i smallint default 1;
+ declare v_dig smallint;
+ declare v_schedmax smallint;
+
+ select count(*) into v_schedmax from sudoku_schedule;
+
+ more:
+ loop
+ begin
+ declare v_tcounter bigint default 0;
+
+ sched:
+ while v_i <= v_schedmax do
+ begin
+ declare v_row, v_col smallint;
+
+ select row,col into v_row,v_col from sudoku_schedule where v_i = idx;
+
+ select dig into v_dig from sudoku_work
+ where v_row = row and v_col = col;
+
+ case v_dig
+ when 0 then
+ set v_dig = 1;
+ update sudoku_work set dig = 1
+ where v_row = row and v_col = col;
+ when 9 then
+ if v_i > 0 then
+ update sudoku_work set dig = 0
+ where v_row = row and v_col = col;
+ set v_i = v_i - 1;
+ iterate sched;
+ else
+ select v_scounter as 'Solutions';
+ leave more;
+ end if;
+ else
+ set v_dig = v_dig + 1;
+ update sudoku_work set dig = v_dig
+ where v_row = row and v_col = col;
+ end case;
+
+ set v_tcounter = v_tcounter + 1;
+ if not sudoku_digit_ok(v_row, v_col, v_dig) then
+ iterate sched;
+ end if;
+ set v_i = v_i + 1;
+ end;
+ end while sched;
+
+ select dig from sudoku_work;
+ select v_tcounter as 'Tests';
+ set v_scounter = v_scounter + 1;
+
+ if p_all and v_i > 0 then
+ set v_i = v_i - 1;
+ else
+ leave more;
+ end if;
+ end;
+ end loop more;
+ end;
+
+ drop temporary table sudoku_work, sudoku_schedule;
+end//
+delimiter ;//
+
+# The interestings parts are where the code for the two "leave" are:
+# ...
+#| 26 | jump_if_not 30 (v_i@3 > 0) |
+# ...
+#| 30 | stmt 0 "select v_scounter as 'Solutions'" |
+#| 31 | jump 45 |
+# ...
+#| 42 | jump_if_not 45 (p_all@1 and (v_i@3 > 0)) |
+#| 43 | set v_i@3 (v_i@3 - 1) |
+#| 44 | jump 14 |
+#| 45 | stmt 9 "drop temporary table sudoku_work, sud..." |
+#+-----+-----------------------------------------------------------------------+
+# The bug appeared at position 42 (with the wrong destination).
+show procedure code sudoku_solve;
+
+drop procedure sudoku_solve;
+
+#
+# Bug#19194 (Right recursion in parser for CASE causes excessive stack
+# usage, limitation)
+# This bug also exposed a flaw in the generated code with nested case
+# statements
+#
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS proc_19194_simple;
+DROP PROCEDURE IF EXISTS proc_19194_searched;
+DROP PROCEDURE IF EXISTS proc_19194_nested_1;
+DROP PROCEDURE IF EXISTS proc_19194_nested_2;
+DROP PROCEDURE IF EXISTS proc_19194_nested_3;
+DROP PROCEDURE IF EXISTS proc_19194_nested_4;
+--enable_warnings
+
+delimiter |;
+
+CREATE PROCEDURE proc_19194_simple(i int)
+BEGIN
+ DECLARE str CHAR(10);
+
+ CASE i
+ WHEN 1 THEN SET str="1";
+ WHEN 2 THEN SET str="2";
+ WHEN 3 THEN SET str="3";
+ ELSE SET str="unknown";
+ END CASE;
+
+ SELECT str;
+END|
+
+CREATE PROCEDURE proc_19194_searched(i int)
+BEGIN
+ DECLARE str CHAR(10);
+
+ CASE
+ WHEN i=1 THEN SET str="1";
+ WHEN i=2 THEN SET str="2";
+ WHEN i=3 THEN SET str="3";
+ ELSE SET str="unknown";
+ END CASE;
+
+ SELECT str;
+END|
+
+# Outer SIMPLE case, inner SEARCHED case
+CREATE PROCEDURE proc_19194_nested_1(i int, j int)
+BEGIN
+ DECLARE str_i CHAR(10);
+ DECLARE str_j CHAR(10);
+
+ CASE i
+ WHEN 10 THEN SET str_i="10";
+ WHEN 20 THEN
+ BEGIN
+ set str_i="20";
+ CASE
+ WHEN j=1 THEN SET str_j="1";
+ WHEN j=2 THEN SET str_j="2";
+ WHEN j=3 THEN SET str_j="3";
+ ELSE SET str_j="unknown";
+ END CASE;
+ select "i was 20";
+ END;
+ WHEN 30 THEN SET str_i="30";
+ WHEN 40 THEN SET str_i="40";
+ ELSE SET str_i="unknown";
+ END CASE;
+
+ SELECT str_i, str_j;
+END|
+
+# Outer SEARCHED case, inner SIMPLE case
+CREATE PROCEDURE proc_19194_nested_2(i int, j int)
+BEGIN
+ DECLARE str_i CHAR(10);
+ DECLARE str_j CHAR(10);
+
+ CASE
+ WHEN i=10 THEN SET str_i="10";
+ WHEN i=20 THEN
+ BEGIN
+ set str_i="20";
+ CASE j
+ WHEN 1 THEN SET str_j="1";
+ WHEN 2 THEN SET str_j="2";
+ WHEN 3 THEN SET str_j="3";
+ ELSE SET str_j="unknown";
+ END CASE;
+ select "i was 20";
+ END;
+ WHEN i=30 THEN SET str_i="30";
+ WHEN i=40 THEN SET str_i="40";
+ ELSE SET str_i="unknown";
+ END CASE;
+
+ SELECT str_i, str_j;
+END|
+
+# Outer SIMPLE case, inner SIMPLE case
+CREATE PROCEDURE proc_19194_nested_3(i int, j int)
+BEGIN
+ DECLARE str_i CHAR(10);
+ DECLARE str_j CHAR(10);
+
+ CASE i
+ WHEN 10 THEN SET str_i="10";
+ WHEN 20 THEN
+ BEGIN
+ set str_i="20";
+ CASE j
+ WHEN 1 THEN SET str_j="1";
+ WHEN 2 THEN SET str_j="2";
+ WHEN 3 THEN SET str_j="3";
+ ELSE SET str_j="unknown";
+ END CASE;
+ select "i was 20";
+ END;
+ WHEN 30 THEN SET str_i="30";
+ WHEN 40 THEN SET str_i="40";
+ ELSE SET str_i="unknown";
+ END CASE;
+
+ SELECT str_i, str_j;
+END|
+
+# Outer SEARCHED case, inner SEARCHED case
+CREATE PROCEDURE proc_19194_nested_4(i int, j int)
+BEGIN
+ DECLARE str_i CHAR(10);
+ DECLARE str_j CHAR(10);
+
+ CASE
+ WHEN i=10 THEN SET str_i="10";
+ WHEN i=20 THEN
+ BEGIN
+ set str_i="20";
+ CASE
+ WHEN j=1 THEN SET str_j="1";
+ WHEN j=2 THEN SET str_j="2";
+ WHEN j=3 THEN SET str_j="3";
+ ELSE SET str_j="unknown";
+ END CASE;
+ select "i was 20";
+ END;
+ WHEN i=30 THEN SET str_i="30";
+ WHEN i=40 THEN SET str_i="40";
+ ELSE SET str_i="unknown";
+ END CASE;
+
+ SELECT str_i, str_j;
+END|
+
+delimiter ;|
+
+SHOW PROCEDURE CODE proc_19194_simple;
+SHOW PROCEDURE CODE proc_19194_searched;
+SHOW PROCEDURE CODE proc_19194_nested_1;
+SHOW PROCEDURE CODE proc_19194_nested_2;
+SHOW PROCEDURE CODE proc_19194_nested_3;
+SHOW PROCEDURE CODE proc_19194_nested_4;
+
+CALL proc_19194_nested_1(10, 1);
+
+#
+# Before 19194, the generated code was:
+# 20 jump_if_not 23(27) 30
+# 21 set str_i@2 _latin1'30'
+# As opposed to the expected:
+# 20 jump_if_not 23(27) (case_expr@0 = 30)
+# 21 set str_i@2 _latin1'30'
+#
+# and as a result, this call returned "30",
+# because the expression 30 is always true,
+# masking the case 40, case 0 and the else.
+#
+CALL proc_19194_nested_1(25, 1);
+
+CALL proc_19194_nested_1(20, 1);
+CALL proc_19194_nested_1(20, 2);
+CALL proc_19194_nested_1(20, 3);
+CALL proc_19194_nested_1(20, 4);
+CALL proc_19194_nested_1(30, 1);
+CALL proc_19194_nested_1(40, 1);
+CALL proc_19194_nested_1(0, 0);
+
+CALL proc_19194_nested_2(10, 1);
+
+#
+# Before 19194, the generated code was:
+# 20 jump_if_not 23(27) (case_expr@0 = (i@0 = 30))
+# 21 set str_i@2 _latin1'30'
+# As opposed to the expected:
+# 20 jump_if_not 23(27) (i@0 = 30)
+# 21 set str_i@2 _latin1'30'
+# and as a result, this call crashed the server, because there is no
+# such variable as "case_expr@0".
+#
+CALL proc_19194_nested_2(25, 1);
+
+CALL proc_19194_nested_2(20, 1);
+CALL proc_19194_nested_2(20, 2);
+CALL proc_19194_nested_2(20, 3);
+CALL proc_19194_nested_2(20, 4);
+CALL proc_19194_nested_2(30, 1);
+CALL proc_19194_nested_2(40, 1);
+CALL proc_19194_nested_2(0, 0);
+
+CALL proc_19194_nested_3(10, 1);
+CALL proc_19194_nested_3(25, 1);
+CALL proc_19194_nested_3(20, 1);
+CALL proc_19194_nested_3(20, 2);
+CALL proc_19194_nested_3(20, 3);
+CALL proc_19194_nested_3(20, 4);
+CALL proc_19194_nested_3(30, 1);
+CALL proc_19194_nested_3(40, 1);
+CALL proc_19194_nested_3(0, 0);
+
+CALL proc_19194_nested_4(10, 1);
+CALL proc_19194_nested_4(25, 1);
+CALL proc_19194_nested_4(20, 1);
+CALL proc_19194_nested_4(20, 2);
+CALL proc_19194_nested_4(20, 3);
+CALL proc_19194_nested_4(20, 4);
+CALL proc_19194_nested_4(30, 1);
+CALL proc_19194_nested_4(40, 1);
+CALL proc_19194_nested_4(0, 0);
+
+DROP PROCEDURE proc_19194_simple;
+DROP PROCEDURE proc_19194_searched;
+DROP PROCEDURE proc_19194_nested_1;
+DROP PROCEDURE proc_19194_nested_2;
+DROP PROCEDURE proc_19194_nested_3;
+DROP PROCEDURE proc_19194_nested_4;
+
+#
+# Bug#19207: Final parenthesis omitted for CREATE INDEX in Stored
+# Procedure
+#
+# Wrong criteria was used to distinguish the case when there was no
+# lookahead performed in the parser. Bug affected only statements
+# ending in one-character token without any optional tail, like CREATE
+# INDEX and CALL.
+#
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+CREATE PROCEDURE p1() CREATE INDEX idx ON t1 (c1);
+SHOW PROCEDURE CODE p1;
+
+DROP PROCEDURE p1;
+
+
+#
+# Bug#26977 exception handlers never hreturn
+#
+--disable_warnings
+drop table if exists t1;
+drop procedure if exists proc_26977_broken;
+drop procedure if exists proc_26977_works;
+--enable_warnings
+
+create table t1(a int unique);
+
+delimiter //;
+
+create procedure proc_26977_broken(v int)
+begin
+ declare i int default 5;
+
+ declare continue handler for sqlexception
+ begin
+ select 'caught something';
+ retry:
+ while i > 0 do
+ begin
+ set i = i - 1;
+ select 'looping', i;
+ end;
+ end while retry;
+ end;
+
+ select 'do something';
+ insert into t1 values (v);
+ select 'do something again';
+ insert into t1 values (v);
+end//
+
+create procedure proc_26977_works(v int)
+begin
+ declare i int default 5;
+
+ declare continue handler for sqlexception
+ begin
+ select 'caught something';
+ retry:
+ while i > 0 do
+ begin
+ set i = i - 1;
+ select 'looping', i;
+ end;
+ end while retry;
+ select 'optimizer: keep hreturn';
+ end;
+
+ select 'do something';
+ insert into t1 values (v);
+ select 'do something again';
+ insert into t1 values (v);
+end//
+delimiter ;//
+
+show procedure code proc_26977_broken;
+
+show procedure code proc_26977_works;
+
+## This caust an error because of jump short cut
+## optimization.
+call proc_26977_broken(1);
+
+## This works
+call proc_26977_works(2);
+
+drop table t1;
+drop procedure proc_26977_broken;
+drop procedure proc_26977_works;
+
+#
+# Bug#33618 Crash in sp_rcontext
+#
+
+--disable_warnings
+drop procedure if exists proc_33618_h;
+drop procedure if exists proc_33618_c;
+--enable_warnings
+
+delimiter //;
+
+create procedure proc_33618_h(num int)
+begin
+ declare count1 int default '0';
+ declare vb varchar(30);
+ declare last_row int;
+
+ while(num>=1) do
+ set num=num-1;
+ begin
+ declare cur1 cursor for select `a` from t_33618;
+ declare continue handler for not found set last_row = 1;
+ set last_row:=0;
+ open cur1;
+ rep1:
+ repeat
+ begin
+ declare exit handler for 1062 begin end;
+ fetch cur1 into vb;
+ if (last_row = 1) then
+ ## should generate a hpop instruction here
+ leave rep1;
+ end if;
+ end;
+ until last_row=1
+ end repeat;
+ close cur1;
+ end;
+ end while;
+end//
+
+create procedure proc_33618_c(num int)
+begin
+ declare count1 int default '0';
+ declare vb varchar(30);
+ declare last_row int;
+
+ while(num>=1) do
+ set num=num-1;
+ begin
+ declare cur1 cursor for select `a` from t_33618;
+ declare continue handler for not found set last_row = 1;
+ set last_row:=0;
+ open cur1;
+ rep1:
+ repeat
+ begin
+ declare cur2 cursor for select `b` from t_33618;
+ fetch cur1 into vb;
+ if (last_row = 1) then
+ ## should generate a cpop instruction here
+ leave rep1;
+ end if;
+ end;
+ until last_row=1
+ end repeat;
+ close cur1;
+ end;
+ end while;
+end//
+delimiter ;//
+
+show procedure code proc_33618_h;
+show procedure code proc_33618_c;
+
+drop procedure proc_33618_h;
+drop procedure proc_33618_c;
+
+#
+# Bug#20906 (Multiple assignments in SET in stored routine produce incorrect
+# instructions)
+#
+
+--disable_warnings
+drop procedure if exists p_20906_a;
+drop procedure if exists p_20906_b;
+--enable_warnings
+
+create procedure p_20906_a() SET @a=@a+1, @b=@b+1;
+show procedure code p_20906_a;
+
+set @a=1;
+set @b=1;
+
+call p_20906_a();
+select @a, @b;
+
+create procedure p_20906_b() SET @a=@a+1, @b=@b+1, @c=@c+1;
+show procedure code p_20906_b;
+
+set @a=1;
+set @b=1;
+set @c=1;
+
+call p_20906_b();
+select @a, @b, @c;
+
+drop procedure p_20906_a;
+drop procedure p_20906_b;
+
+--echo End of 5.0 tests.
+
+#
+# Bug #26303: reserve() not called before qs_append() may lead to buffer
+# overflow
+#
+DELIMITER //;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE dummy int default 0;
+
+ CASE 12
+ WHEN 12
+ THEN SET dummy = 0;
+ END CASE;
+END//
+DELIMITER ;//
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+--echo #
+--echo # Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP
+--echo #
+
+--echo
+--echo # - Case 4: check that "No Data trumps Warning".
+--echo
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (1), (2), (3);
+
+delimiter |;
+
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE c CURSOR FOR SELECT a FROM t1;
+
+ OPEN c;
+
+ BEGIN
+ DECLARE v INT;
+
+ DECLARE CONTINUE HANDLER FOR SQLWARNING
+ BEGIN
+ SELECT "Warning found!";
+ SHOW WARNINGS;
+ END;
+
+ DECLARE EXIT HANDLER FOR NOT FOUND
+ BEGIN
+ SELECT "End of Result Set found!";
+ SHOW WARNINGS;
+ END;
+
+ WHILE TRUE DO
+ FETCH c INTO v;
+ END WHILE;
+ END;
+
+ CLOSE c;
+
+ SELECT a INTO @foo FROM t1 LIMIT 1; # Clear warning stack
+END|
+
+delimiter ;|
+
+SET @save_dbug = @@debug_dbug;
+SET SESSION debug_dbug="+d,bug23032_emit_warning";
+CALL p1();
+SET SESSION debug_dbug=@save_dbug;
+
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE
+--echo #
+SET @@SQL_MODE = '';
+DELIMITER $;
+CREATE FUNCTION testf_bug11763507() RETURNS INT
+BEGIN
+ RETURN 0;
+END
+$
+
+CREATE PROCEDURE testp_bug11763507()
+BEGIN
+ SELECT "PROCEDURE testp_bug11763507";
+END
+$
+
+DELIMITER ;$
+
+# STORED FUNCTIONS
+SHOW FUNCTION CODE testf_bug11763507;
+SHOW FUNCTION CODE TESTF_bug11763507;
+
+# STORED PROCEDURE
+SHOW PROCEDURE CODE testp_bug11763507;
+SHOW PROCEDURE CODE TESTP_bug11763507;
+
+DROP PROCEDURE testp_bug11763507;
+DROP FUNCTION testf_bug11763507;
+
+--echo #END OF BUG#11763507 test.
+
+
+--echo #
+--echo # MDEV-23408 Wrong result upon query from I_S and further Assertion `!alias_arg || strlen(alias_arg->str) == alias_arg->length' failed with certain connection charset
+--echo #
+
+SET NAMES utf8;
+SET SESSION character_set_connection=latin1;
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE a VARCHAR(10) CHARACTER SET utf8;
+ SET a='ä';
+ SELECT a, 'ä' AS b;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1;
+DROP PROCEDURE p1;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
+
+
+--echo #
+--echo # MDEV-13581 ROW TYPE OF t1 and t1%ROWTYPE for routine parameters
+--echo #
+
+CREATE TABLE t1 (a INT, b TEXT);
+DELIMITER $$;
+CREATE PROCEDURE p1(a ROW TYPE OF t1, OUT b ROW TYPE OF t1)
+BEGIN
+ SET a.a = 100;
+ SET a.b = 'aaa';
+ SET b.a = 200;
+ SET b.b = 'bbb';
+ SET a = b;
+ SET b = a;
+ SET a.a = b.a;
+ SET b.a = a.a;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT
+--echo #
+
+--echo # Integer range FOR loop
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ FOR i IN 1..3
+ DO
+ SELECT i;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo # Nested integer range FOR loops
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ fori:
+ FOR i IN 1..3
+ DO
+ forj:
+ FOR j IN 1..3
+ DO
+ IF i = 3 THEN
+ LEAVE fori;
+ END IF;
+ IF j = 3 THEN
+ LEAVE forj;
+ END IF;
+ SELECT i,j;
+ END FOR;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo # Explicit cursor FOR loops
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE cur0 CURSOR FOR SELECT 10 AS a, 'b0' AS b;
+ DECLARE cur1 CURSOR FOR SELECT 10 AS a, 'b0' AS b;
+ DECLARE cur2 CURSOR FOR SELECT 10 AS a, 'b0' AS b;
+ FOR rec1 IN cur1
+ DO
+ SELECT rec1.a, rec1.b;
+ SET rec1.a= 11;
+ SET rec1.b= 'b1';
+ SELECT rec1.a, rec1.b;
+ END FOR;
+ FOR rec0 IN cur0
+ DO
+ SET rec0.a= 10;
+ SET rec0.b='b0';
+ END FOR;
+ FOR rec2 IN cur2
+ DO
+ SET rec2.a= 10;
+ SET rec2.b='b0';
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo # Nested explicit cursor FOR loops
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE cur0 CURSOR FOR SELECT 10 AS a, 'b0' AS b;
+ FOR rec0 IN cur0
+ DO
+ BEGIN
+ DECLARE cur1 CURSOR FOR SELECT 11 AS a, 'b1' AS b;
+ SET rec0.a= 11;
+ SET rec0.b= 'b0';
+ FOR rec1 IN cur1
+ DO
+ SET rec1.a= 11;
+ SET rec1.b= 'b1';
+ BEGIN
+ DECLARE cur2 CURSOR FOR SELECT 12 AS a, 'b2' AS b;
+ FOR rec2 IN cur2
+ DO
+ SET rec2.a=12;
+ SET rec2.b='b2';
+ END FOR;
+ END;
+ END FOR;
+ END;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo # Implicit cursor FOR loops
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
+ DO
+ SELECT rec1.a, rec1.b;
+ SET rec1.a= 11;
+ SET rec1.b= 'b1';
+ SELECT rec1.a, rec1.b;
+ END FOR;
+ FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
+ DO
+ SET rec0.a= 10;
+ SET rec0.b='b0';
+ END FOR;
+ FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
+ DO
+ SET rec2.a= 10;
+ SET rec2.b='b0';
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+--echo # Nested implicit cursor FOR loops
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
+ DO
+ SET rec0.a= 11;
+ SET rec0.b= 'b0';
+ FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
+ DO
+ SET rec1.a= 11;
+ SET rec1.b= 'b1';
+ FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
+ DO
+ SET rec2.a=12;
+ SET rec2.b='b2';
+ END FOR;
+ END FOR;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+--echo #
+--echo # MDEV-14623: Output of show function code does not show FETCH GROUP NEXT ROW
+--echo # for custom aggregates
+--echo #
+
+delimiter |;
+create aggregate function f1(x INT) returns int
+begin
+ declare continue handler for not found return 0;
+ loop
+ fetch group next row;
+ insert into t2 (sal) values (x);
+ end loop;
+end|
+
+delimiter ;|
+show function code f1;
+drop function f1;
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
+
+--echo #
+--echo # Start of 10.4 tests
+--echo #
+
+--echo #
+--echo # MDEV-32275 getting error 'Illegal parameter data types row and bigint for operation '+' ' when using ITERATE in a FOR..DO
+--echo #
+
+--echo #
+--echo # Unlabeled FOR/cursor with a nested labeled LOOP inside
+--echo #
+
+DELIMITER $$;
+CREATE OR REPLACE PROCEDURE p1()
+BEGIN
+ DECLARE loopDone TINYINT DEFAULT FALSE;
+ FOR _row IN (SELECT '' AS a) DO
+ SELECT 'start of outerLoop';
+ innerLoop: LOOP
+ SELECT 'start of innerLoop';
+ IF loopDone THEN
+ LEAVE innerLoop;
+ END IF;
+ SET loopDone = TRUE;
+ IF _row.a = 'v1'
+ THEN
+ SELECT 'start of THEN block';
+ ITERATE innerLoop;
+ END IF;
+ SELECT 'end of innerLoop';
+ END LOOP;
+ SELECT 'end of outerLoop';
+ END FOR;
+END
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Labeled FOR/cursor with a nested labeled LOOP
+--echo #
+
+DELIMITER $$;
+CREATE OR REPLACE PROCEDURE p1()
+BEGIN
+ DECLARE loopDone TINYINT DEFAULT FALSE;
+outerLoop:
+ FOR _row IN (SELECT '' AS a) DO
+ SELECT 'start of outerLoop';
+ innerLoop: LOOP
+ SELECT 'start of innerLoop';
+ IF loopDone THEN
+ LEAVE innerLoop;
+ END IF;
+ SET loopDone = TRUE;
+ IF _row.a = 'v1'
+ THEN
+ SELECT 'start of IF/v1/THEN block';
+ ITERATE innerLoop;
+ END IF;
+ IF _row.a = 'v2'
+ THEN
+ SELECT 'start of IF/v2/THEN block';
+ ITERATE outerLoop;
+ END IF;
+ SELECT 'end of innerLoop';
+ END LOOP;
+ SELECT 'end of outerLoop';
+ END FOR;
+END
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Unlabeled FOR/integer with a labeled LOOP inside
+--echo #
+
+DELIMITER $$;
+CREATE OR REPLACE PROCEDURE p1()
+BEGIN
+ DECLARE loopDone TINYINT DEFAULT FALSE;
+ FOR _index IN 1..10 DO
+ SELECT 'start of outerLoop';
+ innerLoop: LOOP
+ SELECT 'start of innerLoop';
+ IF loopDone THEN
+ LEAVE innerLoop;
+ END IF;
+ SET loopDone = TRUE;
+ IF _index = 1
+ THEN
+ SELECT 'start of THEN block';
+ ITERATE innerLoop;
+ END IF;
+ SELECT 'end of innerLoop';
+ END LOOP;
+ SELECT 'end of outerLoop';
+ END FOR;
+END
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Labeled FOR/integer with a labeled LOOP inside
+--echo #
+
+DELIMITER $$;
+CREATE OR REPLACE PROCEDURE p1()
+BEGIN
+ DECLARE loopDone TINYINT DEFAULT FALSE;
+outerLoop:
+ FOR _index IN 1..10 DO
+ SELECT 'start of outerLoop';
+ innerLoop: LOOP
+ SELECT 'start of innerLoop';
+ IF loopDone THEN
+ LEAVE innerLoop;
+ END IF;
+ SET loopDone = TRUE;
+ IF _index = 1
+ THEN
+ SELECT 'start of IF/1/THEN block';
+ ITERATE innerLoop;
+ END IF;
+ IF _index = 2
+ THEN
+ SELECT 'start of IF/2/THEN block';
+ ITERATE outerLoop;
+ END IF;
+ SELECT 'end of innerLoop';
+ END LOOP;
+ SELECT 'end of outerLoop';
+ END FOR;
+END
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Unlabeled FOR/integer with a labeled FOR inside
+--echo #
+
+DELIMITER $$;
+CREATE OR REPLACE PROCEDURE p1()
+BEGIN
+ DECLARE loopDone TINYINT DEFAULT FALSE;
+ FOR _index_outer IN 1..10 DO
+ SELECT 'start of outerLoop';
+ innerLoop:
+ FOR _index_inner IN 1..10 DO
+ SELECT 'start of innerLoop';
+ IF loopDone THEN
+ LEAVE innerLoop;
+ END IF;
+ SET loopDone = TRUE;
+ IF _index_inner = 1
+ THEN
+ SELECT 'start of IF/1/THEN block';
+ ITERATE innerLoop;
+ END IF;
+ SELECT 'end of innerLoop';
+ END FOR;
+ SELECT 'end of outerLoop';
+ END FOR;
+END
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+
+--echo #
+--echo # Labeled FOR/integer with a labeled FOR inside
+--echo #
+
+DELIMITER $$;
+CREATE OR REPLACE PROCEDURE p1()
+BEGIN
+ DECLARE loopDone TINYINT DEFAULT FALSE;
+outerLoop:
+ FOR _index_outer IN 1..10 DO
+ SELECT 'start of outerLoop';
+ innerLoop:
+ FOR _index_inner IN 1..10 DO
+ SELECT 'start of innerLoop';
+ IF loopDone THEN
+ LEAVE innerLoop;
+ END IF;
+ SET loopDone = TRUE;
+ IF _index_inner = 1
+ THEN
+ SELECT 'start of IF/1/THEN block';
+ ITERATE innerLoop;
+ END IF;
+ IF _index_inner = 2
+ THEN
+ SELECT 'start of IF/2/THEN block';
+ ITERATE outerLoop;
+ END IF;
+ SELECT 'end of innerLoop';
+ END FOR;
+ SELECT 'end of outerLoop';
+ END FOR;
+END
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
+
+
+--echo #
+--echo # MDEV-19640 Wrong SHOW PROCEDURE output for SET GLOBAL sysvar1=expr, sysvar2=expr
+--echo #
+
+DELIMITER $$;
+CREATE OR REPLACE PROCEDURE p1()
+BEGIN
+ SET GLOBAL max_allowed_packet=16000000, max_error_count=60;
+ SELECT @@GLOBAL.max_allowed_packet, @@GLOBAL.max_error_count;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-23 13:04:10 +0000