diff options
Diffstat (limited to 'mysql-test/main/sp-vars.test')
| -rw-r--r-- | mysql-test/main/sp-vars.test | 2200 |
1 files changed, 2200 insertions, 0 deletions
diff --git a/mysql-test/main/sp-vars.test b/mysql-test/main/sp-vars.test new file mode 100644 index 00000000..4049cf19 --- /dev/null +++ b/mysql-test/main/sp-vars.test @@ -0,0 +1,2200 @@ +########################################################################### +# +# Cleanup. +# +########################################################################### + +--disable_warnings + +# Drop stored routines (if any) for general SP-vars test cases. These routines +# are created in include/sp-vars.inc file. + +DROP PROCEDURE IF EXISTS sp_vars_check_dflt; +DROP PROCEDURE IF EXISTS sp_vars_check_assignment; +DROP FUNCTION IF EXISTS sp_vars_check_ret1; +DROP FUNCTION IF EXISTS sp_vars_check_ret2; +DROP FUNCTION IF EXISTS sp_vars_check_ret3; +DROP FUNCTION IF EXISTS sp_vars_check_ret4; +DROP FUNCTION IF EXISTS sp_vars_div_zero; + +--enable_warnings + +########################################################################### +# +# Some general tests for SP-vars functionality. +# +########################################################################### + +# Create the procedure in ANSI mode. Check that all necessary warnings are +# emitted properly. + +SET @@sql_mode = 'ansi'; + +--source include/sp-vars.inc + +--echo +--echo --------------------------------------------------------------- +--echo Calling the routines, created in ANSI mode. +--echo --------------------------------------------------------------- +--echo + +CALL sp_vars_check_dflt(); + +CALL sp_vars_check_assignment(); + +SELECT sp_vars_check_ret1(); + +SELECT sp_vars_check_ret2(); + +SELECT sp_vars_check_ret3(); + +SELECT sp_vars_check_ret4(); + +SELECT sp_vars_div_zero(); + +# Check that changing sql_mode after creating a store procedure does not +# matter. + +SET @@sql_mode = 'traditional'; + +--echo +--echo --------------------------------------------------------------- +--echo Calling in TRADITIONAL mode the routines, created in ANSI mode. +--echo --------------------------------------------------------------- +--echo + +CALL sp_vars_check_dflt(); + +CALL sp_vars_check_assignment(); + +SELECT sp_vars_check_ret1(); + +SELECT sp_vars_check_ret2(); + +SELECT sp_vars_check_ret3(); + +SELECT sp_vars_check_ret4(); + +SELECT sp_vars_div_zero(); + +# Create the procedure in TRADITIONAL mode. Check that error will be thrown on +# execution. + +DROP PROCEDURE sp_vars_check_dflt; +DROP PROCEDURE sp_vars_check_assignment; +DROP FUNCTION sp_vars_check_ret1; +DROP FUNCTION sp_vars_check_ret2; +DROP FUNCTION sp_vars_check_ret3; +DROP FUNCTION sp_vars_check_ret4; +DROP FUNCTION sp_vars_div_zero; + +--source include/sp-vars.inc + +--echo +--echo --------------------------------------------------------------- +--echo Calling the routines, created in TRADITIONAL mode. +--echo --------------------------------------------------------------- +--echo + +--error ER_WARN_DATA_OUT_OF_RANGE +CALL sp_vars_check_dflt(); + +--error ER_WARN_DATA_OUT_OF_RANGE +CALL sp_vars_check_assignment(); + +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT sp_vars_check_ret1(); + +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT sp_vars_check_ret2(); + +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +SELECT sp_vars_check_ret3(); + +# TODO: Is it an error, that only a warning is emitted here? Check the same +# behaviour with tables. + +SELECT sp_vars_check_ret4(); + +--error ER_DIVISION_BY_ZERO +SELECT sp_vars_div_zero(); + +SET @@sql_mode = 'ansi'; + +# +# Cleanup. +# + +DROP PROCEDURE sp_vars_check_dflt; +DROP PROCEDURE sp_vars_check_assignment; +DROP FUNCTION sp_vars_check_ret1; +DROP FUNCTION sp_vars_check_ret2; +DROP FUNCTION sp_vars_check_ret3; +DROP FUNCTION sp_vars_check_ret4; +DROP FUNCTION sp_vars_div_zero; + +########################################################################### +# +# Tests for BIT data type. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BIT data type tests +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + DECLARE v1 BIT; + DECLARE v2 BIT(1); + DECLARE v3 BIT(3) DEFAULT b'101'; + DECLARE v4 BIT(64) DEFAULT 0x5555555555555555; + DECLARE v5 BIT(3); + DECLARE v6 BIT(64); + DECLARE v7 BIT(8) DEFAULT 128; + DECLARE v8 BIT(8) DEFAULT '128'; + DECLARE v9 BIT(8) DEFAULT ' 128'; + DECLARE v10 BIT(8) DEFAULT 'x 128'; + + SET v1 = v4; + SET v2 = 0; + SET v5 = v4; # check overflow + SET v6 = v3; # check padding + + SELECT HEX(v1); + SELECT HEX(v2); + SELECT HEX(v3); + SELECT HEX(v4); + SELECT HEX(v5); + SELECT HEX(v6); + SELECT HEX(v7); + SELECT HEX(v8); + SELECT HEX(v9); + SELECT HEX(v10); +END| +delimiter ;| + +CALL p1(); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Tests for CASE statements functionality: +# - test for general functionality (scopes, nested cases, CASE in loops); +# - test that if type of the CASE expression is changed on each iteration, +# the execution will be correct. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo CASE expression tests. +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP TABLE IF EXISTS t1; + +# +# Test case. +# + +CREATE TABLE t1(log_msg VARCHAR(1024)); + +delimiter |; + +CREATE PROCEDURE p1(arg VARCHAR(255)) +BEGIN + INSERT INTO t1 VALUES('p1: step1'); + + CASE arg * 10 + WHEN 10 * 10 THEN + INSERT INTO t1 VALUES('p1: case1: on 10'); + WHEN 10 * 10 + 10 * 10 THEN + BEGIN + CASE arg / 10 + WHEN 1 THEN + INSERT INTO t1 VALUES('p1: case1: case2: on 1'); + WHEN 2 THEN + BEGIN + DECLARE i TINYINT DEFAULT 10; + + WHILE i > 0 DO + INSERT INTO t1 VALUES(CONCAT('p1: case1: case2: loop: i: ', i)); + + CASE MOD(i, 2) + WHEN 0 THEN + INSERT INTO t1 VALUES('p1: case1: case2: loop: i is even'); + WHEN 1 THEN + INSERT INTO t1 VALUES('p1: case1: case2: loop: i is odd'); + ELSE + INSERT INTO t1 VALUES('p1: case1: case2: loop: ERROR'); + END CASE; + + SET i = i - 1; + END WHILE; + END; + ELSE + INSERT INTO t1 VALUES('p1: case1: case2: ERROR'); + END CASE; + + CASE arg + WHEN 10 THEN + INSERT INTO t1 VALUES('p1: case1: case3: on 10'); + WHEN 20 THEN + INSERT INTO t1 VALUES('p1: case1: case3: on 20'); + ELSE + INSERT INTO t1 VALUES('p1: case1: case3: ERROR'); + END CASE; + END; + ELSE + INSERT INTO t1 VALUES('p1: case1: ERROR'); + END CASE; + + CASE arg * 10 + WHEN 10 * 10 THEN + INSERT INTO t1 VALUES('p1: case4: on 10'); + WHEN 10 * 10 + 10 * 10 THEN + BEGIN + CASE arg / 10 + WHEN 1 THEN + INSERT INTO t1 VALUES('p1: case4: case5: on 1'); + WHEN 2 THEN + BEGIN + DECLARE i TINYINT DEFAULT 10; + + WHILE i > 0 DO + INSERT INTO t1 VALUES(CONCAT('p1: case4: case5: loop: i: ', i)); + + CASE MOD(i, 2) + WHEN 0 THEN + INSERT INTO t1 VALUES('p1: case4: case5: loop: i is even'); + WHEN 1 THEN + INSERT INTO t1 VALUES('p1: case4: case5: loop: i is odd'); + ELSE + INSERT INTO t1 VALUES('p1: case4: case5: loop: ERROR'); + END CASE; + + SET i = i - 1; + END WHILE; + END; + ELSE + INSERT INTO t1 VALUES('p1: case4: case5: ERROR'); + END CASE; + + CASE arg + WHEN 10 THEN + INSERT INTO t1 VALUES('p1: case4: case6: on 10'); + WHEN 20 THEN + INSERT INTO t1 VALUES('p1: case4: case6: on 20'); + ELSE + INSERT INTO t1 VALUES('p1: case4: case6: ERROR'); + END CASE; + END; + ELSE + INSERT INTO t1 VALUES('p1: case4: ERROR'); + END CASE; +END| + +CREATE PROCEDURE p2() +BEGIN + DECLARE i TINYINT DEFAULT 3; + + WHILE i > 0 DO + IF MOD(i, 2) = 0 THEN + SET @_test_session_var = 10; + ELSE + SET @_test_session_var = 'test'; + END IF; + + CASE @_test_session_var + WHEN 10 THEN + INSERT INTO t1 VALUES('p2: case: numerical type'); + WHEN 'test' THEN + INSERT INTO t1 VALUES('p2: case: string type'); + ELSE + INSERT INTO t1 VALUES('p2: case: ERROR'); + END CASE; + + SET i = i - 1; + END WHILE; +END| + +delimiter ;| + +CALL p1(10); +CALL p1(20); + +CALL p2(); + +SELECT * FROM t1; + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; + +########################################################################### +# +# Test case for BUG#14161: Stored procedure cannot retrieve bigint unsigned. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#14161 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(col BIGINT UNSIGNED); + +INSERT INTO t1 VALUE(18446744073709551614); + +delimiter |; +CREATE PROCEDURE p1(IN arg BIGINT UNSIGNED) +BEGIN + SELECT arg; + SELECT * FROM t1; + SELECT * FROM t1 WHERE col = arg; +END| +delimiter ;| + +CALL p1(18446744073709551614); + +# +# Cleanup. +# + +DROP TABLE t1; +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#13705: parameters to stored procedures are not verified. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13705 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1(x VARCHAR(10), y CHAR(3)) READS SQL DATA +BEGIN + SELECT x, y; +END| +delimiter ;| + +CALL p1('alpha', 'abc'); +CALL p1('alpha', 'abcdef'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#13675: DATETIME/DATE type in store proc param seems to be +# converted as varbinary. +# +# TODO: test case failed. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13675 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1(x DATETIME) +BEGIN + CREATE TABLE t1 SELECT x; + SHOW CREATE TABLE t1; + DROP TABLE t1; +END| +delimiter ;| + +CALL p1(NOW()); + +CALL p1('test'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#12976: Boolean values reversed in stored procedures? +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#12976 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(b BIT(1)); + +INSERT INTO t1(b) VALUES(b'0'), (b'1'); + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + SELECT HEX(b), + b = 0, + b = FALSE, + b IS FALSE, + b = 1, + b = TRUE, + b IS TRUE + FROM t1; +END| + +CREATE PROCEDURE p2() +BEGIN + DECLARE vb BIT(1); + SELECT b INTO vb FROM t1 WHERE b = 0; + + SELECT HEX(vb), + vb = 0, + vb = FALSE, + vb IS FALSE, + vb = 1, + vb = TRUE, + vb IS TRUE; + + SELECT b INTO vb FROM t1 WHERE b = 1; + + SELECT HEX(vb), + vb = 0, + vb = FALSE, + vb IS FALSE, + vb = 1, + vb = TRUE, + vb IS TRUE; +END| +delimiter ;| + +call p1(); + +call p2(); + +# +# Cleanup. +# + +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +# Additional tests for Bug#12976 + +--disable_warnings +DROP TABLE IF EXISTS table_12976_a; +DROP TABLE IF EXISTS table_12976_b; +DROP PROCEDURE IF EXISTS proc_12976_a; +DROP PROCEDURE IF EXISTS proc_12976_b; +--enable_warnings + +CREATE TABLE table_12976_a (val bit(1)); + +CREATE TABLE table_12976_b( + appname varchar(15), + emailperm bit not null default 1, + phoneperm bit not null default 0); + +insert into table_12976_b values ('A', b'1', b'1'), ('B', b'0', b'0'); + +delimiter ||; +CREATE PROCEDURE proc_12976_a() +BEGIN + declare localvar bit(1); + SELECT val INTO localvar FROM table_12976_a; + SELECT coalesce(localvar, 1)+1, coalesce(val, 1)+1 FROM table_12976_a; +END|| + +CREATE PROCEDURE proc_12976_b( + name varchar(15), + out ep bit, + out msg varchar(10)) +BEGIN + SELECT emailperm into ep FROM table_12976_b where (appname = name); + IF ep is true THEN + SET msg = 'True'; + ELSE + SET msg = 'False'; + END IF; +END|| + +delimiter ;|| + +INSERT table_12976_a VALUES (0); +call proc_12976_a(); +UPDATE table_12976_a set val=1; +call proc_12976_a(); + +call proc_12976_b('A', @ep, @msg); +select @ep, @msg; + +call proc_12976_b('B', @ep, @msg); +select @ep, @msg; + +DROP TABLE table_12976_a; +DROP TABLE table_12976_b; +DROP PROCEDURE proc_12976_a; +DROP PROCEDURE proc_12976_b; + + +########################################################################### +# +# Test case for BUG#9572: Stored procedures: variable type declarations +# ignored. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#9572 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; + +DROP PROCEDURE IF EXISTS p4; +DROP PROCEDURE IF EXISTS p5; +DROP PROCEDURE IF EXISTS p6; +--enable_warnings + +# +# Test case. +# + +SET @@sql_mode = 'traditional'; + +delimiter |; + +CREATE PROCEDURE p1() +BEGIN + DECLARE v TINYINT DEFAULT 1e200; + SELECT v; +END| + +CREATE PROCEDURE p2() +BEGIN + DECLARE v DECIMAL(5) DEFAULT 1e200; + SELECT v; +END| + +CREATE PROCEDURE p3() +BEGIN + DECLARE v CHAR(5) DEFAULT 'abcdef'; + SELECT v LIKE 'abc___'; +END| + +CREATE PROCEDURE p4(arg VARCHAR(2)) +BEGIN + DECLARE var VARCHAR(1); + SET var := arg; + SELECT arg, var; +END| + +CREATE PROCEDURE p5(arg CHAR(2)) +BEGIN + DECLARE var CHAR(1); + SET var := arg; + SELECT arg, var; +END| + +CREATE PROCEDURE p6(arg DECIMAL(2)) +BEGIN + DECLARE var DECIMAL(1); + SET var := arg; + SELECT arg, var; +END| + +delimiter ;| + +--error ER_WARN_DATA_OUT_OF_RANGE +CALL p1(); +--error ER_WARN_DATA_OUT_OF_RANGE +CALL p2(); +--error ER_DATA_TOO_LONG +CALL p3(); + +--error ER_DATA_TOO_LONG +CALL p4('aaa'); +--error ER_DATA_TOO_LONG +CALL p5('aa'); +--error ER_WARN_DATA_OUT_OF_RANGE +CALL p6(10); + +# +# Cleanup. +# + +SET @@sql_mode = 'ansi'; + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; + +DROP PROCEDURE p4; +DROP PROCEDURE p5; +DROP PROCEDURE p6; + +########################################################################### +# +# Test case for BUG#9078: STORED PROCDURE: Decimal digits are not displayed +# when we use DECIMAL datatype. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#9078 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1 (arg DECIMAL(64,2)) +BEGIN + DECLARE var DECIMAL(64,2); + + SET var = arg; + SELECT var; +END| +delimiter ;| + +CALL p1(1929); +CALL p1(1929.00); +CALL p1(1929.003); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#8768: Functions: For any unsigned data type, -ve values can +# be passed and returned. +# +# TODO: there is a bug here -- the function created in ANSI mode should not +# throw errors instead of warnings if called in TRADITIONAL mode. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#8768 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +# Create a function in ANSI mode. + +delimiter |; +CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT +BEGIN + RETURN arg; +END| +delimiter ;| + +SELECT f1(-2500); + +# Call in TRADITIONAL mode the function created in ANSI mode. + +SET @@sql_mode = 'traditional'; + +# TODO: a warning should be emitted here. +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT f1(-2500); + +# Recreate the function in TRADITIONAL mode. + +DROP FUNCTION f1; + +delimiter |; +CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT +BEGIN + RETURN arg; +END| +delimiter ;| + +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT f1(-2500); + +# +# Cleanup. +# + +SET @@sql_mode = 'ansi'; + +DROP FUNCTION f1; + +########################################################################### +# +# Test case for BUG#8769: Functions: For Int datatypes, out of range values can +# be passed and returned. +# +# TODO: there is a bug here -- the function created in ANSI mode should not +# throw errors instead of warnings if called in TRADITIONAL mode. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#8769 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +# Create a function in ANSI mode. + +delimiter |; +CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT +BEGIN + RETURN arg; +END| +delimiter ;| + +SELECT f1(8388699); + +# Call in TRADITIONAL mode the function created in ANSI mode. + +SET @@sql_mode = 'traditional'; + +# TODO: a warning should be emitted here. +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT f1(8388699); + +# Recreate the function in TRADITIONAL mode. + +DROP FUNCTION f1; + +delimiter |; +CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT +BEGIN + RETURN arg; +END| +delimiter ;| + +--error ER_WARN_DATA_OUT_OF_RANGE +SELECT f1(8388699); + +# +# Cleanup. +# + +SET @@sql_mode = 'ansi'; + +DROP FUNCTION f1; + +########################################################################### +# +# Test case for BUG#8702: Stored Procedures: No Error/Warning shown for +# inappropriate data type matching. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#8702 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(col VARCHAR(255)); + +INSERT INTO t1(col) VALUES('Hello, world!'); + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + DECLARE sp_var INTEGER; + + SELECT col INTO sp_var FROM t1 LIMIT 1; + SET @user_var = sp_var; + + SELECT sp_var; + SELECT @user_var; +END| +delimiter ;| + +CALL p1(); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP TABLE t1; + +########################################################################### +# +# Test case for BUG#12903: upper function does not work inside a function. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#12903 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(txt VARCHAR(255)); + +delimiter |; +CREATE FUNCTION f1(arg VARCHAR(255)) RETURNS VARCHAR(255) +BEGIN + DECLARE v1 VARCHAR(255); + DECLARE v2 VARCHAR(255); + + SET v1 = CONCAT(LOWER(arg), UPPER(arg)); + SET v2 = CONCAT(LOWER(v1), UPPER(v1)); + + INSERT INTO t1 VALUES(v1), (v2); + + RETURN CONCAT(LOWER(arg), UPPER(arg)); +END| +delimiter ;| + +--disable_ps2_protocol +SELECT f1('_aBcDe_'); +--enable_ps2_protocol + +SELECT * FROM t1; + +# +# Cleanup. +# + +DROP FUNCTION f1; +DROP TABLE t1; + +########################################################################### +# +# Test case for BUG#13808: ENUM type stored procedure parameter accepts +# non-enumerated data. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13808 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +delimiter |; + +CREATE PROCEDURE p1(arg ENUM('a', 'b')) +BEGIN + SELECT arg; +END| + +CREATE PROCEDURE p2(arg ENUM('a', 'b')) +BEGIN + DECLARE var ENUM('c', 'd') DEFAULT arg; + + SELECT arg, var; +END| + +CREATE FUNCTION f1(arg ENUM('a', 'b')) RETURNS ENUM('c', 'd') +BEGIN + RETURN arg; +END| + +delimiter ;| + +CALL p1('c'); + +CALL p2('a'); + +SELECT f1('a'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP FUNCTION f1; + +########################################################################### +# +# Test case for BUG#13909: Varchar Stored Procedure Parameter always BINARY +# string (ignores CHARACTER SET). +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13909 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +--enable_warnings + +# +# Test case. +# + +delimiter |; + +CREATE PROCEDURE p1(arg VARCHAR(255)) +BEGIN + SELECT CHARSET(arg); +END| + +CREATE PROCEDURE p2(arg VARCHAR(255) CHARACTER SET UTF8) +BEGIN + SELECT CHARSET(arg); +END| + +delimiter ;| + +CALL p1('t'); +CALL p1(_UTF8 't'); + + +CALL p2('t'); +CALL p2(_LATIN1 't'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +########################################################################### +# +# Test case for BUG#14188: BINARY variables have no 0x00 padding. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#14188 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE PROCEDURE p1(arg1 BINARY(2), arg2 VARBINARY(2)) +BEGIN + DECLARE var1 BINARY(2) DEFAULT 0x41; + DECLARE var2 VARBINARY(2) DEFAULT 0x42; + + SELECT HEX(arg1), HEX(arg2); + SELECT HEX(var1), HEX(var2); +END| +delimiter ;| + +CALL p1(0x41, 0x42); + +# +# Cleanup. +# + +DROP PROCEDURE p1; + +########################################################################### +# +# Test case for BUG#15148: Stored procedure variables accept non-scalar values. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#15148 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# Test case. +# + +CREATE TABLE t1(col1 TINYINT, col2 TINYINT); + +INSERT INTO t1 VALUES(1, 2), (11, 12); + +delimiter |; +CREATE PROCEDURE p1(arg TINYINT) +BEGIN + SELECT arg; +END| +delimiter ;| + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +CALL p1((1, 2)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +CALL p1((SELECT * FROM t1 LIMIT 1)); + +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +CALL p1((SELECT col1, col2 FROM t1 LIMIT 1)); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP TABLE t1; + +########################################################################### +# +# Test case for BUG#13613: substring function in stored procedure. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13613 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +delimiter |; + +CREATE PROCEDURE p1(x VARCHAR(50)) +BEGIN + SET x = SUBSTRING(x, 1, 3); + SELECT x; +END| + +CREATE FUNCTION f1(x VARCHAR(50)) RETURNS VARCHAR(50) +BEGIN + RETURN SUBSTRING(x, 1, 3); +END| + +delimiter ;| + +CALL p1('abcdef'); + +SELECT f1('ABCDEF'); + +# +# Cleanup. +# + +DROP PROCEDURE p1; +DROP FUNCTION f1; + +########################################################################### +# +# Test case for BUG#13665: concat with '' produce incorrect results in SP. +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#13665 +--echo --------------------------------------------------------------- +--echo + +# +# Prepare. +# + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +# +# Test case. +# + +delimiter |; +CREATE FUNCTION f1() RETURNS VARCHAR(20000) +BEGIN + DECLARE var VARCHAR(2000); + + SET var = ''; + SET var = CONCAT(var, 'abc'); + SET var = CONCAT(var, ''); + + RETURN var; +END| +delimiter ;| + +SELECT f1(); + +# +# Cleanup. +# + +DROP FUNCTION f1; + + +# +# Bug#17226: Variable set in cursor on first iteration is assigned +# second iterations value +# +# The problem was in incorrect handling of local variables of type +# TEXT (BLOB). +# +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + DECLARE v_char VARCHAR(255); + DECLARE v_text TEXT DEFAULT ''; + + SET v_char = 'abc'; + + SET v_text = v_char; + + SET v_char = 'def'; + + SET v_text = concat(v_text, '|', v_char); + + SELECT v_text; +END| +delimiter ;| + +CALL p1(); + +DROP PROCEDURE p1; + +# +# Bug #27415 Text Variables in stored procedures +# If the SP varible was also referenced on the right side +# the result was corrupted. +# +DELIMITER |; + +--disable_warnings +DROP PROCEDURE IF EXISTS bug27415_text_test| +DROP PROCEDURE IF EXISTS bug27415_text_test2| +--enable_warnings + +CREATE PROCEDURE bug27415_text_test(entity_id_str_in text) +BEGIN + DECLARE str_remainder text; + + SET str_remainder = entity_id_str_in; + + select 'before substr', str_remainder; + SET str_remainder = SUBSTRING(str_remainder, 3); + select 'after substr', str_remainder; +END| + +CREATE PROCEDURE bug27415_text_test2(entity_id_str_in text) +BEGIN + DECLARE str_remainder text; + DECLARE str_remainder2 text; + + SET str_remainder2 = entity_id_str_in; + select 'before substr', str_remainder2; + SET str_remainder = SUBSTRING(str_remainder2, 3); + select 'after substr', str_remainder; +END| + +CALL bug27415_text_test('a,b,c')| +CALL bug27415_text_test('a,b,c')| +CALL bug27415_text_test2('a,b,c')| +CALL bug27415_text_test('a,b,c')| + +DROP PROCEDURE bug27415_text_test| +DROP PROCEDURE bug27415_text_test2| + +DELIMITER ;| + +# End of 5.0 tests. + +# +# Bug #26277 User variable returns one type in SELECT @v and other for CREATE as SELECT @v +# +--disable_warnings +drop function if exists f1; +drop table if exists t1; +--enable_warnings + +delimiter |; +create function f1() returns int +begin + if @a=1 then set @b='abc'; + else set @b=1; + end if; + set @a=1; + return 0; +end| + +create table t1 (a int)| +insert into t1 (a) values (1), (2)| + +--disable_ps2_protocol +set @b=1| +set @a=0| +select f1(), @b from t1| + +set @b:='test'| +set @a=0| +select f1(), @b from t1| +--enable_ps2_protocol + +delimiter ;| + +drop function f1; +drop table t1; +# End of 5.1 tests. + + +########################################################################### +# +# Test case for BUG#28299: To-number conversion warnings work +# differenly with CHAR and VARCHAR sp variables +# +########################################################################### + +--echo +--echo --------------------------------------------------------------- +--echo BUG#28299 +--echo --------------------------------------------------------------- +--echo + +DELIMITER |; +CREATE PROCEDURE ctest() +BEGIN + DECLARE i CHAR(16); + DECLARE j INT; + SET i= 'string'; + SET j= 1 + i; +END| +DELIMITER ;| + +CALL ctest(); +DROP PROCEDURE ctest; + +DELIMITER |; +CREATE PROCEDURE vctest() +BEGIN + DECLARE i VARCHAR(16); + DECLARE j INT; + SET i= 'string'; + SET j= 1 + i; +END| +DELIMITER ;| + +CALL vctest(); +DROP PROCEDURE vctest; + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-12876 Wrong data type for CREATE..SELECT sp_var +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE i8 TINYINT; + DECLARE i16 SMALLINT; + DECLARE i32 INT; + DECLARE i64 BIGINT; + DECLARE f FLOAT; + DECLARE d DOUBLE; + DECLARE b8 BIT(8); + DECLARE y YEAR; + DECLARE t1 TINYTEXT; + DECLARE t2 TEXT; + DECLARE t3 MEDIUMTEXT; + DECLARE t4 LONGTEXT; + CREATE TABLE t1 AS SELECT i8, i16, i32, i64, f, d, b8, y, t1, t2, t3, t4; +END; +$$ +DELIMITER ;$$ +CALL p1; +DESCRIBE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + +--echo # +--echo # MDEV-12917 Wrong data type for CREATE..SELECT year_sp_variable +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a YEAR; + CREATE OR REPLACE TABLE t1 AS SELECT a; + SHOW CREATE TABLE t1; + DROP TABLE t1; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP PROCEDURE p1; + + +--echo # +--echo # MDEV-15960 Wrong data type on CREATE..SELECT char_or_enum_or_text_spvar +--echo # + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE var TINYTEXT CHARACTER SET utf8; + CREATE TABLE t1 AS SELECT var; +END; +$$ +DELIMITER ;$$ +SHOW CREATE TABLE t1; +DROP TABLE t1; + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE var TEXT CHARACTER SET utf8; + CREATE TABLE t1 AS SELECT var; +END; +$$ +DELIMITER ;$$ +SHOW CREATE TABLE t1; +DROP TABLE t1; + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE var MEDIUMTEXT CHARACTER SET utf8; + CREATE TABLE t1 AS SELECT var; +END; +$$ +DELIMITER ;$$ +SHOW CREATE TABLE t1; +DROP TABLE t1; + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE var LONGTEXT CHARACTER SET utf8; + CREATE TABLE t1 AS SELECT var; +END; +$$ +DELIMITER ;$$ +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE var CHAR(1); + CREATE TABLE t1 AS SELECT var; +END; +$$ +DELIMITER ;$$ +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE var ENUM('a'); + CREATE TABLE t1 AS SELECT var; +END; +$$ +DELIMITER ;$$ +SHOW CREATE TABLE t1; +DROP TABLE t1; + + + +--echo # +--echo # MDEV-14347 CREATE PROCEDURE returns no error when using an unknown variable +--echo # + +--echo # +--echo # Simple cases (without subqueries) - the most typical problem: +--echo # a typo in an SP variable name +--echo # + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1(a INT) +BEGIN + DECLARE res INT DEFAULT 0; + IF (a < 0) THEN + SET res= a_long_variable_name_with_a_typo; + END IF; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1(a INT) +BEGIN + DECLARE res INT DEFAULT 0; + IF (a < 0) THEN + SET res= 1 + a_long_variable_name_with_a_typo; + END IF; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # Complex cases with subqueries +--echo # + +--echo # +--echo # Maybe a table field identifier (there are some tables) - no error +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE res INT DEFAULT 0; + SET res=(SELECT * FROM t1 LEFT OUTER JOIN t2 USING (c1)); +END; +$$ +DELIMITER ;$$ +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE res INT DEFAULT 0; + SET res=(SELECT * FROM t1 LEFT OUTER JOIN t2 ON (c1=c2)); +END; +$$ +DELIMITER ;$$ +DROP PROCEDURE p1; + + +--echo # +--echo # One unknown identifier, no tables +--echo # + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=unknown_ident; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=unknown_ident1.unknown_ident2; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=unknown_ident1.unknown_ident2.unknown_ident3; +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT unknown_ident); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT unknown_ident FROM dual); +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT (SELECT unknown_ident)); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT (SELECT unknown_ident FROM dual)); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT 1 WHERE unknown_ident); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT 1 WHERE unknown_ident=1); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT 1 LIMIT unknown_ident); +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # GROUP, HAVING, ORDER are not tested yet for unknown identifiers +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT 1 GROUP BY unknown_ident); +END; +$$ +DELIMITER ;$$ +DROP PROCEDURE p1; + + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE res INT DEFAULT 0; + SET res=(SELECT 1 HAVING unknown_ident); +END; +$$ +DELIMITER ;$$ +DROP PROCEDURE p1; + + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT 1 ORDER BY unknown_ident); +END; +$$ +DELIMITER ;$$ +DROP PROCEDURE p1; + + +--echo # +--echo # HAVING + aggregate_function(unknown_identifier) is a special case +--echo # + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE res INT DEFAULT 0; + SET res=(SELECT 1 HAVING SUM(unknown_ident)); +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # Known indentifier + unknown identifier, no tables +--echo # + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=a+unknown_ident; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=a+(SELECT unknown_ident); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=a+(SELECT unknown_ident FROM dual); +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT (a+(SELECT unknown_ident))); +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT (a+(SELECT unknown_ident FROM dual))); +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # Unknown indentifier + known identifier, no tables +--echo # + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=unknown_ident+a; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT unknown_ident)+a; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT unknown_ident FROM dual)+a; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT (SELECT unknown_ident)+a); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT (SELECT unknown_ident FROM dual)+a); +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # Maybe a table field indentifier + unknown identifier +--echo # + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT c1 FROM t1)+unknown_ident; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT c1 FROM t1)+(SELECT unknown_ident); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT c1 FROM t1)+(SELECT unknown_ident FROM dual); +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT ((SELECT c1 FROM t1)+(SELECT unknown_ident))); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT ((SELECT c1 FROM t1)+(SELECT unknown_ident FROM dual))); +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # Unknown indentifier + maybe a table field identifier +--echo # + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=unknown_ident+(SELECT c1 FROM t1); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT unknown_ident)+(SELECT c1 FROM t1); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT unknown_ident FROM dual)+(SELECT c1 FROM t1); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT (SELECT unknown_ident)+(SELECT c1 FROM t1)); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + SET a=(SELECT (SELECT unknown_ident FROM dual)+(SELECT c1 FROM t1)); +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # Maybe a table field identifier + maybe a field table identifier +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a INT; + -- c2 does not have a table on its level + -- but it can be a field of a table on the uppder level, i.e. t1 + SET a=(SELECT c1+(SELECT c2) FROM t1); +END; +$$ +DELIMITER ;$$ +DROP PROCEDURE p1; + + +--echo # +--echo # TVC - unknown identifier +--echo # + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1(a INT) +BEGIN + DECLARE res INT DEFAULT 0; + SET res=(VALUES(unknown_ident)); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1(a INT) +BEGIN + DECLARE res INT DEFAULT 0; + SET res=(VALUES(1),(unknown_ident)); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1(a INT) +BEGIN + DECLARE res INT DEFAULT 0; + SET res=(VALUES((SELECT unknown_ident))); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1(a INT) +BEGIN + DECLARE res INT DEFAULT 0; + SET res=(VALUES(1),((SELECT unknown_ident))); +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1(a INT) +BEGIN + DECLARE res INT DEFAULT 0; + SET res=(VALUES(1) LIMIT unknown_ident); +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # TVC - ORDER BY - not tested yet for unknown identifiers +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(a INT) +BEGIN + DECLARE res INT DEFAULT 0; + SET res=(VALUES(1) ORDER BY unknown_ident); +END; +$$ +DELIMITER ;$$ +DROP PROCEDURE p1; + + +--echo # +--echo # TVC - maybe a table field identifier - no error +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(a INT) +BEGIN + DECLARE res INT DEFAULT 0; + SET res=(VALUES((SELECT c1 FROM t1))); +END; +$$ +DELIMITER ;$$ +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE PROCEDURE p1(a INT) +BEGIN + DECLARE res INT DEFAULT 0; + SET res=(VALUES(1),((SELECT c1 FROM t1))); +END; +$$ +DELIMITER ;$$ +DROP PROCEDURE p1; + + +--echo # +--echo # Functions DEFAULT(x) and VALUE(x) +--echo # + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE res INT DEFAULT 0; + SET res=DEFAULT(unknown_ident); + SELECT res; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +CREATE PROCEDURE p1() +BEGIN + DECLARE res INT DEFAULT 0; + SET res=VALUE(unknown_ident); + SELECT res; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # End of MDEV-14347 CREATE PROCEDURE returns no error when using an unknown variable +--echo # |