From dfb138c3694f3408423463da0d1ecf06773a5de4 Mon Sep 17 00:00:00 2001 From: Dmitry Shulga Date: Thu, 1 Sep 2022 16:52:41 +0700 Subject: MDEV-5816: Stored programs: validation of stored program statements Follow-up patch that fixes existing mtr tests. --- mysql-test/main/join.result | 4 ++-- mysql-test/main/sp.result | 10 +++++----- mysql-test/main/sp.test | 7 +++---- mysql-test/main/subselect3.inc | 1 - mysql-test/main/subselect3.result | 2 +- mysql-test/main/subselect3_jcl6.result | 2 +- mysql-test/suite/sys_vars/r/stored_program_cache_func.result | 3 +-- mysql-test/suite/sys_vars/t/stored_program_cache_func.test | 6 ++++-- 8 files changed, 17 insertions(+), 18 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/sp.result b/mysql-test/main/sp.result index 359deffb4d1..14ef86aabdb 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 512f149e4b8..b4d90f9a66f 100644 --- a/mysql-test/main/sp.test +++ b/mysql-test/main/sp.test @@ -8687,11 +8687,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 8ed1c9ee210..29f6f991eaf 100644 --- a/mysql-test/main/subselect3.inc +++ b/mysql-test/main/subselect3.inc @@ -1205,7 +1205,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); -- cgit v1.2.1