diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/information_schema_db.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/sp-error.result | 143 | ||||
| -rw-r--r-- | mysql-test/r/sp.result | 42 | ||||
| -rw-r--r-- | mysql-test/r/trigger.result | 37 | ||||
| -rw-r--r-- | mysql-test/r/view.result | 6 | ||||
| -rw-r--r-- | mysql-test/t/sp-error.test | 193 | ||||
| -rw-r--r-- | mysql-test/t/sp.test | 53 | ||||
| -rw-r--r-- | mysql-test/t/trigger.test | 74 |
8 files changed, 530 insertions, 24 deletions
diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index db14b7b6600..2a6a3e6e0fb 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -98,13 +98,13 @@ where table_schema='test'; table_name table_type table_comment t1 BASE TABLE v1 VIEW VIEW -v2 VIEW View 'test.v2' references invalid table(s) or column(s) or function(s) or define +v2 VIEW VIEW drop table t1; select table_name, table_type, table_comment from information_schema.tables where table_schema='test'; table_name table_type table_comment -v1 VIEW View 'test.v1' references invalid table(s) or column(s) or function(s) or define -v2 VIEW View 'test.v2' references invalid table(s) or column(s) or function(s) or define +v1 VIEW VIEW +v2 VIEW VIEW drop function f1; drop function f2; drop view v1, v2; diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 0ed92aa9e7a..8c933927250 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1128,9 +1128,9 @@ drop view if exists v1, v2, v3, v4; create function bug11555_1() returns int return (select max(i) from t1); create function bug11555_2() returns int return bug11555_1(); create view v1 as select bug11555_1(); -ERROR 42S02: Table 'test.t1' doesn't exist +drop view v1; create view v2 as select bug11555_2(); -ERROR 42S02: Table 'test.t1' doesn't exist +drop view v2; create table t1 (i int); create view v1 as select bug11555_1(); create view v2 as select bug11555_2(); @@ -1143,8 +1143,7 @@ ERROR HY000: View 'test.v2' references invalid table(s) or column(s) or function select * from v3; ERROR HY000: View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them create view v4 as select * from v1; -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -drop view v1, v2, v3; +drop view v1, v2, v3, v4; drop function bug11555_1; drop function bug11555_2; create table t1 (i int); @@ -1153,12 +1152,12 @@ create trigger t1_ai after insert on t1 for each row insert into t2 values (new. create view v1 as select * from t1; drop table t2; insert into v1 values (1); -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +ERROR HY000: Table 't2' was not locked with LOCK TABLES 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(); insert into v1 values (2); -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +ERROR HY000: Table 't2' was not locked with LOCK TABLES drop function bug11555_1; drop table t1; drop view v1; @@ -1269,6 +1268,138 @@ call bug24491(); ERROR 42S22: Unknown column 'y.value' in 'field list' drop procedure bug24491; drop tables t1; +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; +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; +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 | +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW +CALL bug18914_p1(); +INSERT INTO t1 VALUES (1); +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +SELECT bug18914_f1(); +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +SELECT bug18914_f2(); +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +SELECT * FROM t2; +ERROR 42S02: Table 'test.t2' doesn't exist +DROP FUNCTION bug18914_f1; +DROP FUNCTION bug18914_f2; +DROP PROCEDURE bug18914_p1; +DROP PROCEDURE bug18914_p2; +DROP TABLE t1; +drop table if exists bogus_table_20713; +drop function if exists func_20713_a; +drop function if exists func_20713_b; +create table bogus_table_20713( id int(10) not null primary key); +insert into bogus_table_20713 values (1), (2), (3); +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// +set @in_func := 0; +select func_20713_a(); +func_20713_a() +NULL +select @in_func; +@in_func +2 +set @in_func := 0; +select func_20713_b(); +func_20713_b() +NULL +select @in_func; +@in_func +2 +drop table bogus_table_20713; +set @in_func := 0; +select func_20713_a(); +func_20713_a() +NULL +select @in_func; +@in_func +2 +set @in_func := 0; +select func_20713_b(); +func_20713_b() +NULL +select @in_func; +@in_func +2 +drop function if exists func_20713_a; +drop function if exists func_20713_b; +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; +create table table_25345_a (a int); +create table table_25345_b (b int); +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 || +call proc_25345(); +result +1 +select func_25345(); +ERROR 0A000: Not allowed to return a result set from a function +select func_25345_b(); +func_25345_b() +1 +drop table table_25345_a; +call proc_25345(); +result +1 +select func_25345(); +ERROR 0A000: Not allowed to return a result set from a function +select func_25345_b(); +func_25345_b() +1 +drop table table_25345_b; +drop procedure proc_25345; +drop function func_25345; +drop function func_25345_b; End of 5.0 tests drop function if exists bug16164; create function bug16164() returns int diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 51ab8d5e139..9b96781b58a 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -1155,9 +1155,13 @@ create function f12_2() returns int return (select count(*) from t3)| drop temporary table t3| select f12_1()| -ERROR 42S02: Table 'test.t3' doesn't exist +f12_1() +3 +Warnings: +Note 1051 Unknown table 't3' select f12_1() from t1 limit 1| -ERROR 42S02: Table 'test.t3' doesn't exist +f12_1() +3 drop function f0| drop function f1| drop function f2| @@ -5832,4 +5836,38 @@ END| CALL bug24117()| DROP PROCEDURE bug24117| DROP TABLE t3| +drop function if exists func_8407_a| +drop function if exists func_8407_b| +create function func_8407_a() returns int +begin +declare x int; +declare continue handler for sqlexception +begin +end; +select 1 from no_such_view limit 1 into x; +return x; +end| +create function func_8407_b() returns int +begin +declare x int default 0; +declare continue handler for sqlstate '42S02' + begin +set x:= x+1000; +end; +case (select 1 from no_such_view limit 1) +when 1 then set x:= x+1; +when 2 then set x:= x+2; +else set x:= x+100; +end case; +set x:=x + 500; +return x; +end| +select func_8407_a()| +func_8407_a() +NULL +select func_8407_b()| +func_8407_b() +1500 +drop function func_8407_a| +drop function func_8407_b| drop table t1,t2; diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 9e5e9c8244c..e5d1b5a3f1f 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1335,4 +1335,41 @@ SELECT fubar_id FROM t2; fubar_id 1 DROP TABLE t1,t2; +DROP TABLE IF EXISTS bug21825_A; +DROP TABLE IF EXISTS bug21825_B; +CREATE TABLE bug21825_A (id int(10)); +CREATE TABLE bug21825_B (id int(10)); +CREATE TRIGGER trgA AFTER INSERT ON bug21825_A +FOR EACH ROW +BEGIN +INSERT INTO bug21825_B (id) values (1); +END// +INSERT INTO bug21825_A (id) VALUES (10); +INSERT INTO bug21825_A (id) VALUES (20); +DROP TABLE bug21825_B; +DELETE FROM bug21825_A WHERE id = 20; +DROP TABLE bug21825_A; +DROP TABLE IF EXISTS bug22580_t1; +DROP PROCEDURE IF EXISTS bug22580_proc_1; +DROP PROCEDURE IF EXISTS bug22580_proc_2; +CREATE TABLE bug22580_t1 (a INT, b INT); +CREATE PROCEDURE bug22580_proc_2() +BEGIN +DROP TABLE IF EXISTS bug22580_tmp; +CREATE TEMPORARY TABLE bug22580_tmp (a INT); +DROP TABLE bug22580_tmp; +END|| +CREATE PROCEDURE bug22580_proc_1() +BEGIN +CALL bug22580_proc_2(); +END|| +CREATE TRIGGER t1bu BEFORE UPDATE ON bug22580_t1 +FOR EACH ROW +BEGIN +CALL bug22580_proc_1(); +END|| +INSERT INTO bug22580_t1 VALUES (1,1); +DROP TABLE bug22580_t1; +DROP PROCEDURE bug22580_proc_1; +DROP PROCEDURE bug22580_proc_2; End of 5.0 tests diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 5e81df99d18..30043e066db 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1935,11 +1935,11 @@ create function f1 () returns int return (select max(col1) from t1); DROP TABLE t1; CHECK TABLE v1, v2, v3, v4, v5, v6; Table Op Msg_type Msg_text -test.v1 check error View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v1 check status OK test.v2 check status OK -test.v3 check error View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v3 check status OK test.v4 check status OK -test.v5 check error View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +test.v5 check status OK test.v6 check status OK drop function f1; drop function f2; diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index e67d6370153..6dc94869f04 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1611,10 +1611,12 @@ 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 +# 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(); ---error ER_NO_SUCH_TABLE +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(); @@ -1629,9 +1631,8 @@ select * from v2; 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; +drop view v1, v2, v3, v4; # We also should hide details about broken triggers which are # invoked for view. drop function bug11555_1; @@ -1641,12 +1642,14 @@ 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 +# Limitation, the desired error is ER_VIEW_INVALID +--error ER_TABLE_NOT_LOCKED 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 +# Limitation, the desired error is ER_VIEW_INVALID +--error ER_TABLE_NOT_LOCKED insert into v1 values (2); drop function bug11555_1; drop table t1; @@ -1843,6 +1846,184 @@ 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 diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 38753ed3ac0..6c7fde71e78 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -1369,7 +1369,7 @@ end| select f11()| --error ER_CANT_REOPEN_TABLE select f11() from t1| -# We don't handle temporary tables used by nested functions well +# Test that using a single table instance at a time works create function f12_1() returns int begin drop temporary table if exists t3; @@ -1379,11 +1379,9 @@ begin end| create function f12_2() returns int return (select count(*) from t3)| -# We need clean start to get error + drop temporary table t3| ---error ER_NO_SUCH_TABLE select f12_1()| ---error ER_NO_SUCH_TABLE select f12_1() from t1 limit 1| # Cleanup @@ -6803,6 +6801,53 @@ DROP PROCEDURE bug24117| DROP TABLE t3| # +# Bug#8407(Stored functions/triggers ignore exception handler) +# + +--disable_warnings +drop function if exists func_8407_a| +drop function if exists func_8407_b| +--enable_warnings + +create function func_8407_a() returns int +begin + declare x int; + + declare continue handler for sqlexception + begin + end; + + select 1 from no_such_view limit 1 into x; + + return x; +end| + +create function func_8407_b() returns int +begin + declare x int default 0; + + declare continue handler for sqlstate '42S02' + begin + set x:= x+1000; + end; + + case (select 1 from no_such_view limit 1) + when 1 then set x:= x+1; + when 2 then set x:= x+2; + else set x:= x+100; + end case; + set x:=x + 500; + + return x; +end| + +select func_8407_a()| +select func_8407_b()| + +drop function func_8407_a| +drop function func_8407_b| + +# # NOTE: The delimiter is `|`, and not `;`. It is changed to `;` # at the end of the file! # diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index b6bf8fcb40e..14608a3b193 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -1625,4 +1625,78 @@ SELECT fubar_id FROM t2; DROP TABLE t1,t2; +# +# Bug#21285 (Incorrect message error deleting records in a table with a +# trigger for inserting) +# + +--disable_warnings +DROP TABLE IF EXISTS bug21825_A; +DROP TABLE IF EXISTS bug21825_B; +--enable_warnings + +CREATE TABLE bug21825_A (id int(10)); +CREATE TABLE bug21825_B (id int(10)); + +delimiter //; + +CREATE TRIGGER trgA AFTER INSERT ON bug21825_A +FOR EACH ROW +BEGIN + INSERT INTO bug21825_B (id) values (1); +END// +delimiter ;// + +INSERT INTO bug21825_A (id) VALUES (10); +INSERT INTO bug21825_A (id) VALUES (20); + +DROP TABLE bug21825_B; + +# Must pass, the missing table in the insert trigger should not matter. +DELETE FROM bug21825_A WHERE id = 20; + +DROP TABLE bug21825_A; + +# +# Bug#22580 (DROP TABLE in nested stored procedure causes strange dependancy +# error) +# + +--disable_warnings +DROP TABLE IF EXISTS bug22580_t1; +DROP PROCEDURE IF EXISTS bug22580_proc_1; +DROP PROCEDURE IF EXISTS bug22580_proc_2; +--enable_warnings + +CREATE TABLE bug22580_t1 (a INT, b INT); + +DELIMITER ||; + +CREATE PROCEDURE bug22580_proc_2() +BEGIN + DROP TABLE IF EXISTS bug22580_tmp; + CREATE TEMPORARY TABLE bug22580_tmp (a INT); + DROP TABLE bug22580_tmp; +END|| + +CREATE PROCEDURE bug22580_proc_1() +BEGIN + CALL bug22580_proc_2(); +END|| + +CREATE TRIGGER t1bu BEFORE UPDATE ON bug22580_t1 +FOR EACH ROW +BEGIN + CALL bug22580_proc_1(); +END|| + +DELIMITER ;|| + +# Must pass, the actions of the update trigger should not matter +INSERT INTO bug22580_t1 VALUES (1,1); + +DROP TABLE bug22580_t1; +DROP PROCEDURE bug22580_proc_1; +DROP PROCEDURE bug22580_proc_2; + --echo End of 5.0 tests |