Add tests + modifications according to review

mysql-test/r/ps_ddl.result:
  Updated results
mysql-test/t/ps_ddl.test:
  - add missing subtests
  - add comments + minor reformatting for better overview on content
mysql-test/r/ps_ddl1.result:
  Expected results
mysql-test/t/ps_ddl1.test:
  Script for tests which do not really fit into ps_ddl

1 files changed, 168 insertions, 2 deletions

diff --git a/mysql-test/r/ps_ddl.result b/mysql-test/r/ps_ddl.result
index cca00dd4781..9e1db062368 100644
--- a/mysql-test/r/ps_ddl.result
+++ b/mysql-test/r/ps_ddl.result
@@ -35,6 +35,7 @@ Part 3: NOTHING -> VIEW transitions
 =====================================================================
 Part 4: TABLE -> NOTHING transitions
 =====================================================================
+# Test 4-a: select ... from <table>
 create table t1 (a int);
 prepare stmt from "select * from t1";
 execute stmt;
@@ -59,6 +60,32 @@ call p_verify_reprepare_count(0);
 SUCCESS
 
 deallocate prepare stmt;
+# Test 4-b: TABLE -> NOTHING by renaming the table
+create table t1 (a int);
+prepare stmt from "select * from t1";
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+rename table t1 to t2;
+execute stmt;
+ERROR 42S02: Table 'test.t1' doesn't exist
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+ERROR 42S02: Table 'test.t1' doesn't exist
+call p_verify_reprepare_count(0);
+SUCCESS
+
+deallocate prepare stmt;
+drop table t2;
 =====================================================================
 Part 5: TABLE -> TABLE (DDL) transitions
 =====================================================================
@@ -445,6 +472,7 @@ deallocate prepare stmt;
 =====================================================================
 Part 8: TABLE -> TEMPORARY TABLE transitions
 =====================================================================
+# Test 8-a: base table used recreated as temporary table
 create table t1 (a int);
 prepare stmt from "select * from t1";
 execute stmt;
@@ -463,6 +491,37 @@ SUCCESS
 
 drop table t1;
 deallocate prepare stmt;
+# Test 8-b: temporary table has precedence over base table with same name
+create table t1 (a int);
+prepare stmt from 'select count(*) from t1';
+execute stmt;
+count(*)
+0
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+count(*)
+0
+call p_verify_reprepare_count(0);
+SUCCESS
+
+create temporary table t1 AS SELECT 1;
+execute stmt;
+count(*)
+1
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+count(*)
+1
+call p_verify_reprepare_count(0);
+SUCCESS
+
+deallocate prepare stmt;
+drop temporary table t1;
+drop table t1;
 =====================================================================
 Part 9: TABLE -> VIEW transitions
 =====================================================================
@@ -504,6 +563,7 @@ deallocate prepare stmt;
 =====================================================================
 Part 11: TEMPORARY TABLE -> TABLE transitions
 =====================================================================
+# Test 11-a: temporary table replaced by base table
 create table t1 (a int);
 insert into t1 (a) value (1);
 create temporary table t1 (a int);
@@ -525,6 +585,38 @@ a
 1
 drop table t1;
 deallocate prepare stmt;
+# Test 11-b: temporary table has precedence over base table with same name
+#            temporary table disappears
+create table t1 (a int);
+create temporary table t1 as select 1 as a;
+prepare stmt from "select count(*) from t1";
+execute stmt;
+count(*)
+1
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+count(*)
+1
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop temporary table t1;
+execute stmt;
+count(*)
+0
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+count(*)
+0
+call p_verify_reprepare_count(0);
+SUCCESS
+
+deallocate prepare stmt;
+drop table t1;
 =====================================================================
 Part 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions
 =====================================================================
@@ -1505,11 +1597,11 @@ deallocate prepare stmt_sp;
 Ensure that metadata validation is performed for every type of
 SQL statement where it is needed.
 =====================================================================
-drop table if exists t1;
-create table t1 (a int);
 #
 # SQLCOM_SELECT
 #
+drop table if exists t1;
+create table t1 (a int);
 prepare stmt from "select 1 as res from dual where (1) in (select * from t1)";
 drop table t1;
 create table t1 (x int);
@@ -1568,6 +1660,18 @@ call p_verify_reprepare_count(0);
 SUCCESS
 
 drop table t2;
+create view t2 as select 1;
+execute stmt;
+Got one of the listed errors
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+Got one of the listed errors
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop view t2;
 drop table t1;
 create table t1 (x varchar(20));
 execute stmt;
@@ -1582,6 +1686,18 @@ call p_verify_reprepare_count(0);
 SUCCESS
 
 drop table t2;
+alter table t1 add column y decimal(10,3);
+execute stmt;
+call p_verify_reprepare_count(1);
+SUCCESS
+
+select * from t2;
+x	y
+drop table t2;
+execute stmt;
+call p_verify_reprepare_count(0);
+SUCCESS
+
 drop table t1;
 deallocate prepare stmt;
 # XXX: no validation of the first table in case of
@@ -1637,6 +1753,56 @@ call p_verify_reprepare_count(0);
 SUCCESS
 
 drop table t1;
+drop temporary table t2;
+drop table t2;
+deallocate prepare stmt;
+create table t1 (a int);
+prepare stmt from "create table t2 like t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t2;
+execute stmt;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t2;
+drop table t1;
+execute stmt;
+ERROR 42S02: Table 'test.t1' doesn't exist
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+ERROR 42S02: Table 'test.t1' doesn't exist
+call p_verify_reprepare_count(0);
+SUCCESS
+
+create table t1 (x char(17));
+execute stmt;
+call p_verify_reprepare_count(1);
+SUCCESS
+
+drop table t2;
+execute stmt;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t2;
+alter table t1 add column y time;
+execute stmt;
+call p_verify_reprepare_count(1);
+SUCCESS
+
+select * from t2;
+x	y
+drop table t2;
+execute stmt;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t1;
 drop table t2;
 deallocate prepare stmt;
 #