MDEV-5816: Stored programs: validation of stored program statements

Follow-up patch that fixes existing mtr tests.

10 files changed, 39 insertions, 32 deletions

diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result
index 65dccc6b9a3..91d90b11d2d 100644
--- a/mysql-test/main/join.result
+++ b/mysql-test/main/join.result
@@ -1532,9 +1532,9 @@ f
 DROP TABLE t;
 CREATE TABLE t (i INT);
 CALL p;
-ERROR 42S22: Unknown column 't1.f' in 'field list'
+ERROR 42S22: Unknown column 'f' in 'from clause'
 CALL p;
-ERROR 42S22: Unknown column 't1.f' in 'field list'
+ERROR 42S22: Unknown column 'f' in 'from clause'
 DROP PROCEDURE p;
 DROP TABLE t;
 CREATE TABLE t1 (a INT, b INT);
diff --git a/mysql-test/main/ps_ddl.result b/mysql-test/main/ps_ddl.result
index dcbb6982702..17fa6e2bb0f 100644
--- a/mysql-test/main/ps_ddl.result
+++ b/mysql-test/main/ps_ddl.result
@@ -322,8 +322,8 @@ create table t3 (a int unique);
 create view v1 as select a from t2;
 create trigger t1_ai after insert on t1 for each row
 insert into v1 (a) values (new.a);
-# Demonstrate that the same bug is present
-# without prepared statements
+# Demonstrate that this bug is fixed by MDEV-5816
+# both for regular and prepared statements
 insert into t1 (a) values (5);
 select * from t2;
 a
@@ -333,15 +333,15 @@ a
 drop view v1;
 create view v1 as select a from t3;
 insert into t1 (a) values (6);
-ERROR 42S02: Table 'test.t2' doesn't exist
 flush table t1;
-insert into t1 (a) values (6);
+insert into t1 (a) values (60);
 select * from t2;
 a
 5
 select * from t3;
 a
 6
+60
 prepare stmt from "insert into t1 (a) values (?)";
 set @var=7;
 execute stmt using @var;
@@ -352,6 +352,7 @@ select * from t3;
 a
 6
 7
+60
 select * from t2;
 a
 5
@@ -366,23 +367,26 @@ set @var=8;
 # but repreparation of the main statement doesn't cause repreparation
 # of trigger statements.
 #
-# The following EXECUTE results in ER_NO_SUCH_TABLE (t3) error, because
-# pre-locking list of the prepared statement has been changed
+# Prior MDEV-5816, the following EXECUTE resulted in
+# ER_NO_SUCH_TABLE (t3) error, because pre-locking list of the prepared
+# statement has been changed
 # (the prepared statement has noticed the meta-data change),
 # but the trigger still tries to deal with 't3', which is not opened.
 # That's why '8' is not inserted neither into 't2', nor into 't3'.
+# After the task MDEV-5816 be implemented this issue does't exist.
 execute stmt using @var;
-ERROR 42S02: Table 'test.t3' doesn't exist
 call p_verify_reprepare_count(1);
 SUCCESS
 
 select * from t2;
 a
 5
+8
 select * from t3;
 a
 6
 7
+60
 flush table t1;
 set @var=9;
 execute stmt using @var;
@@ -392,11 +396,13 @@ SUCCESS
 select * from t2;
 a
 5
+8
 9
 select * from t3;
 a
 6
 7
+60
 drop view v1;
 drop table t1,t2,t3;
 # Test 7-d: dependent TABLE has changed
diff --git a/mysql-test/main/ps_ddl.test b/mysql-test/main/ps_ddl.test
index e12fb11d50f..e17539b3a7e 100644
--- a/mysql-test/main/ps_ddl.test
+++ b/mysql-test/main/ps_ddl.test
@@ -334,17 +334,18 @@ create view v1 as select a from t2;
 create trigger t1_ai after insert on t1 for each row
   insert into v1 (a) values (new.a);
 
---echo # Demonstrate that the same bug is present
---echo # without prepared statements
+--echo # Demonstrate that this bug is fixed by MDEV-5816
+--echo # both for regular and prepared statements
 insert into t1 (a) values (5);
 select * from t2;
 select * from t3;
 drop view v1;
 create view v1 as select a from t3;
---error ER_NO_SUCH_TABLE
+# Before MDEV-5816 the following statement would fail
+# with the error ER_NO_SUCH_TABLE
 insert into t1 (a) values (6);
 flush table t1;
-insert into t1 (a) values (6);
+insert into t1 (a) values (60);
 select * from t2;
 select * from t3;
 
@@ -365,12 +366,13 @@ set @var=8;
 --echo # but repreparation of the main statement doesn't cause repreparation
 --echo # of trigger statements.
 --echo #
---echo # The following EXECUTE results in ER_NO_SUCH_TABLE (t3) error, because
---echo # pre-locking list of the prepared statement has been changed
+--echo # Prior MDEV-5816, the following EXECUTE resulted in
+--echo # ER_NO_SUCH_TABLE (t3) error, because pre-locking list of the prepared
+--echo # statement has been changed
 --echo # (the prepared statement has noticed the meta-data change),
 --echo # but the trigger still tries to deal with 't3', which is not opened.
 --echo # That's why '8' is not inserted neither into 't2', nor into 't3'.
---error ER_NO_SUCH_TABLE
+--echo # After the task MDEV-5816 be implemented this issue does't exist.
 execute stmt using @var;
 call p_verify_reprepare_count(1);
 select * from t2;
diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result
index 37c22e45b45..ea6dc6a1482 100644
--- a/mysql-test/main/sp.result
+++ b/mysql-test/main/sp.result
@@ -6782,7 +6782,7 @@ call p1$
 a
 alter table t1 add b integer$
 call p1$
-a
+a	b
 drop table t1;
 drop procedure p1;
 # ------------------------------------------------------------------
@@ -7247,12 +7247,12 @@ create table t1 (a int);
 insert into t1 (a) values (1), (2), (3), (4), (5);
 #
 # Do we correctly resolve identifiers in LIMIT?
-# Since DROP and CREATE did not invalidate
-# the SP cache, we can't test until
-# we drop and re-create the procedure.
+# DROP and CREATE results in SP statement recompilation
+# so second execution of the procedure p1() doesn't lead
+# to issuing an error
 #
 call p1();
-ERROR 42S22: Unknown column 'test.t1.c1' in 'field list'
+a
 # 
 # Drop and recreate the procedure, then repeat
 #
diff --git a/mysql-test/main/sp.test b/mysql-test/main/sp.test
index 2e3a492659e..3270212f654 100644
--- a/mysql-test/main/sp.test
+++ b/mysql-test/main/sp.test
@@ -8700,11 +8700,10 @@ create table t1 (a int);
 insert into t1 (a) values (1), (2), (3), (4), (5);
 --echo #
 --echo # Do we correctly resolve identifiers in LIMIT?
---echo # Since DROP and CREATE did not invalidate
---echo # the SP cache, we can't test until
---echo # we drop and re-create the procedure.
+--echo # DROP and CREATE results in SP statement recompilation
+--echo # so second execution of the procedure p1() doesn't lead
+--echo # to issuing an error
 --echo #
---error ER_BAD_FIELD_ERROR
 call p1();
 --echo # 
 --echo # Drop and recreate the procedure, then repeat
diff --git a/mysql-test/main/subselect3.inc b/mysql-test/main/subselect3.inc
index af7b45542bd..e45a701ef5f 100644
--- a/mysql-test/main/subselect3.inc
+++ b/mysql-test/main/subselect3.inc
@@ -1208,7 +1208,6 @@ CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2);
 delimiter ;|
 CALL p1;
 ALTER TABLE t2 CHANGE COLUMN f1 my_column INT;
---error ER_BAD_FIELD_ERROR
 CALL p1;
 DROP PROCEDURE p1;
 DROP TABLE t1, t2;
diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result
index 28187e0ffdd..28180147951 100644
--- a/mysql-test/main/subselect3.result
+++ b/mysql-test/main/subselect3.result
@@ -1443,7 +1443,7 @@ CALL p1;
 f1
 ALTER TABLE t2 CHANGE COLUMN f1 my_column INT;
 CALL p1;
-ERROR 42S22: Unknown column 'f1' in 'where clause'
+f1
 DROP PROCEDURE p1;
 DROP TABLE t1, t2;
 #
diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result
index 9df821e07dc..cd8337547ed 100644
--- a/mysql-test/main/subselect3_jcl6.result
+++ b/mysql-test/main/subselect3_jcl6.result
@@ -1446,7 +1446,7 @@ CALL p1;
 f1
 ALTER TABLE t2 CHANGE COLUMN f1 my_column INT;
 CALL p1;
-ERROR 42S22: Unknown column 'f1' in 'where clause'
+f1
 DROP PROCEDURE p1;
 DROP TABLE t1, t2;
 #
diff --git a/mysql-test/suite/sys_vars/r/stored_program_cache_func.result b/mysql-test/suite/sys_vars/r/stored_program_cache_func.result
index 11151847d6b..13b15ea76bf 100644
--- a/mysql-test/suite/sys_vars/r/stored_program_cache_func.result
+++ b/mysql-test/suite/sys_vars/r/stored_program_cache_func.result
@@ -41,8 +41,7 @@ end |
 call pr(1);
 b
 call pr(2);
-ERROR 42S22: Unknown column 'test.t1.b' in 'field list'
-drop table t1;
+a
 set global stored_program_cache=0;
 call pr(1);
 b
diff --git a/mysql-test/suite/sys_vars/t/stored_program_cache_func.test b/mysql-test/suite/sys_vars/t/stored_program_cache_func.test
index 611c33b530e..0a7340ebb49 100644
--- a/mysql-test/suite/sys_vars/t/stored_program_cache_func.test
+++ b/mysql-test/suite/sys_vars/t/stored_program_cache_func.test
@@ -33,9 +33,11 @@ create procedure pr(i int) begin
 end |
 --delimiter ;
 call pr(1);
---error ER_BAD_FIELD_ERROR
+#--error ER_BAD_FIELD_ERROR
+# MDEV-5816 added support for recompilation of statements
+# inside a stored routine so the error ER_BAD_FIELD_ERROR is
+# no more expcted
 call pr(2);
-drop table t1;
 
 set global stored_program_cache=0;
 call pr(1);