1 ###########################################################################
5 ###########################################################################
9 # Drop stored routines (if any) for general SP-vars test cases. These routines
10 # are created in include/sp-vars.inc file.
12 DROP PROCEDURE IF EXISTS sp_vars_check_dflt;
13 DROP PROCEDURE IF EXISTS sp_vars_check_assignment;
14 DROP FUNCTION IF EXISTS sp_vars_check_ret1;
15 DROP FUNCTION IF EXISTS sp_vars_check_ret2;
16 DROP FUNCTION IF EXISTS sp_vars_check_ret3;
17 DROP FUNCTION IF EXISTS sp_vars_check_ret4;
18 DROP FUNCTION IF EXISTS sp_vars_div_zero;
22 ###########################################################################
24 # Some general tests for SP-vars functionality.
26 ###########################################################################
28 # Create the procedure in ANSI mode. Check that all necessary warnings are
31 SET @@sql_mode = 'ansi';
33 --source include/sp-vars.inc
36 --echo ---------------------------------------------------------------
37 --echo Calling the routines, created in ANSI mode.
38 --echo ---------------------------------------------------------------
41 CALL sp_vars_check_dflt();
43 CALL sp_vars_check_assignment();
45 SELECT sp_vars_check_ret1();
47 SELECT sp_vars_check_ret2();
49 SELECT sp_vars_check_ret3();
51 SELECT sp_vars_check_ret4();
53 SELECT sp_vars_div_zero();
55 # Check that changing sql_mode after creating a store procedure does not
58 SET @@sql_mode = 'traditional';
61 --echo ---------------------------------------------------------------
62 --echo Calling in TRADITIONAL mode the routines, created in ANSI mode.
63 --echo ---------------------------------------------------------------
66 CALL sp_vars_check_dflt();
68 CALL sp_vars_check_assignment();
70 SELECT sp_vars_check_ret1();
72 SELECT sp_vars_check_ret2();
74 SELECT sp_vars_check_ret3();
76 SELECT sp_vars_check_ret4();
78 SELECT sp_vars_div_zero();
80 # Create the procedure in TRADITIONAL mode. Check that error will be thrown on
83 DROP PROCEDURE sp_vars_check_dflt;
84 DROP PROCEDURE sp_vars_check_assignment;
85 DROP FUNCTION sp_vars_check_ret1;
86 DROP FUNCTION sp_vars_check_ret2;
87 DROP FUNCTION sp_vars_check_ret3;
88 DROP FUNCTION sp_vars_check_ret4;
89 DROP FUNCTION sp_vars_div_zero;
91 --source include/sp-vars.inc
94 --echo ---------------------------------------------------------------
95 --echo Calling the routines, created in TRADITIONAL mode.
96 --echo ---------------------------------------------------------------
99 --error ER_WARN_DATA_OUT_OF_RANGE
100 CALL sp_vars_check_dflt();
102 --error ER_WARN_DATA_OUT_OF_RANGE
103 CALL sp_vars_check_assignment();
105 --error ER_WARN_DATA_OUT_OF_RANGE
106 SELECT sp_vars_check_ret1();
108 --error ER_WARN_DATA_OUT_OF_RANGE
109 SELECT sp_vars_check_ret2();
111 --error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
112 SELECT sp_vars_check_ret3();
114 # TODO: Is it an error, that only a warning is emitted here? Check the same
115 # behaviour with tables.
117 SELECT sp_vars_check_ret4();
119 --error ER_DIVISION_BY_ZERO
120 SELECT sp_vars_div_zero();
122 SET @@sql_mode = 'ansi';
128 DROP PROCEDURE sp_vars_check_dflt;
129 DROP PROCEDURE sp_vars_check_assignment;
130 DROP FUNCTION sp_vars_check_ret1;
131 DROP FUNCTION sp_vars_check_ret2;
132 DROP FUNCTION sp_vars_check_ret3;
133 DROP FUNCTION sp_vars_check_ret4;
134 DROP FUNCTION sp_vars_div_zero;
136 ###########################################################################
138 # Tests for BIT data type.
140 ###########################################################################
143 --echo ---------------------------------------------------------------
144 --echo BIT data type tests
145 --echo ---------------------------------------------------------------
153 DROP PROCEDURE IF EXISTS p1;
161 CREATE PROCEDURE p1()
165 DECLARE v3 BIT(3) DEFAULT b'101';
166 DECLARE v4 BIT(64) DEFAULT 0x5555555555555555;
169 DECLARE v7 BIT(8) DEFAULT 128;
170 DECLARE v8 BIT(8) DEFAULT '128';
171 DECLARE v9 BIT(8) DEFAULT ' 128';
172 DECLARE v10 BIT(8) DEFAULT 'x 128';
176 SET v5 = v4; # check overflow
177 SET v6 = v3; # check padding
200 ###########################################################################
202 # Tests for CASE statements functionality:
203 # - test for general functionality (scopes, nested cases, CASE in loops);
204 # - test that if type of the CASE expression is changed on each iteration,
205 # the execution will be correct.
207 ###########################################################################
210 --echo ---------------------------------------------------------------
211 --echo CASE expression tests.
212 --echo ---------------------------------------------------------------
219 DROP PROCEDURE IF EXISTS p1;
220 DROP PROCEDURE IF EXISTS p2;
221 DROP TABLE IF EXISTS t1;
227 CREATE TABLE t1(log_msg VARCHAR(1024));
231 CREATE PROCEDURE p1(arg VARCHAR(255))
233 INSERT INTO t1 VALUES('p1: step1');
237 INSERT INTO t1 VALUES('p1: case1: on 10');
238 WHEN 10 * 10 + 10 * 10 THEN
242 INSERT INTO t1 VALUES('p1: case1: case2: on 1');
245 DECLARE i TINYINT DEFAULT 10;
248 INSERT INTO t1 VALUES(CONCAT('p1: case1: case2: loop: i: ', i));
252 INSERT INTO t1 VALUES('p1: case1: case2: loop: i is even');
254 INSERT INTO t1 VALUES('p1: case1: case2: loop: i is odd');
256 INSERT INTO t1 VALUES('p1: case1: case2: loop: ERROR');
263 INSERT INTO t1 VALUES('p1: case1: case2: ERROR');
268 INSERT INTO t1 VALUES('p1: case1: case3: on 10');
270 INSERT INTO t1 VALUES('p1: case1: case3: on 20');
272 INSERT INTO t1 VALUES('p1: case1: case3: ERROR');
276 INSERT INTO t1 VALUES('p1: case1: ERROR');
281 INSERT INTO t1 VALUES('p1: case4: on 10');
282 WHEN 10 * 10 + 10 * 10 THEN
286 INSERT INTO t1 VALUES('p1: case4: case5: on 1');
289 DECLARE i TINYINT DEFAULT 10;
292 INSERT INTO t1 VALUES(CONCAT('p1: case4: case5: loop: i: ', i));
296 INSERT INTO t1 VALUES('p1: case4: case5: loop: i is even');
298 INSERT INTO t1 VALUES('p1: case4: case5: loop: i is odd');
300 INSERT INTO t1 VALUES('p1: case4: case5: loop: ERROR');
307 INSERT INTO t1 VALUES('p1: case4: case5: ERROR');
312 INSERT INTO t1 VALUES('p1: case4: case6: on 10');
314 INSERT INTO t1 VALUES('p1: case4: case6: on 20');
316 INSERT INTO t1 VALUES('p1: case4: case6: ERROR');
320 INSERT INTO t1 VALUES('p1: case4: ERROR');
324 CREATE PROCEDURE p2()
326 DECLARE i TINYINT DEFAULT 3;
329 IF MOD(i, 2) = 0 THEN
330 SET @_test_session_var = 10;
332 SET @_test_session_var = 'test';
335 CASE @_test_session_var
337 INSERT INTO t1 VALUES('p2: case: numerical type');
339 INSERT INTO t1 VALUES('p2: case: string type');
341 INSERT INTO t1 VALUES('p2: case: ERROR');
365 ###########################################################################
367 # Test case for BUG#14161: Stored procedure cannot retrieve bigint unsigned.
369 ###########################################################################
372 --echo ---------------------------------------------------------------
374 --echo ---------------------------------------------------------------
382 DROP TABLE IF EXISTS t1;
383 DROP PROCEDURE IF EXISTS p1;
390 CREATE TABLE t1(col BIGINT UNSIGNED);
392 INSERT INTO t1 VALUE(18446744073709551614);
395 CREATE PROCEDURE p1(IN arg BIGINT UNSIGNED)
399 SELECT * FROM t1 WHERE col = arg;
403 CALL p1(18446744073709551614);
412 ###########################################################################
414 # Test case for BUG#13705: parameters to stored procedures are not verified.
416 ###########################################################################
419 --echo ---------------------------------------------------------------
421 --echo ---------------------------------------------------------------
429 DROP PROCEDURE IF EXISTS p1;
437 CREATE PROCEDURE p1(x VARCHAR(10), y CHAR(3)) READS SQL DATA
443 CALL p1('alpha', 'abc');
444 CALL p1('alpha', 'abcdef');
452 ###########################################################################
454 # Test case for BUG#13675: DATETIME/DATE type in store proc param seems to be
455 # converted as varbinary.
457 # TODO: test case failed.
459 ###########################################################################
462 --echo ---------------------------------------------------------------
464 --echo ---------------------------------------------------------------
472 DROP PROCEDURE IF EXISTS p1;
473 DROP TABLE IF EXISTS t1;
481 CREATE PROCEDURE p1(x DATETIME)
483 CREATE TABLE t1 SELECT x;
484 SHOW CREATE TABLE t1;
499 ###########################################################################
501 # Test case for BUG#12976: Boolean values reversed in stored procedures?
503 ###########################################################################
506 --echo ---------------------------------------------------------------
508 --echo ---------------------------------------------------------------
516 DROP TABLE IF EXISTS t1;
517 DROP PROCEDURE IF EXISTS p1;
518 DROP PROCEDURE IF EXISTS p2;
525 CREATE TABLE t1(b BIT(1));
527 INSERT INTO t1(b) VALUES(b'0'), (b'1');
530 CREATE PROCEDURE p1()
542 CREATE PROCEDURE p2()
545 SELECT b INTO vb FROM t1 WHERE b = 0;
555 SELECT b INTO vb FROM t1 WHERE b = 1;
579 # Additional tests for Bug#12976
582 DROP TABLE IF EXISTS table_12976_a;
583 DROP TABLE IF EXISTS table_12976_b;
584 DROP PROCEDURE IF EXISTS proc_12976_a;
585 DROP PROCEDURE IF EXISTS proc_12976_b;
588 CREATE TABLE table_12976_a (val bit(1));
590 CREATE TABLE table_12976_b(
592 emailperm bit not null default 1,
593 phoneperm bit not null default 0);
595 insert into table_12976_b values ('A', b'1', b'1'), ('B', b'0', b'0');
598 CREATE PROCEDURE proc_12976_a()
600 declare localvar bit(1);
601 SELECT val INTO localvar FROM table_12976_a;
602 SELECT coalesce(localvar, 1)+1, coalesce(val, 1)+1 FROM table_12976_a;
605 CREATE PROCEDURE proc_12976_b(
610 SELECT emailperm into ep FROM table_12976_b where (appname = name);
620 INSERT table_12976_a VALUES (0);
622 UPDATE table_12976_a set val=1;
625 call proc_12976_b('A', @ep, @msg);
628 call proc_12976_b('B', @ep, @msg);
631 DROP TABLE table_12976_a;
632 DROP TABLE table_12976_b;
633 DROP PROCEDURE proc_12976_a;
634 DROP PROCEDURE proc_12976_b;
637 ###########################################################################
639 # Test case for BUG#9572: Stored procedures: variable type declarations
642 ###########################################################################
645 --echo ---------------------------------------------------------------
647 --echo ---------------------------------------------------------------
655 DROP PROCEDURE IF EXISTS p1;
656 DROP PROCEDURE IF EXISTS p2;
657 DROP PROCEDURE IF EXISTS p3;
659 DROP PROCEDURE IF EXISTS p4;
660 DROP PROCEDURE IF EXISTS p5;
661 DROP PROCEDURE IF EXISTS p6;
668 SET @@sql_mode = 'traditional';
672 CREATE PROCEDURE p1()
674 DECLARE v TINYINT DEFAULT 1e200;
678 CREATE PROCEDURE p2()
680 DECLARE v DECIMAL(5) DEFAULT 1e200;
684 CREATE PROCEDURE p3()
686 DECLARE v CHAR(5) DEFAULT 'abcdef';
687 SELECT v LIKE 'abc___';
690 CREATE PROCEDURE p4(arg VARCHAR(2))
692 DECLARE var VARCHAR(1);
697 CREATE PROCEDURE p5(arg CHAR(2))
704 CREATE PROCEDURE p6(arg DECIMAL(2))
706 DECLARE var DECIMAL(1);
713 --error ER_WARN_DATA_OUT_OF_RANGE
715 --error ER_WARN_DATA_OUT_OF_RANGE
717 --error ER_DATA_TOO_LONG
720 --error ER_DATA_TOO_LONG
722 --error ER_DATA_TOO_LONG
724 --error ER_WARN_DATA_OUT_OF_RANGE
731 SET @@sql_mode = 'ansi';
741 ###########################################################################
743 # Test case for BUG#9078: STORED PROCDURE: Decimal digits are not displayed
744 # when we use DECIMAL datatype.
746 ###########################################################################
749 --echo ---------------------------------------------------------------
751 --echo ---------------------------------------------------------------
759 DROP PROCEDURE IF EXISTS p1;
767 CREATE PROCEDURE p1 (arg DECIMAL(64,2))
769 DECLARE var DECIMAL(64,2);
786 ###########################################################################
788 # Test case for BUG#8768: Functions: For any unsigned data type, -ve values can
789 # be passed and returned.
791 # TODO: there is a bug here -- the function created in ANSI mode should not
792 # throw errors instead of warnings if called in TRADITIONAL mode.
794 ###########################################################################
797 --echo ---------------------------------------------------------------
799 --echo ---------------------------------------------------------------
807 DROP FUNCTION IF EXISTS f1;
814 # Create a function in ANSI mode.
817 CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT
825 # Call in TRADITIONAL mode the function created in ANSI mode.
827 SET @@sql_mode = 'traditional';
829 # TODO: a warning should be emitted here.
830 --error ER_WARN_DATA_OUT_OF_RANGE
833 # Recreate the function in TRADITIONAL mode.
838 CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT
844 --error ER_WARN_DATA_OUT_OF_RANGE
851 SET @@sql_mode = 'ansi';
855 ###########################################################################
857 # Test case for BUG#8769: Functions: For Int datatypes, out of range values can
858 # be passed and returned.
860 # TODO: there is a bug here -- the function created in ANSI mode should not
861 # throw errors instead of warnings if called in TRADITIONAL mode.
863 ###########################################################################
866 --echo ---------------------------------------------------------------
868 --echo ---------------------------------------------------------------
876 DROP FUNCTION IF EXISTS f1;
883 # Create a function in ANSI mode.
886 CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT
894 # Call in TRADITIONAL mode the function created in ANSI mode.
896 SET @@sql_mode = 'traditional';
898 # TODO: a warning should be emitted here.
899 --error ER_WARN_DATA_OUT_OF_RANGE
902 # Recreate the function in TRADITIONAL mode.
907 CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT
913 --error ER_WARN_DATA_OUT_OF_RANGE
920 SET @@sql_mode = 'ansi';
924 ###########################################################################
926 # Test case for BUG#8702: Stored Procedures: No Error/Warning shown for
927 # inappropriate data type matching.
929 ###########################################################################
932 --echo ---------------------------------------------------------------
934 --echo ---------------------------------------------------------------
942 DROP PROCEDURE IF EXISTS p1;
943 DROP TABLE IF EXISTS t1;
950 CREATE TABLE t1(col VARCHAR(255));
952 INSERT INTO t1(col) VALUES('Hello, world!');
955 CREATE PROCEDURE p1()
957 DECLARE sp_var INTEGER;
959 SELECT col INTO sp_var FROM t1 LIMIT 1;
960 SET @user_var = sp_var;
976 ###########################################################################
978 # Test case for BUG#12903: upper function does not work inside a function.
980 ###########################################################################
983 --echo ---------------------------------------------------------------
985 --echo ---------------------------------------------------------------
993 DROP FUNCTION IF EXISTS f1;
994 DROP TABLE IF EXISTS t1;
1001 CREATE TABLE t1(txt VARCHAR(255));
1004 CREATE FUNCTION f1(arg VARCHAR(255)) RETURNS VARCHAR(255)
1006 DECLARE v1 VARCHAR(255);
1007 DECLARE v2 VARCHAR(255);
1009 SET v1 = CONCAT(LOWER(arg), UPPER(arg));
1010 SET v2 = CONCAT(LOWER(v1), UPPER(v1));
1012 INSERT INTO t1 VALUES(v1), (v2);
1014 RETURN CONCAT(LOWER(arg), UPPER(arg));
1018 SELECT f1('_aBcDe_');
1029 ###########################################################################
1031 # Test case for BUG#13808: ENUM type stored procedure parameter accepts
1032 # non-enumerated data.
1034 ###########################################################################
1037 --echo ---------------------------------------------------------------
1039 --echo ---------------------------------------------------------------
1047 DROP PROCEDURE IF EXISTS p1;
1048 DROP PROCEDURE IF EXISTS p2;
1049 DROP FUNCTION IF EXISTS f1;
1058 CREATE PROCEDURE p1(arg ENUM('a', 'b'))
1063 CREATE PROCEDURE p2(arg ENUM('a', 'b'))
1065 DECLARE var ENUM('c', 'd') DEFAULT arg;
1070 CREATE FUNCTION f1(arg ENUM('a', 'b')) RETURNS ENUM('c', 'd')
1091 ###########################################################################
1093 # Test case for BUG#13909: Varchar Stored Procedure Parameter always BINARY
1094 # string (ignores CHARACTER SET).
1096 ###########################################################################
1099 --echo ---------------------------------------------------------------
1101 --echo ---------------------------------------------------------------
1109 DROP PROCEDURE IF EXISTS p1;
1110 DROP PROCEDURE IF EXISTS p2;
1119 CREATE PROCEDURE p1(arg VARCHAR(255))
1121 SELECT CHARSET(arg);
1124 CREATE PROCEDURE p2(arg VARCHAR(255) CHARACTER SET UTF8)
1126 SELECT CHARSET(arg);
1136 CALL p2(_LATIN1 't');
1145 ###########################################################################
1147 # Test case for BUG#14188: BINARY variables have no 0x00 padding.
1149 ###########################################################################
1152 --echo ---------------------------------------------------------------
1154 --echo ---------------------------------------------------------------
1162 DROP PROCEDURE IF EXISTS p1;
1170 CREATE PROCEDURE p1(arg1 BINARY(2), arg2 VARBINARY(2))
1172 DECLARE var1 BINARY(2) DEFAULT 0x41;
1173 DECLARE var2 VARBINARY(2) DEFAULT 0x42;
1175 SELECT HEX(arg1), HEX(arg2);
1176 SELECT HEX(var1), HEX(var2);
1180 CALL p1(0x41, 0x42);
1188 ###########################################################################
1190 # Test case for BUG#15148: Stored procedure variables accept non-scalar values.
1192 ###########################################################################
1195 --echo ---------------------------------------------------------------
1197 --echo ---------------------------------------------------------------
1205 DROP PROCEDURE IF EXISTS p1;
1206 DROP TABLE IF EXISTS t1;
1213 CREATE TABLE t1(col1 TINYINT, col2 TINYINT);
1215 INSERT INTO t1 VALUES(1, 2), (11, 12);
1218 CREATE PROCEDURE p1(arg TINYINT)
1224 --error ER_OPERAND_COLUMNS
1227 --error ER_OPERAND_COLUMNS
1228 CALL p1((SELECT * FROM t1 LIMIT 1));
1230 --error ER_OPERAND_COLUMNS
1231 CALL p1((SELECT col1, col2 FROM t1 LIMIT 1));
1240 ###########################################################################
1242 # Test case for BUG#13613: substring function in stored procedure.
1244 ###########################################################################
1247 --echo ---------------------------------------------------------------
1249 --echo ---------------------------------------------------------------
1257 DROP PROCEDURE IF EXISTS p1;
1258 DROP FUNCTION IF EXISTS f1;
1267 CREATE PROCEDURE p1(x VARCHAR(50))
1269 SET x = SUBSTRING(x, 1, 3);
1273 CREATE FUNCTION f1(x VARCHAR(50)) RETURNS VARCHAR(50)
1275 RETURN SUBSTRING(x, 1, 3);
1282 SELECT f1('ABCDEF');
1291 ###########################################################################
1293 # Test case for BUG#13665: concat with '' produce incorrect results in SP.
1295 ###########################################################################
1298 --echo ---------------------------------------------------------------
1300 --echo ---------------------------------------------------------------
1308 DROP FUNCTION IF EXISTS f1;
1316 CREATE FUNCTION f1() RETURNS VARCHAR(20000)
1318 DECLARE var VARCHAR(2000);
1321 SET var = CONCAT(var, 'abc');
1322 SET var = CONCAT(var, '');
1338 # Bug#17226: Variable set in cursor on first iteration is assigned
1339 # second iterations value
1341 # The problem was in incorrect handling of local variables of type
1345 DROP PROCEDURE IF EXISTS p1;
1349 CREATE PROCEDURE p1()
1351 DECLARE v_char VARCHAR(255);
1352 DECLARE v_text TEXT DEFAULT '';
1356 SET v_text = v_char;
1360 SET v_text = concat(v_text, '|', v_char);
1371 # Bug #27415 Text Variables in stored procedures
1372 # If the SP varible was also referenced on the right side
1373 # the result was corrupted.
1378 DROP PROCEDURE IF EXISTS bug27415_text_test|
1379 DROP PROCEDURE IF EXISTS bug27415_text_test2|
1382 CREATE PROCEDURE bug27415_text_test(entity_id_str_in text)
1384 DECLARE str_remainder text;
1386 SET str_remainder = entity_id_str_in;
1388 select 'before substr', str_remainder;
1389 SET str_remainder = SUBSTRING(str_remainder, 3);
1390 select 'after substr', str_remainder;
1393 CREATE PROCEDURE bug27415_text_test2(entity_id_str_in text)
1395 DECLARE str_remainder text;
1396 DECLARE str_remainder2 text;
1398 SET str_remainder2 = entity_id_str_in;
1399 select 'before substr', str_remainder2;
1400 SET str_remainder = SUBSTRING(str_remainder2, 3);
1401 select 'after substr', str_remainder;
1404 CALL bug27415_text_test('a,b,c')|
1405 CALL bug27415_text_test('a,b,c')|
1406 CALL bug27415_text_test2('a,b,c')|
1407 CALL bug27415_text_test('a,b,c')|
1409 DROP PROCEDURE bug27415_text_test|
1410 DROP PROCEDURE bug27415_text_test2|
1417 # Bug #26277 User variable returns one type in SELECT @v and other for CREATE as SELECT @v
1420 drop function if exists f1;
1421 drop table if exists t1;
1425 create function f1() returns int
1427 if @a=1 then set @b='abc';
1434 create table t1 (a int)|
1435 insert into t1 (a) values (1), (2)|
1439 select f1(), @b from t1|
1443 select f1(), @b from t1|