Merging upstream version 1:11.4.2.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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;