Improve the testcases for Bug 12093 in ps_ddl.test

Details:
- add subtest with drop unrelated view
- rearrange existing tests so that a distinction
  between drop procedure and drop function effects
  is possible

3 files changed, 84 insertions, 24 deletions

diff --git a/mysql-test/include/ps_ddl_1.inc b/mysql-test/include/ps_ddl_1.inc
new file mode 100644
index 00000000000..f6b4c8108a1
--- /dev/null
+++ b/mysql-test/include/ps_ddl_1.inc
@@ -0,0 +1,25 @@
+# include/ps_ddl_1.inc 
+#
+# Auxiliary script to be used in ps_ddl.test
+#
+
+prepare stmt_sf from 'select f_12093();';
+prepare stmt_sp from 'call p_12093(f_12093())';
+execute stmt_sf;
+execute stmt_sp;
+
+connection con1;
+eval $my_drop;
+#
+connection default;
+--echo # XXX: used to be a bug
+execute stmt_sf;
+--echo # XXX: used to be a bug
+execute stmt_sp;
+#
+--echo # XXX: used to be a bug
+execute stmt_sf;
+--echo # XXX: used to be a bug
+execute stmt_sp;
+
+connection default;
diff --git a/mysql-test/r/ps_ddl.result b/mysql-test/r/ps_ddl.result
index 58542f23cf2..c7e8812320c 100644
--- a/mysql-test/r/ps_ddl.result
+++ b/mysql-test/r/ps_ddl.result
@@ -1567,11 +1567,13 @@ drop table if exists t_12093;
 drop function if exists f_12093;
 drop function if exists f_12093_unrelated;
 drop procedure if exists p_12093;
+drop view if exists v_12093_unrelated;
 create table t_12093 (a int);
 create function f_12093() returns int return (select count(*) from t_12093);
 create procedure p_12093(a int) select * from t_12093;
 create function f_12093_unrelated() returns int return 2;
 create procedure p_12093_unrelated() begin end;
+create view v_12093_unrelated as select * from t_12093;
 prepare stmt_sf from 'select f_12093();';
 prepare stmt_sp from 'call p_12093(f_12093())';
 execute stmt_sf;
@@ -1580,6 +1582,27 @@ f_12093()
 execute stmt_sp;
 a
 drop function f_12093_unrelated;
+# XXX: used to be a bug
+execute stmt_sf;
+f_12093()
+0
+# XXX: used to be a bug
+execute stmt_sp;
+a
+# XXX: used to be a bug
+execute stmt_sf;
+f_12093()
+0
+# XXX: used to be a bug
+execute stmt_sp;
+a
+prepare stmt_sf from 'select f_12093();';
+prepare stmt_sp from 'call p_12093(f_12093())';
+execute stmt_sf;
+f_12093()
+0
+execute stmt_sp;
+a
 drop procedure p_12093_unrelated;
 # XXX: used to be a bug
 execute stmt_sf;
@@ -1595,7 +1618,29 @@ f_12093()
 # XXX: used to be a bug
 execute stmt_sp;
 a
-call p_verify_reprepare_count(2);
+prepare stmt_sf from 'select f_12093();';
+prepare stmt_sp from 'call p_12093(f_12093())';
+execute stmt_sf;
+f_12093()
+0
+execute stmt_sp;
+a
+drop view v_12093_unrelated;
+# XXX: used to be a bug
+execute stmt_sf;
+f_12093()
+0
+# XXX: used to be a bug
+execute stmt_sp;
+a
+# XXX: used to be a bug
+execute stmt_sf;
+f_12093()
+0
+# XXX: used to be a bug
+execute stmt_sp;
+a
+call p_verify_reprepare_count(6);
 SUCCESS
 
 drop table t_12093;
diff --git a/mysql-test/t/ps_ddl.test b/mysql-test/t/ps_ddl.test
index 8899ca967ca..fee235cd36c 100644
--- a/mysql-test/t/ps_ddl.test
+++ b/mysql-test/t/ps_ddl.test
@@ -1363,44 +1363,34 @@ drop table if exists t_12093;
 drop function if exists f_12093;
 drop function if exists f_12093_unrelated;
 drop procedure if exists p_12093;
+drop view if exists v_12093_unrelated;
 --enable_warnings
 
 create table t_12093 (a int);
-
 create function f_12093() returns int return (select count(*) from t_12093);
-
 create procedure p_12093(a int) select * from t_12093;
 
 create function f_12093_unrelated() returns int return 2;
 create procedure p_12093_unrelated() begin end;
-
-prepare stmt_sf from 'select f_12093();';
-prepare stmt_sp from 'call p_12093(f_12093())';
-
-execute stmt_sf;
-execute stmt_sp;
+create view v_12093_unrelated as select * from t_12093;
 
 connect (con1,localhost,root,,);
-connection con1;
-
-drop function f_12093_unrelated;
-drop procedure p_12093_unrelated;
-
 connection default;
 
---echo # XXX: used to be a bug
-execute stmt_sf;
---echo # XXX: used to be a bug
-execute stmt_sp;
+let $my_drop = drop function f_12093_unrelated;
+--source include/ps_ddl_1.inc
+#
+let $my_drop = drop procedure p_12093_unrelated;
+--source include/ps_ddl_1.inc
+#
+# A reprepare of stmt_sf and stmt_sp is necessary because there is no
+# information about views within the table definition cache.
+let $my_drop = drop view v_12093_unrelated;
+--source include/ps_ddl_1.inc
 
---echo # XXX: used to be a bug
-execute stmt_sf;
---echo # XXX: used to be a bug
-execute stmt_sp;
-call p_verify_reprepare_count(2);
+call p_verify_reprepare_count(6);
 
 disconnect con1;
-
 drop table t_12093;
 drop function f_12093;
 drop procedure p_12093;