sp-vars.test

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

    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 ;|

SELECT f1('_aBcDe_');

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_OPERAND_COLUMNS
CALL p1((1, 2));

--error ER_OPERAND_COLUMNS
CALL p1((SELECT * FROM t1 LIMIT 1));

--error ER_OPERAND_COLUMNS
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;