memory_storedproc_02.result

来自「这个文件是windows mysql源码」· RESULT 代码 · 共 1,399 行 · 第 1/3 页

--source suite/funcs_1/storedproc/load_sp_tb.inc
--------------------------------------------------------------------------------

--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
--------------------------------------------------------------------------------
DROP DATABASE IF EXISTS db_storedproc;
DROP DATABASE IF EXISTS db_storedproc_1;
CREATE DATABASE db_storedproc;
CREATE DATABASE db_storedproc_1;
USE db_storedproc;
create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data_ln/funcs_1/t4.txt' into table t1;
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data_ln/funcs_1/t4.txt' into table t2;
create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data_ln/funcs_1/t3.txt' into table t3;
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data_ln/funcs_1/t4.txt' into table t4;
USE db_storedproc_1;
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data_ln/funcs_1/t4.txt' into table t6;
USE db_storedproc;
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data_ln/funcs_1/t7.txt' into table t7;
Warnings:
Warning	1265	Data truncated for column 'f3' at row 1
Warning	1265	Data truncated for column 'f3' at row 2
Warning	1265	Data truncated for column 'f3' at row 3
Warning	1265	Data truncated for column 'f3' at row 4
Warning	1265	Data truncated for column 'f3' at row 5
Warning	1265	Data truncated for column 'f3' at row 6
Warning	1265	Data truncated for column 'f3' at row 7
Warning	1265	Data truncated for column 'f3' at row 8
Warning	1265	Data truncated for column 'f3' at row 9
Warning	1265	Data truncated for column 'f3' at row 10
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data_ln/funcs_1/t7.txt' into table t8;
Warnings:
Warning	1265	Data truncated for column 'f3' at row 1
Warning	1265	Data truncated for column 'f3' at row 2
Warning	1265	Data truncated for column 'f3' at row 3
Warning	1265	Data truncated for column 'f3' at row 4
Warning	1265	Data truncated for column 'f3' at row 5
Warning	1265	Data truncated for column 'f3' at row 6
Warning	1265	Data truncated for column 'f3' at row 7
Warning	1265	Data truncated for column 'f3' at row 8
Warning	1265	Data truncated for column 'f3' at row 9
Warning	1265	Data truncated for column 'f3' at row 10
create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data_ln/funcs_1/t9.txt' into table t9;
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data_ln/funcs_1/t4.txt' into table t10;
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data_ln/funcs_1/t4.txt' into table t11;

Section 3.1.2 - Syntax checks for the stored procedure-specific
programming statements BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE:
--------------------------------------------------------------------------------

Testcase 3.1.2.8:
-----------------

Ensure that the scope of each BEGIN/END compound statement within a stored
procedure definition is properly applied
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( )
begin_label: BEGIN
declare x char DEFAULT 'x';
declare y char DEFAULT 'y';
set x = '1';
set y = '2';
label1: BEGIN
declare x char DEFAULT 'X';
declare y char DEFAULT 'Y';
SELECT f1, f2 into x, y from t2 limit 1;
SELECT '1.1', x, y;
label2: BEGIN
declare x char default 'a';
declare y char default 'b';
label3: BEGIN
declare x char default 'c';
declare y char default 'd';
label4: BEGIN
declare x char default 'e';
declare y char default 'f';
label5: BEGIN
declare x char default 'g';
declare y char default 'h';
SELECT 5, x, y;
END label5;
SELECT 4, x, y;
END label4;
SELECT 3, x, y;
END label3;
SELECT 2, x, y;
END label2;
END label1;
set @v1 = x;
set @v2 = y;
SELECT '1.2', @v1, @v2;
END begin_label//
CALL sp1();
1.1	x	y
1.1	a	a
5	x	y
5	g	h
4	x	y
4	e	f
3	x	y
3	c	d
2	x	y
2	a	b
1.2	@v1	@v2
1.2	1	2
Warnings:
Warning	1265	Data truncated for column 'x' at row 1
Warning	1265	Data truncated for column 'y' at row 1
DROP PROCEDURE IF EXISTS sp1;

Testcase 3.1.2.26:
------------------

Ensure that the initial value of every variable declared for a stored procedure
is either NULL or its DEFAULT value, as appropriate.
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp1;
set @v1=0;
set @v2=0;
CREATE PROCEDURE sp1( )
BEGIN
declare x1 char default 'x';
declare y1 char;
declare x2 tinytext default 'tinytext';
declare y2 tinytext;
declare x3 datetime default '2005-10-03 12:13:14';
declare y3 datetime;
declare x4 float default 1.2;
declare y4 float;
declare x5 blob default 'b';
declare y5 blob;
declare x6 smallint default 127;
declare y6 smallint;
SELECT x1, x2, x3, x4, x5, x6, y1, y2, y3, y4, y5, y6;
END//
CALL sp1();
x1	x2	x3	x4	x5	x6	y1	y2	y3	y4	y5	y6
x	tinytext	2005-10-03 12:13:14	1.2	b	127	NULL	NULL	NULL	NULL	NULL	NULL
DROP PROCEDURE sp1;

Testcase 3.1.2.30:
------------------

Ensure that, when a stored procedure is called/executed, every variable always
uses the correct value: either the value with which it is initialized or the
value to which it is subsequently SET or otherwise assigned, as appropriate.
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( IN invar INT, OUT outvar INT )
BEGIN
declare x integer;
declare y integer default 1;
set @x = x;
set @y = y;
set @z = 234;
SELECT f1, f2 into @x, @y from t2 where f1='a`' and f2='a`' limit 1;
SELECT @x, @y, @z, invar;
BEGIN
set @x = 2;
SELECT @x, @y, @z;
SET outvar = @x * invar + @z * @f;
SET invar = outvar;
BEGIN
set @y = null, @z = 'abcd';
SELECT @x, @y, @z;
END;
END;
END//
SET @invar  = 100;
SET @outvar = @invar;
SET @f      = 10;
SELECT @x, @y, @z, @invar, @outvar;
@x	@y	@z	@invar	@outvar
NULL	NULL	NULL	100	100
CALL sp1( @invar, @outvar );
@x	@y	@z	invar
a`	a`	234	100
@x	@y	@z
2	a`	234
@x	@y	@z
2	NULL	abcd
SELECT @x, @y, @z, @invar, @outvar;
@x	@y	@z	@invar	@outvar
2	NULL	abcd	100	2540
DROP PROCEDURE sp1;

Testcase 3.1.2.31:
------------------

Ensure that the SELECT ... INTO statement properly assigns values to the
variables in its variable list.
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( )
BEGIN
declare x integer; declare y integer;
set @x=x;
set @y=y;
SELECT f4, f3 into @x, @y from t2 where f4=-5000 and f3='1000-01-01' limit 1;
SELECT @x, @y;
END//
CALL sp1();
@x	@y
-5000	1000-01-01
DROP PROCEDURE sp1;

Testcase 3.1.2.32:
------------------

Ensure that a SELECT ... INTO statement that retrieves multiple rows is
rejected, with an appropriate error message.
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( )
BEGIN
declare x integer; declare y integer;
set @x=x;
set @y=y;
SELECT f4, f3 into @x, @y from t2;
END//
CALL sp1();
ERROR 42000: Result consisted of more than one row
DROP PROCEDURE sp1;

Testcase 3.1.2.33:
------------------

Ensure that a SELECT ... INTO statement that retrieves too many columns for the
number of variables in its variable list is rejected, with an appropriate error
message.
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( )
BEGIN
declare x integer; declare y integer;
set @x=x;
set @y=y;
SELECT f4, f3, f2, f1 into @x, @y from t2;
END//
CALL sp1();
ERROR 21000: The used SELECT statements have a different number of columns
DROP PROCEDURE sp1;

Testcase 3.1.2.34:
------------------

Ensure that a SELECT ... INTO statement that retrieves too few columns for the
number of variables in its variable list is rejected, with an appropriate error
message.
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( )
BEGIN
declare x integer; declare y integer; declare z integer;
set @x=x;
set @y=y;
set @z=z;
SELECT f4 into @x, @y, @z from t2;
END//
CALL sp1();
ERROR 21000: The used SELECT statements have a different number of columns
DROP PROCEDURE sp1;

Testcase 3.1.2.38:
------------------

Ensure that the scope of every condition declared is properly applied.
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS h1;
DROP TABLE IF EXISTS res_t1;
create table res_t1(w char unique, x char);
insert into res_t1 values('a', 'b');
CREATE PROCEDURE h1 ()
BEGIN
declare x1, x2, x3, x4, x5, x6 int default 0;
SELECT '-1-', x1, x2, x3, x4, x5, x6;
BEGIN
declare condname condition for sqlstate '23000';
declare continue handler for condname set x5 = 1;
set x6 = 0;
insert into res_t1 values ('a', 'b');
set x6 = 1;
SELECT '-2-', x1, x2, x3, x4, x5, x6;
END;
begin1_label: BEGIN
BEGIN
declare condname condition for sqlstate '20000';
declare continue handler for condname set x1 = 1;
set x2 = 0;
case x2
when 1 then set x2=10;
when 2 then set x2=11;
END case;
set x2 = 1;
SELECT '-3-', x1, x2, x3, x4, x5, x6;
begin2_label: BEGIN
BEGIN
declare condname condition for sqlstate '23000';
declare exit handler for condname set x3 = 1;
set x4= 1;
SELECT '-4a', x1, x2, x3, x4, x5, x6;
insert into res_t1 values ('a', 'b');
set x4= 2;
SELECT '-4b', x1, x2, x3, x4, x5, x6;
END;
SELECT '-5-', x1, x2, x3, x4, x5, x6;
END begin2_label;
SELECT '-6-', x1, x2, x3, x4, x5, x6;
END;
SELECT '-7-', x1, x2, x3, x4, x5, x6;
END begin1_label;
SELECT 'END', x1, x2, x3, x4, x5, x6;
END//
CALL h1();
-1-	x1	x2	x3	x4	x5	x6
-1-	0	0	0	0	0	0
-2-	x1	x2	x3	x4	x5	x6
-2-	0	0	0	0	1	1
-3-	x1	x2	x3	x4	x5	x6
-3-	1	1	0	0	1	1
-4a	x1	x2	x3	x4	x5	x6
-4a	1	1	0	1	1	1
-5-	x1	x2	x3	x4	x5	x6
-5-	1	1	1	1	1	1
-6-	x1	x2	x3	x4	x5	x6
-6-	1	1	1	1	1	1
-7-	x1	x2	x3	x4	x5	x6
-7-	1	1	1	1	1	1
END	x1	x2	x3	x4	x5	x6
END	1	1	1	1	1	1
DROP TABLE IF EXISTS tnull;
DROP PROCEDURE IF EXISTS sp1;
CREATE TABLE tnull(f1 int);
CREATE PROCEDURE sp1()
BEGIN
declare cond1 condition for sqlstate '42S02';
declare continue handler for cond1 set @var2 = 1;
BEGIN
declare cond1 condition for sqlstate '23000';
declare continue handler for cond1 set @var2 = 1;
END;
insert into tnull values(1);
END//
CALL sp1();
DROP PROCEDURE h1;
drop table res_t1;
DROP PROCEDURE sp1;
DROP TABLE tnull;

Testcase 3.1.2.43:
------------------

Ensure that the DECLARE ... HANDLER FOR statement can not declare any handler
for a condition declared outside of the scope of the handler.
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS h1;
DROP PROCEDURE IF EXISTS h2;
drop table IF EXISTS res_t1;
create table res_t1(w char unique, x char);
insert into res_t1 values ('a', 'b');
CREATE PROCEDURE h1 ()
BEGIN
declare x1, x2, x3, x4, x5, x6 int default 0;
BEGIN
declare cond_1 condition for sqlstate '23000';
declare continue handler for cond_1 set x5 = 1;
BEGIN
declare cond_2 condition for sqlstate '20000';
declare continue handler for cond_1 set x1 = 1;
BEGIN
declare continue handler for cond_2 set x3 = 1;
set x2 = 1;
END;
set x6 = 0;
END;
BEGIN
declare continue handler for cond_1 set x1 = 1;
BEGIN
declare continue handler for cond_2 set x3 = 1;
set x2 = 1;
END;
set x6 = 0;
END;
END;
SELECT x1, x2, x3, x4, x5, x6;
END//
ERROR 42000: Undefined CONDITION: cond_2
CREATE PROCEDURE h2 ()
BEGIN
declare x1, x2, x3, x4, x5, x6 int default 0;
BEGIN
declare condname condition for sqlstate '23000';
declare continue handler for condname set x5 = 1;
BEGIN
declare condname condition for sqlstate '20000';
declare continue handler for condname set x1 = 1;
BEGIN
declare condname condition for sqlstate '42000';
declare continue handler for condname set x3 = 1;
set x6 = 0;
insert into res_t1 values ('a', 'b');
set x6 = 1;
set x4= 0;
CALL sp1();
set x4= 1;
set x2 = 0;
case x2
when 1 then set x2=10;
when 2 then set x2=11;
END case;
set x2 = 1;
END;
set x2 = 0;
case x2
when 1 then set x2=10;
when 2 then set x2=11;
END case;
set x2 = 1;
set x6 = 0;
insert into res_t1 values ('a', 'b');
set x6 = 1;
END;
END;
SELECT x1, x2, x3, x4, x5, x6;
END//
CALL h2();
x1	x2	x3	x4	x5	x6
1	1	1	1	1	1
SELECT * FROM res_t1;
w	x
a	b
DROP PROCEDURE h2;
drop table res_t1;

Testcase 3.1.2.44:
------------------

Ensure that the DECLARE ... HANDLER FOR statement cannot declare a handler for
any invalid, or undeclared, condition.
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS h1;
CREATE PROCEDURE h1 ()
BEGIN
declare x1, x2, x3, x4, x5, x6 int default 0;
BEGIN
declare condname1 condition for sqlstate '23000';
BEGIN
declare condname2 condition for sqlstate '20000';