diff options
Diffstat (limited to 'mysql-test/main/sp_validation.result')
| -rw-r--r-- | mysql-test/main/sp_validation.result | 1996 |
1 files changed, 1996 insertions, 0 deletions
diff --git a/mysql-test/main/sp_validation.result b/mysql-test/main/sp_validation.result new file mode 100644 index 00000000..b95e84aa --- /dev/null +++ b/mysql-test/main/sp_validation.result @@ -0,0 +1,1996 @@ + +# +# WL#4179: Stored programs: validation of stored program statements. +# + +SET @orig_debug=@@debug_dbug; +# The test case below demonstrates that meta-data changes are detected +# by triggers. +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT, b INT); +CREATE TABLE t3 (a INT); +INSERT INTO t2 VALUES (11, 12), (21, 22); +CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW +INSERT INTO t1 SELECT * FROM t2; +INSERT INTO t3 (a) VALUES (1); +SELECT * FROM t1; +a b +11 12 +21 22 +SELECT * FROM t2; +a b +11 12 +21 22 +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +ALTER TABLE t1 ADD COLUMN c INT; +ALTER TABLE t2 ADD COLUMN c INT; +INSERT INTO t2 VALUES (31, 32, 33); +INSERT INTO t3 (a) VALUES (2); +SELECT * FROM t1; +a b c +11 12 NULL +21 22 NULL +11 12 NULL +21 22 NULL +31 32 33 +SELECT * FROM t2; +a b c +11 12 NULL +21 22 NULL +31 32 33 +SET @@debug_dbug=@orig_dbug; +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; + +# Check that NEW/OLD rows work within triggers. + +CREATE TABLE t1 (a INT); +INSERT INTO t1(a) VALUES (1); +CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW +BEGIN +SET @a = OLD.a; +SET @b = NEW.a; +SELECT OLD.a INTO @c; +SELECT NEW.a INTO @d; +SET NEW.a = NEW.a * 2; +END| +UPDATE t1 SET a = a * 10; +SELECT @a, @c, @b, @d; +@a @c @b @d +1 1 10 10 +SELECT a FROM t1; +a +20 +DROP TABLE t1; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE PROCEDURE p1() +SELECT * FROM t1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +a +1 +2 + +# 1.1 Check if added column into table is recognized correctly +# in a stored procedure. +ALTER TABLE t1 ADD COLUMN b INT DEFAULT 0; +CALL p1(); +a b +1 0 +2 0 + +# 1.2 Check if dropped column is not appeared in SELECT query +# executed inside a stored procedure. +ALTER TABLE t1 DROP COLUMN a; +CALL p1(); +b +0 +0 + +# 1.3 Check if changed column is picked up properly. +ALTER TABLE t1 CHANGE COLUMN b b VARCHAR(255) DEFAULT 'n/a'; +DELETE FROM t1; +INSERT INTO t1 VALUES (b), ('hello'); +CALL p1(); +b +n/a +hello +SET @@debug_dbug=@orig_dbug; + +# 1.4 Check if table's recreation is handled correctly +# inside a call of stored procedure. +DROP TABLE t1; +DROP PROCEDURE p1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE PROCEDURE p1() +SELECT * FROM t1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +a +1 +2 +DROP TABLE t1; +CALL p1(); +ERROR 42S02: Table 'test.t1' doesn't exist +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CALL p1(); +a +1 +2 +SET @@debug_dbug=@orig_dbug; + +# 1.5 Recreate table t1 with another set of columns and +# re-call a stored procedure. +DROP TABLE t1; +DROP PROCEDURE p1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE PROCEDURE p1() +SELECT * FROM t1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +a +1 +2 +DROP TABLE t1; +CALL p1(); +ERROR 42S02: Table 'test.t1' doesn't exist +CREATE TABLE t1 (b VARCHAR(10), c VARCHAR(10)); +INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd'); +CALL p1(); +b c +a b +c d +SET @@debug_dbug=@orig_dbug; +DROP TABLE t1; +DROP PROCEDURE p1; + +# 2.1 Stored program that uses query like 'SELECT * FROM v' must be +# re-executed successfully if some columns were added into the view +# definition by ALTER VIEW; +CREATE VIEW v1 AS SELECT 1, 2, 3; +CREATE PROCEDURE p1() +SELECT * FROM v1; +CALL p1(); +1 2 3 +1 2 3 +ALTER VIEW v1 AS SELECT 1, 2, 3, 4, 5; +CALL p1(); +1 2 3 4 5 +1 2 3 4 5 + +# 2.2 Stored program that uses query like 'SELECT * FROM v' must be +# re-executed successfully if some columns were removed from the view +# definition by ALTER VIEW; +ALTER VIEW v1 AS SELECT 1, 5; +CALL p1(); +1 5 +1 5 + +# 2.3 Stored program that uses query like 'SELECT * FROM v' must be +# re-executed successfully if a base table for the view being used was +# extended by new columns (by ALTER TABLE); +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +DROP VIEW v1; +CREATE VIEW v1 AS SELECT * FROM t1; +DROP PROCEDURE p1; +CREATE PROCEDURE p1() +SELECT * FROM v1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +a b +1 2 +ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3; +CALL p1(); +a b +1 2 +SET @@debug_dbug=@orig_dbug; + +# 2.4 Stored program that uses query like 'SELECT * FROM v' must be +# re-executed successfully if not used columns were removed from the +# base table of this view (by ALTER TABLE); +DROP TABLE t1; +CREATE TABLE t1(a INT, b INT, c INT); +INSERT INTO t1 VALUES (1, 2, 3); +DROP VIEW v1; +CREATE VIEW v1 AS SELECT b, c FROM t1; +DROP PROCEDURE p1; +CREATE PROCEDURE p1() +SELECT * FROM v1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +b c +2 3 +ALTER TABLE t1 DROP COLUMN a; +CALL p1(); +b c +2 3 +ALTER TABLE t1 DROP COLUMN b; +CALL p1(); +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 +SET @@debug_dbug=@orig_dbug; + +# 2.5 Stored program that uses query like 'SELECT * FROM v' must be +# re-executed successfully if a type of some base table's columns were +# changed (by ALTER TABLE); +DROP TABLE t1; +CREATE TABLE t1(a INT, b INT, c INT); +INSERT INTO t1 VALUES (1, 2, 3); +DROP VIEW v1; +CREATE VIEW v1 AS SELECT b, c FROM t1; +DROP PROCEDURE p1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CREATE PROCEDURE p1() +SELECT * FROM v1; +CALL p1(); +b c +2 3 +ALTER TABLE t1 CHANGE COLUMN b b VARCHAR(255) DEFAULT 'n/a'; +DELETE FROM t1; +INSERT INTO t1(a, c) VALUES (10, 30); +CALL p1(); +b c +n/a 30 +SET @@debug_dbug=@orig_dbug; + +# 2.6 Stored program that uses query like 'SELECT * FROM v' must be +# re-executed successfully if the view 'v' was dropped and created again +# with the same definition; +# +# 2.7 Stored program that uses query like 'SELECT * FROM v' must be +# re-executed successfully if the view 'v' was dropped and created again +# with different, but compatible definition. +DROP VIEW v1; +DROP TABLE t1; +DROP PROCEDURE p1; +CREATE VIEW v1 AS SELECT 1, 2, 3; +CREATE PROCEDURE p1() +SELECT * FROM v1; +CALL p1(); +1 2 3 +1 2 3 +DROP VIEW v1; +CALL p1(); +ERROR 42S02: Table 'test.v1' doesn't exist +CREATE VIEW v1 AS SELECT 4, 5, 6; +CALL p1(); +4 5 6 +4 5 6 + +# 2.8 Stored program that uses query like 'SELECT * FROM v' must be +# re-executed successfully if the view base tables have been re-created +# using the same or compatible definition. +DROP VIEW v1; +DROP PROCEDURE p1; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE PROCEDURE p1() +SELECT * FROM v1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +a b +1 2 +DROP TABLE t1; +CALL p1(); +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 +CREATE TABLE t1(a VARCHAR(255), b VARCHAR(255)); +INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd'); +CALL p1(); +a b +a b +c d +SET @@debug_dbug=@orig_dbug; +DROP VIEW v1; +DROP TABLE t1; +DROP PROCEDURE p1; + +# 3.1 Stored program that uses query like 'SELECT * FROM t' must be +# re-executed successfully if some columns were added into temporary table +# table 't' (by ALTER TABLE); +CREATE TEMPORARY TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() SELECT * FROM t1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +a b +1 2 +ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3; +CALL p1(); +a b c +1 2 3 + +# 3.2 Stored program that uses query like 'SELECT * FROM t' must be +# re-executed successfully if some columns were removed from temporary +# table 't' (by ALTER TABLE); +ALTER TABLE t1 DROP COLUMN a; +CALL p1(); +b c +2 3 + +# 3.3 Stored program that uses query like 'SELECT * FROM t' must be +# re-executed successfully if a type of some temporary table's columns were +# changed (by ALTER TABLE); +ALTER TABLE t1 CHANGE COLUMN b b VARCHAR(255) DEFAULT 'n/a'; +INSERT INTO t1(c) VALUES (4); +CALL p1(); +b c +2 3 +n/a 4 + +# 3.4 Stored program that uses query like 'SELECT * FROM t' must be +# re-executed successfully if the temporary table 't' was dropped and +# created again with the same definition; +# +# 3.5 Stored program that uses query like 'SELECT * FROM t' must be +# re-executed successfully if the temporary table 't' was dropped and +# created again with different, but compatible definition. +DROP TEMPORARY TABLE t1; +CREATE TEMPORARY TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CALL p1(); +a b +1 2 +DROP TEMPORARY TABLE t1; +CREATE TEMPORARY TABLE t1(a VARCHAR(255), b VARCHAR(255), c VARCHAR(255)); +INSERT INTO t1 VALUES ('aa', 'bb', 'cc'); +CALL p1(); +a b c +aa bb cc +SET @@debug_dbug=@orig_dbug; +DROP TEMPORARY TABLE t1; +DROP PROCEDURE p1; + +# 4.1 Stored program must fail when it is re-executed after a table's column +# that this program is referenced to has been removed; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() SELECT a, b FROM t1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +a b +1 2 +ALTER TABLE t1 DROP COLUMN b; +CALL p1(); +ERROR 42S22: Unknown column 'b' in 'field list' +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP TABLE t1; + +# 4.2 Stored program must fail when it is re-executed after a temporary +# table's column that this program is referenced to has been removed; +CREATE TEMPORARY TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() SELECT a, b FROM t1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +a b +1 2 +ALTER TABLE t1 DROP COLUMN b; +CALL p1(); +ERROR 42S22: Unknown column 'b' in 'field list' +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP TEMPORARY TABLE t1; + +# 4.3 Stored program must fail when it is re-executed after a view's +# column that this program is referenced to has been removed; +CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; +CREATE PROCEDURE p1() SELECT a, b FROM v1; +CALL p1(); +a b +1 2 +ALTER VIEW v1 AS SELECT 1 AS a; +CALL p1(); +ERROR 42S22: Unknown column 'b' in 'field list' +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP VIEW v1; + +# 4.4 Stored program must fail when it is re-executed after a regular table +# that this program referenced to was removed; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() SELECT a, b FROM t1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +a b +1 2 +DROP TABLE t1; +CALL p1(); +ERROR 42S02: Table 'test.t1' doesn't exist +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; + +# 4.5 Stored program must fail when it is re-executed after a view that +# this program referenced to was removed; +CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; +CREATE PROCEDURE p1() SELECT a, b FROM v1; +CALL p1(); +a b +1 2 +DROP VIEW v1; +CALL p1(); +ERROR 42S02: Table 'test.v1' doesn't exist +DROP PROCEDURE p1; + +# 4.6 Stored program must fail when it is re-executed after a temporary +# table that this program referenced to was removed; +CREATE TEMPORARY TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() SELECT a, b FROM t1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +a b +1 2 +DROP TABLE t1; +CALL p1(); +ERROR 42S02: Table 'test.t1' doesn't exist +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; + +# 4.7 Stored program must fail if the program executes some +# SQL-statement and afterwards re-executes it again when some table 't' +# referenced by the statement was dropped in the period between statement +# execution; +CREATE TABLE t1(a INT); +CREATE TABLE t2(a INT); +CREATE PROCEDURE p1() +BEGIN +DECLARE CONTINUE HANDLER FOR 1146 +SELECT 'Table t1 does not exist anymore' as msg; +SELECT * FROM t1; +INSERT INTO t2 VALUES (1); +SELECT GET_LOCK('m1', 10000); +SELECT * FROM t1; +END| + +# -- connection: con1 +connect con1, localhost, root; +SELECT GET_LOCK('m1', 0); +GET_LOCK('m1', 0) +1 + +# -- connection: default +connection default; +CALL p1(); + +# -- connection: con1 +connection con1; +DROP TABLE t1; +SELECT RELEASE_LOCK('m1'); +RELEASE_LOCK('m1') +1 + +# -- connection: default +connection default; +a +GET_LOCK('m1', 10000) +1 +msg +Table t1 does not exist anymore +disconnect con1; +DROP TABLE t2; +DROP PROCEDURE p1; + +# 5.1 Regular table -> View +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() SELECT * FROM t1; +CALL p1(); +a b +1 2 +DROP TABLE t1; +CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b; +CALL p1(); +a b +1 2 +DROP PROCEDURE p1; +DROP VIEW t1; + +# 5.2 Regular table -> Temporary table +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() SELECT * FROM t1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +a b +1 2 +DROP TABLE t1; +CREATE TEMPORARY TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CALL p1(); +a b +1 2 +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP TEMPORARY TABLE t1; + +# 5.3 View -> Regular table +CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b; +CREATE PROCEDURE p1() SELECT * FROM t1; +CALL p1(); +a b +1 2 +DROP VIEW t1; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CALL p1(); +a b +1 2 +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP TABLE t1; + +# 5.4 View -> Temporary table +CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b; +CREATE PROCEDURE p1() SELECT * FROM t1; +CALL p1(); +a b +1 2 +DROP VIEW t1; +CREATE TEMPORARY TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CALL p1(); +a b +1 2 +DROP PROCEDURE p1; +DROP TEMPORARY TABLE t1; + +# 5.5 Temporary table -> View +CREATE TEMPORARY TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() SELECT * FROM t1; +CALL p1(); +a b +1 2 +DROP TEMPORARY TABLE t1; +CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b; +CALL p1(); +a b +1 2 +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP VIEW t1; + +# 5.6 Temporary table -> Regular table +CREATE TEMPORARY TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() SELECT * FROM t1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +a b +1 2 +DROP TEMPORARY TABLE t1; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CALL p1(); +a b +1 2 +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP TABLE t1; + +# 6.1 Trigger that uses column 'a' of table 't' via pseudo-variable NEW +# must be re-executed successfully if the table definition has been changed +# in a compatible way. "Compatible way" in this case is that if the table +# 't' still has a column named 'a' and the column type is compatible with +# the operation that NEW.a takes part of. +# +# 6.2 Trigger that uses column 'a' of table 't' via pseudo-variable OLD +# must be re-executed successfully if the table definition has been changed +# in a compatible way. "Compatible way" in this case is that if the table +# 't' still has a column named 'a' and the column type is compatible with +# the operation that OLD.a takes part of. +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW +BEGIN +SET @x = OLD.a; +SET @y = NEW.a; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +SET @x = 0, @y = 0; +UPDATE t1 SET a = 3, b = 4; +SELECT @x, @y; +@x @y +1 3 + +ALTER TABLE t1 ADD COLUMN c INT DEFAULT -1; + +SET @x = 0, @y = 0; +UPDATE t1 SET a = 5, b = 6; +SELECT @x, @y; +@x @y +3 5 + +ALTER TABLE t1 CHANGE COLUMN a a VARCHAR(255); + +SET @x = 0, @y = 0; +UPDATE t1 SET a = CONCAT('xxx_', a), b = 7; +SELECT @x, @y; +@x @y +5 xxx_5 +SET @@debug_dbug=@orig_dbug; + +DROP TABLE t1; + +# 6.3 Re-execution of a trigger that uses column 'a' of table 't' via +# pseudo-variable NEW must fail if the table definition has been changed in +# the way that the column 'a' does not exist anymore. +# +# 6.4 Re-execution of a trigger that uses column 'a' of table 't' via +# pseudo-variable OLD must fail if the table definition has been changed in +# the way that the column 'a' does not exist anymore. +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW +BEGIN +SET @x = OLD.a; +SET @y = NEW.b; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +UPDATE t1 SET a = 3, b = 4; + +ALTER TABLE t1 CHANGE COLUMN a a2 INT; + +UPDATE t1 SET a2 = 5, b = 6; +ERROR 42S22: Unknown column 'a' in 'OLD' + +ALTER TABLE t1 CHANGE COLUMN a2 a INT; +ALTER TABLE t1 CHANGE COLUMN b b2 INT; + +UPDATE t1 SET a = 5, b2 = 6; +ERROR 42S22: Unknown column 'b' in 'NEW' +SET @@debug_dbug=@orig_dbug; + +DROP TABLE t1; + +# 7.1 Setup: +# - stored program 'a', which alters regular table 't' in a compatible +# way; +# - stored program 'b', which calls 'a' and uses 't' before and after the +# call; +# Stored program 'b' must be executed successfully. +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() +ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3| +CREATE PROCEDURE p2() +BEGIN +SELECT a, b FROM t1; +CALL p1(); +SELECT a, b FROM t1; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p2(); +a b +1 2 +a b +1 2 + +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; + +# 7.2 Setup: +# - stored program 'a', which alters temporary table 't' in a compatible +# way; +# - stored program 'b', which calls 'a' and uses 't' before and after the +# call; +# Stored program 'b' must be executed successfully. +CREATE TEMPORARY TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() +ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3| +CREATE PROCEDURE p2() +BEGIN +SELECT a, b FROM t1; +CALL p1(); +SELECT a, b FROM t1; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p2(); +a b +1 2 +a b +1 2 + +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TEMPORARY TABLE t1; + +# 7.3 Setup: +# - stored program 'a', which re-creates regular table 't' in a +# compatible way; +# - stored program 'b', which calls 'a' and uses 't' before and after the +# call; +# Stored program 'b' must be executed successfully. +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() +BEGIN +DROP TABLE t1; +CREATE TABLE t1(a INT, b INT, c INT); +INSERT INTO t1 VALUES (1, 2, 3); +END| +CREATE PROCEDURE p2() +BEGIN +SELECT a, b FROM t1; +CALL p1(); +SELECT a, b FROM t1; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p2(); +a b +1 2 +a b +1 2 + +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; + +# 7.4 Setup: +# - stored program 'a', which re-creates temporary table 't' in a +# compatible way; +# - stored program 'b', which calls 'a' and uses 't' before and after the +# call; +# Stored program 'b' must be executed successfully. +CREATE TEMPORARY TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() +BEGIN +DROP TEMPORARY TABLE t1; +CREATE TEMPORARY TABLE t1(a INT, b INT, c INT); +INSERT INTO t1 VALUES (1, 2, 3); +END| +CREATE PROCEDURE p2() +BEGIN +SELECT a, b FROM t1; +CALL p1(); +SELECT a, b FROM t1; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p2(); +a b +1 2 +a b +1 2 + +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TEMPORARY TABLE t1; + +# 7.5 Setup: +# - stored program 'a', which re-creates view 'v' in a compatible way; +# - stored program 'b', which calls 'a' and uses 'v' before and after the +# call; +# Stored program 'b' must be executed successfully. +CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; +CREATE PROCEDURE p1() +BEGIN +DROP VIEW v1; +CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b, 3 AS c; +END| +CREATE PROCEDURE p2() +BEGIN +SELECT a, b FROM v1; +CALL p1(); +SELECT a, b FROM v1; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p2(); +a b +1 2 +a b +1 2 + +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP VIEW v1; + +# 7.6 Setup: +# - stored program 'a', which alters regular table 't' in an incompatible +# way; +# - stored program 'b', which calls 'a' and uses 't' before and after the +# call; +# Stored program 'b' must fail on access to the table after its +# modification. +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() +ALTER TABLE t1 DROP COLUMN a| +CREATE PROCEDURE p2() +BEGIN +SELECT a, b FROM t1; +CALL p1(); +SELECT a, b FROM t1; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p2(); +a b +1 2 +ERROR 42S22: Unknown column 'a' in 'field list' + +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; + +# 7.7 Setup: +# - stored program 'a', which alters temporary table 't' in an +# incompatible way; +# - stored program 'b', which calls 'a' and uses 't' before and after the +# call; +# Stored program 'b' must fail on access to the table after its +# modification. +CREATE TEMPORARY TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() +ALTER TABLE t1 DROP COLUMN a| +CREATE PROCEDURE p2() +BEGIN +SELECT a, b FROM t1; +CALL p1(); +SELECT a, b FROM t1; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p2(); +a b +1 2 +ERROR 42S22: Unknown column 'a' in 'field list' + +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TEMPORARY TABLE t1; + +# 7.8 Setup: +# - stored program 'a', which re-creates regular table 't' in an +# incompatible way; +# - stored program 'b', which calls 'a' and uses 't' before and after the +# call; +# Stored program 'b' must fail on access to the table after its +# modification. +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() +BEGIN +DROP TABLE t1; +CREATE TABLE t1(b INT, c INT); +INSERT INTO t1 VALUES (2, 3); +END| +CREATE PROCEDURE p2() +BEGIN +SELECT a, b FROM t1; +CALL p1(); +SELECT a, b FROM t1; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p2(); +a b +1 2 +ERROR 42S22: Unknown column 'a' in 'field list' + +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; + +# 7.9 Setup: +# - stored program 'a', which re-creates temporary table 't' in an +# incompatible way; +# - stored program 'b', which calls 'a' and uses 't' before and after the +# call; +# Stored program 'b' must fail on access to the table after its +# modification. +CREATE TEMPORARY TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +CREATE PROCEDURE p1() +BEGIN +DROP TEMPORARY TABLE t1; +CREATE TEMPORARY TABLE t1(b INT, c INT); +INSERT INTO t1 VALUES (2, 3); +END| +CREATE PROCEDURE p2() +BEGIN +SELECT a, b FROM t1; +CALL p1(); +SELECT a, b FROM t1; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p2(); +a b +1 2 +ERROR 42S22: Unknown column 'a' in 'field list' + +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TEMPORARY TABLE t1; + +# 7.10 Setup: +# - stored program 'a', which re-creates view 'v' in an incompatible way; +# - stored program 'b', which calls 'a' and uses 'v' before and after the +# call; +# Stored program 'b' must fail on access to the view after its +# modification. +CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; +CREATE PROCEDURE p1() +BEGIN +DROP VIEW v1; +CREATE VIEW v1 AS SELECT 2 AS b, 3 AS c; +END| +CREATE PROCEDURE p2() +BEGIN +SELECT a, b FROM v1; +CALL p1(); +SELECT a, b FROM v1; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p2(); +a b +1 2 +ERROR 42S22: Unknown column 'a' in 'field list' + +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP VIEW v1; +# 8. Stored program must be executed successfully when: +# a. the program uses a table/view/temporary table that doesn't exist +# at the time of start program execution +# b. failed reference to the missed table/view/temporary table handled +# by stored program +# c. this table/view/temporary table is created as part of the +# program execution +# d. stored program gets access to newly created table/view/temporary +# table from some SQL-statement during subsequent stored program execution. +CREATE PROCEDURE p1() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +SELECT 'SQLEXCEPTION caught' AS msg; +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +END; +SELECT * FROM t1; +SELECT * FROM t1; +DROP TABLE t1; +END| +CREATE PROCEDURE p2() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +SELECT 'SQLEXCEPTION caught' AS msg; +CREATE TEMPORARY TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1, 2); +END; +SELECT * FROM t1; +SELECT * FROM t1; +DROP TEMPORARY TABLE t1; +END| +CREATE PROCEDURE p3() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +SELECT 'SQLEXCEPTION caught' AS msg; +CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; +END; +SELECT * FROM v1; +SELECT * FROM v1; +DROP VIEW v1; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +msg +SQLEXCEPTION caught +a b +1 2 +CALL p2(); +msg +SQLEXCEPTION caught +a b +1 2 +CALL p3(); +msg +SQLEXCEPTION caught +a b +1 2 +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; + +# 9. Stored program must be executed successfully when +# - the stored program has an expression in one of the following +# statements +# - RETURN +# - IF +# - CASE +# - WHILE +# - UNTIL +# - SET +# - the expression depends on the meta-data of some table/view/temporary table; +# - the meta-data of dependent object has changed in a compatible way. +# +# Note, that CASE-expression must be evaluated once even if (some) +# CASE-expressions need to be re-parsed. +# +# 10. Subsequent executions of a stored program must fail when +# - the stored program has an expression in one of the following +# statements +# - RETURN +# - IF +# - CASE +# - WHILE +# - UNTIL +# - SET +# - the expression depends on the meta-data of some table/view/temporary table; +# - the meta-data of dependent object has changed in a non-compatible way. +# +# Note, that CASE-expression must be evaluated once even if (some) +# CASE-expressions need to be re-parsed. + +# Check IF-statement. + +CREATE PROCEDURE p1() +BEGIN +IF(SELECT * FROM t1)THEN +SELECT 1; +ELSE +SELECT 2; +END IF; +END| +CREATE PROCEDURE p2() +BEGIN +DECLARE v INT DEFAULT 1; +IF v * (SELECT * FROM t1) THEN +SELECT 1; +ELSE +SELECT 2; +END IF; +END| +CREATE FUNCTION f1() RETURNS INT +BEGIN +IF (SELECT * FROM t1) THEN +RETURN 1; +ELSE +RETURN 2; +END IF; +RETURN 3; +END| +CREATE FUNCTION f2() RETURNS INT +BEGIN +DECLARE v INT DEFAULT 1; +IF v * (SELECT * FROM t1) THEN +RETURN 1; +ELSE +RETURN 2; +END IF; +RETURN 3; +END| +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p1(); +1 +1 +CALL p2(); +1 +1 +SELECT f1(); +f1() +1 +SELECT f2(); +f2() +1 + +UPDATE t1 SET a = 0; + +CALL p1(); +2 +2 +CALL p2(); +2 +2 +SELECT f1(); +f1() +2 +SELECT f2(); +f2() +2 + +ALTER TABLE t1 ADD COLUMN b INT DEFAULT 1; + +CALL p1(); +ERROR 21000: Operand should contain 1 column(s) +CALL p2(); +ERROR HY000: Illegal parameter data types int and row for operation '*' +SELECT f1(); +ERROR 21000: Operand should contain 1 column(s) +SELECT f2(); +ERROR HY000: Illegal parameter data types int and row for operation '*' + +ALTER TABLE t1 DROP COLUMN a; + +CALL p1(); +1 +1 +CALL p2(); +1 +1 +SELECT f1(); +f1() +1 +SELECT f2(); +f2() +1 + +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; + +# Check WHILE-statement. + +CREATE PROCEDURE p1(x INT) +BEGIN +WHILE(SELECT * FROM t1)DO +SELECT x; +UPDATE t1 SET a = x; +SET x = x - 1; +END WHILE; +END| +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0); +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(3); +UPDATE t1 SET a = 1; +CALL p1(3); +x +3 +x +2 +x +1 +x +0 +UPDATE t1 SET a = 1; +ALTER TABLE t1 ADD COLUMN b INT DEFAULT 1; +CALL p1(3); +ERROR 21000: Operand should contain 1 column(s) +ALTER TABLE t1 DROP COLUMN a; +CALL p1(3); +x +3 +ERROR 42S22: Unknown column 'a' in 'field list' +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP TABLE t1; + +# Check REPEAT-statement. + +CREATE PROCEDURE p1(x INT) +BEGIN +REPEAT +SELECT x; +UPDATE t1 SET a = x; +SET x = x - 1; +UNTIL(NOT (SELECT * FROM t1))END REPEAT; +END| +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0); +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(3); +x +3 +x +2 +x +1 +x +0 +UPDATE t1 SET a = 1; +CALL p1(3); +x +3 +x +2 +x +1 +x +0 +UPDATE t1 SET a = 1; +ALTER TABLE t1 ADD COLUMN b INT DEFAULT 1; +CALL p1(3); +x +3 +ERROR 21000: Operand should contain 1 column(s) +ALTER TABLE t1 DROP COLUMN a; +CALL p1(3); +x +3 +ERROR 42S22: Unknown column 'a' in 'field list' +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP TABLE t1; + +# Check CASE-statement (round #1). + +CREATE PROCEDURE p1() +BEGIN +CASE +WHEN (SELECT * FROM t1) = 1 THEN SELECT 'a1'; +WHEN (SELECT * FROM t1) = 2 THEN SELECT 'a2'; +WHEN (SELECT * FROM t1) = 3 THEN SELECT 'a3'; +ELSE SELECT 'a4'; +END CASE; +END| +CREATE PROCEDURE p2() +BEGIN +CASE (SELECT * FROM t1) +WHEN 1 THEN SELECT 'a1'; +WHEN 2 THEN SELECT 'a2'; +WHEN 3 THEN SELECT 'a3'; +ELSE SELECT 'a4'; +END CASE; +END| +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0); +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p1(); +a4 +a4 +CALL p2(); +a4 +a4 + +UPDATE t1 SET a = 3; + +CALL p1(); +a3 +a3 +CALL p2(); +a3 +a3 + +ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; + +CALL p1(); +ERROR HY000: Illegal parameter data types row and int for operation '=' +CALL p2(); +ERROR 21000: Operand should contain 1 column(s) + +ALTER TABLE t1 DROP COLUMN a; + +CALL p1(); +a2 +a2 +CALL p2(); +a2 +a2 +SET @@debug_dbug=@orig_dbug; + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; + +# Check CASE-statement (round #2). +# +# Check that CASE-expression is executed once even if the metadata, used +# in a WHEN-expression, have changed. + +CREATE TABLE t1(a INT); +CREATE TABLE t2(a INT); +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1); +CREATE FUNCTION f1() RETURNS INT +BEGIN +SET @x = @x + 1; +RETURN (SELECT a FROM t1); +END| +CREATE PROCEDURE p1() +BEGIN +CASE f1() +WHEN 1 THEN SELECT 'a1'; +WHEN 2 THEN SELECT 'a2'; +WHEN (SELECT * FROM t2) THEN SELECT 'subselect'; +ELSE SELECT 'else'; +END CASE; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +SET @x = 0; +CALL p1(); +a1 +a1 +SELECT @x; +@x +1 + +UPDATE t1 SET a = 3; +ALTER TABLE t2 ADD COLUMN b INT DEFAULT 3; + +SET @x = 0; +CALL p1(); +ERROR HY000: Illegal parameter data types int and row for operation '=' +SELECT @x; +@x +1 + +ALTER TABLE t2 DROP COLUMN a; + +SET @x = 0; +CALL p1(); +subselect +subselect +SELECT @x; +@x +1 +SET @@debug_dbug=@orig_dbug; + +DROP PROCEDURE p1; +DROP FUNCTION f1; +DROP TABLE t1; +DROP TABLE t2; + +# Check DEFAULT clause. +# + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +CREATE PROCEDURE p1() +BEGIN +DECLARE v INT DEFAULT (SELECT * FROM t1); +SELECT v; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p1(); +v +1 + +ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; + +CALL p1(); +ERROR HY000: Cannot cast 'row' as 'int' in assignment of `v` + +ALTER TABLE t1 DROP COLUMN a; + +CALL p1(); +v +2 +SET @@debug_dbug=@orig_dbug; + +DROP PROCEDURE p1; +DROP TABLE t1; + +# Check SET. +# + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2(a INT); +INSERT INTO t2 VALUES (1); +CREATE PROCEDURE p1() +BEGIN +DECLARE x INT; +SET x = (SELECT * FROM t1); +SELECT x; +END| +CREATE PROCEDURE p2() +BEGIN +SET @x = NULL; +SET @x = (SELECT * FROM t1); +SELECT @x; +END| +CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW +BEGIN +SET NEW.a = (SELECT * FROM t1) * 2; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p1(); +x +1 + +CALL p2(); +@x +1 + +UPDATE t2 SET a = 10; + +ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; + +CALL p1(); +ERROR HY000: Cannot cast 'row' as 'int' in assignment of `x` + +CALL p2(); +ERROR 21000: Operand should contain 1 column(s) + +UPDATE t2 SET a = 20; +ERROR HY000: Illegal parameter data types row and int for operation '*' + +ALTER TABLE t1 DROP COLUMN a; + +CALL p1(); +x +2 + +CALL p2(); +@x +2 + +UPDATE t2 SET a = 30; +SET @@debug_dbug=@orig_dbug; + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; +DROP TABLE t2; + +# 11.1 If metadata of the objects (regular tables, temporary tables, +# views), used in SELECT-statement changed between DECLARE CURSOR and +# OPEN statements, the SELECT-statement should be re-parsed to use +# up-to-date metadata. + + +# - Regular table. + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +CREATE PROCEDURE p1() +BEGIN +DECLARE v INT; +DECLARE c CURSOR FOR SELECT * FROM t1; +ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; +ALTER TABLE t1 DROP COLUMN a; +OPEN c; +FETCH c INTO v; +CLOSE c; +SELECT v; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p1(); +v +2 +SET @@debug_dbug=@orig_dbug; + +DROP TABLE t1; +DROP PROCEDURE p1; + +# - Temporary table. + +CREATE TEMPORARY TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +CREATE PROCEDURE p1() +BEGIN +DECLARE v INT; +DECLARE c CURSOR FOR SELECT * FROM t1; +ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; +ALTER TABLE t1 DROP COLUMN a; +OPEN c; +FETCH c INTO v; +CLOSE c; +SELECT v; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p1(); +v +2 +SET @@debug_dbug=@orig_dbug; + +DROP TEMPORARY TABLE t1; +DROP PROCEDURE p1; + +# 11.2 If the metadata changed between OPEN and FETCH or CLOSE +# statements, those changes should not be noticed. + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +CREATE PROCEDURE p1() +BEGIN +DECLARE v INT; +DECLARE c CURSOR FOR SELECT * FROM t1; +OPEN c; +ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; +ALTER TABLE t1 DROP COLUMN a; +FETCH c INTO v; +CLOSE c; +SELECT v; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p1(); +v +1 +SET @@debug_dbug=@orig_dbug; + +DROP TABLE t1; +DROP PROCEDURE p1; + +# 11.3 Re-parsing of the SELECT-statement should be made correctly +# (in the correct parsing context) if the metadata changed between +# DECLARE CURSOR and OPEN statements, and those statements reside in different +# parsing contexts. + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +CREATE PROCEDURE p1() +BEGIN +DECLARE f1 INT; +DECLARE f2 INT; +DECLARE f3 INT; +DECLARE x INT DEFAULT 1; +DECLARE y INT DEFAULT 2; +DECLARE c CURSOR FOR SELECT x, y, t1.a FROM t1; +ALTER TABLE t1 ADD COLUMN b INT; +BEGIN +DECLARE x INT DEFAULT 10; +DECLARE y INT DEFAULT 20; +OPEN c; +FETCH c INTO f1, f2, f3; +SELECT f1, f2, f3; +CLOSE c; +END; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +CALL p1(); +f1 f2 f3 +1 2 1 +SET @@debug_dbug=@orig_dbug; + +DROP TABLE t1; +DROP PROCEDURE p1; + +# Test procedure behaviour after view recreation. +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE PROCEDURE p1() +SELECT * FROM v1; +CALL p1(); +a +1 +2 +# Alter underlying table and recreate the view. +ALTER TABLE t1 ADD COLUMN (b INT); +ALTER VIEW v1 AS SELECT * FROM t1; +# And check whether the call of stored procedure handles it correctly. +CALL p1(); +a b +1 NULL +2 NULL +DROP VIEW v1; +DROP TABLE t1; +DROP PROCEDURE p1; +# Test if metadata changes for temporary table is handled +# correctly inside a stored procedure. +CREATE TEMPORARY TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE PROCEDURE p1() +SELECT * FROM t1; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1(); +a +1 +2 +# Test if added temporary table's column is recognized during +# procedure invocation. +ALTER TABLE t1 ADD COLUMN (b INT); +CALL p1(); +a b +1 NULL +2 NULL +# Test if dropped temporary table's column is not appeared +# in procedure's result. +ALTER TABLE t1 DROP COLUMN a; +CALL p1(); +b +NULL +NULL +SET @@debug_dbug=@orig_dbug; +DROP PROCEDURE p1; +DROP TABLE t1; +# Test handle of metadata changes with stored function. +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE FUNCTION f1() RETURNS INT +BEGIN +CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM t1; +RETURN 0; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +SELECT f1(); +f1() +0 +SELECT * FROM t1_result_set; +a +1 +2 +DROP TABLE t1_result_set; +# Check if added column is noticed by invocation of stored function. +ALTER TABLE t1 ADD COLUMN (b INT); +SELECT f1(); +f1() +0 +SELECT * FROM t1_result_set; +a b +1 NULL +2 NULL +DROP TABLE t1_result_set; +# Check if dropped column is noticed by invocation of stored function. +ALTER TABLE t1 DROP COLUMN a; +SELECT f1(); +f1() +0 +SELECT * FROM t1_result_set; +b +NULL +NULL +SET @@debug_dbug=@orig_dbug; +DROP TABLE t1_result_set; +DROP TABLE t1; +DROP FUNCTION f1; +# Test if table's recreation is handled correctly +# inside a stored function. +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE FUNCTION f1() RETURNS INT +BEGIN +CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM t1; +RETURN 0; +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +SELECT f1(); +f1() +0 +SELECT * FROM t1_result_set; +a +1 +2 +DROP TABLE t1_result_set; +# Recreate table and check if it is handled correctly +# by function invocation. +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +SELECT f1(); +f1() +0 +SELECT * FROM t1_result_set; +a +1 +2 +SET @@debug_dbug=@orig_dbug; +DROP TABLE t1_result_set; +DROP FUNCTION f1; +DROP TABLE t1; +# Test if changes in the view's metadata is handled +# correctly by function call. +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE FUNCTION f1() RETURNS INT +BEGIN +CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM v1; +RETURN 0; +END| +SELECT f1(); +f1() +0 +SELECT * FROM t1_result_set; +a +1 +2 +DROP TABLE t1_result_set; +ALTER TABLE t1 ADD COLUMN (b INT); +ALTER VIEW v1 AS SELECT * FROM t1; +SELECT f1(); +f1() +0 +SELECT * FROM t1_result_set; +a b +1 NULL +2 NULL +DROP TABLE t1_result_set; +DROP TABLE t1; +DROP VIEW v1; +DROP FUNCTION f1; +# Check if queried object's type substitution (table->view, view->table, +# table->temp table, etc.) is handled correctly during invocation of +# stored function/procedure. +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE FUNCTION f1() RETURNS INT +BEGIN +CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM t1; +RETURN 0; +END| +CREATE PROCEDURE p1() +SELECT * FROM t1| +CALL p1(); +a +1 +2 +SELECT f1(); +f1() +0 +SELECT * FROM t1_result_set; +a +1 +2 +DROP TABLE t1_result_set; +DROP TABLE t1; +CREATE TEMPORARY TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +CALL p1; +a +1 +2 +SELECT f1(); +f1() +0 +SELECT * FROM t1_result_set; +a +1 +2 +DROP TABLE t1_result_set; +SET @@debug_dbug=@orig_dbug; +DROP TABLE t1; +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (1), (2); +CREATE VIEW t1 AS SELECT * FROM t2; +CALL p1; +a +1 +2 +SELECT f1(); +f1() +0 +SELECT * FROM t1_result_set; +a +1 +2 +DROP TABLE t1_result_set; +DROP TABLE t2; +DROP VIEW t1; +DROP FUNCTION f1; +DROP PROCEDURE p1; +# Test handle of metadata changes with triggers. +CREATE TABLE t1 (a INT); +CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW +SET new.a = new.a + 100; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +INSERT INTO t1 VALUES (1), (2); +SELECT * FROM t1; +a +101 +102 +# Check if added table's column is handled correctly inside trigger. +ALTER TABLE t1 ADD COLUMN (b INT); +INSERT INTO t1 VALUES (3, 4); +SELECT * FROM t1; +a b +101 NULL +102 NULL +103 4 +SET @@debug_dbug=@orig_dbug; +DROP TRIGGER trg1; +DROP TABLE t1; +# Test if deleted column is handled correctly by trigger invocation. +CREATE TABLE t1 (a INT, b INT); +CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW +SET new.a = new.a + 100; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +INSERT INTO t1 VALUES (1, 2), (3, 4); +SELECT * FROM t1; +a b +101 2 +103 4 +ALTER TABLE t1 DROP COLUMN b; +INSERT INTO t1 VALUES (5); +SELECT * FROM t1; +a +101 +103 +105 +SET @@debug_dbug=@orig_dbug; +DROP TRIGGER trg1; +DROP TABLE t1; +# Check if server returns and error when was dropped a column +# that is used inside a trigger body. +CREATE TABLE t1 (a INT, b INT); +CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW +SET new.a = new.a + 100; +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; +INSERT INTO t1 VALUES (1, 2), (3, 4); +SELECT * FROM t1; +a b +101 2 +103 4 +ALTER TABLE t1 DROP COLUMN a; +INSERT INTO t1 VALUES (5); +ERROR 42S22: Unknown column 'a' in 'NEW' +SET @@debug_dbug=@orig_dbug; +DROP TRIGGER trg1; +DROP TABLE t1; + +# Check updateable views inside triggers. +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2(a INT); +INSERT INTO t2 VALUES (1); +CREATE VIEW v1 AS SELECT a FROM t1; +CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW +BEGIN +INSERT INTO v1 VALUES (NEW.a); +SET @x = (SELECT CHARSET(a) FROM v1 LIMIT 1); +END| +SET @@debug_dbug='+d,check_sp_cache_not_invalidated'; + +SET @x = NULL; +UPDATE t2 SET a = 10; +SELECT * FROM v1; +a +1 +10 +SELECT @x; +@x +binary + +ALTER TABLE t1 CHANGE COLUMN a a CHAR(2); + +SET @x = NULL; +UPDATE t2 SET a = 20; +SELECT * FROM v1; +a +1 +10 +20 +SELECT @x; +@x +latin1 +SET @@debug_dbug=@orig_dbug; + +DROP TABLE t1; +DROP TABLE t2; +DROP VIEW v1; +# +# MDEV-31661: Assertion `thd->lex == sp_instr_lex' failed in LEX* sp_lex_instr::parse_expr(THD*, sp_head*, LEX*) +# +CREATE OR REPLACE PROCEDURE p1() +BEGIN +DECLARE c CURSOR FOR SELECT * FROM t1; +OPEN c; +CLOSE c; +END; +$ +CALL p1; +ERROR 42S02: Table 'test.t1' doesn't exist +CREATE TABLE t1 (id INT); +CALL p1; +# Second execution of the stored procedure p1() after the dependent +# table t1 has been created resulted in assert failure for server built +# with debug +CALL p1; +# Clean up +DROP PROCEDURE p1; +DROP TABLE t1; +# +# MDEV-31799 Unexpected ER_TRG_NO_SUCH_ROW_IN_TRG and server crash after ALTER TABLE +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3),(4); +CREATE TABLE t2 (b INT); +# Check that AFTER DELETE trigger is re-compiled on changing table's metadata +CREATE TRIGGER tr AFTER DELETE ON t1 FOR EACH ROW INSERT INTO t2 (b) VALUES (OLD.a); +DELETE FROM t1 LIMIT 1; +SELECT * FROM t2; +b +1 +ALTER TABLE t2 FORCE; +DELETE FROM t1 LIMIT 1; +SELECT * FROM t2; +b +1 +2 +DELETE FROM t1 LIMIT 1; +SELECT * FROM t2; +b +1 +2 +3 +DROP TRIGGER tr; +# Check that AFTER UPDATE trigger is re-compiled on changing table's metadata +CREATE TRIGGER tr AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO t2 (b) VALUES (OLD.a); +# Remove records interted by AFTER DELETE trogger +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +INSERT INTO t1 VALUES (1); +UPDATE t1 SET a = 2; +# Above statement should insert the row (1) into the table t2 +# Expected output contains one row: (1) +SELECT * FROM t2; +b +1 +ALTER TABLE t2 FORCE; +# The following statement should insert the row (2) into the table t2 +UPDATE t1 SET a = 3; +# Expected output contains two rows: (1), (2) +SELECT * FROM t2; +b +1 +2 +# The following statement should insert the row (3) into the table t2 +UPDATE t1 SET a = 5; +# Expected output contains three rows: (1), (2), (3) +SELECT * FROM t2; +b +1 +2 +3 +DROP TABLE t1, t2; +# +# MDEV-33525: Recreate/reuse temporary table +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +CREATE FUNCTION f1() +RETURNS INT +BEGIN +DECLARE res INT; +DECLARE t1_cur CURSOR FOR SELECT 100 FROM t1, t1_tmp; +CREATE TEMPORARY TABLE t1_tmp SELECT 1 a; +OPEN t1_cur; +CLOSE t1_cur; +DROP TEMPORARY TABLE t1_tmp; +RETURN 0; +END +| +SELECT f1(); +f1() +0 +# Without the patch, the second call of f1 would result in error: +# ER_NO_SUCH_TABLE (1146): Table 'test.t1' doesn't exist +SELECT f1(); +f1() +0 +# Clean up +DROP FUNCTION f1; +DROP TABLE t1; +SET sql_mode = default; |