mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
1240 lines
26 KiB
Text
1240 lines
26 KiB
Text
#
|
|
# 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 causes 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 #
|
|
|
|
--echo # Start of 11.8 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-10862 Stored procedures: default values for parameters (optional parameters)
|
|
--echo #
|
|
|
|
DELIMITER $$;
|
|
CREATE OR REPLACE PROCEDURE p1(par1 INT, par2 INT DEFAULT 1)
|
|
BEGIN
|
|
DECLARE var1 INT DEFAULT 5;
|
|
SELECT par1, par2 FROM DUAL;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
SHOW PROCEDURE CODE p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
--echo # End of 11.8 tests
|