sp-error.test

#
# Stored PROCEDURE error tests
#

--disable_warnings ONCE
drop table if exists t1, t2;

# Backup the mysql.proc table
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval SELECT * FROM mysql.proc INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/proc.txt';

# Make sure we don't have any procedures left.
delete from mysql.proc;

delimiter |;

# This should give three syntax errors (sometimes crashed; bug #643)
# (Unfortunately, this is not a 100% test, on some platforms this
#  passed despite the bug.)
--error 1064
create procedure syntaxerror(t int)|
--error 1064
create procedure syntaxerror(t int)|
--error 1064
create procedure syntaxerror(t int)|

# Check that we get the right error, i.e. UDF declaration parses correctly,
# but foo.so doesn't exist.
#    This generates an error message containing a misleading errno which
#    might vary between systems (it usually doesn't have anything to do with
#    the actual failing dlopen()).
#--error 1126
#create function foo returns real soname "foo.so"|


--disable_warnings ONCE
drop table if exists t3|
create table t3 ( x int )|
insert into t3 values (2), (3)|

create procedure bad_into(out param int)
  select x from t3 into param|

--error 1172
call bad_into(@x)|

drop procedure bad_into|
drop table t3|


create procedure proc1()
  set @x = 42|

create function func1() returns int
  return 42|

# Can't create recursively
--error 1303
create procedure foo()
  create procedure bar() set @x=3|
--error 1303
create procedure foo()
  create function bar() returns double return 2.3|

# Already exists
--error 1304
create procedure proc1()
  set @x = 42|
--error 1304
create function func1() returns int
  return 42|

drop procedure proc1|
drop function func1|

# Does not exist
--error 1305
alter procedure foo|
--error 1305
alter function foo|
--error 1305
drop procedure foo|
--error 1305
drop function foo|
--error 1305
call foo()|
drop procedure if exists foo|
--error 1305
show create procedure foo|
--error 1305
show create function foo|

# LEAVE/ITERATE with no match
--error 1308
create procedure foo()
foo: loop
  leave bar;
end loop|
--error 1308
create procedure foo()
foo: loop
  iterate bar;
end loop|
--error 1308
create procedure foo()
foo: begin
  iterate foo;
end|

# Redefining label
--error 1309
create procedure foo()
foo: loop
  foo: loop
    set @x=2;
  end loop foo;
end loop foo|

# End label mismatch
--error 1310
create procedure foo()
foo: loop
  set @x=2;
end loop bar|

# RETURN in FUNCTION only
--error 1313
create procedure foo()
  return 42|

# Wrong number of arguments
create procedure p(x int)
  set @x = x|
create function f(x int) returns int
  return x+42|

--error 1318
call p()|
--error 1318
call p(1, 2)|
--error 1318
select f()|
--error 1318
select f(1, 2)|

drop procedure p|
drop function f|

--error 1319 
create procedure p(val int, out res int)
begin
  declare x int default 0;
  declare continue handler for foo set x = 1;

  insert into test.t1 values (val);
  if (x) then
    set res = 0;
  else
    set res = 1;
  end if;
end|

--error 1319 
create procedure p(val int, out res int)
begin
  declare x int default 0;
  declare foo condition for 1146;
  declare continue handler for bar set x = 1;

  insert into test.t1 values (val);
  if (x) then
    set res = 0;
  else
    set res = 1;
  end if;
end|

--error 1320
create function f(val int) returns int
begin
  declare x int;

  set x = val+3;
end|

create function f(val int) returns int
begin
  declare x int;

  set x = val+3;
  if x < 4 then
    return x;
  end if;
end|

--error 1321
select f(10)|

drop function f|

--error ER_PARSE_ERROR
create procedure p()
begin
  declare c cursor for insert into test.t1 values ("foo", 42);

  open c;
  close c;
end|

--error 1323
create procedure p()
begin
  declare x int;
  declare c cursor for select * into x from test.t limit 1;

  open c;
  close c;
end|

--error 1324
create procedure p()
begin
  declare c cursor for select * from test.t;

  open cc;
  close c;
end|

--disable_warnings ONCE
drop table if exists t1|
create table t1 (val int)|

create procedure p()
begin
  declare c cursor for select * from test.t1;

  open c;
  open c;
  close c;
end|
--error 1325
call p()|
drop procedure p|

create procedure p()
begin
  declare c cursor for select * from test.t1;

  open c;
  close c;
  close c;
end|
--error 1326
call p()|
drop procedure p|

--error 1305
alter procedure bar3 sql security invoker|

drop table t1|

--disable_warnings ONCE
drop table if exists t1|
create table t1 (val int, x float)|
insert into t1 values (42, 3.1), (19, 1.2)|

--error 1327
create procedure p()
begin
  declare x int;
  declare c cursor for select * from t1;

  open c;
  fetch c into x, y;
  close c;
end|

create procedure p()
begin
  declare x int;
  declare c cursor for select * from t1;

  open c;
  fetch c into x;
  close c;
end|
--error 1328
call p()|
drop procedure p|

create procedure p()
begin
  declare x int;
  declare y float;
  declare z int;
  declare c cursor for select * from t1;

  open c;
  fetch c into x, y, z;
  close c;
end|
--error 1328
call p()|
drop procedure p|

--error 1330
create procedure p(in x int, x char(10))
begin
end|
--error 1330
create function p(x int, x char(10))
begin
end|

--error 1331
create procedure p()
begin
  declare x float;
  declare x int;
end|

--error 1332
create procedure p()
begin
  declare c condition for 1064;
  declare c condition for 1065;
end|

--error 1333
create procedure p()
begin
  declare c cursor for select * from t1;
  declare c cursor for select field from t1;
end|

# USE is not allowed
--error ER_SP_BADSTATEMENT 
create procedure u()
  use sptmp|

# Enforced standard order of declarations
--error 1337
create procedure p()
begin
  declare c cursor for select * from t1;
  declare x int;
end|
--error 1337
create procedure p()
begin
  declare x int;
  declare continue handler for sqlstate '42S99' set x = 1;
  declare foo condition for sqlstate '42S99';
end|

--error 1338
create procedure p()
begin
  declare x int;
  declare continue handler for sqlstate '42S99' set x = 1;
  declare c cursor for select * from t1;
end|

# Check in and inout arguments.
--disable_warnings ONCE
drop procedure if exists p|
create procedure p(in x int, inout y int, out z int)
begin
  set y = x+y;
  set z = x+y;
end|

set @tmp_x = 42|
set @tmp_y = 3|
set @tmp_z = 0|
# For reference: this is ok
call p(@tmp_x, @tmp_y, @tmp_z)|
select @tmp_x, @tmp_y, @tmp_z|

--error ER_SP_NOT_VAR_ARG
call p(42, 43, @tmp_z)|
--error ER_SP_NOT_VAR_ARG
call p(42, @tmp_y, 43)|

drop procedure p|


#
# Let us test that we can access mysql.proc table for routines
# definitions lookup without locking it explicitly.
#
create procedure p() begin end|
lock table t1 read|
# This should succeed
call p()|
unlock tables|
drop procedure p|
# Let us check restrictions which this ability puts on mysql.proc locking.
--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
lock tables t1 read, mysql.proc write|
--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
lock tables mysql.proc write, mysql.user write|
# Locking for read should be OK
lock tables t1 read, mysql.proc read|
unlock tables|
# You also should be able lock only mysql.proc for write
lock tables mysql.proc write|
unlock tables|


#
# Check that in functions we don't allow to update tables which
# are used by statements which invoke these functions.
#
--disable_warnings ONCE
drop function if exists f1|
create function f1(i int) returns int
begin
  insert into t1 (val) values (i);
  return 0;
end|
--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
select val, f1(val) from t1|
# Table alias should not matter
--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
select val, f1(val) from t1 as tab|
select * from t1|
--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
update t1 set val= f1(val)|
select * from t1|
# But this should be OK
select f1(17)|
select * from t1|
# Cleanup
delete from t1 where val= 17|
drop function f1|


#
# BUG#1965
#
create procedure bug1965()
begin
  declare c cursor for select val from t1 order by valname;
  open c;
  close c;
end|

--error 1054
call bug1965()|
drop procedure bug1965|

#
# BUG#1966
#
--error 1327
select 1 into a|

#
# BUG#1653
#
--disable_warnings ONCE
drop table if exists t3|
create table t3 (column_1_0 int)|

create procedure bug1653()
  update t3 set column_1 = 0|

--error 1054
call bug1653()|
drop table t3|
create table t3 (column_1 int)|
call bug1653()|

drop procedure bug1653|
drop table t3|

#
# BUG#2259
#
# Note: When this bug existed, it did not necessarily cause a crash
#       in all builds, but valgrind did give warnings.
create procedure bug2259()
begin
  declare v1 int;
  declare c1 cursor for select s1 from t1;

  fetch c1 into v1;
end|

--error 1326
call bug2259()|
drop procedure bug2259|

#
# BUG#2272
#
create procedure bug2272()
begin
  declare v int;

  update t1 set v = 42;
end|

insert into t1 values (666, 51.3)|
--error 1054
call bug2272()|
truncate table t1|
drop procedure bug2272|

#
# BUG#2329
#
create procedure bug2329_1()
begin
  declare v int;

  insert into t1 (v) values (5);
end|

create procedure bug2329_2()
begin
  declare v int;

  replace t1 set v = 5;
end|

--error 1054
call bug2329_1()|
--error 1054
call bug2329_2()|
drop procedure bug2329_1|
drop procedure bug2329_2|

#
# BUG#3287
#
create function bug3287() returns int
begin
  declare v int default null;

  case
    when v is not null then return 1;
  end case;
  return 2;
end|
--error 1339
select bug3287()|
drop function bug3287|

create procedure bug3287(x int)
case x
when 0 then
  insert into test.t1 values (x, 0.1);
when 1 then
  insert into test.t1 values (x, 1.1);
end case|
--error 1339
call bug3287(2)|
drop procedure bug3287|

#
# BUG#3297
#
--disable_warnings ONCE
drop table if exists t3|
create table t3 (s1 int, primary key (s1))|
insert into t3 values (5),(6)|

create procedure bug3279(out y int) 
begin
  declare x int default 0;
  begin
    declare exit handler for sqlexception set x = x+1;
    insert into t3 values (5);
  end;
  if x < 2 then
    set x = x+1;
    insert into t3 values (6);
  end if;
  set y = x;
end|

set @x = 0|
--error ER_DUP_ENTRY
call bug3279(@x)|
select @x|
drop procedure bug3279|
drop table t3|

#
# BUG#3339
#
--error 1049
create procedure nodb.bug3339() begin end|

#
# BUG#2653
#
create procedure bug2653_1(a int, out b int)
  set b = aa|

create procedure bug2653_2(a int, out b int)
begin
  if aa < 0 then
    set b = - a;
  else
    set b = a;
  end if;
end|

--error 1054
call bug2653_1(1, @b)|
--error 1054
call bug2653_2(2, @b)|

drop procedure bug2653_1|
drop procedure bug2653_2|

#
# BUG#4344
#
--error 1357
create procedure bug4344() drop procedure bug4344|
--error 1357
create procedure bug4344() drop function bug4344|

#
# BUG#3294: Stored procedure crash if table dropped before use
# (Actually, when an error occurs within an error handler.)
--disable_warnings ONCE
drop procedure if exists bug3294|
create procedure bug3294()
begin
  declare continue handler for sqlexception drop table t5;
  drop table t5;
  drop table t5;
end|

create table t5 (x int)|
--error 1051
call bug3294()|
drop procedure bug3294|

#
# BUG#876: Stored Procedures: Invalid SQLSTATE is allowed in 
#          a DECLARE ? HANDLER FOR stmt.
#
--disable_warnings
drop procedure if exists bug8776_1|
drop procedure if exists bug8776_2|
drop procedure if exists bug8776_3|
drop procedure if exists bug8776_4|
--enable_warnings
--error ER_SP_BAD_SQLSTATE
create procedure bug8776_1()
begin
  declare continue handler for sqlstate '42S0200test' begin end;
  begin end;
end|

--error ER_SP_BAD_SQLSTATE
create procedure bug8776_2()
begin
  declare continue handler for sqlstate '4200' begin end;
  begin end;
end|

--error ER_SP_BAD_SQLSTATE
create procedure bug8776_3()
begin
  declare continue handler for sqlstate '420000' begin end;
  begin end;
end|

--error ER_SP_BAD_SQLSTATE
create procedure bug8776_4()
begin
  declare continue handler for sqlstate '42x00' begin end;
  begin end;
end|


#
# BUG#6600: Stored procedure crash after repeated calls with check table
#
--error ER_SP_BADSTATEMENT
create procedure bug6600()
  check table t1|

# Check these two as well, while we're at it. (Although it isn't really
# related to the bug report, but to the fix.)
--error ER_SP_BADSTATEMENT
create procedure bug6600()
  lock table t1 read|
--error ER_SP_BADSTATEMENT
create procedure bug6600()
  unlock table t1|

#
# BUG#9566: explicit LOCK TABLE and store procedures result in illegal state
#
# We should not think that mysql.proc table does not exist if we are unable
# to open it under LOCK TABLE or in prelocked mode.
#
--disable_warnings ONCE
drop procedure if exists bug9566|
create procedure bug9566()
begin
  select * from t1;
end|
lock table t1 read|
# This should fail since we forgot to lock mysql.proc for writing
# explicitly, and we can't open mysql.proc for _writing_ if there
# are locked tables.
--error ER_LOCK_OR_ACTIVE_TRANSACTION
alter procedure bug9566 comment 'Some comment'|
unlock tables|
# This should succeed
drop procedure bug9566|


#
# BUG#7299: Stored procedures: exception handler catches not-found conditions
#
--disable_warnings ONCE
drop procedure if exists bug7299|
create procedure bug7299()
begin
  declare v int;
  declare c cursor for select val from t1;
  declare exit handler for sqlexception select 'Error!'; 

  open c;
  fetch c into v;
end|

truncate table t1|
--error ER_SP_FETCH_NO_DATA
call bug7299()|
drop procedure bug7299|


#
# BUG#9073: Able to declare two handlers for same condition in same scope
#
--error ER_SP_DUP_HANDLER
create procedure bug9073()
begin
  declare continue handler for sqlexception select 1;
  declare continue handler for sqlexception select 2;
end|
--error ER_SP_DUP_HANDLER
create procedure bug9073()
begin
  declare condname1 condition for 1234;
  declare continue handler for condname1 select 1;
  declare exit handler for condname1 select 2;
end|
--error ER_SP_DUP_HANDLER
create procedure bug9073()
begin
  declare condname1 condition for sqlstate '42000';
  declare condname2 condition for sqlstate '42000';
  declare exit handler for condname1 select 1;
  declare continue handler for condname2 select 2;
end|
--error ER_SP_DUP_HANDLER
create procedure bug9073()
begin
  declare condname1 condition for sqlstate '42000';
  declare exit handler for condname1 select 1;
  declare exit handler for sqlstate '42000' select 2;
end|

# This should still work.
--disable_warnings ONCE
drop procedure if exists bug9073|
create procedure bug9073()
begin
  declare condname1 condition for sqlstate '42000';
  declare continue handler for condname1 select 1;
  begin
    declare exit handler for sqlstate '42000' select 2;
    begin
      declare continue handler for sqlstate '42000' select 3;
    end;
  end;
end|
drop procedure bug9073|


#
# BUG#7047: Stored procedure crash if alter procedure
#
--error ER_SP_NO_DROP_SP
create procedure bug7047()
  alter procedure bug7047|
--error ER_SP_NO_DROP_SP
create function bug7047() returns int
begin
  alter function bug7047;
  return 0;
end|


#
# BUG#8408: Stored procedure crash if function contains SHOW
# BUG#9058: Stored Procedures: Crash if function included SELECT
#

# Some things are caught when parsing
--error ER_SP_NO_RETSET
create function bug8408() returns int
begin
  select * from t1;
  return 0;
end|
--error ER_SP_NO_RETSET
create function bug8408() returns int
begin
  show warnings;
  return 0;
end|
--error ER_SP_NO_RETSET
create function bug8408(a int) returns int
begin
  declare b int;
  select b;
  return b;
end|

--disable_warnings
drop function if exists bug8408_f|
drop procedure if exists bug8408_p|
--enable_warnings

# Some things must be caught at invokation time
create function bug8408_f() returns int
begin
  call bug8408_p();
  return 0;
end|
create procedure bug8408_p()
  select * from t1|

call bug8408_p()|
--error ER_SP_NO_RETSET
select bug8408_f()|

drop procedure bug8408_p|
drop function bug8408_f|

# But this is ok
create function bug8408() returns int
begin
  declare n int default 0;
  select count(*) into n from t1;
  return n;
end|

insert into t1 value (2, 2.7), (3, 3.14), (7, 7.0)|
select *,bug8408() from t1|

drop function bug8408|
truncate table t1|


#
# BUG#10537: Server crashes while loading data file into table through
#            procedure.
# Disable load until it's PS and SP safe
--disable_warnings ONCE
drop procedure if exists bug10537|
--error ER_SP_BADSTATEMENT
create procedure bug10537()
  load data local infile '/tmp/somefile' into table t1|


#
# BUG#8409: Stored procedure crash if function contains FLUSH
#
--disable_warnings ONCE
drop function if exists bug8409|
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create function bug8409()
  returns int
begin
  flush tables;
  return 5;
end|
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create function bug8409() returns int begin reset query cache;
return 1; end|
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create function bug8409() returns int begin reset master;
return 1; end|
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create function bug8409() returns int begin reset slave;
return 1; end|
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create function bug8409() returns int begin flush hosts;
return 1; end|
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create function bug8409() returns int begin flush privileges;
return 1; end|
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create function bug8409() returns int begin flush tables with read lock;
return 1; end|
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create function bug8409() returns int begin flush tables;
return 1; end|
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create function bug8409() returns int begin flush logs;
return 1; end|
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create function bug8409() returns int begin flush status;
return 1; end|
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create function bug8409() returns int begin flush des_key_file;
return 1; end|
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
create function bug8409() returns int begin flush user_resources;
return 1; end|


#
# BUG#9529: Stored Procedures: No Warning on truncation of procedure name
#           during creation.
# BUG#17015: Routine name truncation not an error
#            When we started using utf8 for mysql.proc, this limit appeared
#            to be higher, but in reality the names were truncated.
--error ER_TOO_LONG_IDENT
create procedure bug9529_901234567890123456789012345678901234567890123456789012345()
begin
end|

--disable_warnings ONCE
drop procedure if exists bug17015_0123456789012345678901234567890123456789012345678901234|
# Check the upper limit, just to make sure.
create procedure bug17015_0123456789012345678901234567890123456789012345678901234()
begin
end|

--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show procedure status like 'bug17015%'|
drop procedure bug17015_0123456789012345678901234567890123456789012345678901234|


#
# BUG#10969: Stored procedures: crash if default() function
#
--disable_warnings ONCE
drop procedure if exists bug10969|
--error ER_WRONG_COLUMN_NAME
create procedure bug10969()
begin
  declare s1 int default 0;
  select default(s1) from t30;
end|

# This should work
create procedure bug10969()
begin
  declare s1 int default 0;
  select default(t30.s1) from t30;
end|

drop procedure bug10969|


drop table t1|

delimiter ;|

# BUG#9814: Closing a cursor that is not open 
create table t1(f1 int);
create table t2(f1 int);
 
delimiter |;
CREATE PROCEDURE SP001()
P1: BEGIN
  DECLARE ENDTABLE INT DEFAULT 0;
  DECLARE TEMP_NUM INT;
  DECLARE TEMP_SUM INT;
  DECLARE C1 CURSOR FOR SELECT F1 FROM t1;
  DECLARE C2 CURSOR FOR SELECT F1 FROM t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET ENDTABLE = 1;

  SET ENDTABLE=0;
  SET TEMP_SUM=0;
  SET TEMP_NUM=0;
  
  OPEN C1;

  FETCH C1 INTO TEMP_NUM;
  WHILE ENDTABLE = 0 DO
          SET TEMP_SUM=TEMP_NUM+TEMP_SUM;
          FETCH C1 INTO TEMP_NUM;
  END WHILE;
  SELECT TEMP_SUM;
  CLOSE C1;
  CLOSE C1;
  SELECT 'end of proc';
END P1|
delimiter ;|
--error 1326
call SP001();
drop procedure SP001;
drop table t1, t2;

# Bug #11394 "Recursion in SP crash server" and bug #11600 "Stored
# procedures: crash with function calling itself".
# We have to disable recursion since in many cases LEX and many
# Item's can't be used in reentrant way nowdays.
delimiter |;
--disable_warnings
drop function if exists bug11394|
drop function if exists bug11394_1|
drop function if exists bug11394_2|
drop procedure if exists bug11394|
--enable_warnings
create function bug11394(i int) returns int
begin
  if i <= 0 then
    return 0;
  else
    return (i in (100, 200, bug11394(i-1), 400));
  end if;
end|
# If we allow recursive functions without additional modifications
# this will crash server since Item for "IN" is not reenterable.
--error 1424
select bug11394(2)|
drop function bug11394|
create function bug11394_1(i int) returns int
begin
  if i <= 0 then
    return 0;
  else
    return (select bug11394_1(i-1));
  end if;
end|
# The following statement will crash because some LEX members responsible
# for selects cannot be used in reentrant fashion.
--error 1424
select bug11394_1(2)|
drop function bug11394_1|
# Note that the following should be allowed since it does not contains
# recursion
create function bug11394_2(i int) returns int return i|
select bug11394_2(bug11394_2(10))|
drop function bug11394_2|
create procedure bug11394(i int, j int)
begin
  if i > 0 then
    call bug11394(i - 1,(select 1));
  end if;
end|
--error ER_SP_RECURSION_LIMIT
call bug11394(2, 1)|
set @@max_sp_recursion_depth=10|
call bug11394(2, 1)|
set @@max_sp_recursion_depth=default|
drop procedure bug11394|
delimiter ;|


#
# BUG 12490 (Packets out of order if calling HELP CONTENTS from Stored Procedure)
#
--error ER_SP_BADSTATEMENT
CREATE PROCEDURE BUG_12490() HELP CONTENTS;
--error ER_SP_BADSTATEMENT
CREATE FUNCTION BUG_12490() RETURNS INT HELP CONTENTS;
CREATE TABLE t_bug_12490(a int);
--error ER_SP_BADSTATEMENT
CREATE TRIGGER BUG_12490 BEFORE UPDATE ON t_bug_12490 FOR EACH ROW HELP CONTENTS;
DROP TABLE t_bug_12490;

#
# Bug#11834 "Re-execution of prepared statement with dropped function
# crashes server". Also tests handling of prepared stmts which use
# stored functions but does not require prelocking.
#
--disable_warnings
drop function if exists bug11834_1;
drop function if exists bug11834_2;
--enable_warnings
create function bug11834_1() returns int return 10;
create function bug11834_2() returns int return bug11834_1();
prepare stmt from "select bug11834_2()";
execute stmt;
# Re-execution of statement should not crash server.
execute stmt;
drop function bug11834_1;
# Attempt to execute statement should return proper error and 
# should not crash server.

# NOTE! The error we get from the below query indicates that the sp bug11834_2
# does not exist(this is wrong but can be accepted)
# This behaviour has been reported as bug#21294
--error ER_SP_DOES_NOT_EXIST
execute stmt;
deallocate prepare stmt;
drop function bug11834_2;

#
# Bug#12953 "Stored procedures: crash if OPTIMIZE TABLE in function"
#
delimiter |;
--disable_warnings ONCE
DROP FUNCTION IF EXISTS bug12953|
--error ER_SP_NO_RETSET
CREATE FUNCTION bug12953() RETURNS INT
BEGIN
  OPTIMIZE TABLE t1;
  RETURN 1;
END|
delimiter ;|

#
# Bug##12995 "Inside function "Table 't4' was not locked with LOCK TABLES"
#
delimiter |;
--disable_warnings ONCE
DROP FUNCTION IF EXISTS bug12995|
--error ER_SP_BADSTATEMENT
CREATE FUNCTION bug12995() RETURNS INT
BEGIN
  HANDLER t1 OPEN;
  RETURN 1;
END|
--error ER_SP_BADSTATEMENT
CREATE FUNCTION bug12995() RETURNS INT
BEGIN
  HANDLER t1 READ FIRST;
  RETURN 1;
END|
--error ER_SP_BADSTATEMENT
CREATE FUNCTION bug12995() RETURNS INT
BEGIN
  HANDLER t1 CLOSE;
  RETURN 1;
END|
--error 1305
SELECT bug12995()|
delimiter ;|


#
# BUG#12712: SET AUTOCOMMIT should fail within SP/functions/triggers
#
--disable_warnings
drop procedure if exists bug12712;
drop function if exists bug12712;
--enable_warnings
# Can...
create procedure bug12712()
  set session autocommit = 0;

select @@autocommit;
set @au = @@autocommit;
call bug12712();
select @@autocommit;
set session autocommit = @au;

delimiter |;
create function bug12712()
  returns int
begin
  call bug12712();
  return 0;
end|

# Can't...
--error ER_SP_CANT_SET_AUTOCOMMIT
set @x = bug12712()|
drop procedure bug12712|
drop function bug12712|
--error ER_SP_CANT_SET_AUTOCOMMIT
create function bug12712()
    returns int
begin
  set session autocommit = 0;
  return 0;
end|
--error ER_SP_CANT_SET_AUTOCOMMIT
create function bug12712()
    returns int
begin
  set @@autocommit = 0;
  return 0;
end|
--error ER_SP_CANT_SET_AUTOCOMMIT
create function bug12712()
    returns int
begin
  set local autocommit = 0;
  return 0;
end|
delimiter ;|
--error ER_SP_CANT_SET_AUTOCOMMIT
create trigger bug12712
  before insert on t1 for each row set session autocommit = 0;

#
# BUG#9367: Stored procedures: client hang after "show warnings"
#
--disable_parsing
--disable_warnings ONCE
drop procedure if exists bug9367;
create table t1 (s1 int);
select s1 from t1;
delimiter |;
create procedure bug9367()
begin
  declare v int;
  declare c cursor for select s1 from t1;
  open c;
  show warnings;
  fetch c into v;
  select v;
end|
delimiter ;|
call bug9367();
drop procedure bug9367;
drop table t1;
--enable_parsing

#
# BUG#13510: Setting password local variable changes current password
#
delimiter |;
--disable_warnings
drop procedure if exists bug13510_1|
drop procedure if exists bug13510_2|
drop procedure if exists bug13510_3|
drop procedure if exists bug13510_4|
--enable_warnings

--error ER_SP_BAD_VAR_SHADOW
create procedure bug13510_1()
begin
  declare password varchar(10);

  set password = 'foo1';
  select password;
end|

# Check that an error message is sent
--error ER_PARSE_ERROR
set names='foo2'|

--error ER_SP_BAD_VAR_SHADOW
create procedure bug13510_2()
begin
  declare names varchar(10);

  set names = 'foo2';
  select names;
end|

create procedure bug13510_3()
begin
  declare password varchar(10);

  set `password` = 'foo3';
  select password;
end|

create procedure bug13510_4()
begin
  declare names varchar(10);

  set `names` = 'foo4';
  select names;
end|

call bug13510_3()|
call bug13510_4()|

drop procedure bug13510_3|
drop procedure bug13510_4|


#
# Test that statements which implicitly commit transaction are prohibited
# in stored function and triggers. Attempt to create function or trigger
# containing such statement should produce error (includes test for
# bug #13627).
#
--disable_warnings ONCE
drop function if exists bug_13627_f|

CREATE TABLE t1 (a int)|
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DROP TRIGGER test1; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN DROP TRIGGER test1; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create table t2 (a int); END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN create table t2 (a int); return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create index t1_i on t1 (a); END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN create index t1_i on t1 (a); return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter table t1 add column  b int; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN alter table t1 add column  b int; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename table t1 to t2; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN rename table t1 to t2; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN truncate table t1; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN truncate table t1; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop table t1; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN drop table t1; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop index t1_i on t1; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN drop index t1_i on t1; return 1; END |

-- error ER_SP_BADSTATEMENT
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN unlock tables; END |
-- error ER_SP_BADSTATEMENT
CREATE FUNCTION bug_13627_f() returns int BEGIN unlock tables; return 1; END |

-- error ER_SP_BADSTATEMENT
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN LOCK TABLE t1 READ; END |
-- error ER_SP_BADSTATEMENT
CREATE FUNCTION bug_13627_f() returns int BEGIN LOCK TABLE t1 READ; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create database mysqltest; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN create database mysqltest; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop database mysqltest; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN drop database mysqltest; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create user 'mysqltest_1'; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN create user 'mysqltest_1'; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN grant select on t1 to 'mysqltest_1'; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug21975() returns int BEGIN grant select on t1 to 'mysqltest_1'; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke select on t1 from 'mysqltest_1'; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug21975() returns int BEGIN revoke select on t1 from 'mysqltest_1'; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke all privileges on *.* from 'mysqltest_1'; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug21975() returns int BEGIN revoke all privileges on *.* from 'mysqltest_1'; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop user 'mysqltest_1'; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN drop user 'mysqltest_1'; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as select 1; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END |

-- error ER_SP_BADSTATEMENT
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END |
-- error ER_SP_BADSTATEMENT
CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END |

-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop view v1; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE FUNCTION bug_13627_f() returns int BEGIN drop view v1; return 1; END |

-- error ER_SP_NO_RECURSIVE_CREATE
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create trigger tr2 before insert on t1 for each row do select 1; END |
-- error ER_SP_NO_RECURSIVE_CREATE
CREATE FUNCTION bug_13627_f() returns int BEGIN create trigger tr2 before insert on t1 for each row do select 1; return 1; END |

-- error ER_SP_NO_DROP_SP
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop function bug_13627_f; END |
-- error ER_SP_NO_DROP_SP
CREATE FUNCTION bug_13627_f() returns int BEGIN drop function bug_13627_f; return 1; END |

-- error ER_SP_NO_RECURSIVE_CREATE
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create function f2 () returns int return 1; END |
-- error ER_SP_NO_RECURSIVE_CREATE
CREATE FUNCTION bug_13627_f() returns int BEGIN create function f2 () returns int return 1; return 1; END |

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW
  BEGIN
    CREATE TEMPORARY TABLE t2 (a int);
    DROP TEMPORARY TABLE t2;
  END |
CREATE FUNCTION bug_13627_f() returns int
  BEGIN
    CREATE TEMPORARY TABLE t2 (a int);
    DROP TEMPORARY TABLE t2;
    return 1;
  END |

drop table t1|
drop function bug_13627_f|

delimiter ;|

# BUG#12329: "Bogus error msg when executing PS with stored procedure after
# SP was re-created". See also test for related bug#13399 in trigger.test
drop function if exists bug12329;
--enable_warnings
create table t1 as select 1 a;
create table t2 as select 1 a;
create function bug12329() returns int return (select a from t1);
prepare stmt1 from 'select bug12329()';
execute stmt1;
drop function bug12329;
create function bug12329() returns int return (select a+100 from t2);
select bug12329();
execute stmt1;
deallocate prepare stmt1;
drop function bug12329; 
drop table t1, t2;

#
# Bug#13514 "server crash when create a stored procedure before choose a
# database" and
# Bug#13587 "Server crash when SP is created without database
# selected"
#
create database mysqltest1;
use mysqltest1;
drop database mysqltest1;
--error ER_NO_DB_ERROR 
create function f1() returns int return 1;
delimiter |;
--error ER_NO_DB_ERROR 
create procedure p1(out param1 int)
begin
  select count(*) into param1 from t3;
end|
delimiter ;|
use test;


#
# BUG#13037: undefined variable in IF cause erroneous error-message
#

--disable_warnings
DROP PROCEDURE IF EXISTS bug13037_p1;
DROP PROCEDURE IF EXISTS bug13037_p2;
DROP PROCEDURE IF EXISTS bug13037_p3;
--enable_warnings

delimiter |;

CREATE PROCEDURE bug13037_p1()
BEGIN
  IF bug13037_foo THEN
    SELECT 1;
  END IF;
END|

CREATE PROCEDURE bug13037_p2()
BEGIN
  SET @bug13037_foo = bug13037_bar;
END|

CREATE PROCEDURE bug13037_p3()
BEGIN
  SELECT bug13037_foo;
END|

delimiter ;|

--echo

--error 1054
CALL bug13037_p1();
--error 1054
CALL bug13037_p2();
--error 1054
CALL bug13037_p3();

--error 1054
CALL bug13037_p1();
--error 1054
CALL bug13037_p2();
--error 1054
CALL bug13037_p3();

DROP PROCEDURE bug13037_p1;
DROP PROCEDURE bug13037_p2;
DROP PROCEDURE bug13037_p3;

#
# Bug#14569 "editing a stored procedure kills mysqld-nt"
#
create database mysqltest1;
create database mysqltest2;
use mysqltest1;
drop database mysqltest1;
create procedure mysqltest2.p1() select version();
--error ER_NO_DB_ERROR 
create procedure p2() select version();
use mysqltest2;
--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show procedure status;
drop database mysqltest2;
use test;

#
# Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server"
#
delimiter |;
--disable_warnings ONCE
DROP FUNCTION IF EXISTS bug13012|
--error ER_SP_NO_RETSET
CREATE FUNCTION bug13012() RETURNS INT
BEGIN
  REPAIR TABLE t1;
  RETURN 1;
END|
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 ONCE
drop procedure if exists ` bug15658`;

--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 ONCE
drop procedure if exists bug15091;

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 ONCE
drop function if exists bug16896;

--error ER_PARSE_ERROR
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 ONCE
DROP PROCEDURE IF EXISTS bug14702;

--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 ONCE
DROP TABLE IF EXISTS t1;

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();
CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1;
DROP PROCEDURE bug20953;
--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;

#
# End of 5.0 tests
#
--echo End of 5.0 tests

#
# BUG#20701: BINARY keyword should be forbidden in stored routines
#
--disable_warnings ONCE
drop function if exists bug20701;
#
# This was disabled in 5.1.12. See bug #20701
# When collation support in SP is implemented, then this test should
# be removed.
#
--error ER_NOT_SUPPORTED_YET
create function bug20701() returns varchar(25) binary return "test";
create function bug20701() returns varchar(25) return "test";
drop function bug20701;

#
# 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#28360 (RENAME DATABASE destroys routines)
#

--disable_warnings
drop procedure if exists proc_28360;
drop function if exists func_28360;
--enable_warnings

delimiter //;

--error ER_SP_NO_DROP_SP
CREATE PROCEDURE proc_28360()
BEGIN
  ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME;
END//

--error ER_SP_NO_DROP_SP
CREATE FUNCTION func_28360() RETURNS int
BEGIN
  ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME;
  RETURN 0;
END//

delimiter ;//


#
# Bug#29223 declare cursor c for SHOW .....
#

--disable_warnings ONCE
DROP PROCEDURE IF EXISTS p1;
--delimiter |
--error ER_PARSE_ERROR
CREATE PROCEDURE p1()
BEGIN
  DECLARE c char(100);
  DECLARE cur1 CURSOR FOR SHOW TABLES;

  OPEN cur1;
  FETCH cur1 INTO c;
  select c;
  CLOSE cur1;
END|
--delimiter ;

#
# Bug#29816 Syntactically wrong query fails with misleading error message
#

--disable_warnings ONCE
DROP DATABASE IF EXISTS mysqltest;
CREATE DATABASE mysqltest;
USE mysqltest;
DROP DATABASE mysqltest;
# Both ER_SP_DOES_NOT_EXIST and ER_PARSE_ERROR are valid here, the result is
# implementation dependent: See Bug#29816 for details.  
# ER_SP_DOES_NOT_EXIST has been replaced by ER_NO_DB_ERROR since it is valid in
# this scenario. See Bug# 13864485 for details.
--error ER_PARSE_ERROR
SELECT inexistent(), 1 + ,;
--error ER_NO_DB_ERROR
SELECT inexistent();
--error ER_PARSE_ERROR
SELECT .inexistent();
--error ER_PARSE_ERROR
SELECT ..inexistent();
USE test;

#
# Bug#30904 SET PASSWORD statement is non-transactional
#

delimiter |;

--error ER_SP_CANT_SET_AUTOCOMMIT
create function f1() returns int
begin
 set password = 'foo';
  return 1;
end|

--error ER_SP_CANT_SET_AUTOCOMMIT
create trigger t1
  before insert on t2 for each row set password = 'foo';|

delimiter ;|

#
# Bug#30882 Dropping a temporary table inside a stored function may cause a server crash
#

--disable_warnings
drop function if exists f1;
drop function if exists f2;
drop table if exists t1, t2;
--enable_warnings

delimiter |;
create function f1() returns int
begin
 drop temporary table t1;
 return 1;
end|
delimiter ;|
--error ER_CANT_REOPEN_TABLE
create temporary table t1 as select f1();

delimiter |;
create function f2() returns int
begin
 create temporary table t2 as select f1();
 return 1;
end|
delimiter ;|
--error ER_CANT_REOPEN_TABLE
create temporary table t1 as select f2();

drop function f1;
drop function f2;

delimiter |;
create function f1() returns int
begin
 drop temporary table t2,t1;
 return 1;
end|
create function f2() returns int
begin
 create temporary table t2 as select f1();
 return 1;
end|
delimiter ;|
--error ER_CANT_REOPEN_TABLE
create temporary table t1 as select f2();

drop function f1;
drop function f2;

create temporary table t2(a int);
select * from t2;
delimiter |;
create function f2() returns int
begin
 drop temporary table t2;
 return 1;
end|
delimiter ;|
select f2();

drop function f2;
--error ER_BAD_TABLE_ERROR
drop table t2;

--echo End of 5.1 tests

#
# Bug#33983 (Stored Procedures: wrong end <label> syntax is accepted)
#

--disable_warnings
drop procedure if exists proc_33983_a;
drop procedure if exists proc_33983_b;
drop procedure if exists proc_33983_c;
drop procedure if exists proc_33983_d;
--enable_warnings

delimiter |;

--error ER_SP_LABEL_MISMATCH
create procedure proc_33983_a()
begin
  label1:
    begin
      label2:
      begin
        select 1;
      end label1;
    end;
end|

--error ER_SP_LABEL_MISMATCH
create procedure proc_33983_b()
begin
  label1:
    repeat
      label2:
      repeat
        select 1;
      until FALSE end repeat label1;
    until FALSE end repeat;
end|

--error ER_SP_LABEL_MISMATCH
create procedure proc_33983_c()
begin
  label1:
    while TRUE do
      label2:
      while TRUE do
        select 1;
      end while label1;
    end while;
end|

--error ER_SP_LABEL_MISMATCH
create procedure proc_33983_d()
begin
  label1:
    loop
      label2:
      loop
        select 1;
      end loop label1;
    end loop;
end|

CREATE TABLE t1 (a INT)|
INSERT INTO t1 VALUES (1),(2)|
CREATE PROCEDURE p1(a INT) BEGIN END|
--error ER_SUBQUERY_NO_1_ROW
CALL p1((SELECT * FROM t1))|
DROP PROCEDURE IF EXISTS p1|
DROP TABLE t1|

delimiter ;|

#
# Bug#21801: SQL exception handlers and warnings
#

--disable_warnings ONCE
drop procedure if exists p1;
delimiter |;
create procedure p1()
begin
  create table t1 (a int);
  drop table t1;
end|
delimiter ;|
call p1();
call p1();
drop procedure p1;

#
# Bug#8759 (Stored Procedures: SQLSTATE '00000' should be illegal)
#

--disable_warnings ONCE
drop procedure if exists proc_8759;

delimiter $$;

--error ER_SP_BAD_SQLSTATE
create procedure proc_8759()
begin
  declare should_be_illegal condition for sqlstate '00000';
  declare continue handler for should_be_illegal set @x=0;
end$$

--error ER_SP_BAD_SQLSTATE
create procedure proc_8759()
begin
  declare continue handler for sqlstate '00000' set @x=0;
end$$

delimiter ;$$

#
# Bug#36510 (Stored Procedures: mysql_error_code 0 should be illegal)
#

--disable_warnings ONCE
drop procedure if exists proc_36510;

delimiter $$;

--error ER_SP_BAD_SQLSTATE
create procedure proc_36510()
begin
  declare should_be_illegal condition for sqlstate '00123';
  declare continue handler for should_be_illegal set @x=0;
end$$

--error ER_SP_BAD_SQLSTATE
create procedure proc_36510()
begin
  declare continue handler for sqlstate '00123' set @x=0;
end$$

--error ER_WRONG_VALUE
create procedure proc_36510()
begin
  declare should_be_illegal condition for 0;
  declare continue handler for should_be_illegal set @x=0;
end$$

--error ER_WRONG_VALUE
create procedure proc_36510()
begin
  declare continue handler for 0 set @x=0;
end$$
delimiter ;$$

#
# Bug#15192: "fatal errors" are caught by handlers in stored procedures
#

--disable_warnings ONCE
drop procedure if exists p1;
set @old_recursion_depth = @@max_sp_recursion_depth;
set @@max_sp_recursion_depth = 255;
delimiter |;
create procedure p1(a int)
begin
  declare continue handler for 1436 -- ER_STACK_OVERRUN_NEED_MORE
    select 'exception';
  call p1(a+1);
end|
delimiter ;|
--error 0,ER_STACK_OVERRUN_NEED_MORE,ER_SP_RECURSION_LIMIT
call p1(1);
set @@max_sp_recursion_depth = @old_recursion_depth;
drop procedure p1;

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

#
# CLEANUP and RESTORE
#
LOAD DATA INFILE '../../tmp/proc.txt' INTO TABLE mysql.proc;
remove_file $MYSQLTEST_VARDIR/tmp/proc.txt;

#
# Bug #38159: Function parsing problem generates misleading error message
#

CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,1), (2,2);
--error ER_FUNC_INEXISTENT_NAME_COLLISION
SELECT MAX (a) FROM t1 WHERE b = 999999;
SELECT AVG (a) FROM t1 WHERE b = 999999;
--error ER_SP_DOES_NOT_EXIST
SELECT non_existent (a) FROM t1 WHERE b = 999999;
DROP TABLE t1;


#
# Bug #46374 crash, INSERT INTO t1 uses function, function modifies t1
#
CREATE TABLE t1 ( f2 INTEGER, f3 INTEGER );
INSERT INTO t1 VALUES  ( 1, 1 );

delimiter |;

CREATE FUNCTION func_1 () RETURNS INTEGER
BEGIN
  INSERT INTO t1 SELECT * FROM t1 ;
  RETURN 1 ;
END|

delimiter ;|

# The bug caused the following INSERT statement to trigger
# an assertion.   Error 1442 is the correct response
#
--error 1442
INSERT INTO t1 SELECT * FROM (SELECT 2 AS f1, 2 AS f2) AS A WHERE func_1() = 5;

# Cleanup
DROP FUNCTION func_1;
DROP TABLE t1;



--echo #
--echo # Bug #47788: Crash in TABLE_LIST::hide_view_error on UPDATE + VIEW + 
--echo #   SP + MERGE + ALTER
--echo #

CREATE TABLE t1 (pk INT, b INT, KEY (b));
CREATE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT * FROM t1;

CREATE PROCEDURE p1 (a int) UPDATE IGNORE v1 SET b = a; 

--error ER_NON_UPDATABLE_TABLE
CALL p1(5);

ALTER TABLE t1 CHANGE COLUMN b b2 INT;

--error ER_VIEW_INVALID
CALL p1(7);

DROP PROCEDURE p1;
DROP VIEW v1;
DROP TABLE t1;

--echo #
--echo # Bug#12428824 - PARSER STACK OVERFLOW AND CRASH IN SP_ADD_USED_ROUTINE
--echo #                WITH OBSCURE QUERY
--echo #

--error ER_TOO_LONG_IDENT
SELECT very_long_fn_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999();

--error ER_TOO_LONG_IDENT
CALL very_long_pr_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999();

--error ER_WRONG_DB_NAME
SELECT very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_func();

--error ER_WRONG_DB_NAME
CALL very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_proc();

--error ER_TOO_LONG_IDENT
SELECT db_name.very_long_fn_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999();

--error ER_TOO_LONG_IDENT
CALL db_name.very_long_pr_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999();
--echo End of 5.1 tests

--echo #
--echo # Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP
--echo #

--echo
--echo # - Case 1
--echo

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
DROP PROCEDURE IF EXISTS p3;
DROP PROCEDURE IF EXISTS p4;
DROP PROCEDURE IF EXISTS p5;
DROP PROCEDURE IF EXISTS p6;
--enable_warnings
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
delimiter |;

CREATE PROCEDURE p1()
  BEGIN
    SELECT CAST('10 ' as unsigned integer);
    SELECT 1;
    CALL p2();
  END|

CREATE PROCEDURE p2()
  BEGIN
    SELECT CAST('10 ' as unsigned integer);
  END|

delimiter ;|

CALL p1();

DROP PROCEDURE p1;
DROP PROCEDURE p2;

--echo
--echo # - Case 2
--echo

--disable_warnings ONCE
DROP TABLE IF EXISTS t1;

CREATE TABLE t1(a INT);

delimiter |;

CREATE PROCEDURE p1()
  BEGIN
    DECLARE c INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLWARNING
    BEGIN
      SET c = c + 1;
      SELECT 'Warning caught!' AS Msg;
    END;
    CALL p2(); # 1 warning
    CALL p3(); # 1 warning
    CALL p4(); # No warnings
    CALL p5(); # 1 warning
    SELECT c;
    SELECT @@warning_count;
    SHOW WARNINGS;
  END|

CREATE PROCEDURE p2()
  BEGIN
    SELECT CAST('2 ' as unsigned integer);
  END| # 1 warning is passed to the caller

CREATE PROCEDURE p3()
  BEGIN
    SELECT CAST('3 ' as unsigned integer);
    GET DIAGNOSTICS @n = NUMBER; # does not clear the warning
  END| # 1 warning is passed to the caller

CREATE PROCEDURE p4()
  BEGIN
    SELECT CAST('4 ' as unsigned integer);
    INSERT INTO t1 VALUES(1); # Clears the warning
  END| # No warnings to pass

CREATE PROCEDURE p5()
  BEGIN
    SELECT CAST('5 ' as unsigned integer);
    CALL p2();
  END|

CREATE PROCEDURE p6()
  BEGIN
    SELECT CAST('6 ' as unsigned integer);
    SHOW WARNINGS;
  END|

CREATE PROCEDURE p7()
  BEGIN
    DECLARE c INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLWARNING
    BEGIN
      SET c = c + 1;
      SELECT 'Warning caught!' AS Msg;
    END;
    CALL p6();
    SELECT c;
  END|

delimiter ;|

--echo
CALL p1();

--echo
CALL p7();

--echo

DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
DROP PROCEDURE p4;
DROP PROCEDURE p5;
DROP PROCEDURE p6;
DROP PROCEDURE p7;

DROP TABLE t1;
SET sql_mode = default;
--echo
--echo # - Case 3: check that "Exception trumps No Data".
--echo

--disable_warnings ONCE
DROP TABLE IF EXISTS t1;

CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1), (2), (3);

delimiter |;

CREATE PROCEDURE p1()
BEGIN
  DECLARE c CURSOR FOR SELECT a FROM t1;

  OPEN c;
  
  BEGIN
    DECLARE v1 INT;
    DECLARE v2 INT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
      SELECT "Error caught (expected)";

    DECLARE EXIT HANDLER FOR NOT FOUND
      SELECT "End of Result Set found!";

    WHILE TRUE DO
      FETCH c INTO v1, v2;
    END WHILE;
  END;

  CLOSE c;

  SELECT a INTO @foo FROM t1 LIMIT 1; # Clear warning stack
END|

delimiter ;|

CALL p1();

DROP PROCEDURE p1;
DROP TABLE t1;

--echo #
--echo # Bug#36185: Incorrect precedence for warning and exception handlers
--echo #

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
--enable_warnings

CREATE TABLE t1 (a INT, b INT NOT NULL);

delimiter |;

CREATE PROCEDURE p1()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLWARNING SELECT 'warning';
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'exception';
  INSERT INTO t1 VALUES (CAST('10 ' AS SIGNED), NULL);
END|

delimiter ;|

CALL p1();

DROP TABLE t1;
DROP PROCEDURE p1;

--echo #
--echo # Bug#5889: Exit handler for a warning doesn't hide the warning in trigger
--echo #
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1, 2);

delimiter |;

CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
BEGIN
  DECLARE EXIT HANDLER FOR SQLWARNING
    SET NEW.a = 10;

  SET NEW.a = 99999999999;
END|

delimiter ;|

UPDATE t1 SET b = 20;

SHOW WARNINGS;

SELECT * FROM t1;

DROP TRIGGER t1_bu;
DROP TABLE t1;
SET sql_mode = default;
--echo #
--echo # Bug#9857: Stored procedures: handler for sqlwarning ignored
--echo #

CREATE TABLE t1(a INT PRIMARY KEY);
INSERT INTO t1 VALUES(1);
SET @sql_mode_saved = @@sql_mode;
SET sql_mode = traditional;

delimiter |;

CREATE PROCEDURE p1()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLWARNING
    SELECT 'warning caught (expected)';
  
  INSERT IGNORE INTO t1 VALUES (1);
END|

CREATE PROCEDURE p2()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SELECT 'error caught (unexpected)';
  
  INSERT IGNORE INTO t1 VALUES (1);
END|

delimiter ;|

CALL p1();
SHOW WARNINGS;

CALL p2();
SHOW WARNINGS;

DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;
SET sql_mode = @sql_mode_saved;

--echo #
--echo # Bug#55850: Trigger warnings not cleared.
--echo #
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP PROCEDURE IF EXISTS p1;
--enable_warnings

CREATE TABLE t1(x SMALLINT, y SMALLINT, z SMALLINT);
CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT,
                d SMALLINT, e SMALLINT, f SMALLINT);

CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
  INSERT INTO t2(a, b, c) VALUES(99999, 99999, 99999);

CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
  INSERT INTO t2(d, e, f) VALUES(99999, 99999, 99999);

CREATE PROCEDURE p1()
  INSERT INTO t1 VALUES(99999, 99999, 99999);

# What happened before the patch was:
#  - INSERT INTO t1 added 3 warnings about overflow in 'x', 'y' and 'z' columns;
#  - t1_bi run and added 3 warnings about overflow in 'a', 'b' and 'c' columns;
#  - t1_ai run and added 3 warnings about overflow in 'd', 'e' and 'f' columns;
# => we had 9 warnings.
#
# Now what happens is:
#  - INSERT INTO t1 adds 3 warnings about overflow in 'x', 'y' and 'z' columns;
#  - t1_bi adds 3 warnings about overflow in 'a', 'b' and 'c' columns;
#  - The warnings added by triggers are cleared;
#  - t1_ai run and added 3 warnings about overflow in 'd', 'e' and 'f' columns;
#  - The warnings added by triggers are cleared;
# => we have 3 warnings.

--echo
CALL p1();

--echo
SHOW WARNINGS;

--echo
DROP TABLE t1;
DROP TABLE t2;
DROP PROCEDURE p1;

--echo # ----------------------------------------------------------------------

CREATE TABLE t1(x SMALLINT, y SMALLINT, z SMALLINT);
CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT NOT NULL);

delimiter |;

CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
  INSERT INTO t2 VALUES(
    CAST('111111 ' AS SIGNED),
    CAST('222222 ' AS SIGNED),
    NULL);
END|

delimiter ;|

CREATE PROCEDURE p1()
  INSERT INTO t1 VALUES(99999, 99999, 99999);

--echo
--error ER_BAD_NULL_ERROR
CALL p1();

--echo
SHOW WARNINGS;

--echo
DROP TABLE t1;
DROP TABLE t2;
DROP PROCEDURE p1;
SET sql_mode = default;
--echo
--echo ###################################################################
--echo # Tests for the following bugs:
--echo #   - Bug#11763171: 55852 - Possibly inappropriate handler activation.
--echo #   - Bug#11749343: 38806 - Wrong scope for SQL HANDLERS in SP.
--echo ###################################################################
--echo

#
# Structure of SQL-block:
# BEGIN
#   <Handler declaration block>
#   <Statement block>
# END
#
# Scope of Handler-decl-block is Statement-block.
# I.e. SQL-conditions thrown in the Handler-decl-block can not be handled by
# the same block, only by outer SQL-blocks.
#
# This rule is recursive, i.e. if a Handler-decl-block has nested SQL-blocks,
# the SQL-conditions from those nested blocks can not be handled by the this
# Handler-decl-block, only by outer SQL-blocks.
#

delimiter |;

--echo
--echo # -- Check that SQL-conditions thrown by Statement-blocks are
--echo # -- handled by Handler-decl blocks properly.
--echo

CREATE PROCEDURE p1()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SELECT 'H1' AS HandlerId;

  DECLARE CONTINUE HANDLER FOR SQLWARNING
    SELECT 'H2' AS HandlerId;

  SIGNAL SQLSTATE '01000'; # Should be handled by H2.
END|

--echo
CALL p1()|

--echo
--echo # -- Check that SQL-conditions thrown by Statement-blocks are
--echo # -- handled by Handler-decl blocks properly in case of nested
--echo # -- SQL-blocks.
--echo

CREATE PROCEDURE p2()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SELECT 'H1' AS HandlerId;

  DECLARE CONTINUE HANDLER FOR SQLWARNING
    SELECT 'H2' AS HandlerId;

  BEGIN

    SELECT 'B1' AS BlockId;
    BEGIN

      SELECT 'B2' AS BlockId;
      BEGIN
        SELECT 'B3' AS BlockId;
        SIGNAL SQLSTATE '01000'; # Should be handled by H2.
      END;

    END;

  END;

END|

--echo
CALL p2()|

--echo
--echo # -- Check SQL-handler resolution rules.
--echo

CREATE PROCEDURE p3()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SELECT 'H1' AS HandlerId;

  DECLARE CONTINUE HANDLER FOR SQLWARNING
    SELECT 'H2' AS HandlerId;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
    SELECT 'H3' AS HandlerId;

  SIGNAL SQLSTATE '01000'; # Should be handled by H3.
END|

--echo
CALL p3()|
--echo

CREATE PROCEDURE p4()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SELECT 'H1' AS HandlerId;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
    SELECT 'H2' AS HandlerId;

  DECLARE CONTINUE HANDLER FOR SQLWARNING
    SELECT 'H3' AS HandlerId;

  SIGNAL SQLSTATE '01000'; # Should be handled by H2.
END|

--echo
CALL p4()|
--echo

CREATE PROCEDURE p5()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SELECT 'H1' AS HandlerId;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
    SELECT 'H2' AS HandlerId;

  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLWARNING
      SELECT 'H3' AS HandlerId;

    SIGNAL SQLSTATE '01000'; # Should be handled by H3.
  END;
END|

--echo
CALL p5()|

--echo
--echo # -- Check that handlers don't handle its own exceptions.
--echo

CREATE PROCEDURE p6()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    SELECT 'H1' AS HandlerId;
    SIGNAL SQLSTATE 'HY000'; # Should *not* be handled by H1.
  END;

  SELECT 'S1' AS SignalId;
  SIGNAL SQLSTATE 'HY000'; # Should be handled by H1.
END|

--echo
--error ER_SIGNAL_EXCEPTION
CALL p6()|

--echo
--echo # -- Check that handlers don't handle its own warnings.
--echo

CREATE PROCEDURE p7()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLWARNING
  BEGIN
    SELECT 'H1' AS HandlerId;
    SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1.
  END;

  SELECT 'S1' AS SignalId;
  SIGNAL SQLSTATE '01000'; # Should be handled by H1.
END|

--echo
CALL p7()|

--echo
--echo # -- Check that conditions for handlers are not handled by the handlers
--echo # -- from the same block.
--echo

CREATE PROCEDURE p8()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLWARNING
    SELECT 'H1' AS HandlerId;

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    SELECT 'H2' AS HandlerId;
    SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1.
  END;

  SELECT 'S1' AS SignalId;
  SIGNAL SQLSTATE 'HY000'; # Should be handled by H2.
END|

--echo
CALL p8()|

--echo
--echo # -- Check that conditions for handlers are not handled by the handlers
--echo # -- from the same block even if they are thrown deep down the stack.
--echo

CREATE PROCEDURE p9()
BEGIN

  DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
    SELECT 'Wrong:H1:1' AS HandlerId;

  DECLARE CONTINUE HANDLER FOR SQLWARNING
    SELECT 'Wrong:H1:2' AS HandlerId;

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN

    DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
      SELECT 'Wrong:H2:1' AS HandlerId;

    DECLARE CONTINUE HANDLER FOR SQLWARNING
      SELECT 'Wrong:H2:2' AS HandlerId;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN

      DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
        SELECT 'Wrong:H3:1' AS HandlerId;

      DECLARE CONTINUE HANDLER FOR SQLWARNING
        SELECT 'Wrong:H3:2' AS HandlerId;

      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
      BEGIN

        DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
          SELECT 'Wrong:H4:1' AS HandlerId;

        DECLARE CONTINUE HANDLER FOR SQLWARNING
          SELECT 'Wrong:H4:2' AS HandlerId;

        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN

          DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
            SELECT 'Wrong:H5:1' AS HandlerId;

          DECLARE CONTINUE HANDLER FOR SQLWARNING
            SELECT 'Wrong:H5:2' AS HandlerId;

          DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
          BEGIN

            DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
              SELECT 'Wrong:H6:1' AS HandlerId;

            DECLARE CONTINUE HANDLER FOR SQLWARNING
              SELECT 'Wrong:H6:2' AS HandlerId;

            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
            BEGIN
              SELECT 'H2' AS HandlerId;
              SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1.
            END;

            SELECT 'S6' AS SignalId;
            SIGNAL SQLSTATE 'HY000';
          END;

          SELECT 'S5' AS SignalId;
          SIGNAL SQLSTATE 'HY000';

        END;

        SELECT 'S4' AS SignalId;
        SIGNAL SQLSTATE 'HY000';

      END;

      SELECT 'S3' AS SignalId;
      SIGNAL SQLSTATE 'HY000';

    END;

    SELECT 'S2' AS SignalId;
    SIGNAL SQLSTATE 'HY000';

  END;

  SELECT 'S1' AS SignalId;
  SIGNAL SQLSTATE 'HY000'; # Should be handled by H2.

END|

--echo
CALL p9()|

--echo
--echo # -- Check that handlers are choosen properly in case of deep stack and
--echo # -- nested SQL-blocks.
--echo

CREATE PROCEDURE p10()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
    SELECT 'H1' AS HandlerId;

  DECLARE CONTINUE HANDLER FOR SQLWARNING
    SELECT 'H2' AS HandlerId;

  BEGIN
    BEGIN
      BEGIN

        DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
          SELECT 'Wrong:H1:1' AS HandlerId;

        DECLARE CONTINUE HANDLER FOR SQLWARNING
          SELECT 'Wrong:H1:2' AS HandlerId;

        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN

          DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
            SELECT 'Wrong:H2:1' AS HandlerId;

          DECLARE CONTINUE HANDLER FOR SQLWARNING
            SELECT 'Wrong:H2:2' AS HandlerId;

          DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
          BEGIN

            DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
              SELECT 'Wrong:H3:1' AS HandlerId;

            DECLARE CONTINUE HANDLER FOR SQLWARNING
              SELECT 'Wrong:H3:2' AS HandlerId;

            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
            BEGIN

              DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
                SELECT 'Wrong:H4:1' AS HandlerId;

              DECLARE CONTINUE HANDLER FOR SQLWARNING
                SELECT 'Wrong:H4:2' AS HandlerId;

              DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
              BEGIN

                DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
                  SELECT 'Wrong:H5:1' AS HandlerId;

                DECLARE CONTINUE HANDLER FOR SQLWARNING
                  SELECT 'Wrong:H5:2' AS HandlerId;

                DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
                BEGIN

                  DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'
                    SELECT 'Wrong:H6:1' AS HandlerId;

                  DECLARE CONTINUE HANDLER FOR SQLWARNING
                    SELECT 'Wrong:H6:2' AS HandlerId;

                  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
                  BEGIN
                    SELECT 'H2' AS HandlerId;
                    SIGNAL SQLSTATE '01000'; # Should be handled by H1.
                  END;

                  SELECT 'S6' AS SignalId;
                  SIGNAL SQLSTATE 'HY000';
                END;

                SELECT 'S5' AS SignalId;
                SIGNAL SQLSTATE 'HY000';

              END;

              SELECT 'S4' AS SignalId;
              SIGNAL SQLSTATE 'HY000';

            END;

            SELECT 'S3' AS SignalId;
            SIGNAL SQLSTATE 'HY000';

          END;

          SELECT 'S2' AS SignalId;
          SIGNAL SQLSTATE 'HY000';

        END;

        SELECT 'S1' AS SignalId;
        SIGNAL SQLSTATE 'HY000'; # Should be handled by H2.

      END;
    END;
  END;
END|

--echo
CALL p10()|

--echo
--echo # -- Test stored procedure from Peter's mail.
--echo

CREATE PROCEDURE p11()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SELECT 'H1' AS HandlerId;

  DECLARE CONTINUE HANDLER FOR SQLWARNING
    SELECT 'H2' AS HandlerId;

  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '01000', 1249
    BEGIN
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        SELECT 'H3' AS HandlerId;

      DECLARE CONTINUE HANDLER FOR SQLWARNING
        SELECT 'H4' AS HandlerId;

      BEGIN
        SELECT 'H5' AS HandlerId;

        SELECT 'S3' AS SignalId;
        SIGNAL SQLSTATE 'HY000'; # H3

        SELECT 'S4' AS SignalId;
        SIGNAL SQLSTATE '22003'; # H3

        SELECT 'S5' AS SignalId;
        SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H4
      END;
    END;

    SELECT 'S6' AS SignalId;
    SIGNAL SQLSTATE 'HY000'; # H1

    SELECT 'S7' AS SignalId;
    SIGNAL SQLSTATE '22003'; # H1

    SELECT 'S8' AS SignalId;
    SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H5
  END;

  SELECT 'S1' AS SignalId;
  SIGNAL SQLSTATE 'HY000'; # H1

  SELECT 'S2' AS SignalId;
  SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H2
END|

--echo
CALL p11()|

--echo
--echo # -- Check that runtime stack-trace can be deeper than parsing-time one.
--echo

CREATE PROCEDURE p12()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '01001'
  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '01001'
    BEGIN
      DECLARE CONTINUE HANDLER FOR SQLSTATE '01001'
      BEGIN
        DECLARE CONTINUE HANDLER FOR SQLSTATE '01001'
        BEGIN
          DECLARE CONTINUE HANDLER FOR SQLSTATE '01001'
          BEGIN
            SELECT 'H1:5' AS HandlerId;
            SIGNAL SQLSTATE '01002';
          END;
          SELECT 'H1:4' AS HandlerId;
          SIGNAL SQLSTATE '01001';
        END;
        SELECT 'H1:3' AS HandlerId;
        SIGNAL SQLSTATE '01001';
      END;
      SELECT 'H1:2' AS HandlerId;
      SIGNAL SQLSTATE '01001';
    END;
    SELECT 'H1:1' AS HandlerId;
    SIGNAL SQLSTATE '01001';
  END;

  #########################################################

  DECLARE CONTINUE HANDLER FOR SQLSTATE '01002'
    SELECT 'OK' AS Msg;

  #########################################################

  BEGIN

    DECLARE CONTINUE HANDLER FOR SQLWARNING
    BEGIN
      DECLARE CONTINUE HANDLER FOR SQLWARNING
      BEGIN
        DECLARE CONTINUE HANDLER FOR SQLWARNING
        BEGIN
          DECLARE CONTINUE HANDLER FOR SQLWARNING
          BEGIN
            DECLARE CONTINUE HANDLER FOR SQLWARNING
            BEGIN
              SELECT 'H2:5' AS HandlerId;
              SIGNAL SQLSTATE '01001';
            END;
            SELECT 'H2:4' AS HandlerId;
            SIGNAL SQLSTATE '01000';
          END;
          SELECT 'H2:3' AS HandlerId;
          SIGNAL SQLSTATE '01000';
        END;
        SELECT 'H2:2' AS HandlerId;
        SIGNAL SQLSTATE '01000';
      END;
      SELECT 'H2:1' AS HandlerId;
      SIGNAL SQLSTATE '01000';
    END;

    #######################################################

    SELECT 'Throw 01000' AS Msg;
    SIGNAL SQLSTATE '01000';
  END;

END|

--echo
CALL p12()|

--echo
--echo # -- Check that handler-call-frames are removed properly for EXIT
--echo # -- handlers.
--echo

CREATE PROCEDURE p13()
BEGIN
  
  DECLARE CONTINUE HANDLER FOR SQLWARNING
  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLWARNING
    BEGIN
      DECLARE EXIT HANDLER FOR SQLWARNING
      BEGIN
        SELECT 'EXIT handler 3' AS Msg;
      END;

      SELECT 'CONTINUE handler 2: 1' AS Msg;
      SIGNAL SQLSTATE '01000';
      SELECT 'CONTINUE handler 2: 2' AS Msg;
    END;

    SELECT 'CONTINUE handler 1: 1' AS Msg;
    SIGNAL SQLSTATE '01000';
    SELECT 'CONTINUE handler 1: 2' AS Msg;
  END;

  SELECT 'Throw 01000' AS Msg;
  SIGNAL SQLSTATE '01000';
END|

--echo
CALL p13()|

delimiter ;|

--echo
--echo # That's it. Cleanup.
--echo

DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
DROP PROCEDURE p4;
DROP PROCEDURE p5;
DROP PROCEDURE p6;
DROP PROCEDURE p7;
DROP PROCEDURE p8;
DROP PROCEDURE p9;
DROP PROCEDURE p10;
DROP PROCEDURE p11;
DROP PROCEDURE p12;
DROP PROCEDURE p13;

--echo
--echo # Bug#12731619: NESTED SP HANDLERS CAN TRIGGER ASSERTION
--echo
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
--disable_warnings
DROP FUNCTION IF EXISTS f1;
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1(msg VARCHAR(255));

delimiter |;
CREATE FUNCTION f1() RETURNS INT
BEGIN

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION               # handler 1
  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION             # handler 2
    BEGIN
      INSERT INTO t1 VALUE('WRONG: Inside H2');
      RETURN 2;
    END;

    INSERT INTO t1 VALUE('CORRECT: Inside H1');
    RETURN 1;
  END;

  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLWARNING               # handler 3
    BEGIN
      INSERT INTO t1 VALUE('WRONG: Inside H3');
      RETURN 3;
    END;

    INSERT INTO t1 VALUE('CORRECT: Calling f1()');
    RETURN f1(); # -- exception here
  END;

  INSERT INTO t1 VALUE('WRONG: Returning 10');
  RETURN 10;

END|

delimiter ;|

--echo
SELECT f1();
--echo
SELECT * FROM t1;
--echo

DROP FUNCTION f1;
DROP TABLE t1;


--echo
--echo # Check that handled SQL-conditions are properly cleared from DA.
--echo

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
DROP PROCEDURE IF EXISTS p3;
DROP PROCEDURE IF EXISTS p4;
DROP PROCEDURE IF EXISTS p5;
--enable_warnings

CREATE TABLE t1(a CHAR, b CHAR, c CHAR);
CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT);

delimiter |;

--echo
--echo # Check that SQL-conditions for which SQL-handler has been invoked,
--echo # are cleared from the Diagnostics Area. Note, there might be several
--echo # SQL-conditions, but SQL-handler must be invoked only once.
--echo

CREATE PROCEDURE p1()
BEGIN
  DECLARE EXIT HANDLER FOR SQLWARNING
    SELECT 'Warning caught' AS msg;

  # The INSERT below raises 3 SQL-conditions (warnings). The EXIT HANDLER
  # above must be invoked once (for one condition), but all three conditions
  # must be cleared from the Diagnostics Area.

  INSERT INTO t1 VALUES('qqqq', 'ww', 'eee');

  # The following INSERT will not be executed, because of the EXIT HANDLER.

  INSERT INTO t1 VALUES('zzz', 'xx', 'yyyy');
END|

--echo
CALL p1()|
--echo
SELECT * FROM t1|

--echo
--echo # Check that SQL-conditions for which SQL-handler has *not* been
--echo # invoked, are *still* cleared from the Diagnostics Area.
--echo

CREATE PROCEDURE p2()
BEGIN
  DECLARE CONTINUE HANDLER FOR 1292
    SELECT 'Warning 1292 caught' AS msg;

  # The following INSERT raises 6 SQL-warnings with code 1292,
  # and 3 SQL-warnings with code 1264. The CONTINUE HANDLER above must be
  # invoked once, and all nine SQL-warnings must be cleared from
  # the Diagnostics Area.

  INSERT INTO t2
  SELECT
    CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER),
    CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER),
    CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER);
END|

--echo
CALL p2()|

--echo
--echo # Check that if there are two equally ranked SQL-handlers to handle
--echo # SQL-conditions from SQL-statement, only one of them will be invoked.
--echo

CREATE PROCEDURE p3()
BEGIN
  DECLARE CONTINUE HANDLER FOR 1292
    SELECT 'Warning 1292 caught' AS msg;

  DECLARE CONTINUE HANDLER FOR 1264
    SELECT 'Warning 1264 caught' AS msg;

  # The following INSERT raises 6 SQL-warnings with code 1292,
  # and 3 SQL-warnings with code 1264. Only one of the CONTINUE HANDLERs above
  # must be called, and only once. The SQL Standard does not define, which one
  # should be invoked.

  INSERT INTO t2
  SELECT
    CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER),
    CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER),
    CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER);
END|

--echo
CALL p3()|

--echo
--echo # The same as p3, but 1264 comes first.
--echo

CREATE PROCEDURE p4()
BEGIN
  DECLARE CONTINUE HANDLER FOR 1292
    SELECT 'Warning 1292 caught' AS msg;

  DECLARE CONTINUE HANDLER FOR 1264
    SELECT 'Warning 1264 caught' AS msg;

  # The following INSERT raises 4 SQL-warnings with code 1292,
  # and 3 SQL-warnings with code 1264. Only one of the CONTINUE HANDLERs above
  # must be called, and only once. The SQL Standard does not define, which one
  # should be invoked.

  INSERT INTO t2
  SELECT
    CAST(999999 AS SIGNED INTEGER),
    CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER),
    CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER);
END|

--echo
CALL p4()|

--echo
--echo # Check that if a SQL-handler raised its own SQL-conditions, there are
--echo # preserved after handler exit.
--echo

CREATE PROCEDURE p5()
BEGIN
  DECLARE EXIT HANDLER FOR 1292
  BEGIN
    SELECT 'Handler for 1292' AS Msg;
    SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1234;
    SHOW WARNINGS;
  END;

  INSERT INTO t2
  SELECT
    CAST(999999 AS SIGNED INTEGER),
    CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER),
    CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER);
END|

--echo
CALL p5()|

--echo
--echo # Check that SQL-conditions are available inside the handler, but
--echo # cleared after the handler exits.
--echo

CREATE PROCEDURE p6()
BEGIN
  DECLARE CONTINUE HANDLER FOR 1292
  BEGIN
    SHOW WARNINGS;
    SELECT 'Handler for 1292' Msg;
  END;

  INSERT INTO t2
  SELECT
    CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER),
    CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER),
    CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999  ') AS SIGNED INTEGER);
END|

--echo
CALL p6()|

delimiter ;|

--echo
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
DROP PROCEDURE p4;
DROP PROCEDURE p5;
DROP PROCEDURE p6;
DROP TABLE t1;
DROP TABLE t2;
SET sql_mode = default;
--echo
--echo # Bug#13059316: ASSERTION FAILURE IN SP_RCONTEXT.CC 
--echo # Check DECLARE statements that raise conditions before handlers
--echo # are declared.
--echo
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
--enable_warnings

delimiter |;

CREATE PROCEDURE p1()
BEGIN
  DECLARE var1 INTEGER DEFAULT 'string';
  DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'H1';
END|

--echo
CALL p1()|
--echo

CREATE PROCEDURE p2()
BEGIN
  DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'H2';
  CALL p1();
END|

--echo
CALL p2()|

delimiter ;|

--echo
DROP PROCEDURE p1;
DROP PROCEDURE p2;
SET sql_mode = default;

--echo #
--echo # Bug#13113222 RQG_SIGNAL_RESIGNAL FAILED WITH ASSERTION.
--echo #

--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
--enable_warnings

delimiter |;
CREATE PROCEDURE p1()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'triggered p1';
  # This will trigger an error.
  SIGNAL SQLSTATE 'HY000';
END|

CREATE PROCEDURE p2()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLWARNING SELECT 'triggered p2';
  # This will trigger a warning.
  SIGNAL SQLSTATE '01000';
END|
delimiter ;|

SET @old_max_error_count=  @@session.max_error_count;
SET SESSION max_error_count= 0;
CALL p1();
CALL p2();
SET SESSION max_error_count= @old_max_error_count;

DROP PROCEDURE p1;
DROP PROCEDURE p2;

--echo
--echo # Bug#12652873: 61392: Continue handler for NOT FOUND being triggered
--echo # from internal stored function.
--echo

--disable_warnings
DROP FUNCTION IF EXISTS f1;
DROP FUNCTION IF EXISTS f2;
DROP TABLE IF EXISTS t1;
--enable_warnings

--echo

CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1, 2);

delimiter |;

--echo
--echo # f1() raises NOT_FOUND condition.
--echo # Raising NOT_FOUND can not be simulated by SIGNAL,
--echo # because SIGNAL would raise SQL-error in that case.
--echo

CREATE FUNCTION f1() RETURNS INTEGER
BEGIN
  DECLARE v VARCHAR(5) DEFAULT -1;
  SELECT b FROM t1 WHERE a = 2 INTO v;
  RETURN v;
END|

--echo
--echo # Here we check that the NOT_FOUND condition raised in f1()
--echo # is not visible in the outer function (f2), i.e. the continue
--echo # handler in f2() will not be called.
--echo

CREATE FUNCTION f2() RETURNS INTEGER
BEGIN
  DECLARE v INTEGER;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET @msg = 'Handler activated.';

  SELECT f1() INTO v;

  RETURN v;
END|

delimiter ;|

SET @msg = '';

--echo
SELECT f2();
--echo
SELECT @msg;
--echo

DROP FUNCTION f1;
DROP FUNCTION f2;
DROP TABLE t1;


--echo #
--echo # Bug #17903490 "ASSERT FAILURE IN MDL.H WHEN DROP FUNCTION CALLED
--echo #                WITH INVALID FUNCTION NAME".
--echo #
--error ER_TOO_LONG_IDENT
drop function f111111111122222222223333333333444444444455555555556666666666777777777788888888889999999999aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjjkk;
--error ER_TOO_LONG_IDENT
drop function test.f111111111122222222223333333333444444444455555555556666666666777777777788888888889999999999aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjjkk;
--error ER_WRONG_DB_NAME 
drop function mysqltest111111111122222222223333333333444444444455555555556666666666777777777788888888889999999999aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjj.test;


--echo #
--echo # Bug #24357244: CANNOT CREATE A STORED FUNCTION IF A DATABASE IS NOT
--echo #                SELECTED
--echo #

CREATE DATABASE mysqltest;
USE mysqltest;
DROP DATABASE mysqltest;

# Should return NULL
SELECT DATABASE();

CREATE DATABASE mysqltest;
CREATE FUNCTION mysqltest.f1() RETURNS INT RETURN 0;
CREATE FUNCTION mysqltest.f2() RETURNS INT RETURN f1();

USE mysqltest;
SELECT f1();
SELECT f2();

DROP FUNCTION IF EXISTS f2;
DROP FUNCTION IF EXISTS f1;
DROP DATABASE mysqltest;