sp-error.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

create table t1 (a int)|
CREATE PROCEDURE bug13012_1() REPAIR TABLE t1|
CREATE FUNCTION bug13012_2() RETURNS INT
BEGIN
  CALL bug13012_1();
  RETURN 1;
END|
--error ER_SP_NO_RETSET
SELECT bug13012_2()|
drop table t1|
drop procedure bug13012_1|
drop function bug13012_2|
delimiter ;|

#
# BUG#11555 "Stored procedures: current SP tables locking make 
# impossible view security". We should not expose names of tables
# which are implicitly used by view (via stored routines/triggers).
#
# Note that SQL standard assumes that you simply won't be able drop table
# and leave some objects (routines/views/triggers) which were depending on
# it. Such objects should be dropped in advance (by default) or will be
# dropped simultaneously with table (DROP TABLE with CASCADE clause).
# So these tests probably should go away once we will implement standard
# behavior.
--disable_warnings
drop function if exists bug11555_1;
drop function if exists bug11555_2;
drop view if exists v1, v2, v3, v4;
--enable_warnings
create function bug11555_1() returns int return (select max(i) from t1);
create function bug11555_2() returns int return bug11555_1();
# It is OK to report name of implicitly used table which is missing
# when we create view.
# For stored functions however, because of exceptions handlers, there is
# no easy way to find out if a missing table makes the view invalid.
create view v1 as select bug11555_1();
drop view v1;
create view v2 as select bug11555_2();
drop view v2;
# But we should hide name of missing implicitly used table when we use view
create table t1 (i int);
create view v1 as select bug11555_1();
create view v2 as select bug11555_2();
create view v3 as select * from v1;
drop table t1;
--error ER_VIEW_INVALID
select * from v1;
--error ER_VIEW_INVALID
select * from v2;
--error ER_VIEW_INVALID
select * from v3;
# Note that creation of view which depends on broken view is yet 
# another form of view usage.
create view v4 as select * from v1;
drop view v1, v2, v3, v4;
# We also should hide details about broken triggers which are
# invoked for view.
drop function bug11555_1;
drop function bug11555_2;
create table t1 (i int);
create table t2 (i int);
create trigger t1_ai after insert on t1 for each row insert into t2 values (new.i);
create view v1 as select * from t1;
drop table t2;
# Limitation, the desired error is ER_VIEW_INVALID
--error ER_NO_SUCH_TABLE
insert into v1 values (1);
drop trigger t1_ai;
create function bug11555_1() returns int return (select max(i) from t2);
create trigger t1_ai after insert on t1 for each row set @a:=bug11555_1();
# Limitation, the desired error is ER_VIEW_INVALID
--error ER_NO_SUCH_TABLE 
insert into v1 values (2);
drop function bug11555_1;
drop table t1;
drop view v1;

#
# BUG#15658: Server crashes after creating function as empty string
#
--disable_warnings
drop procedure if exists ` bug15658`;
--enable_warnings

--error ER_SP_WRONG_NAME
create procedure ``() select 1;
--error ER_SP_WRONG_NAME
create procedure ` `() select 1;
--error ER_SP_WRONG_NAME
create procedure `bug15658 `() select 1;
--error ER_WRONG_DB_NAME
create procedure ``.bug15658() select 1;
--error ER_WRONG_DB_NAME
create procedure `x `.bug15658() select 1;

# This should work
create procedure ` bug15658`() select 1;
call ` bug15658`();
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show procedure status;
drop procedure ` bug15658`;


#
# BUG#14270: Stored procedures: crash if load index
#
--disable_warnings
drop function if exists bug14270;
drop table if exists t1;
--enable_warnings

create table t1 (s1 int primary key);

delimiter |;
--error ER_SP_NO_RETSET
create function bug14270() returns int
begin
  load index into cache t1;
  return 1;
end|

--error ER_SP_NO_RETSET
create function bug14270() returns int
begin
  cache index t1 key (`primary`) in keycache1;
  return 1;
end|
delimiter ;|

drop table t1;


#
# BUG#15091: Sp Returns Unknown error in order clause....and 
#            there is no order by clause
#
--disable_warnings
drop procedure if exists bug15091;
--enable_warnings

delimiter |;
create procedure bug15091()
begin
  declare selectstr varchar(6000) default ' ';
  declare conditionstr varchar(5000)  default '';

  set selectstr = concat(selectstr,
                         ' and ',
                         c.operatorid,
                         'in (',conditionstr, ')');
end|
delimiter ;|

# The error message used to be:
#   ERROR 1109 (42S02): Unknown table 'c' in order clause
# but is now rephrased to something less misleading:
#   ERROR 1109 (42S02): Unknown table 'c' in field list
--error ER_UNKNOWN_TABLE
call bug15091();

drop procedure bug15091;


#
# BUG#16896: Stored function: unused AGGREGATE-clause in CREATE FUNCTION
#
--disable_warnings
drop function if exists bug16896;
--enable_warnings

--error ER_SP_NO_AGGREGATE
create aggregate function bug16896() returns int return 1;


#
# BUG#14702: misleading error message when syntax error in CREATE
# PROCEDURE
#
# Misleading error message was given when IF NOT EXISTS was used in
# CREATE PROCEDURE.
#
--disable_warnings
DROP PROCEDURE IF EXISTS bug14702;
--enable_warnings

--error ER_PARSE_ERROR
CREATE IF NOT EXISTS PROCEDURE bug14702()
BEGIN
END;

--error ER_PARSE_ERROR
CREATE PROCEDURE IF NOT EXISTS bug14702()
BEGIN
END;


#
# BUG#20953: create proc with a create view that uses local
# vars/params should fail to create
#
# See test case for what syntax is forbidden in a view.
#
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (i INT);

# We do not have to drop this procedure and view because they won't be
# created.
--error ER_VIEW_SELECT_CLAUSE
CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO @a;
--error ER_VIEW_SELECT_CLAUSE
CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO DUMPFILE "file";
--error ER_VIEW_SELECT_CLAUSE
CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO OUTFILE "file";
--error ER_VIEW_SELECT_CLAUSE
CREATE PROCEDURE bug20953()
  CREATE VIEW v AS SELECT i FROM t1 PROCEDURE ANALYSE();
--error ER_VIEW_SELECT_DERIVED
CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1;
--error ER_VIEW_SELECT_VARIABLE
CREATE PROCEDURE bug20953(i INT) CREATE VIEW v AS SELECT i;
delimiter |;
--error ER_VIEW_SELECT_VARIABLE
CREATE PROCEDURE bug20953()
BEGIN
  DECLARE i INT;
  CREATE VIEW v AS SELECT i;
END |
delimiter ;|
--error ER_VIEW_SELECT_VARIABLE
PREPARE stmt FROM "CREATE VIEW v AS SELECT ?";

DROP TABLE t1;


#
# BUG#24491 "using alias from source table in insert ... on duplicate key"
#
--disable_warnings
drop tables if exists t1;
drop procedure if exists bug24491;
--enable_warnings
create table t1 (id int primary key auto_increment, value varchar(10));
insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD');
# Let us create routine with INSERT ... SELECT ... ON DUPLICATE KEY UPDATE 
# statement which in its ON DUPLICATE KEY clause erroneously tries to assign
# value to a column which is mentioned only in SELECT part.
create procedure bug24491()
  insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on duplicate key update v = 'DUP';
# Both first and second calls to it should fail
--error ER_BAD_FIELD_ERROR 
call bug24491();
--error ER_BAD_FIELD_ERROR
call bug24491();
drop procedure bug24491;
# And now the same test for more complex case which is more close
# to the one that was reported originally.
create procedure bug24491()
  insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as value) as y on duplicate key update y.value = 'DUP';
--error ER_BAD_FIELD_ERROR 
call bug24491();
--error ER_BAD_FIELD_ERROR
call bug24491();
drop procedure bug24491;
drop tables t1;

#
# BUG#18914: Calling certain SPs from triggers fail
#
# Failing to call a procedure that does implicit commit from a trigger
# is a correct behaviour, however the error message was misleading.
#
# DROP TABLE IF EXISTS is also fixed to give correct error instead of
# "Table doesn't exist". 
#
--disable_warnings
DROP FUNCTION IF EXISTS bug18914_f1;
DROP FUNCTION IF EXISTS bug18914_f2;
DROP PROCEDURE IF EXISTS bug18914_p1;
DROP PROCEDURE IF EXISTS bug18914_p2;
DROP TABLE IF EXISTS t1, t2;
--enable_warnings

CREATE TABLE t1 (i INT);

CREATE PROCEDURE bug18914_p1() CREATE TABLE t2 (i INT);
CREATE PROCEDURE bug18914_p2() DROP TABLE IF EXISTS no_such_table;

delimiter |;
CREATE FUNCTION bug18914_f1() RETURNS INT
BEGIN
  CALL bug18914_p1();
  RETURN 1;
END |

CREATE FUNCTION bug18914_f2() RETURNS INT
BEGIN
  CALL bug18914_p2();
  RETURN 1;
END |
delimiter ;|

CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
  CALL bug18914_p1();

--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
INSERT INTO t1 VALUES (1);

--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
SELECT bug18914_f1();

--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
SELECT bug18914_f2();

--error ER_NO_SUCH_TABLE
SELECT * FROM t2;

DROP FUNCTION bug18914_f1;
DROP FUNCTION bug18914_f2;
DROP PROCEDURE bug18914_p1;
DROP PROCEDURE bug18914_p2;
DROP TABLE t1;

#
# Bug#20713 (Functions will not not continue for SQLSTATE VALUE '42S02')
#

--disable_warnings
drop table if exists bogus_table_20713;
drop function if exists func_20713_a;
drop function if exists func_20713_b;
--enable_warnings

create table bogus_table_20713( id int(10) not null primary key);
insert into bogus_table_20713 values (1), (2), (3);

delimiter //;

create function func_20713_a() returns int(11)
begin
  declare id int;

  declare continue handler for sqlexception set id=null;

  set @in_func := 1;
  set id = (select id from bogus_table_20713 where id = 3);
  set @in_func := 2;

  return id;
end//

create function func_20713_b() returns int(11)
begin
  declare id int;

  declare continue handler for sqlstate value '42S02' set id=null;

  set @in_func := 1;
  set id = (select id from bogus_table_20713 where id = 3);
  set @in_func := 2;

  return id;
end//

delimiter ;//

set @in_func := 0;
select func_20713_a();
select @in_func;

set @in_func := 0;
select func_20713_b();
select @in_func;

drop table bogus_table_20713;

set @in_func := 0;
select func_20713_a();
select @in_func;

set @in_func := 0;
select func_20713_b();
select @in_func;

drop function if exists func_20713_a;
drop function if exists func_20713_b;

#
# Bug#25345 (Cursors from Functions)
#

--disable_warnings
drop table if exists table_25345_a;
drop table if exists table_25345_b;
drop procedure if exists proc_25345;
drop function if exists func_25345;
drop function if exists func_25345_b;
--enable_warnings

create table table_25345_a (a int);
create table table_25345_b (b int);

delimiter ||;

create procedure proc_25345()
begin
  declare c1 cursor for select a from table_25345_a;
  declare c2 cursor for select b from table_25345_b;

  select 1 as result;
end ||

create function func_25345() returns int(11)
begin
  call proc_25345();
  return 1;
end ||

create function func_25345_b() returns int(11)
begin
  declare c1 cursor for select a from table_25345_a;
  declare c2 cursor for select b from table_25345_b;

  return 1;
end ||

delimiter ;||

call proc_25345();
--error ER_SP_NO_RETSET
select func_25345();
select func_25345_b();

drop table table_25345_a;

call proc_25345();
--error ER_SP_NO_RETSET
select func_25345();
select func_25345_b();

drop table table_25345_b;
drop procedure proc_25345;
drop function func_25345;
drop function func_25345_b;

#
# Bug#26503 (Illegal SQL exception handler code causes the server to crash)
#

delimiter //;

--error ER_SP_LILABEL_MISMATCH
create procedure proc_26503_error_1()
begin
retry:
  repeat
    begin
      declare continue handler for sqlexception
      begin
        iterate retry;
      end

      select "do something";
    end
  until true end repeat retry;
end//

--error ER_SP_LILABEL_MISMATCH
create procedure proc_26503_error_2()
begin
retry:
  repeat
    begin
      declare continue handler for sqlexception
        iterate retry;

      select "do something";
    end
  until true end repeat retry;
end//

--error ER_SP_LILABEL_MISMATCH
create procedure proc_26503_error_3()
begin
retry:
  repeat
    begin
      declare continue handler for sqlexception
      begin
        leave retry;
      end

      select "do something";
    end
  until true end repeat retry;
end//

--error ER_SP_LILABEL_MISMATCH
create procedure proc_26503_error_4()
begin
retry:
  repeat
    begin
      declare continue handler for sqlexception
        leave retry;

      select "do something";
    end
  until true end repeat retry;
end//

delimiter ;//

#
# BUG#NNNN: New bug synopsis
#
#--disable_warnings
#drop procedure if exists bugNNNN|
#--enable_warnings
#create procedure bugNNNN...