summaryrefslogtreecommitdiff
path: root/mysql-test/t/sp-code.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/sp-code.test')
-rw-r--r--mysql-test/t/sp-code.test235
1 files changed, 235 insertions, 0 deletions
diff --git a/mysql-test/t/sp-code.test b/mysql-test/t/sp-code.test
index 72efa831059..97bc29fcad2 100644
--- a/mysql-test/t/sp-code.test
+++ b/mysql-test/t/sp-code.test
@@ -191,6 +191,241 @@ 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
View Lorry Controller Status