Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

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

1 files changed, 2296 insertions, 0 deletions

diff --git a/mysql-test/main/ps_ddl.test b/mysql-test/main/ps_ddl.test
new file mode 100644
index 00000000..e12fb11d
--- /dev/null
+++ b/mysql-test/main/ps_ddl.test
@@ -0,0 +1,2296 @@
+#
+# Testing the behavior of 'PREPARE', 'DDL', 'EXECUTE' scenarios
+#
+# Background:
+# In a statement like "select * from t1", t1 can be:
+# - nothing (the table does not exist)
+# - a real table
+# - a temporary table
+# - a view
+#
+# Changing the nature of "t1" between a PREPARE and an EXECUTE
+# can invalidate the internal state of a prepared statement, so that,
+# during the execute, the server should:
+# - detect state changes and fail to execute a statement,
+#   instead of crashing the server or returning wrong results
+# - "RE-PREPARE" the statement to restore a valid internal state.
+#
+# Also, changing the physical structure of "t1", by:
+# - changing the definition of t1 itself (DDL on tables, views)
+# - changing TRIGGERs associated with a table
+# - changing PROCEDURE, FUNCTION referenced by a TRIGGER body,
+# - changing PROCEDURE, FUNCTION referenced by a VIEW body,
+# impacts the internal structure of a prepared statement, and should
+# cause the same verifications at execute time to be performed.
+#
+# This test provided in this file cover the different state transitions
+# between a PREPARE and an EXECUTE, and are organized as follows:
+# - Part  1: NOTHING -> TABLE
+# - Part  2: NOTHING -> TEMPORARY TABLE
+# - Part  3: NOTHING -> VIEW
+# - Part  4: TABLE -> NOTHING
+# - Part  5: TABLE -> TABLE (DDL)
+# - Part  6: TABLE -> TABLE (TRIGGER)
+# - Part  7: TABLE -> TABLE (TRIGGER dependencies)
+# - Part  8: TABLE -> TEMPORARY TABLE
+# - Part  9: TABLE -> VIEW
+# - Part 10: TEMPORARY TABLE -> NOTHING
+# - Part 11: TEMPORARY TABLE -> TABLE
+# - Part 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL)
+# - Part 13: TEMPORARY TABLE -> VIEW
+# - Part 14: VIEW -> NOTHING
+# - Part 15: VIEW -> TABLE
+# - Part 16: VIEW -> TEMPORARY TABLE
+# - Part 17: VIEW -> VIEW (DDL)
+# - Part 18: VIEW -> VIEW (VIEW dependencies)
+# - Part 19: Special tables (INFORMATION_SCHEMA)
+# - Part 20: Special tables (log tables)
+# - Part 21: Special tables (system tables)
+# - Part 22: Special tables (views temp tables)
+# - Part 23: Special statements
+# - Part 24: Testing the strength of TABLE_SHARE version
+--disable_warnings
+drop temporary table if exists t1, t2, t3;
+drop table if exists t1, t2, t3;
+drop procedure if exists p_verify_reprepare_count;
+drop procedure if exists p1;
+drop function if exists f1;
+drop view if exists v1, v2;
+--enable_warnings
+
+# Avoid selecting from a huge table possibly left over from previous tests,
+# as this really hurts --valgrind testing.
+TRUNCATE TABLE mysql.general_log;
+
+delimiter |;
+--enable_prepare_warnings
+create procedure p_verify_reprepare_count(expected int)
+begin
+  declare old_reprepare_count int default @reprepare_count;
+
+  select variable_value from
+  information_schema.session_status where
+  variable_name='com_stmt_reprepare'
+  into @reprepare_count;
+
+  if old_reprepare_count + expected <> @reprepare_count then
+    select concat("Expected: ", expected,
+                   ", actual: ", @reprepare_count - old_reprepare_count)
+    as "ERROR";
+  else
+    select '' as "SUCCESS";
+  end if;
+end|
+--disable_prepare_warnings
+delimiter ;|
+set @reprepare_count= 0;
+flush status;
+
+--echo =====================================================================
+--echo Part 1: NOTHING -> TABLE transitions
+--echo =====================================================================
+
+# can not be tested since prepare failed
+--error ER_NO_SUCH_TABLE
+prepare stmt from "select * from t1";
+
+--echo =====================================================================
+--echo Part 2: NOTHING -> TEMPORARY TABLE transitions
+--echo =====================================================================
+
+# can not be tested
+
+--echo =====================================================================
+--echo Part 3: NOTHING -> VIEW transitions
+--echo =====================================================================
+
+# can not be tested
+
+--echo =====================================================================
+--echo Part 4: TABLE -> NOTHING transitions
+--echo =====================================================================
+
+--echo # Test 4-a: select ... from <table>
+create table t1 (a int);
+
+prepare stmt from "select * from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t1;
+--error ER_NO_SUCH_TABLE
+execute stmt;
+call p_verify_reprepare_count(0);
+--error ER_NO_SUCH_TABLE
+execute stmt;
+call p_verify_reprepare_count(0);
+deallocate prepare stmt;
+
+--echo # Test 4-b: TABLE -> NOTHING by renaming the table
+create table t1 (a int);
+prepare stmt from "select * from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+rename table t1 to t2;
+--error ER_NO_SUCH_TABLE
+execute stmt;
+call p_verify_reprepare_count(0);
+--error ER_NO_SUCH_TABLE
+execute stmt;
+call p_verify_reprepare_count(0);
+
+deallocate prepare stmt;
+drop table t2;
+
+--echo =====================================================================
+--echo Part 5: TABLE -> TABLE (DDL) transitions
+--echo =====================================================================
+
+create table t1 (a int);
+
+prepare stmt from "select a from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+alter table t1 add column (b int);
+
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t1;
+deallocate prepare stmt;
+
+
+--echo =====================================================================
+--echo Part 6: TABLE -> TABLE (TRIGGER) transitions
+--echo =====================================================================
+
+--echo # Test 6-a: adding a relevant trigger
+
+create table t1 (a int);
+
+prepare stmt from "insert into t1 (a) value (?)";
+set @val=1;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+
+# Relevant trigger: execute should reprepare
+create trigger t1_bi before insert on t1 for each row
+  set @message= new.a;
+
+set @val=2;
+execute stmt using @val;
+call p_verify_reprepare_count(1);
+select @message;
+set @val=3;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+select @message;
+
+prepare stmt from "insert into t1 (a) value (?)";
+set @val=4;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+select @message;
+
+--echo # Test 6-b: adding an irrelevant trigger
+
+# Unrelated trigger: reprepare may or may not happen, implementation dependent
+create trigger t1_bd before delete on t1 for each row
+  set @message= old.a;
+
+set @val=5;
+execute stmt using @val;
+call p_verify_reprepare_count(1);
+select @message;
+set @val=6;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+select @message;
+
+prepare stmt from "insert into t1 (a) value (?)";
+set @val=7;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+select @message;
+
+--echo # Test 6-c: changing a relevant trigger
+
+# Relevant trigger: execute should reprepare
+drop trigger t1_bi;
+create trigger t1_bi before insert on t1 for each row
+  set @message= concat("new trigger: ", new.a);
+
+set @val=8;
+execute stmt using @val;
+call p_verify_reprepare_count(1);
+select @message;
+set @val=9;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+select @message;
+
+prepare stmt from "insert into t1 (a) value (?)";
+set @val=10;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+select @message;
+
+--echo # Test 6-d: changing an irrelevant trigger
+
+# Unrelated trigger: reprepare may or may not happen, implementation dependent
+drop trigger t1_bd;
+
+set @val=11;
+execute stmt using @val;
+# No trigger in opened table => nothing to check => no reprepare
+call p_verify_reprepare_count(0);
+select @message;
+
+--echo Test 6-e: removing a relevant trigger
+
+drop trigger t1_bi;
+
+set @val=12;
+execute stmt using @val;
+# No trigger in opened table => nothing to check => no reprepare
+call p_verify_reprepare_count(0);
+select @message;
+set @val=13;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+select @message;
+
+prepare stmt from "insert into t1 (a) value (?)";
+set @val=14;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+select @message;
+
+select * from t1 order by a;
+drop table t1;
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 7: TABLE -> TABLE (TRIGGER dependencies) transitions
+--echo =====================================================================
+
+--echo # Test 7-a: dependent PROCEDURE has changed
+--echo #
+
+create table t1 (a int);
+create trigger t1_ai after insert on t1 for each row
+  call p1(new.a);
+create procedure p1(a int) begin end;
+prepare stmt from "insert into t1 (a) values (?)";
+set @var= 1;
+execute stmt using @var;
+drop procedure p1;
+create procedure p1 (a int) begin end;
+set @var= 2;
+execute stmt using @var;
+--echo # Cleanup
+drop procedure p1;
+call p_verify_reprepare_count(1);
+
+--echo # Test 7-b: dependent FUNCTION has changed
+--echo #
+--echo # Note, this scenario is supported, subject of Bug#12093
+--echo #
+drop trigger t1_ai;
+create trigger t1_ai after insert on t1 for  each row
+  select f1(new.a+1) into @var;
+create function f1 (a int) returns int return a;
+prepare stmt from "insert into t1(a) values (?)";
+set @var=3;
+execute stmt using @var;
+select @var;
+drop function f1;
+create function f1 (a int) returns int return 0;
+execute stmt using @var;
+call p_verify_reprepare_count(1);
+drop function f1;
+deallocate prepare stmt;
+
+--echo # Test 7-c: dependent VIEW has changed
+--echo #
+--echo # Note, this scenario is not functioning correctly, see
+--echo # Bug#33255 Trigger using views and view ddl : corrupted triggers
+--echo # and Bug #33000 Triggers do not detect changes in meta-data.
+--echo #
+drop trigger t1_ai;
+create table t2 (a int unique);
+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);
+
+--echo # Demonstrate that the same bug is present
+--echo # without 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
+insert into t1 (a) values (6);
+flush table t1;
+insert into t1 (a) values (6);
+select * from t2;
+select * from t3;
+
+prepare stmt from "insert into t1 (a) values (?)";
+set @var=7;
+execute stmt using @var;
+call p_verify_reprepare_count(0);
+select * from t3;
+select * from t2;
+drop view v1;
+create view v1 as select a from t2;
+set @var=8;
+--echo # View in the INSERT-statement in the trigger is still pointing to
+--echo # table 't3', because the trigger hasn't noticed the change
+--echo # in view definition. This will be fixed by WL#4179.
+--echo #
+--echo # The prepared INSERT-statement however does notice the change,
+--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 # (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
+execute stmt using @var;
+call p_verify_reprepare_count(1);
+select * from t2;
+select * from t3;
+flush table t1;
+set @var=9;
+execute stmt using @var;
+# flush tables now do not mean reprepare
+call p_verify_reprepare_count(0);
+select * from t2;
+select * from t3;
+drop view v1;
+drop table t1,t2,t3;
+
+--echo # Test 7-d: dependent TABLE has changed
+create table t1 (a int);
+create trigger t1_ai after insert on t1 for each row
+  insert into t2 (a) values (new.a);
+create table t2 (a int);
+
+prepare stmt from "insert into t1 (a) values (?)";
+set @var=1;
+execute stmt using @var;
+alter table t2 add column comment varchar(255);
+set @var=2;
+--echo # Since the dependent table is tracked in the prelocked
+--echo # list of the prepared statement, invalidation happens
+--echo # and the statement is re-prepared. This is an unnecessary
+--echo # side effect, since the statement that *is* dependent
+--echo # on t2 definition is inside the trigger, and it is currently
+--echo # not reprepared (see the previous test case).
+execute stmt using @var;
+call p_verify_reprepare_count(1);
+select * from t1;
+select * from t2;
+drop table t1,t2;
+
+--echo # Test 7-e: dependent TABLE TRIGGER has changed
+create table t1 (a int);
+create trigger t1_ai after insert on t1 for each row
+  insert into t2 (a) values (new.a);
+create table t2 (a int unique);
+create trigger t2_ai after insert on t2 for each row
+  insert into t3 (a) values (new.a);
+create table t3 (a int unique);
+create table t4 (a int unique);
+
+insert into t1 (a) values (1);
+select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.a=t3.a);
+drop trigger t2_ai;
+create trigger t2_ai after insert on t2 for each row
+  insert into t4 (a) values (new.a);
+insert into t1 (a) values (2);
+select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a);
+
+prepare stmt from "insert into t1 (a) values (?)";
+set @var=3;
+execute stmt using @var;
+select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a);
+drop trigger t2_ai;
+create trigger t2_ai after insert on t2 for each row
+  insert into t3 (a) values (new.a);
+set @var=4;
+execute stmt using @var;
+call p_verify_reprepare_count(1);
+select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.a=t3.a);
+select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a);
+
+drop table t1, t2, t3, t4;
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 8: TABLE -> TEMPORARY TABLE transitions
+--echo =====================================================================
+
+--echo # Test 8-a: base table used recreated as temporary table
+create table t1 (a int);
+
+prepare stmt from "select * from t1";
+execute stmt;
+
+drop table t1;
+create temporary table t1 (a int);
+
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t1;
+deallocate prepare stmt;
+
+--echo # 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;
+call p_verify_reprepare_count(0);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+create temporary table t1 AS SELECT 1;
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+deallocate prepare stmt;
+drop temporary table t1;
+drop table t1;
+
+
+--echo =====================================================================
+--echo Part 9: TABLE -> VIEW transitions
+--echo =====================================================================
+
+create table t1 (a int);
+
+prepare stmt from "select * from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t1;
+create table t2 (a int);
+create view t1 as select * from t2;
+
+execute stmt;
+call p_verify_reprepare_count(1);
+
+drop view t1;
+drop table t2;
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 10: TEMPORARY TABLE -> NOTHING transitions
+--echo =====================================================================
+
+create temporary table t1 (a int);
+
+prepare stmt from "select * from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop temporary table t1;
+--error ER_NO_SUCH_TABLE
+execute stmt;
+call p_verify_reprepare_count(0);
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 11: TEMPORARY TABLE -> TABLE transitions
+--echo =====================================================================
+
+--echo # 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);
+
+prepare stmt from "select * from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop temporary table t1;
+
+execute stmt;
+call p_verify_reprepare_count(1);
+
+select * from t1;
+drop table t1;
+deallocate prepare stmt;
+
+
+--echo # Test 11-b: temporary table has precedence over base table with same name
+--echo #            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;
+call p_verify_reprepare_count(0);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop temporary table t1;
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+deallocate prepare stmt;
+drop table t1;
+
+
+--echo =====================================================================
+--echo Part 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions
+--echo =====================================================================
+
+create temporary table t1 (a int);
+
+prepare stmt from "select a from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop temporary table t1;
+create temporary table t1 (a int, b int);
+
+execute stmt;
+call p_verify_reprepare_count(1);
+
+select * from t1;
+drop temporary table t1;
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 13: TEMPORARY TABLE -> VIEW transitions
+--echo =====================================================================
+
+create temporary table t1 (a int);
+create table t2 (a int);
+
+prepare stmt from "select * from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop temporary table t1;
+create view t1 as select * from t2;
+
+execute stmt;
+call p_verify_reprepare_count(1);
+
+drop view t1;
+drop table t2;
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 14: VIEW -> NOTHING transitions
+--echo =====================================================================
+
+create table t2 (a int);
+create view t1 as select * from t2;
+
+prepare stmt from "select * from t1";
+execute stmt;
+drop view t1;
+
+--error ER_NO_SUCH_TABLE
+execute stmt;
+call p_verify_reprepare_count(0);
+--error ER_NO_SUCH_TABLE
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t2;
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 15: VIEW -> TABLE transitions
+--echo =====================================================================
+
+create table t2 (a int);
+create view t1 as select * from t2;
+
+prepare stmt from "select * from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop view t1;
+create table t1 (a int);
+
+execute stmt;
+call p_verify_reprepare_count(1);
+
+drop table t2;
+drop table t1;
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 16: VIEW -> TEMPORARY TABLE transitions
+--echo =====================================================================
+
+--echo #
+--echo # Test 1: Merged view
+--echo #
+create table t2 (a int);
+insert into t2 (a) values (1);
+create view t1 as select * from t2;
+
+prepare stmt from "select * from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+
+create temporary table t1 (a int);
+# t1 still refers to the view - no reprepare has been done.
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop view t1;
+# t1 still refers to the, now deleted, view - no reprepare has been done.
+--error ER_NO_SUCH_TABLE
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t2;
+drop temporary table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # Test 2: Materialized view
+--echo #
+create table t2 (a int);
+insert into t2 (a) values (1);
+create algorithm = temptable view t1 as select * from t2;
+
+prepare stmt from "select * from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+
+create temporary table t1 (a int);
+# t1 still refers to the view - no reprepare has been done.
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop view t1;
+# t1 still refers to the, now deleted, view - no reprepare has been done.
+--error ER_NO_SUCH_TABLE
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t2;
+drop temporary table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # Test 3: View referencing an Information schema table
+--echo #
+create view t1 as select table_name from information_schema.views where table_schema <> 'sys' order by table_name;
+
+prepare stmt from "select * from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+
+create temporary table t1 (a int);
+# t1 has been substituted with a reference to the IS table
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop view t1;
+--error 1146
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop temporary table t1;
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 17: VIEW -> VIEW (DDL) transitions
+--echo =====================================================================
+
+create table t2 (a int);
+insert into t2 values (10), (20), (30);
+
+create view t1 as select a, 2*a as b, 3*a as c from t2;
+select * from t1;
+
+prepare stmt from "select * from t1";
+execute stmt;
+
+drop view t1;
+create view t1 as select a, 2*a as b, 5*a as c from t2;
+select * from t1;
+
+--echo # This is actually a test case for Bug#11748352 (36002 Prepared
+--echo # statements: if a view used in a statement is replaced, bad data).
+execute stmt;
+call p_verify_reprepare_count(1);
+
+flush table t2;
+
+execute stmt;
+call p_verify_reprepare_count(0);
+
+--echo # Check that we properly handle ALTER VIEW statements.
+execute stmt;
+call p_verify_reprepare_count(0);
+alter view t1 as select a, 3*a as b, 4*a as c from t2;
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+execute stmt;
+call p_verify_reprepare_count(0);
+select * from t1;
+
+--echo # Check that DROP & CREATE is properly handled under LOCK TABLES.
+drop view t1;
+flush tables; # empty TDC
+create view t1 as select a, 5*a as b, 6*a as c from t2;
+lock tables t1 read, t2 read;
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+execute stmt;
+call p_verify_reprepare_count(0);
+unlock tables;
+--echo #   ... and once again...
+drop view t1;
+create view t1 as select a, 6*a as b, 7*a as c from t2;
+lock tables t1 read, t2 read;
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+execute stmt;
+call p_verify_reprepare_count(0);
+unlock tables;
+
+--echo # Check that ALTER VIEW is properly handled under LOCK TABLES.
+alter view t1 as select a, 7*a as b, 8*a as c from t2;
+lock tables t1 read, t2 read;
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+execute stmt;
+call p_verify_reprepare_count(0);
+unlock tables;
+
+drop table t2;
+drop view t1;
+deallocate prepare stmt;
+
+--echo # Check that DROP & CREATE is properly handled under LOCK TABLES when
+--echo # LOCK TABLES does not contain the complete set of views.
+
+create table t1(a int);
+insert into t1 values (1), (2), (3);
+
+create view v1 as select a from t1;
+
+lock tables t1 read, v1 read;
+
+prepare stmt from 'select * from v1';
+
+execute stmt;
+call p_verify_reprepare_count(0);
+
+execute stmt;
+call p_verify_reprepare_count(0);
+
+unlock tables;
+
+drop view v1;
+create view v1 as select 2*a from t1;
+
+# Miss v1.
+lock tables t1 read;
+
+--error ER_TABLE_NOT_LOCKED
+execute stmt;
+
+unlock tables;
+
+drop table t1;
+drop view v1;
+deallocate prepare stmt;
+
+--echo # Check that ALTER VIEW is properly handled under LOCK TABLES when
+--echo # LOCK TABLES does not contain the complete set of views.
+
+create table t1(a int);
+insert into t1 values (1), (2), (3);
+
+create view v1 as select a from t1;
+
+lock tables t1 read, v1 read;
+
+prepare stmt from 'select * from v1';
+
+execute stmt;
+call p_verify_reprepare_count(0);
+
+execute stmt;
+call p_verify_reprepare_count(0);
+
+unlock tables;
+
+alter view v1 as select 2*a from t1;
+
+# Miss v1.
+lock tables t1 read;
+
+--error ER_TABLE_NOT_LOCKED
+execute stmt;
+
+unlock tables;
+
+drop table t1;
+drop view v1;
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 18: VIEW -> VIEW (VIEW dependencies) transitions
+--echo =====================================================================
+
+--echo # Part 18a: dependent function has changed
+create table t1 (a int);
+insert into t1 (a) values (1), (2), (3);
+create function f1() returns int return (select max(a) from t1);
+create view v1 as select f1();
+prepare stmt from "select * from v1";
+execute stmt;
+execute stmt;
+call p_verify_reprepare_count(0);
+drop function f1;
+create function f1() returns int return 2;
+--echo # XXX: Used to be another manifestation of Bug#12093.
+--echo # We only used to get a different error
+--echo # message because the non-existing procedure error is masked
+--echo # by the view.
+execute stmt;
+execute stmt;
+call p_verify_reprepare_count(1);
+
+--echo # Part 18b: dependent procedure has changed (referred to via a function)
+
+create table t2 (a int);
+insert into t2 (a) values (4), (5), (6);
+
+drop function f1;
+delimiter |;
+create function f1() returns int
+begin
+  declare x int;
+  call p1(x);
+  return x;
+end|
+delimiter ;|
+--enable_prepare_warnings
+create procedure p1(out x int) select max(a) from t1 into x;
+--disable_prepare_warnings
+
+prepare stmt from "select * from v1";
+execute stmt;
+execute stmt;
+call p_verify_reprepare_count(0);
+drop procedure p1;
+--enable_prepare_warnings
+create procedure p1(out x int) select max(a) from t2 into x;
+--disable_prepare_warnings
+--echo # XXX: used to be a bug. The prelocked list was not invalidated
+--echo # and we kept opening table t1, whereas the procedure
+--echo # is now referring to table t2
+execute stmt;
+call p_verify_reprepare_count(1);
+flush table t1;
+execute stmt;
+call p_verify_reprepare_count(0);
+execute stmt;
+
+--echo # Test 18-c: dependent VIEW has changed
+
+drop view v1;
+create view v2 as select a from t1;
+create view v1 as select * from v2;
+prepare stmt from "select * from v1";
+execute stmt;
+execute stmt;
+call p_verify_reprepare_count(0);
+drop view v2;
+create view v2 as select a from t2;
+execute stmt;
+execute stmt;
+call p_verify_reprepare_count(1);
+flush table t1;
+execute stmt;
+call p_verify_reprepare_count(0);
+execute stmt;
+--echo # Test 18-d: dependent TABLE has changed
+drop view v2;
+create table v2 as select * from t1;
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+drop table v2;
+create table v2 (a int unique) as select * from t2;
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+--echo # Test 18-e: dependent TABLE trigger has changed
+
+prepare stmt from "insert into v1 (a) values (?)";
+set @var= 7;
+execute stmt using @var;
+call p_verify_reprepare_count(0);
+create trigger v2_bi before insert on v2 for each row set @message="v2_bi";
+set @var=8;
+execute stmt using @var;
+call p_verify_reprepare_count(1);
+select @message;
+drop trigger v2_bi;
+set @message=null;
+set @var=9;
+execute stmt using @var;
+# No trigger in opened table => nothing to check => no reprepare
+call p_verify_reprepare_count(0);
+select @message;
+create trigger v2_bi after insert on v2 for each row set @message="v2_ai";
+set @var= 10;
+execute stmt using @var;
+call p_verify_reprepare_count(1);
+select @message;
+select * from v1;
+
+--echo # Cleanup
+
+--disable_warnings
+drop table if exists t1, t2, v1, v2;
+drop view if exists v1, v2;
+drop function f1;
+drop procedure p1;
+--enable_warnings
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 19: Special tables (INFORMATION_SCHEMA)
+--echo =====================================================================
+
+# Using a temporary table internally should not confuse the prepared
+# statement code, and should not raise ER_PS_INVALIDATED errors
+prepare stmt from
+ "select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
+ from INFORMATION_SCHEMA.ROUTINES where
+ routine_name='p1'";
+
+create procedure p1() select "hi there";
+
+execute stmt;
+execute stmt;
+
+drop procedure p1;
+create procedure p1() select "hi there, again";
+
+execute stmt;
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop procedure p1;
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 20: Special tables (log tables)
+--echo =====================================================================
+
+prepare stmt from
+ "select * from mysql.general_log where argument='IMPOSSIBLE QUERY STRING'";
+
+--disable_result_log
+execute stmt;
+execute stmt;
+execute stmt;
+execute stmt;
+--enable_result_log
+call p_verify_reprepare_count(0);
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 21: Special tables (system tables)
+--echo =====================================================================
+
+prepare stmt from
+ "select type, db, name from mysql.proc where name='p1'";
+
+create procedure p1() select "hi there";
+
+execute stmt;
+execute stmt;
+
+drop procedure p1;
+create procedure p1() select "hi there, again";
+
+execute stmt;
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop procedure p1;
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 22: Special tables (views temp tables)
+--echo =====================================================================
+
+create table t1 (a int);
+
+create algorithm=temptable view v1 as select a*a as a2 from t1;
+
+--echo # Using a temporary table internally should not confuse the prepared
+--echo # statement code, and should not raise ER_PS_INVALIDATED errors
+show create view v1;
+
+prepare stmt from "select * from v1";
+
+insert into t1 values (1), (2), (3);
+execute stmt;
+execute stmt;
+
+insert into t1 values (4), (5), (6);
+execute stmt;
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t1;
+drop view v1;
+
+--echo =====================================================================
+--echo Part 23: Special statements
+--echo =====================================================================
+
+--echo # SQLCOM_ALTER_TABLE:
+
+
+create table t1 (a int);
+
+prepare stmt from "alter table t1 add column b int";
+execute stmt;
+
+drop table t1;
+create table t1 (a1 int, a2 int);
+
+--echo # t1 has changed, and it's does not lead to reprepare
+execute stmt;
+
+alter table t1 drop column b;
+execute stmt;
+
+alter table t1 drop column b;
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t1;
+
+--echo # SQLCOM_REPAIR:
+
+create table t1 (a int);
+
+insert into t1 values (1), (2), (3);
+
+prepare stmt from "repair table t1";
+
+execute stmt;
+execute stmt;
+
+drop table t1;
+create table t1 (a1 int, a2 int);
+insert into t1 values (1, 10), (2, 20), (3, 30);
+
+--echo # t1 has changed, and it's does not lead to reprepare
+execute stmt;
+
+alter table t1 add column b varchar(50) default NULL;
+execute stmt;
+call p_verify_reprepare_count(0);
+
+alter table t1 drop column b;
+execute stmt;
+call p_verify_reprepare_count(0);
+
+--echo # SQLCOM_ANALYZE:
+
+prepare stmt from "analyze table t1";
+execute stmt;
+
+drop table t1;
+create table t1 (a1 int, a2 int);
+insert into t1 values (1, 10), (2, 20), (3, 30);
+--echo # t1 has changed, and it's not a problem
+execute stmt;
+
+alter table t1 add column b varchar(50) default NULL;
+execute stmt;
+
+alter table t1 drop column b;
+execute stmt;
+
+call p_verify_reprepare_count(0);
+
+--echo # SQLCOM_OPTIMIZE:
+
+prepare stmt from "optimize table t1";
+execute stmt;
+
+drop table t1;
+create table t1 (a1 int, a2 int);
+insert into t1 values (1, 10), (2, 20), (3, 30);
+
+--echo # t1 has changed, and it's not a problem
+execute stmt;
+
+alter table t1 add column b varchar(50) default NULL;
+execute stmt;
+
+alter table t1 drop column b;
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t1;
+
+--echo # SQLCOM_SHOW_CREATE_PROC:
+
+prepare stmt from "show create procedure p1";
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+
+create procedure p1() begin end;
+
+--disable_result_log
+execute stmt;
+execute stmt;
+--enable_result_log
+
+drop procedure p1;
+create procedure p1(x int, y int) begin end;
+
+--disable_result_log
+execute stmt;
+execute stmt;
+--enable_result_log
+
+drop procedure p1;
+
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+call p_verify_reprepare_count(0);
+
+--echo # SQLCOM_SHOW_CREATE_FUNC:
+
+prepare stmt from "show create function f1";
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+
+create function f1() returns int return 0;
+
+--disable_result_log
+execute stmt;
+execute stmt;
+--enable_result_log
+
+drop function f1;
+create function f1(x int, y int) returns int return x+y;
+
+--disable_result_log
+execute stmt;
+execute stmt;
+--enable_result_log
+
+drop function f1;
+
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+call p_verify_reprepare_count(0);
+
+--echo # SQLCOM_SHOW_CREATE_TRIGGER:
+
+create table t1 (a int);
+
+prepare stmt from "show create trigger t1_bi";
+--error ER_TRG_DOES_NOT_EXIST
+execute stmt;
+--error ER_TRG_DOES_NOT_EXIST
+execute stmt;
+
+create trigger t1_bi before insert on t1 for each row set @message= "t1_bi";
+
+--disable_result_log
+execute stmt;
+execute stmt;
+--enable_result_log
+
+drop trigger t1_bi;
+
+create trigger t1_bi before insert on t1 for each row set @message= "t1_bi (2)";
+
+--disable_result_log
+execute stmt;
+execute stmt;
+--enable_result_log
+
+drop trigger t1_bi;
+
+--error ER_TRG_DOES_NOT_EXIST
+execute stmt;
+--error ER_TRG_DOES_NOT_EXIST
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t1;
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Part 24: Testing the strength of TABLE_SHARE version
+--echo =====================================================================
+
+--echo # Test 24-a: number of columns
+
+create table t1 (a int);
+
+prepare stmt from "select a from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+
+alter table t1 add column b varchar(50) default NULL;
+
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+--echo # Test 24-b: column name
+
+alter table t1 change b c int;
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+--echo # Test 24-c: column type
+
+alter table t1 change a a varchar(10);
+
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+--echo # Test 24-d: column type length
+
+alter table t1 change a a varchar(20);
+
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+--echo # Test 24-e: column NULL property
+
+alter table t1 change a a varchar(20) NOT NULL;
+
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+--echo # Test 24-f: column DEFAULT
+
+alter table t1 change c c int DEFAULT 20;
+
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+--echo # Test 24-g: number of keys
+create unique index t1_a_idx on t1 (a);
+
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+--echo # Test 24-h: changing index uniqueness
+
+drop index t1_a_idx on t1;
+create index t1_a_idx on t1 (a);
+
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+--echo # Cleanup
+drop table t1;
+
+deallocate prepare stmt;
+
+--echo =====================================================================
+--echo Testing reported bugs
+--echo =====================================================================
+
+--echo #
+--echo # Bug#27420 A combination of PS and view operations cause
+--echo # error + assertion on shutdown
+--echo #
+
+--disable_warnings
+drop table if exists t_27420_100;
+drop table if exists t_27420_101;
+drop view if exists v_27420;
+--enable_warnings
+
+create table t_27420_100(a int);
+insert into t_27420_100 values (1), (2);
+
+create table t_27420_101(a int);
+insert into t_27420_101 values (1), (2);
+
+create view v_27420 as select t_27420_100.a X, t_27420_101.a Y
+  from t_27420_100, t_27420_101
+  where t_27420_100.a=t_27420_101.a;
+
+prepare stmt from "select * from v_27420";
+
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop view v_27420;
+create table v_27420(X int, Y int);
+
+execute stmt;
+call p_verify_reprepare_count(1);
+
+drop table v_27420;
+# passes in 5.0, fails in 5.1, should pass
+create table v_27420 (a int, b int, filler char(200));
+
+execute stmt;
+call p_verify_reprepare_count(1);
+
+drop table t_27420_100;
+drop table t_27420_101;
+drop table v_27420;
+deallocate prepare stmt;
+
+--echo #
+--echo # Bug#27430 Crash in subquery code when in PS and table DDL changed
+--echo # after PREPARE
+--echo #
+
+--disable_warnings
+drop table if exists t_27430_1;
+drop table if exists t_27430_2;
+--enable_warnings
+
+create table t_27430_1 (a int not null, oref int not null, key(a));
+insert into t_27430_1 values
+  (1, 1),
+  (1, 1234),
+  (2, 3),
+  (2, 1234),
+  (3, 1234);
+
+create table t_27430_2 (a int not null, oref int not null);
+insert into t_27430_2 values
+  (1, 1),
+  (2, 2),
+  (1234, 3),
+  (1234, 4);
+
+prepare stmt from
+ "select oref, a, a in (select a from t_27430_1 where oref=t_27430_2.oref) Z from t_27430_2";
+
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t_27430_1, t_27430_2;
+
+create table t_27430_1 (a int, oref int, key(a));
+insert into t_27430_1 values
+  (1, 1),
+  (1, NULL),
+  (2, 3),
+  (2, NULL),
+  (3, NULL);
+
+create table t_27430_2 (a int, oref int);
+insert into t_27430_2 values
+  (1, 1),
+  (2,2),
+  (NULL, 3),
+  (NULL, 4);
+
+execute stmt;
+call p_verify_reprepare_count(1);
+
+drop table t_27430_1;
+drop table t_27430_2;
+deallocate prepare stmt;
+
+--echo #
+--echo # Bug#27690 Re-execution of prepared statement after table
+--echo # was replaced with a view crashes
+--echo #
+
+--disable_warnings
+drop table if exists t_27690_1;
+drop view if exists v_27690_1;
+drop table if exists v_27690_2;
+--enable_warnings
+
+create table t_27690_1 (a int, b int);
+insert into t_27690_1 values (1,1),(2,2);
+
+create table v_27690_1 as select * from t_27690_1;
+create table v_27690_2 as select * from t_27690_1;
+
+prepare stmt from "select * from v_27690_1, v_27690_2";
+
+execute stmt;
+execute stmt;
+
+drop table v_27690_1;
+
+--error ER_NO_SUCH_TABLE
+execute stmt;
+
+--error ER_NO_SUCH_TABLE
+execute stmt;
+call p_verify_reprepare_count(0);
+
+create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B;
+
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t_27690_1;
+drop view v_27690_1;
+drop table v_27690_2;
+deallocate prepare stmt;
+
+--echo #=====================================================================
+--echo #
+--echo # Bug#21294 Executing a prepared statement that executes
+--echo # a stored function which was recreat
+--echo #
+
+create function f1() returns int return 10;
+
+prepare stmt from "select f1()";
+execute stmt;
+
+drop function f1;
+create function f1() returns int return 10;
+
+# might pass or fail, implementation dependent
+execute stmt;
+
+drop function f1;
+create function f1() returns int return 20;
+
+execute stmt;
+call p_verify_reprepare_count(2);
+
+drop function f1;
+deallocate prepare stmt;
+
+--echo #
+--echo # Bug#12093 SP not found on second PS execution if another thread drops
+--echo # other SP in between
+--echo #
+--disable_warnings
+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;
+create view v_12093_unrelated as select * from t_12093;
+
+connect (con1,localhost,root,,);
+connection default;
+
+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
+
+call p_verify_reprepare_count(6);
+
+disconnect con1;
+drop table t_12093;
+drop function f_12093;
+drop procedure p_12093;
+deallocate prepare stmt_sf;
+deallocate prepare stmt_sp;
+
+
+--echo =====================================================================
+--echo Ensure that metadata validation is performed for every type of
+--echo SQL statement where it is needed.
+--echo =====================================================================
+
+--echo #
+--echo # SQLCOM_SELECT
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+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);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+call p_verify_reprepare_count(1);
+
+--echo #
+--echo # SQLCOM_CREATE_TABLE
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+drop table if exists t2;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from 'create table t2 as select * from t1';
+execute stmt;
+drop table t2;
+execute stmt;
+drop table t2;
+execute stmt;
+call p_verify_reprepare_count(0);
+# Base table with name of table to be created exists
+--error ER_TABLE_EXISTS_ERROR
+execute stmt;
+call p_verify_reprepare_count(0);
+--error ER_TABLE_EXISTS_ERROR
+execute stmt;
+call p_verify_reprepare_count(0);
+drop table t2;
+# Temporary table with name of table to be created exists
+create temporary table t2 (a int);
+# Temporary table and base table are not in the same name space.
+execute stmt;
+call p_verify_reprepare_count(0);
+--error ER_TABLE_EXISTS_ERROR
+execute stmt;
+call p_verify_reprepare_count(0);
+drop temporary table t2;
+--error ER_TABLE_EXISTS_ERROR
+execute stmt;
+call p_verify_reprepare_count(0);
+drop table t2;
+execute stmt;
+call p_verify_reprepare_count(0);
+drop table t2;
+# View with name of table to be created exists
+# Attention:
+#    We cannot print the error message because it contains a random filename.
+#    Example: 1050: Table '<some_path>/var/tmp/#sql_6979_0' already exists
+#    Therefore we mangle it via
+#    "--error ER_TABLE_EXISTS_ERROR,9999" (9999 is currently not used)
+#    to "Got one of the listed errors".
+create view t2 as select 1;
+--error ER_TABLE_EXISTS_ERROR,9999
+execute stmt;
+call p_verify_reprepare_count(0);
+--error ER_TABLE_EXISTS_ERROR,9999
+execute stmt;
+call p_verify_reprepare_count(0);
+drop view t2;
+drop table t1;
+# Table to be used recreated (drop,create) with different layout
+create table t1 (x varchar(20));
+execute stmt;
+call p_verify_reprepare_count(1);
+select * from t2;
+drop table t2;
+execute stmt;
+call p_verify_reprepare_count(0);
+drop table t2;
+# Table to be used has a modified (alter table) layout
+alter table t1 add column y decimal(10,3);
+execute stmt;
+call p_verify_reprepare_count(1);
+select * from t2;
+drop table t2;
+execute stmt;
+call p_verify_reprepare_count(0);
+drop table t1;
+deallocate prepare stmt;
+create table t1 (a int);
+insert into t1 (a) values (1);
+#t2-temporary table, cannot create a temporary view
+--disable_view_protocol
+prepare stmt from "create temporary table if not exists t2 as select * from t1";
+execute stmt;
+drop table t2;
+execute stmt;
+call p_verify_reprepare_count(0);
+execute stmt;
+call p_verify_reprepare_count(1);
+select * from t2;
+execute stmt;
+call p_verify_reprepare_count(0);
+select * from t2;
+drop table t2;
+create temporary table t2 (a varchar(10));
+execute stmt;
+select * from t2;
+call p_verify_reprepare_count(1);
+drop table t1;
+create table t1 (x int);
+execute stmt;
+call p_verify_reprepare_count(1);
+execute stmt;
+call p_verify_reprepare_count(0);
+drop table t1;
+drop temporary table t2;
+drop table t2;
+deallocate prepare stmt;
+--enable_view_protocol
+
+create table t1 (a int);
+prepare stmt from "create table t2 like t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+drop table t2;
+execute stmt;
+call p_verify_reprepare_count(0);
+drop table t2;
+# Table to be used does not exist
+drop table t1;
+--error ER_NO_SUCH_TABLE
+execute stmt;
+call p_verify_reprepare_count(0);
+--error ER_NO_SUCH_TABLE
+execute stmt;
+call p_verify_reprepare_count(0);
+# Table to be used recreated (drop,create) with different layout
+create table t1 (x char(17));
+execute stmt;
+call p_verify_reprepare_count(1);
+drop table t2;
+execute stmt;
+call p_verify_reprepare_count(0);
+drop table t2;
+# Table to be used has a modified (alter table) layout
+alter table t1 add column y time;
+execute stmt;
+call p_verify_reprepare_count(1);
+select * from t2;
+drop table t2;
+execute stmt;
+call p_verify_reprepare_count(0);
+drop table t1;
+drop table t2;
+deallocate prepare stmt;
+
+
+--echo #
+--echo # SQLCOM_UPDATE
+--echo #
+
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+prepare stmt from "update t2 set a=a+1 where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1, t2;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_INSERT
+--echo #
+
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+prepare stmt from "insert into t2 set a=((1) in (select * from t1))";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+
+drop table t1, t2;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_INSERT_SELECT
+--echo #
+
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+prepare stmt from "insert into t2 select * from t1";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1, t2;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_REPLACE
+--echo #
+
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+prepare stmt from "replace t2 set a=((1) in (select * from t1))";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1, t2;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_REPLACE_SELECT
+--echo #
+
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+prepare stmt from "replace t2 select * from t1";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1, t2;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_DELETE
+--echo #
+
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+prepare stmt from "delete from t2 where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1, t2;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_DELETE_MULTI
+--echo #
+
+--disable_warnings
+drop table if exists t1, t2, t3;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+prepare stmt from "delete t2, t3 from t2, t3 where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1, t2, t3;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_UPDATE_MULTI
+--echo #
+
+--disable_warnings
+drop table if exists t1, t2, t3;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+prepare stmt from "update t2, t3 set t3.a=t2.a, t2.a=null where (1) in (select * from t1)";
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1, t2, t3;
+deallocate prepare stmt;
+--echo # Intermediate results: 8 SQLCOMs tested, 8 automatic reprepares
+call p_verify_reprepare_count(8);
+
+--echo #
+--echo # SQLCOM_LOAD
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a varchar(20));
+prepare stmt from "load data infile '../std_data_ln/words.dat' into table t1";
+drop table t1;
+
+--echo #
+--echo # SQLCOM_SHOW_DATABASES
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "show databases where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_SHOW_TABLES
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "show tables where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_SHOW_FIELDS
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "show fields from t1 where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_SHOW_KEYS
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "show keys from t1 where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_SHOW_VARIABLES
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "show variables where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_SHOW_STATUS
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "show status where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_SHOW_ENGINE_STATUS, SQLCOM_SHOW_ENGINE_LOGS,
+--echo # SQLCOM_SHOW_ENGINE_MUTEX, SQLCOM_SHOW_PROCESSLIST
+--echo #
+
+--echo # Currently can not have a where clause, need to be covered
+--echo # with tests
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+--error ER_PARSE_ERROR
+prepare stmt from "show engine all status where (1) in (select * from t1)";
+--error ER_PARSE_ERROR
+prepare stmt from "show engine all logs where (1) in (select * from t1)";
+--error ER_PARSE_ERROR
+prepare stmt from "show engine all mutex where (1) in (select * from t1)";
+--error ER_PARSE_ERROR
+prepare stmt from "show processlist where (1) in (select * from t1)";
+drop table t1;
+
+--echo #
+--echo # SQLCOM_SHOW_CHARSETS
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "show charset where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_SHOW_COLLATIONS
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "show collation where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_SHOW_TABLE_STATUS
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "show table status where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_SHOW_TRIGGERS
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "show triggers where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_SHOW_OPEN_TABLES
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "show open tables where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_SHOW_STATUS_PROC
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "show procedure status where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_SHOW_STATUS_FUNC
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "show function status where (1) in (select * from t1)";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_SHOW_EVENTS
+--echo #
+--echo #
+--echo # Please see this test in ps.test, it requires not_embedded.inc
+--echo #
+
+--echo #
+--echo # SQLCOM_SET_OPTION
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "set @a=((1) in (select * from t1))";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_DO
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "do ((1) in (select * from t1))";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_CALL
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+drop procedure if exists p1;
+--enable_warnings
+create procedure p1(a int) begin end;
+create table t1 (a int);
+prepare stmt from "call p1((1) in (select * from t1))";
+execute stmt;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+drop procedure p1;
+deallocate prepare stmt;
+
+--echo #
+--echo # SQLCOM_CREATE_VIEW
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+drop view if exists v1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "create view v1 as select * from t1";
+execute stmt;
+drop view v1;
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop view v1;
+drop table t1;
+deallocate prepare stmt;
+--echo # Intermediate result: number of reprepares matches the number
+--echo # of tests
+call p_verify_reprepare_count(17);
+
+--echo #
+--echo # SQLCOM_ALTER_VIEW
+--echo #
+
+--disable_warnings
+drop view if exists v1;
+--enable_warnings
+create view v1 as select 1;
+prepare stmt from "alter view v1 as select 2";
+drop view v1;
+
+--echo # Cleanup
+--echo #
+--disable_warnings
+drop temporary table if exists t1, t2, t3;
+drop table if exists t1, t2, t3, v1, v2;
+drop procedure if exists p_verify_reprepare_count;
+drop procedure if exists p1;
+drop function if exists f1;
+drop view if exists v1, v2;
+--enable_warnings
+
+
+--echo #
+--echo # Additional coverage for refactoring which was made as part of work
+--echo # on bug '27480: Extend CREATE TEMPORARY TABLES privilege to allow
+--echo # temp table operations'. 
+--echo #
+--echo # Check that we don't try to pre-open temporary tables for the elements
+--echo # from prelocking list, as this can lead to unwarranted ER_CANT_REOPEN
+--echo # errors.
+--disable_warnings ONCE
+DROP TABLE IF EXISTS t1, tm;
+CREATE TABLE t1 (a INT);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  SET @a:= (SELECT COUNT(*) FROM t1);
+--echo # Prelocking list for the below statement should
+--echo # contain t1 twice - once for the INSERT and once
+--echo # SELECT from the trigger.
+PREPARE stmt1 FROM 'INSERT INTO t1 VALUES (1)';
+EXECUTE stmt1;
+--echo # Create temporary table which will shadow t1.
+CREATE TEMPORARY TABLE t1 (b int);
+--echo # The below execution of statement should not fail with ER_CANT_REOPEN
+--echo # error. Instead stmt1 should be auto-matically reprepared and succeed.
+EXECUTE stmt1;
+DEALLOCATE PREPARE stmt1;
+DROP TEMPORARY TABLE t1;
+DROP TABLE t1;
+--echo #
+--echo # Also check that we properly reset table list elements from UNION
+--echo # clause of CREATE TABLE and ALTER TABLE statements.
+--echo #
+CREATE TEMPORARY TABLE t1 (i INT);
+PREPARE stmt2 FROM 'CREATE TEMPORARY TABLE tm (i INT) ENGINE=MERGE UNION=(t1)';
+EXECUTE stmt2;
+DROP TEMPORARY TABLE tm;
+EXECUTE stmt2;
+DEALLOCATE PREPARE stmt2;
+PREPARE stmt3 FROM 'ALTER TABLE tm UNION=(t1)';
+EXECUTE stmt3;
+EXECUTE stmt3;
+DEALLOCATE PREPARE stmt3;
+DROP TEMPORARY TABLES tm, t1;
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-10702 Crash in SET STATEMENT FOR EXECUTE
+--echo #
+CREATE TABLE t1 (a INT);
+PREPARE stmt FROM 'INSERT INTO t1 VALUES (@@max_sort_length)';
+SET STATEMENT max_sort_length=2048 FOR EXECUTE stmt;
+SELECT * FROM t1;
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=NEW.a + 1;
+SET STATEMENT max_sort_length=2048 FOR EXECUTE stmt;
+SELECT * FROM t1;
+DROP TRIGGER tr1;
+SET STATEMENT max_sort_length=2048 FOR EXECUTE stmt;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 10.1 tests
+--echo #