sp-error.test

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

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
drop procedure if exists bug9367;
--enable_warnings
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
drop function if exists bug_13627_f|
--enable_warnings

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_SP_BADSTATEMENT
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN load table t1 from master; END |
-- error ER_SP_BADSTATEMENT
CREATE FUNCTION bug_13627_f() returns int BEGIN load table t1 from master; 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 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_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END |
-- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
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();
# Until we implement proper mechanism for invalidation of PS/SP when table
# or SP's are changed the following statement will fail with 'Table ... was
# not locked' error (this mechanism should be based on the new TDC).
--error 1100 
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
DROP FUNCTION IF EXISTS bug13012|
--enable_warnings
--error ER_SP_NO_RETSET
CREATE FUNCTION bug13012() RETURNS INT
BEGIN
  REPAIR TABLE t1;
  RETURN 1;
END|
--error ER_SP_NO_RETSET
CREATE FUNCTION bug13012() RETURNS INT
BEGIN
  BACKUP TABLE t1 TO '/tmp';
  RETURN 1;
END|
--error ER_SP_NO_RETSET
CREATE FUNCTION bug13012() RETURNS INT
BEGIN
  RESTORE TABLE t1 FROM '/tmp';
  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.
--error ER_NO_SUCH_TABLE
create view v1 as select bug11555_1();
--error ER_NO_SUCH_TABLE
create view v2 as select bug11555_2();
# 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.
--error ER_VIEW_INVALID
create view v4 as select * from v1;
drop view v1, v2, v3;
# 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;
--error ER_VIEW_INVALID
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();
--error ER_VIEW_INVALID
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#NNNN: New bug synopsis
#
#--disable_warnings
#drop procedure if exists bugNNNN|
#--enable_warnings
#create procedure bugNNNN...