storedproc_master.inc

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

#### suite/funcs_1/storedproc/storedproc_master.inc
#
--enable_query_log

let $message= .
              .                         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.
              .;
--source include/show_msg80.inc

#FIXME-START-------------------------------------------------------------------
#
# remove this section prior releasing the test, then we may stop at the first
# error we get ('passed' or 'not passed' - that's the question).
--disable_abort_on_error

   # having ALWAYS a .reject from the changing TIME makes it easier to work with
   # the GUI diff program until the testcase is finished for pushing.
   #
   # let $message= FIXME: Show some information when / where the .result has been created;
   # --source include/show_msg.inc
   --vertical_results
   # SELECT version(), @@version_compile_os AS 'vers_comp_os', current_date, current_time;

   let $message= FIXME: There are subtests that are switched off due to known bugs:;
   --source include/show_msg.inc
   #set a variable here to be able to switch all crashing sub tests with ONE change HERE.
   #change the corresponding variable to '1' to enable the crashing parts.
   --disable_query_log

   let $check_wl2984= 0;
   eval SELECT $check_wl2984 AS 'WL#2984: DECIMAL and NUMERIC problems with differences on some machines';

   --horizontal_results
   --enable_query_log

# As long as
# Bug#11589: mysqltest, --ps-protocol, strange output, float/double/real with zerofill
# is not fixed, we must switch the ps-protocol for some statements off.
# If this bug is fixed, please
# 1. set the following variable to 0
# 2. check, if the test passes
# 3. remove the workarounds
let $have_bug_11589= 1;
if ($have_bug_11589)
{
   let $message= There are some statements where the ps-protocol is switched off.
                 Bug#11589: mysqltest, --ps-protocol, strange output, float/double/real with zerofill;
   --source include/show_msg80.inc
}

#
#FIXME-END---------------------------------------------------------------------


# prepare a variable to be able to suppress machine dependant diffs
#let $SERVER_NAME= `SELECT DISTINCT host FROM mysql.user WHERE host LIKE "%\%" AND host NOT In ("localhost", "127.0.0.1", "%")`;
# can be used in: --replace_result $SERVER_NAME <SERVER_NAME>

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


# ==============================================================================
let $message= 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:;
--source include/show_msg80.inc


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.1:
              ---------------
              Ensure that all clauses that should be supported are supported
              CREATE PROCEDURE;
--source include/show_msg80.inc

USE db_storedproc;

--disable_warnings
DROP PROCEDURE IF EXISTS sp1_thisisaveryverylongname234872934_thisisaveryverylongname234872934;
--enable_warnings

CREATE PROCEDURE sp1_thisisaveryverylongname234872934_thisisaveryverylongname234872934 (f1 char(20) )
            SELECT * from t1 where f2 = f1;
CALL sp1_thisisaveryverylongname234872934_thisisaveryverylongname234872934('aaaa');

--disable_warnings
DROP PROCEDURE IF EXISTS sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde;
--enable_warnings

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

CALL sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde( 'abc' );

--replace_column 5 modified 6 created
SHOW PROCEDURE status;

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

delimiter //;
CREATE PROCEDURE sp1( f1 binary ) language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
    set @v1 = f1;
    SELECT @v1;
END//
delimiter ;//

CALL sp1( 34 );

--replace_column 5 modified 6 created
SHOW PROCEDURE status;

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

delimiter //;
CREATE PROCEDURE sp1( f1 blob ) language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
    set @v1 = f1;
    SELECT @v1;
END//
delimiter ;//

CALL sp1( 34 );

--replace_column 5 modified 6 created
SHOW PROCEDURE status;

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

delimiter //;
CREATE PROCEDURE sp1( f1 int )  language sql not deterministic sql security invoker comment 'this is simple'
BEGIN
    set @v1 = f1;
    SELECT @v1;
END//
delimiter ;//

CALL sp1( 34 );

--replace_column 5 modified 6 created
SHOW PROCEDURE status;

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

delimiter //;
-- echo FIXME: Following test contains a known problem that will be checked again
-- echo FIXME: after WL#2984 has been completed: SP definition accepted with m>60 in DECIMAL(m,n)
#FIXME --error 4711
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//
DROP PROCEDURE IF EXISTS sp1//

# should fail: 66 is not allowed; maximum is 65.
#FIXME --error 4711
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//
DROP PROCEDURE IF EXISTS sp1//

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

CALL sp1( 17976931340000 );

if ($check_wl2984)
{
   # switched off due to big differences with (e.g....) sol10-sparc-b
   CALL sp1( 1.797693134e+13 );
   CALL sp1( 1.7976931348623157493578e+308 );

   -- echo FIXME: Following test contains a known problem that will be checked again
   -- echo FIXME: after WL#2984 has been completed:
   -- echo FIXME: values accepted without warning although m>60 in DECIMAL(m,n)

   # check all ...E+100 to E-100
   let $digits= 100;
   while ($digits)
   {
      eval CALL sp1( 0.1234567890987654321e+$digits );
      eval CALL sp1( 0.1234567890987654321e-$digits );
      dec $digits;
   }
   # check the 2 values which cannot be handled easy in the loop:
   eval CALL sp1( 0.1234567890987654321e+0 );
   eval CALL sp1( 0.1234567890987654321e-0 );
}

--replace_column 5 modified 6 created
SHOW PROCEDURE status;

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

delimiter //;
CREATE PROCEDURE sp1( f1 enum("value1", "value1") )  language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
    SELECT f1;
END//
delimiter ;//

CALL sp1( "value1" );

--replace_column 5 modified 6 created
SHOW PROCEDURE status;

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

delimiter //;
CREATE PROCEDURE sp1( f1 set("value1", "value1") ) language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
    SELECT f1;
END//
delimiter ;//

CALL sp1( "value1, value1" );

--replace_column 5 modified 6 created
SHOW PROCEDURE status;

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

delimiter //;
CREATE PROCEDURE sp1( f1 enum("value1", "value1") )  language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
    SELECT f1;
END//
delimiter ;//

CALL sp1( "value1" );

--replace_column 5 modified 6 created
SHOW PROCEDURE status;

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

CREATE PROCEDURE sp1( f1 text ) language sql SELECT f1;

CALL sp1( 'abc' );

--replace_column 5 modified 6 created
SHOW PROCEDURE status like 'sp1';

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

CREATE PROCEDURE sp1( f1 text ) deterministic SELECT f1;
CALL sp1( 'abc' );

--replace_column 5 modified 6 created
SHOW PROCEDURE status like 'sp1';

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

CREATE PROCEDURE sp1( f1 text ) not deterministic SELECT f1;
CALL sp1( 'abc' );

--replace_column 5 modified 6 created
SHOW PROCEDURE status like 'sp1';

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

CREATE PROCEDURE sp1( f1 text ) sql security definer SELECT f1;
CALL sp1( 'abc' );

--replace_column 5 modified 6 created
SHOW PROCEDURE status like 'sp1';

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

CREATE PROCEDURE sp1( f1 text ) sql security invoker SELECT f1;
CALL sp1( 'abc' );

--replace_column 5 modified 6 created
SHOW PROCEDURE status like 'sp1';

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
--enable_warnings

CREATE PROCEDURE sp1( f1 text ) comment 'this is simple' SELECT f1;
CALL sp1( 'abc' );

--replace_column 5 modified 6 created
SHOW PROCEDURE status like 'sp1';

# cleanup
DROP PROCEDURE sp1_thisisaveryverylongname234872934_thisisaveryverylongname234872934;
DROP PROCEDURE sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde;
DROP PROCEDURE sp1;

# ------------------------------------------------------------------------------
let $message= Testcase 4.1.2:
              ---------------
              Ensure that all clauses that should be supported are supported
              CREATE FUNCTION;
--source include/show_msg80.inc


--disable_warnings
DROP FUNCTION IF EXISTS fn1;
--enable_warnings

CREATE FUNCTION fn1 (s char(20)) returns char(50)
    return concat('hello, ', s, '!');
SELECT fn1('world');

--disable_warnings
DROP FUNCTION IF EXISTS fn1;
--enable_warnings

delimiter //;
CREATE FUNCTION fn1( f1 mediumtext ) returns mediumtext   language sql deterministic sql security definer comment 'this is simple'
  BEGIN
    set @v1 = 'hello';
    set f1 = concat( @v1, f1 );
    return f1;
END//
delimiter ;//

SELECT fn1( ' world');

--replace_column 5 modified 6 created
SHOW FUNCTION STATUS LIKE 'fn1';

--disable_warnings
DROP FUNCTION IF EXISTS fn1;
--enable_warnings

delimiter //;
CREATE FUNCTION fn1( f1 smallint ) returns smallint language sql not deterministic sql security invoker comment 'this is simple'
BEGIN
    set f1 = 1 + f1;
    return f1;
END//
delimiter ;//

SELECT fn1( 126 );

--replace_column 5 modified 6 created
SHOW FUNCTION STATUS LIKE 'fn1';

--disable_warnings
DROP FUNCTION IF EXISTS fn1;
--enable_warnings

delimiter //;
# 1425: Too big scale 63 specified for column ''. Maximum is 30.
--error 1425
CREATE FUNCTION fn1( f1 decimal(63, 31) ) returns decimal(63, 31) language sql not deterministic sql security invoker comment 'this is simple'
BEGIN
    set f1 = 1000000 + f1;
    return f1;
END//
delimiter ;//

--error 1305
SELECT fn1( 1.3326e+8 );

delimiter //;
CREATE FUNCTION fn1( f1 decimal(63, 30) ) returns decimal(63, 30) language sql not deterministic sql security invoker comment 'this is simple'
BEGIN
    set f1 = 1000000 + f1;
    return f1;
END//
delimiter ;//

SELECT fn1( 1.3326e+8 );

--replace_column 5 modified 6 created
SHOW FUNCTION STATUS LIKE 'fn1';

--disable_warnings
DROP FUNCTION IF EXISTS fn1;
--enable_warnings

delimiter //;
CREATE FUNCTION fn1( f1 enum("value1", "value1") ) returns decimal(63, 30)  language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
    return f1;
END//
delimiter ;//

SELECT fn1( "value1" );

--replace_column 5 modified 6 created
SHOW FUNCTION STATUS LIKE 'fn1';

--disable_warnings
DROP FUNCTION IF EXISTS fn1;
--enable_warnings

delimiter //;
CREATE FUNCTION fn1( f1 set("value1", "value1") ) returns decimal(63, 30) language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
    return f1;
END//
delimiter ;//

SELECT fn1( "value1, value1" );

--replace_column 5 modified 6 created
SHOW FUNCTION STATUS LIKE 'fn1';

--disable_warnings
DROP FUNCTION IF EXISTS fn1;
--enable_warnings

delimiter //;
CREATE FUNCTION fn1( f1 smallint ) returns smallint language sql
  BEGIN
      set f1 = 1 + f1;
      return f1;
END//
delimiter ;//

SELECT fn1( 126 );

--replace_column 5 modified 6 created
SHOW FUNCTION STATUS LIKE 'fn1';

--disable_warnings
DROP FUNCTION IF EXISTS fn1;
--enable_warnings

delimiter //;
CREATE FUNCTION fn1( f1 smallint ) returns smallint deterministic
  BEGIN
      set f1 = 1 + f1;
      return f1;
END//
delimiter ;//

SELECT fn1( 126 );

--replace_column 5 modified 6 created
SHOW FUNCTION STATUS LIKE 'fn1';

--disable_warnings
DROP FUNCTION IF EXISTS fn1;
--enable_warnings

delimiter //;
CREATE FUNCTION fn1( f1 smallint ) returns smallint not deterministic
  BEGIN
      set f1 = 1 + f1;
      return f1;
END//
delimiter ;//

SELECT fn1( 126 );

--replace_column 5 modified 6 created
SHOW FUNCTION STATUS LIKE 'fn1';

--disable_warnings
DROP FUNCTION IF EXISTS fn1;
--enable_warnings

delimiter //;
CREATE FUNCTION fn1( f1 smallint ) returns smallint
  sql security definer
  BEGIN
      set f1 = 1 + f1;
      return f1;
END//
delimiter ;//

SELECT fn1( 126 );

--replace_column 5 modified 6 created
SHOW FUNCTION STATUS LIKE 'fn1';

--disable_warnings
DROP FUNCTION IF EXISTS fn1;
--enable_warnings

delimiter //;
CREATE FUNCTION fn1( f1 smallint ) returns smallint
  sql security invoker
BEGIN
   set f1 = 1 + f1;
   return f1;
END//
delimiter ;//

SELECT fn1( 126 );

--replace_column 5 modified 6 created
SHOW FUNCTION STATUS LIKE 'fn1';

--disable_warnings
DROP FUNCTION IF EXISTS fn1;
--enable_warnings

delimiter //;
CREATE FUNCTION fn1( f1 smallint ) returns smallint
   comment 'this is simple'
BEGIN
   set f1 = 1 + f1;
   return f1;
END//
delimiter ;//

SELECT fn1( 126 );
--replace_column 5 modified 6 created
SHOW FUNCTION STATUS LIKE 'fn1';

# cleanup
DROP FUNCTION fn1;

# ------------------------------------------------------------------------------
let $message= Testcase 4.1.3:
              ---------------