memory_storedproc.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 1,325 行 · 第 1/5 页

.
.                         IMPORTANT NOTICE:
.                         -----------------
.
.  FIXME: The <engine>_storedproc.result files are still NOT CHECKED
.         for correctness!
.
.  FIXME: Several tests are affected by known problems around DECIMAL
.  FIXME: and NUMERIC that needs to be checked again after WL#2984
.  FIXME: has been completed. Some of them are marked in the result.
.
.  This .result file has been checked OK with Linux 5.0.23-bk,
.  ChangeSet@1.2211, 2006-06-28 10:11:43-07:00.
.
. This file has been saved although it might contain failures / wrong
. results to be able to detect _new_ differences in the behaviour.
. Hopefully the remaining checks can be made soon.
.
--------------------------------------------------------------------------------
	FIXME: There are subtests that are switched off due to known bugs:
	------------------------------------------------------------------
WL#2984: DECIMAL and NUMERIC problems with differences on some machines
0

There are some statements where the ps-protocol is switched off.
Bug#11589: mysqltest, --ps-protocol, strange output, float/double/real with zerofill
--------------------------------------------------------------------------------

--source suite/funcs_1/storedproc/load_sp_tb.inc
--------------------------------------------------------------------------------
SET @@global.max_heap_table_size=4294967295;
SET @@session.max_heap_table_size=4294967295;

--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 = memory;
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t1;
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = memory;
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t2;
create table t3(f1 char(20),f2 char(20),f3 integer) engine = memory;
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' into table t3;
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = memory;
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/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 = memory;
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t6;
USE db_storedproc;
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = memory;
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/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 = memory;
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/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 = memory;
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table t9;
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = memory;
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t10;
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = memory;
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t11;

Section 3.1.1 - Syntax checks for the CREATE PROCEDURE, CREATE
FUNCTION, ALTER PROCEDURE, ALTER FUNCTION, DROP PROCEDURE, DROP FUNCTION, SHOW
CREATE PROCEDURE, SHOW CREATE FUNCTION, SHOW CREATE PROCEDURE STATUS, SHOW
CREATE FUNCTION STATUS, and CALL statements:
--------------------------------------------------------------------------------

Testcase 4.1.1:
---------------
Ensure that all clauses that should be supported are supported
CREATE PROCEDURE
--------------------------------------------------------------------------------
USE db_storedproc;
DROP PROCEDURE IF EXISTS sp1_thisisaveryverylongname234872934_thisisaveryverylongname234872934;
CREATE PROCEDURE sp1_thisisaveryverylongname234872934_thisisaveryverylongname234872934 (f1 char(20) )
SELECT * from t1 where f2 = f1;
ERROR 42000: Identifier name 'sp1_thisisaveryverylongname234872934_thisisaveryverylongname234872934' is too long
CALL sp1_thisisaveryverylongname234872934_thisisaveryverylongname234872934('aaaa');
ERROR 42000: PROCEDURE db_storedproc.sp1_thisisaveryverylongname234872934_thisisaveryverylongname234872934 does not exist
DROP PROCEDURE IF EXISTS sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde;
CREATE PROCEDURE sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde( f1 tinytext ) language sql deterministic sql security definer comment 'this is simple'
  BEGIN
set @v1 = f1;
SELECT @v1, @v1;
END//
ERROR 42000: Identifier name 'sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde' is too long
CALL sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde( 'abc' );
ERROR 42000: PROCEDURE db_storedproc.sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde does not exist
SHOW PROCEDURE status;
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( f1 binary ) language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
set @v1 = f1;
SELECT @v1;
END//
CALL sp1( 34 );
@v1
3
Warnings:
Warning	1265	Data truncated for column 'f1' at row 1
SHOW PROCEDURE status;
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp1	PROCEDURE	root@localhost	modified	created	INVOKER	this is simple
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( f1 blob ) language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
set @v1 = f1;
SELECT @v1;
END//
CALL sp1( 34 );
@v1
34
SHOW PROCEDURE status;
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp1	PROCEDURE	root@localhost	modified	created	INVOKER	this is simple
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( f1 int )  language sql not deterministic sql security invoker comment 'this is simple'
BEGIN
set @v1 = f1;
SELECT @v1;
END//
CALL sp1( 34 );
@v1
34
SHOW PROCEDURE status;
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp1	PROCEDURE	root@localhost	modified	created	INVOKER	this is simple
DROP PROCEDURE IF EXISTS sp1;
FIXME: Following test contains a known problem that will be checked again
FIXME: after WL#2984 has been completed: SP definition accepted with m>60 in DECIMAL(m,n)
CREATE PROCEDURE sp1( f1 decimal(256, 30) ) language sql not deterministic sql security invoker comment 'this is simple'
BEGIN
set @v1 = f1;
SELECT @v1;
END//
ERROR 42000: Too big precision 256 specified for column ''. Maximum is 65.
DROP PROCEDURE IF EXISTS sp1//
Warnings:
Note	1305	PROCEDURE sp1 does not exist
CREATE PROCEDURE sp1( f1 decimal(66, 30) ) language sql not deterministic sql security invoker comment 'this is simple'
BEGIN
set @v1 = f1;
SELECT @v1;
END//
ERROR 42000: Too big precision 66 specified for column ''. Maximum is 65.
DROP PROCEDURE IF EXISTS sp1//
Warnings:
Note	1305	PROCEDURE sp1 does not exist
CREATE PROCEDURE sp1( f1 decimal(60, 30) ) language sql not deterministic sql security invoker comment 'this is simple'
BEGIN
set @v1 = f1;
SELECT @v1;
END//
CALL sp1( 17976931340000 );
@v1
17976931340000.000000000000000000000000000000
SHOW PROCEDURE status;
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp1	PROCEDURE	root@localhost	modified	created	INVOKER	this is simple
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( f1 enum("value1", "value1") )  language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
SELECT f1;
END//
Warnings:
Note	1291	Column '' has duplicated value 'value1' in ENUM
CALL sp1( "value1" );
f1
value1
Warnings:
Note	1291	Column '' has duplicated value 'value1' in ENUM
SHOW PROCEDURE status;
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp1	PROCEDURE	root@localhost	modified	created	INVOKER	this is simple
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( f1 set("value1", "value1") ) language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
SELECT f1;
END//
Warnings:
Note	1291	Column '' has duplicated value 'value1' in SET
CALL sp1( "value1, value1" );
f1
value1
Warnings:
Note	1291	Column '' has duplicated value 'value1' in SET
Warning	1265	Data truncated for column 'f1' at row 1
SHOW PROCEDURE status;
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp1	PROCEDURE	root@localhost	modified	created	INVOKER	this is simple
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( f1 enum("value1", "value1") )  language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
SELECT f1;
END//
Warnings:
Note	1291	Column '' has duplicated value 'value1' in ENUM
CALL sp1( "value1" );
f1
value1
Warnings:
Note	1291	Column '' has duplicated value 'value1' in ENUM
SHOW PROCEDURE status;
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp1	PROCEDURE	root@localhost	modified	created	INVOKER	this is simple
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( f1 text ) language sql SELECT f1;
CALL sp1( 'abc' );
f1
abc
SHOW PROCEDURE status like 'sp1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp1	PROCEDURE	root@localhost	modified	created	DEFINER	
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( f1 text ) deterministic SELECT f1;
CALL sp1( 'abc' );
f1
abc
SHOW PROCEDURE status like 'sp1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp1	PROCEDURE	root@localhost	modified	created	DEFINER	
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( f1 text ) not deterministic SELECT f1;
CALL sp1( 'abc' );
f1
abc
SHOW PROCEDURE status like 'sp1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp1	PROCEDURE	root@localhost	modified	created	DEFINER	
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( f1 text ) sql security definer SELECT f1;
CALL sp1( 'abc' );
f1
abc
SHOW PROCEDURE status like 'sp1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp1	PROCEDURE	root@localhost	modified	created	DEFINER	
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( f1 text ) sql security invoker SELECT f1;