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;
21
###########################################################################
23
# Some general tests for SP-vars functionality.
25
###########################################################################
27
# Create the procedure in ANSI mode. Check that all necessary warnings are
30
SET @@sql_mode = 'ansi';
32
--source include/sp-vars.inc
35
--echo ---------------------------------------------------------------
36
--echo Calling the routines, created in ANSI mode.
37
--echo ---------------------------------------------------------------
40
CALL sp_vars_check_dflt();
42
CALL sp_vars_check_assignment();
44
SELECT sp_vars_check_ret1();
46
SELECT sp_vars_check_ret2();
48
SELECT sp_vars_check_ret3();
50
SELECT sp_vars_check_ret4();
52
# Check that changing sql_mode after creating a store procedure does not
55
SET @@sql_mode = 'traditional';
58
--echo ---------------------------------------------------------------
59
--echo Calling in TRADITIONAL mode the routines, created in ANSI mode.
60
--echo ---------------------------------------------------------------
63
CALL sp_vars_check_dflt();
65
CALL sp_vars_check_assignment();
67
SELECT sp_vars_check_ret1();
69
SELECT sp_vars_check_ret2();
71
SELECT sp_vars_check_ret3();
73
SELECT sp_vars_check_ret4();
75
# Create the procedure in TRADITIONAL mode. Check that error will be thrown on
78
DROP PROCEDURE sp_vars_check_dflt;
79
DROP PROCEDURE sp_vars_check_assignment;
80
DROP FUNCTION sp_vars_check_ret1;
81
DROP FUNCTION sp_vars_check_ret2;
82
DROP FUNCTION sp_vars_check_ret3;
83
DROP FUNCTION sp_vars_check_ret4;
85
--source include/sp-vars.inc
88
--echo ---------------------------------------------------------------
89
--echo Calling the routines, created in TRADITIONAL mode.
90
--echo ---------------------------------------------------------------
93
--error ER_WARN_DATA_OUT_OF_RANGE
94
CALL sp_vars_check_dflt();
96
--error ER_WARN_DATA_OUT_OF_RANGE
97
CALL sp_vars_check_assignment();
99
--error ER_WARN_DATA_OUT_OF_RANGE
100
SELECT sp_vars_check_ret1();
102
--error ER_WARN_DATA_OUT_OF_RANGE
103
SELECT sp_vars_check_ret2();
105
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
106
SELECT sp_vars_check_ret3();
108
# TODO: Is it an error, that only a warning is emitted here? Check the same
109
# behaviour with tables.
111
SELECT sp_vars_check_ret4();
113
SET @@sql_mode = 'ansi';
119
DROP PROCEDURE sp_vars_check_dflt;
120
DROP PROCEDURE sp_vars_check_assignment;
121
DROP FUNCTION sp_vars_check_ret1;
122
DROP FUNCTION sp_vars_check_ret2;
123
DROP FUNCTION sp_vars_check_ret3;
124
DROP FUNCTION sp_vars_check_ret4;
126
###########################################################################
128
# Tests for BIT data type.
130
###########################################################################
133
--echo ---------------------------------------------------------------
134
--echo BIT data type tests
135
--echo ---------------------------------------------------------------
143
DROP PROCEDURE IF EXISTS p1;
151
CREATE PROCEDURE p1()
155
DECLARE v3 BIT(3) DEFAULT b'101';
156
DECLARE v4 BIT(64) DEFAULT 0x5555555555555555;
159
DECLARE v7 BIT(8) DEFAULT 128;
160
DECLARE v8 BIT(8) DEFAULT '128';
161
DECLARE v9 BIT(8) DEFAULT ' 128';
162
DECLARE v10 BIT(8) DEFAULT 'x 128';
166
SET v5 = v4; # check overflow
167
SET v6 = v3; # check padding
190
###########################################################################
192
# Tests for CASE statements functionality:
193
# - test for general functionality (scopes, nested cases, CASE in loops);
194
# - test that if type of the CASE expression is changed on each iteration,
195
# the execution will be correct.
197
###########################################################################
200
--echo ---------------------------------------------------------------
201
--echo CASE expression tests.
202
--echo ---------------------------------------------------------------
209
DROP PROCEDURE IF EXISTS p1;
210
DROP PROCEDURE IF EXISTS p2;
211
DROP TABLE IF EXISTS t1;
217
CREATE TABLE t1(log_msg VARCHAR(1024));
221
CREATE PROCEDURE p1(arg VARCHAR(255))
223
INSERT INTO t1 VALUES('p1: step1');
227
INSERT INTO t1 VALUES('p1: case1: on 10');
228
WHEN 10 * 10 + 10 * 10 THEN
232
INSERT INTO t1 VALUES('p1: case1: case2: on 1');
235
DECLARE i TINYINT DEFAULT 10;
238
INSERT INTO t1 VALUES(CONCAT('p1: case1: case2: loop: i: ', i));
242
INSERT INTO t1 VALUES('p1: case1: case2: loop: i is even');
244
INSERT INTO t1 VALUES('p1: case1: case2: loop: i is odd');
246
INSERT INTO t1 VALUES('p1: case1: case2: loop: ERROR');
253
INSERT INTO t1 VALUES('p1: case1: case2: ERROR');
258
INSERT INTO t1 VALUES('p1: case1: case3: on 10');
260
INSERT INTO t1 VALUES('p1: case1: case3: on 20');
262
INSERT INTO t1 VALUES('p1: case1: case3: ERROR');
266
INSERT INTO t1 VALUES('p1: case1: ERROR');
271
INSERT INTO t1 VALUES('p1: case4: on 10');
272
WHEN 10 * 10 + 10 * 10 THEN
276
INSERT INTO t1 VALUES('p1: case4: case5: on 1');
279
DECLARE i TINYINT DEFAULT 10;
282
INSERT INTO t1 VALUES(CONCAT('p1: case4: case5: loop: i: ', i));
286
INSERT INTO t1 VALUES('p1: case4: case5: loop: i is even');
288
INSERT INTO t1 VALUES('p1: case4: case5: loop: i is odd');
290
INSERT INTO t1 VALUES('p1: case4: case5: loop: ERROR');
297
INSERT INTO t1 VALUES('p1: case4: case5: ERROR');
302
INSERT INTO t1 VALUES('p1: case4: case6: on 10');
304
INSERT INTO t1 VALUES('p1: case4: case6: on 20');
306
INSERT INTO t1 VALUES('p1: case4: case6: ERROR');
310
INSERT INTO t1 VALUES('p1: case4: ERROR');
314
CREATE PROCEDURE p2()
316
DECLARE i TINYINT DEFAULT 3;
319
IF MOD(i, 2) = 0 THEN
320
SET @_test_session_var = 10;
322
SET @_test_session_var = 'test';
325
CASE @_test_session_var
327
INSERT INTO t1 VALUES('p2: case: numerical type');
329
INSERT INTO t1 VALUES('p2: case: string type');
331
INSERT INTO t1 VALUES('p2: case: ERROR');
355
###########################################################################
357
# Test case for BUG#14161: Stored procedure cannot retrieve bigint unsigned.
359
###########################################################################
362
--echo ---------------------------------------------------------------
364
--echo ---------------------------------------------------------------
372
DROP TABLE IF EXISTS t1;
373
DROP PROCEDURE IF EXISTS p1;
380
CREATE TABLE t1(col BIGINT UNSIGNED);
382
INSERT INTO t1 VALUE(18446744073709551614);
385
CREATE PROCEDURE p1(IN arg BIGINT UNSIGNED)
389
SELECT * FROM t1 WHERE col = arg;
393
CALL p1(18446744073709551614);
402
###########################################################################
404
# Test case for BUG#13705: parameters to stored procedures are not verified.
406
###########################################################################
409
--echo ---------------------------------------------------------------
411
--echo ---------------------------------------------------------------
419
DROP PROCEDURE IF EXISTS p1;
427
CREATE PROCEDURE p1(x VARCHAR(10), y CHAR(3)) READS SQL DATA
433
CALL p1('alpha', 'abc');
434
CALL p1('alpha', 'abcdef');
442
###########################################################################
444
# Test case for BUG#13675: DATETIME/DATE type in store proc param seems to be
445
# converted as varbinary.
447
# TODO: test case failed.
449
###########################################################################
452
--echo ---------------------------------------------------------------
454
--echo ---------------------------------------------------------------
462
DROP PROCEDURE IF EXISTS p1;
463
DROP TABLE IF EXISTS t1;
471
CREATE PROCEDURE p1(x DATETIME)
473
CREATE TABLE t1 SELECT x;
474
SHOW CREATE TABLE t1;
489
###########################################################################
491
# Test case for BUG#12976: Boolean values reversed in stored procedures?
493
# TODO: test case failed.
495
###########################################################################
498
--echo ---------------------------------------------------------------
500
--echo ---------------------------------------------------------------
508
DROP TABLE IF EXISTS t1;
509
DROP PROCEDURE IF EXISTS p1;
510
DROP PROCEDURE IF EXISTS p2;
517
CREATE TABLE t1(b BIT(1));
519
INSERT INTO t1(b) VALUES(b'0'), (b'1');
522
CREATE PROCEDURE p1()
534
CREATE PROCEDURE p2()
537
SELECT b INTO vb FROM t1 WHERE b = 0;
547
SELECT b INTO vb FROM t1 WHERE b = 1;
559
# The expected and correct result.
563
# The wrong result. Note that only hex(vb) works, but is printed with two
564
# digits for some reason in this case.
576
###########################################################################
578
# Test case for BUG#9572: Stored procedures: variable type declarations
581
###########################################################################
584
--echo ---------------------------------------------------------------
586
--echo ---------------------------------------------------------------
594
DROP PROCEDURE IF EXISTS p1;
595
DROP PROCEDURE IF EXISTS p2;
596
DROP PROCEDURE IF EXISTS p3;
598
DROP PROCEDURE IF EXISTS p4;
599
DROP PROCEDURE IF EXISTS p5;
600
DROP PROCEDURE IF EXISTS p6;
607
SET @@sql_mode = 'traditional';
611
CREATE PROCEDURE p1()
613
DECLARE v TINYINT DEFAULT 1e200;
617
CREATE PROCEDURE p2()
619
DECLARE v DECIMAL(5) DEFAULT 1e200;
623
CREATE PROCEDURE p3()
625
DECLARE v CHAR(5) DEFAULT 'abcdef';
626
SELECT v LIKE 'abc___';
629
CREATE PROCEDURE p4(arg VARCHAR(2))
631
DECLARE var VARCHAR(1);
636
CREATE PROCEDURE p5(arg CHAR(2))
643
CREATE PROCEDURE p6(arg DECIMAL(2))
645
DECLARE var DECIMAL(1);
652
--error ER_WARN_DATA_OUT_OF_RANGE
654
--error ER_WARN_DATA_OUT_OF_RANGE
656
--error ER_DATA_TOO_LONG
659
--error ER_DATA_TOO_LONG
661
--error ER_DATA_TOO_LONG
663
--error ER_WARN_DATA_OUT_OF_RANGE
670
SET @@sql_mode = 'ansi';
680
###########################################################################
682
# Test case for BUG#9078: STORED PROCDURE: Decimal digits are not displayed
683
# when we use DECIMAL datatype.
685
###########################################################################
688
--echo ---------------------------------------------------------------
690
--echo ---------------------------------------------------------------
698
DROP PROCEDURE IF EXISTS p1;
706
CREATE PROCEDURE p1 (arg DECIMAL(64,2))
708
DECLARE var DECIMAL(64,2);
725
###########################################################################
727
# Test case for BUG#8768: Functions: For any unsigned data type, -ve values can
728
# be passed and returned.
730
# TODO: there is a bug here -- the function created in ANSI mode should not
731
# throw errors instead of warnings if called in TRADITIONAL mode.
733
###########################################################################
736
--echo ---------------------------------------------------------------
738
--echo ---------------------------------------------------------------
746
DROP FUNCTION IF EXISTS f1;
753
# Create a function in ANSI mode.
756
CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT
764
# Call in TRADITIONAL mode the function created in ANSI mode.
766
SET @@sql_mode = 'traditional';
768
# TODO: a warning should be emitted here.
769
--error ER_WARN_DATA_OUT_OF_RANGE
772
# Recreate the function in TRADITIONAL mode.
777
CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT
783
--error ER_WARN_DATA_OUT_OF_RANGE
790
SET @@sql_mode = 'ansi';
794
###########################################################################
796
# Test case for BUG#8769: Functions: For Int datatypes, out of range values can
797
# be passed and returned.
799
# TODO: there is a bug here -- the function created in ANSI mode should not
800
# throw errors instead of warnings if called in TRADITIONAL mode.
802
###########################################################################
805
--echo ---------------------------------------------------------------
807
--echo ---------------------------------------------------------------
815
DROP FUNCTION IF EXISTS f1;
822
# Create a function in ANSI mode.
825
CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT
833
# Call in TRADITIONAL mode the function created in ANSI mode.
835
SET @@sql_mode = 'traditional';
837
# TODO: a warning should be emitted here.
838
--error ER_WARN_DATA_OUT_OF_RANGE
841
# Recreate the function in TRADITIONAL mode.
846
CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT
852
--error ER_WARN_DATA_OUT_OF_RANGE
859
SET @@sql_mode = 'ansi';
863
###########################################################################
865
# Test case for BUG#8702: Stored Procedures: No Error/Warning shown for
866
# inappropriate data type matching.
868
###########################################################################
871
--echo ---------------------------------------------------------------
873
--echo ---------------------------------------------------------------
881
DROP PROCEDURE IF EXISTS p1;
882
DROP TABLE IF EXISTS t1;
889
CREATE TABLE t1(col VARCHAR(255));
891
INSERT INTO t1(col) VALUES('Hello, world!');
894
CREATE PROCEDURE p1()
896
DECLARE sp_var INTEGER;
898
SELECT col INTO sp_var FROM t1 LIMIT 1;
899
SET @user_var = sp_var;
915
###########################################################################
917
# Test case for BUG#12903: upper function does not work inside a function.
919
###########################################################################
922
--echo ---------------------------------------------------------------
924
--echo ---------------------------------------------------------------
932
DROP FUNCTION IF EXISTS f1;
933
DROP TABLE IF EXISTS t1;
940
CREATE TABLE t1(txt VARCHAR(255));
943
CREATE FUNCTION f1(arg VARCHAR(255)) RETURNS VARCHAR(255)
945
DECLARE v1 VARCHAR(255);
946
DECLARE v2 VARCHAR(255);
948
SET v1 = CONCAT(LOWER(arg), UPPER(arg));
949
SET v2 = CONCAT(LOWER(v1), UPPER(v1));
951
INSERT INTO t1 VALUES(v1), (v2);
953
RETURN CONCAT(LOWER(arg), UPPER(arg));
957
SELECT f1('_aBcDe_');
968
###########################################################################
970
# Test case for BUG#13808: ENUM type stored procedure parameter accepts
971
# non-enumerated data.
973
###########################################################################
976
--echo ---------------------------------------------------------------
978
--echo ---------------------------------------------------------------
986
DROP PROCEDURE IF EXISTS p1;
987
DROP PROCEDURE IF EXISTS p2;
988
DROP FUNCTION IF EXISTS f1;
997
CREATE PROCEDURE p1(arg ENUM('a', 'b'))
1002
CREATE PROCEDURE p2(arg ENUM('a', 'b'))
1004
DECLARE var ENUM('c', 'd') DEFAULT arg;
1009
CREATE FUNCTION f1(arg ENUM('a', 'b')) RETURNS ENUM('c', 'd')
1030
###########################################################################
1032
# Test case for BUG#13909: Varchar Stored Procedure Parameter always BINARY
1033
# string (ignores CHARACTER SET).
1035
###########################################################################
1038
--echo ---------------------------------------------------------------
1040
--echo ---------------------------------------------------------------
1048
DROP PROCEDURE IF EXISTS p1;
1049
DROP PROCEDURE IF EXISTS p2;
1058
CREATE PROCEDURE p1(arg VARCHAR(255))
1060
SELECT CHARSET(arg);
1063
CREATE PROCEDURE p2(arg VARCHAR(255) CHARACTER SET UTF8)
1065
SELECT CHARSET(arg);
1075
CALL p2(_LATIN1 't');
1084
###########################################################################
1086
# Test case for BUG#14188: BINARY variables have no 0x00 padding.
1088
###########################################################################
1091
--echo ---------------------------------------------------------------
1093
--echo ---------------------------------------------------------------
1101
DROP PROCEDURE IF EXISTS p1;
1109
CREATE PROCEDURE p1(arg1 BINARY(2), arg2 VARBINARY(2))
1111
DECLARE var1 BINARY(2) DEFAULT 0x41;
1112
DECLARE var2 VARBINARY(2) DEFAULT 0x42;
1114
SELECT HEX(arg1), HEX(arg2);
1115
SELECT HEX(var1), HEX(var2);
1119
CALL p1(0x41, 0x42);
1127
###########################################################################
1129
# Test case for BUG#15148: Stored procedure variables accept non-scalar values.
1131
###########################################################################
1134
--echo ---------------------------------------------------------------
1136
--echo ---------------------------------------------------------------
1144
DROP PROCEDURE IF EXISTS p1;
1145
DROP TABLE IF EXISTS t1;
1152
CREATE TABLE t1(col1 TINYINT, col2 TINYINT);
1154
INSERT INTO t1 VALUES(1, 2), (11, 12);
1157
CREATE PROCEDURE p1(arg TINYINT)
1163
--error ER_OPERAND_COLUMNS
1166
--error ER_OPERAND_COLUMNS
1167
CALL p1((SELECT * FROM t1 LIMIT 1));
1169
--error ER_OPERAND_COLUMNS
1170
CALL p1((SELECT col1, col2 FROM t1 LIMIT 1));
1179
###########################################################################
1181
# Test case for BUG#13613: substring function in stored procedure.
1183
###########################################################################
1186
--echo ---------------------------------------------------------------
1188
--echo ---------------------------------------------------------------
1196
DROP PROCEDURE IF EXISTS p1;
1197
DROP FUNCTION IF EXISTS f1;
1206
CREATE PROCEDURE p1(x VARCHAR(50))
1208
SET x = SUBSTRING(x, 1, 3);
1212
CREATE FUNCTION f1(x VARCHAR(50)) RETURNS VARCHAR(50)
1214
RETURN SUBSTRING(x, 1, 3);
1221
SELECT f1('ABCDEF');
1230
###########################################################################
1232
# Test case for BUG#13665: concat with '' produce incorrect results in SP.
1234
###########################################################################
1237
--echo ---------------------------------------------------------------
1239
--echo ---------------------------------------------------------------
1247
DROP FUNCTION IF EXISTS f1;
1255
CREATE FUNCTION f1() RETURNS VARCHAR(20000)
1257
DECLARE var VARCHAR(2000);
1260
SET var = CONCAT(var, 'abc');
1261
SET var = CONCAT(var, '');
1277
# Bug#17226: Variable set in cursor on first iteration is assigned
1278
# second iterations value
1280
# The problem was in incorrect handling of local variables of type
1284
DROP PROCEDURE IF EXISTS p1;
1288
CREATE PROCEDURE p1()
1290
DECLARE v_char VARCHAR(255);
1291
DECLARE v_text TEXT DEFAULT '';
1295
SET v_text = v_char;
1299
SET v_text = concat(v_text, '|', v_char);