summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/ps_ddl.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/ps_ddl.result')
-rw-r--r--mysql-test/main/ps_ddl.result2595
1 files changed, 2595 insertions, 0 deletions
diff --git a/mysql-test/main/ps_ddl.result b/mysql-test/main/ps_ddl.result
new file mode 100644
index 00000000..dcbb6982
--- /dev/null
+++ b/mysql-test/main/ps_ddl.result
@@ -0,0 +1,2595 @@
+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;
+TRUNCATE TABLE mysql.general_log;
+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|
+Warnings:
+Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+set @reprepare_count= 0;
+flush status;
+=====================================================================
+Part 1: NOTHING -> TABLE transitions
+=====================================================================
+prepare stmt from "select * from t1";
+ERROR 42S02: Table 'test.t1' doesn't exist
+=====================================================================
+Part 2: NOTHING -> TEMPORARY TABLE transitions
+=====================================================================
+=====================================================================
+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;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+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
+
+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
+=====================================================================
+create table t1 (a int);
+prepare stmt from "select a from t1";
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+alter table t1 add column (b int);
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t1;
+deallocate prepare stmt;
+=====================================================================
+Part 6: TABLE -> TABLE (TRIGGER) transitions
+=====================================================================
+# 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);
+SUCCESS
+
+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);
+SUCCESS
+
+select @message;
+@message
+2
+set @val=3;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select @message;
+@message
+3
+prepare stmt from "insert into t1 (a) value (?)";
+set @val=4;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select @message;
+@message
+4
+# Test 6-b: adding an irrelevant trigger
+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);
+SUCCESS
+
+select @message;
+@message
+5
+set @val=6;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select @message;
+@message
+6
+prepare stmt from "insert into t1 (a) value (?)";
+set @val=7;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select @message;
+@message
+7
+# Test 6-c: changing a relevant trigger
+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);
+SUCCESS
+
+select @message;
+@message
+new trigger: 8
+set @val=9;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select @message;
+@message
+new trigger: 9
+prepare stmt from "insert into t1 (a) value (?)";
+set @val=10;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select @message;
+@message
+new trigger: 10
+# Test 6-d: changing an irrelevant trigger
+drop trigger t1_bd;
+set @val=11;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select @message;
+@message
+new trigger: 11
+Test 6-e: removing a relevant trigger
+drop trigger t1_bi;
+set @val=12;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select @message;
+@message
+new trigger: 11
+set @val=13;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select @message;
+@message
+new trigger: 11
+prepare stmt from "insert into t1 (a) value (?)";
+set @val=14;
+execute stmt using @val;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select @message;
+@message
+new trigger: 11
+select * from t1 order by a;
+a
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+drop table t1;
+deallocate prepare stmt;
+=====================================================================
+Part 7: TABLE -> TABLE (TRIGGER dependencies) transitions
+=====================================================================
+# Test 7-a: dependent PROCEDURE has changed
+#
+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;
+# Cleanup
+drop procedure p1;
+call p_verify_reprepare_count(1);
+SUCCESS
+
+# Test 7-b: dependent FUNCTION has changed
+#
+# Note, this scenario is supported, subject of Bug#12093
+#
+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;
+@var
+4
+drop function f1;
+create function f1 (a int) returns int return 0;
+execute stmt using @var;
+call p_verify_reprepare_count(1);
+SUCCESS
+
+drop function f1;
+deallocate prepare stmt;
+# Test 7-c: dependent VIEW has changed
+#
+# Note, this scenario is not functioning correctly, see
+# Bug#33255 Trigger using views and view ddl : corrupted triggers
+# and Bug #33000 Triggers do not detect changes in meta-data.
+#
+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);
+# Demonstrate that the same bug is present
+# without prepared statements
+insert into t1 (a) values (5);
+select * from t2;
+a
+5
+select * from t3;
+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);
+select * from t2;
+a
+5
+select * from t3;
+a
+6
+prepare stmt from "insert into t1 (a) values (?)";
+set @var=7;
+execute stmt using @var;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select * from t3;
+a
+6
+7
+select * from t2;
+a
+5
+drop view v1;
+create view v1 as select a from t2;
+set @var=8;
+# View in the INSERT-statement in the trigger is still pointing to
+# table 't3', because the trigger hasn't noticed the change
+# in view definition. This will be fixed by WL#4179.
+#
+# The prepared INSERT-statement however does notice the change,
+# 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
+# (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'.
+execute stmt using @var;
+ERROR 42S02: Table 'test.t3' doesn't exist
+call p_verify_reprepare_count(1);
+SUCCESS
+
+select * from t2;
+a
+5
+select * from t3;
+a
+6
+7
+flush table t1;
+set @var=9;
+execute stmt using @var;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select * from t2;
+a
+5
+9
+select * from t3;
+a
+6
+7
+drop view v1;
+drop table t1,t2,t3;
+# 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;
+# Since the dependent table is tracked in the prelocked
+# list of the prepared statement, invalidation happens
+# and the statement is re-prepared. This is an unnecessary
+# side effect, since the statement that *is* dependent
+# on t2 definition is inside the trigger, and it is currently
+# not reprepared (see the previous test case).
+execute stmt using @var;
+call p_verify_reprepare_count(1);
+SUCCESS
+
+select * from t1;
+a
+1
+2
+select * from t2;
+a comment
+1 NULL
+2 NULL
+drop table t1,t2;
+# 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);
+a a a
+1 1 1
+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);
+a a a
+2 2 2
+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);
+a a a
+2 2 2
+3 3 3
+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);
+SUCCESS
+
+select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.a=t3.a);
+a a a
+1 1 1
+4 4 4
+select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a);
+a a a
+2 2 2
+3 3 3
+drop table t1, t2, t3, t4;
+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;
+a
+drop table t1;
+create temporary table t1 (a int);
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+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
+=====================================================================
+create table t1 (a int);
+prepare stmt from "select * from t1";
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t1;
+create table t2 (a int);
+create view t1 as select * from t2;
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+drop view t1;
+drop table t2;
+deallocate prepare stmt;
+=====================================================================
+Part 10: TEMPORARY TABLE -> NOTHING transitions
+=====================================================================
+create temporary table t1 (a int);
+prepare stmt from "select * from t1";
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop temporary table t1;
+execute stmt;
+ERROR 42S02: Table 'test.t1' doesn't exist
+call p_verify_reprepare_count(0);
+SUCCESS
+
+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);
+prepare stmt from "select * from t1";
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop temporary table t1;
+execute stmt;
+a
+1
+call p_verify_reprepare_count(1);
+SUCCESS
+
+select * from t1;
+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
+=====================================================================
+create temporary table t1 (a int);
+prepare stmt from "select a from t1";
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop temporary table t1;
+create temporary table t1 (a int, b int);
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+select * from t1;
+a b
+drop temporary table t1;
+deallocate prepare stmt;
+=====================================================================
+Part 13: TEMPORARY TABLE -> VIEW transitions
+=====================================================================
+create temporary table t1 (a int);
+create table t2 (a int);
+prepare stmt from "select * from t1";
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop temporary table t1;
+create view t1 as select * from t2;
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+drop view t1;
+drop table t2;
+deallocate prepare stmt;
+=====================================================================
+Part 14: VIEW -> NOTHING transitions
+=====================================================================
+create table t2 (a int);
+create view t1 as select * from t2;
+prepare stmt from "select * from t1";
+execute stmt;
+a
+drop view 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
+
+drop table t2;
+deallocate prepare stmt;
+=====================================================================
+Part 15: VIEW -> TABLE transitions
+=====================================================================
+create table t2 (a int);
+create view t1 as select * from t2;
+prepare stmt from "select * from t1";
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop view t1;
+create table t1 (a int);
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+drop table t2;
+drop table t1;
+deallocate prepare stmt;
+=====================================================================
+Part 16: VIEW -> TEMPORARY TABLE transitions
+=====================================================================
+#
+# Test 1: Merged view
+#
+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;
+a
+1
+call p_verify_reprepare_count(0);
+SUCCESS
+
+create temporary table t1 (a int);
+execute stmt;
+a
+1
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop view t1;
+execute stmt;
+ERROR 42S02: Table 'test.t1' doesn't exist
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t2;
+drop temporary table t1;
+deallocate prepare stmt;
+#
+# Test 2: Materialized view
+#
+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;
+a
+1
+call p_verify_reprepare_count(0);
+SUCCESS
+
+create temporary table t1 (a int);
+execute stmt;
+a
+1
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop view t1;
+execute stmt;
+ERROR 42S02: Table 'test.t1' doesn't exist
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t2;
+drop temporary table t1;
+deallocate prepare stmt;
+#
+# Test 3: View referencing an Information schema table
+#
+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;
+table_name
+t1
+user
+call p_verify_reprepare_count(0);
+SUCCESS
+
+create temporary table t1 (a int);
+execute stmt;
+table_name
+t1
+user
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop view t1;
+execute stmt;
+ERROR 42S02: Table 'test.t1' doesn't exist
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop temporary table t1;
+deallocate prepare stmt;
+=====================================================================
+Part 17: VIEW -> VIEW (DDL) transitions
+=====================================================================
+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;
+a b c
+10 20 30
+20 40 60
+30 60 90
+prepare stmt from "select * from t1";
+execute stmt;
+a b c
+10 20 30
+20 40 60
+30 60 90
+drop view t1;
+create view t1 as select a, 2*a as b, 5*a as c from t2;
+select * from t1;
+a b c
+10 20 50
+20 40 100
+30 60 150
+# This is actually a test case for Bug#11748352 (36002 Prepared
+# statements: if a view used in a statement is replaced, bad data).
+execute stmt;
+a b c
+10 20 50
+20 40 100
+30 60 150
+call p_verify_reprepare_count(1);
+SUCCESS
+
+flush table t2;
+execute stmt;
+a b c
+10 20 50
+20 40 100
+30 60 150
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# Check that we properly handle ALTER VIEW statements.
+execute stmt;
+a b c
+10 20 50
+20 40 100
+30 60 150
+call p_verify_reprepare_count(0);
+SUCCESS
+
+alter view t1 as select a, 3*a as b, 4*a as c from t2;
+execute stmt;
+a b c
+10 30 40
+20 60 80
+30 90 120
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a b c
+10 30 40
+20 60 80
+30 90 120
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+a b c
+10 30 40
+20 60 80
+30 90 120
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select * from t1;
+a b c
+10 30 40
+20 60 80
+30 90 120
+# Check that DROP & CREATE is properly handled under LOCK TABLES.
+drop view t1;
+flush tables;
+create view t1 as select a, 5*a as b, 6*a as c from t2;
+lock tables t1 read, t2 read;
+execute stmt;
+a b c
+10 50 60
+20 100 120
+30 150 180
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a b c
+10 50 60
+20 100 120
+30 150 180
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+a b c
+10 50 60
+20 100 120
+30 150 180
+call p_verify_reprepare_count(0);
+SUCCESS
+
+unlock tables;
+# ... 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;
+a b c
+10 60 70
+20 120 140
+30 180 210
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a b c
+10 60 70
+20 120 140
+30 180 210
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+a b c
+10 60 70
+20 120 140
+30 180 210
+call p_verify_reprepare_count(0);
+SUCCESS
+
+unlock tables;
+# 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;
+a b c
+10 70 80
+20 140 160
+30 210 240
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a b c
+10 70 80
+20 140 160
+30 210 240
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+a b c
+10 70 80
+20 140 160
+30 210 240
+call p_verify_reprepare_count(0);
+SUCCESS
+
+unlock tables;
+drop table t2;
+drop view t1;
+deallocate prepare stmt;
+# Check that DROP & CREATE is properly handled under LOCK TABLES when
+# 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;
+a
+1
+2
+3
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+a
+1
+2
+3
+call p_verify_reprepare_count(0);
+SUCCESS
+
+unlock tables;
+drop view v1;
+create view v1 as select 2*a from t1;
+lock tables t1 read;
+execute stmt;
+ERROR HY000: Table 'v1' was not locked with LOCK TABLES
+unlock tables;
+drop table t1;
+drop view v1;
+deallocate prepare stmt;
+# Check that ALTER VIEW is properly handled under LOCK TABLES when
+# 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;
+a
+1
+2
+3
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+a
+1
+2
+3
+call p_verify_reprepare_count(0);
+SUCCESS
+
+unlock tables;
+alter view v1 as select 2*a from t1;
+lock tables t1 read;
+execute stmt;
+ERROR HY000: Table 'v1' was not locked with LOCK TABLES
+unlock tables;
+drop table t1;
+drop view v1;
+deallocate prepare stmt;
+=====================================================================
+Part 18: VIEW -> VIEW (VIEW dependencies) transitions
+=====================================================================
+# 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;
+f1()
+3
+execute stmt;
+f1()
+3
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop function f1;
+create function f1() returns int return 2;
+# XXX: Used to be another manifestation of Bug#12093.
+# We only used to get a different error
+# message because the non-existing procedure error is masked
+# by the view.
+execute stmt;
+f1()
+2
+execute stmt;
+f1()
+2
+call p_verify_reprepare_count(1);
+SUCCESS
+
+# 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;
+create function f1() returns int
+begin
+declare x int;
+call p1(x);
+return x;
+end|
+create procedure p1(out x int) select max(a) from t1 into x;
+Warnings:
+Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+prepare stmt from "select * from v1";
+execute stmt;
+f1()
+3
+execute stmt;
+f1()
+3
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop procedure p1;
+create procedure p1(out x int) select max(a) from t2 into x;
+Warnings:
+Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+# XXX: used to be a bug. The prelocked list was not invalidated
+# and we kept opening table t1, whereas the procedure
+# is now referring to table t2
+execute stmt;
+f1()
+6
+call p_verify_reprepare_count(1);
+SUCCESS
+
+flush table t1;
+execute stmt;
+f1()
+6
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+f1()
+6
+# 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;
+a
+1
+2
+3
+execute stmt;
+a
+1
+2
+3
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop view v2;
+create view v2 as select a from t2;
+execute stmt;
+a
+4
+5
+6
+execute stmt;
+a
+4
+5
+6
+call p_verify_reprepare_count(1);
+SUCCESS
+
+flush table t1;
+execute stmt;
+a
+4
+5
+6
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+a
+4
+5
+6
+# Test 18-d: dependent TABLE has changed
+drop view v2;
+create table v2 as select * from t1;
+execute stmt;
+a
+1
+2
+3
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a
+1
+2
+3
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table v2;
+create table v2 (a int unique) as select * from t2;
+execute stmt;
+a
+4
+5
+6
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a
+4
+5
+6
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# 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);
+SUCCESS
+
+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);
+SUCCESS
+
+select @message;
+@message
+v2_bi
+drop trigger v2_bi;
+set @message=null;
+set @var=9;
+execute stmt using @var;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select @message;
+@message
+NULL
+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);
+SUCCESS
+
+select @message;
+@message
+v2_ai
+select * from v1;
+a
+4
+5
+6
+7
+8
+9
+10
+# Cleanup
+drop table if exists t1, t2, v1, v2;
+drop view if exists v1, v2;
+drop function f1;
+drop procedure p1;
+deallocate prepare stmt;
+=====================================================================
+Part 19: Special tables (INFORMATION_SCHEMA)
+=====================================================================
+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;
+ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE
+test p1 PROCEDURE
+execute stmt;
+ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE
+test p1 PROCEDURE
+drop procedure p1;
+create procedure p1() select "hi there, again";
+execute stmt;
+ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE
+test p1 PROCEDURE
+execute stmt;
+ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE
+test p1 PROCEDURE
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop procedure p1;
+deallocate prepare stmt;
+=====================================================================
+Part 20: Special tables (log tables)
+=====================================================================
+prepare stmt from
+"select * from mysql.general_log where argument='IMPOSSIBLE QUERY STRING'";
+execute stmt;
+execute stmt;
+execute stmt;
+execute stmt;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+deallocate prepare stmt;
+=====================================================================
+Part 21: Special tables (system tables)
+=====================================================================
+prepare stmt from
+"select type, db, name from mysql.proc where name='p1'";
+create procedure p1() select "hi there";
+execute stmt;
+type db name
+PROCEDURE test p1
+execute stmt;
+type db name
+PROCEDURE test p1
+drop procedure p1;
+create procedure p1() select "hi there, again";
+execute stmt;
+type db name
+PROCEDURE test p1
+execute stmt;
+type db name
+PROCEDURE test p1
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop procedure p1;
+deallocate prepare stmt;
+=====================================================================
+Part 22: Special tables (views temp tables)
+=====================================================================
+create table t1 (a int);
+create algorithm=temptable view v1 as select a*a as a2 from t1;
+# Using a temporary table internally should not confuse the prepared
+# statement code, and should not raise ER_PS_INVALIDATED errors
+show create view v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` * `t1`.`a` AS `a2` from `t1` latin1 latin1_swedish_ci
+prepare stmt from "select * from v1";
+insert into t1 values (1), (2), (3);
+execute stmt;
+a2
+1
+4
+9
+execute stmt;
+a2
+1
+4
+9
+insert into t1 values (4), (5), (6);
+execute stmt;
+a2
+1
+4
+9
+16
+25
+36
+execute stmt;
+a2
+1
+4
+9
+16
+25
+36
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t1;
+drop view v1;
+=====================================================================
+Part 23: Special statements
+=====================================================================
+# 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);
+# 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);
+SUCCESS
+
+drop table t1;
+# SQLCOM_REPAIR:
+create table t1 (a int);
+insert into t1 values (1), (2), (3);
+prepare stmt from "repair table t1";
+execute stmt;
+Table Op Msg_type Msg_text
+test.t1 repair status OK
+execute stmt;
+Table Op Msg_type Msg_text
+test.t1 repair status OK
+drop table t1;
+create table t1 (a1 int, a2 int);
+insert into t1 values (1, 10), (2, 20), (3, 30);
+# t1 has changed, and it's does not lead to reprepare
+execute stmt;
+Table Op Msg_type Msg_text
+test.t1 repair status OK
+alter table t1 add column b varchar(50) default NULL;
+execute stmt;
+Table Op Msg_type Msg_text
+test.t1 repair status OK
+call p_verify_reprepare_count(0);
+SUCCESS
+
+alter table t1 drop column b;
+execute stmt;
+Table Op Msg_type Msg_text
+test.t1 repair status OK
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# SQLCOM_ANALYZE:
+prepare stmt from "analyze table t1";
+execute stmt;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+drop table t1;
+create table t1 (a1 int, a2 int);
+insert into t1 values (1, 10), (2, 20), (3, 30);
+# t1 has changed, and it's not a problem
+execute stmt;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+alter table t1 add column b varchar(50) default NULL;
+execute stmt;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+alter table t1 drop column b;
+execute stmt;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# SQLCOM_OPTIMIZE:
+prepare stmt from "optimize table t1";
+execute stmt;
+Table Op Msg_type Msg_text
+test.t1 optimize status Table is already up to date
+drop table t1;
+create table t1 (a1 int, a2 int);
+insert into t1 values (1, 10), (2, 20), (3, 30);
+# t1 has changed, and it's not a problem
+execute stmt;
+Table Op Msg_type Msg_text
+test.t1 optimize status OK
+alter table t1 add column b varchar(50) default NULL;
+execute stmt;
+Table Op Msg_type Msg_text
+test.t1 optimize status OK
+alter table t1 drop column b;
+execute stmt;
+Table Op Msg_type Msg_text
+test.t1 optimize status OK
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t1;
+# SQLCOM_SHOW_CREATE_PROC:
+prepare stmt from "show create procedure p1";
+execute stmt;
+ERROR 42000: PROCEDURE p1 does not exist
+execute stmt;
+ERROR 42000: PROCEDURE p1 does not exist
+create procedure p1() begin end;
+execute stmt;
+execute stmt;
+drop procedure p1;
+create procedure p1(x int, y int) begin end;
+execute stmt;
+execute stmt;
+drop procedure p1;
+execute stmt;
+ERROR 42000: PROCEDURE p1 does not exist
+execute stmt;
+ERROR 42000: PROCEDURE p1 does not exist
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# SQLCOM_SHOW_CREATE_FUNC:
+prepare stmt from "show create function f1";
+execute stmt;
+ERROR 42000: FUNCTION f1 does not exist
+execute stmt;
+ERROR 42000: FUNCTION f1 does not exist
+create function f1() returns int return 0;
+execute stmt;
+execute stmt;
+drop function f1;
+create function f1(x int, y int) returns int return x+y;
+execute stmt;
+execute stmt;
+drop function f1;
+execute stmt;
+ERROR 42000: FUNCTION f1 does not exist
+execute stmt;
+ERROR 42000: FUNCTION f1 does not exist
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# SQLCOM_SHOW_CREATE_TRIGGER:
+create table t1 (a int);
+prepare stmt from "show create trigger t1_bi";
+execute stmt;
+ERROR HY000: Trigger does not exist
+execute stmt;
+ERROR HY000: Trigger does not exist
+create trigger t1_bi before insert on t1 for each row set @message= "t1_bi";
+execute stmt;
+execute stmt;
+drop trigger t1_bi;
+create trigger t1_bi before insert on t1 for each row set @message= "t1_bi (2)";
+execute stmt;
+execute stmt;
+drop trigger t1_bi;
+execute stmt;
+ERROR HY000: Trigger does not exist
+execute stmt;
+ERROR HY000: Trigger does not exist
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t1;
+deallocate prepare stmt;
+=====================================================================
+Part 24: Testing the strength of TABLE_SHARE version
+=====================================================================
+# Test 24-a: number of columns
+create table t1 (a int);
+prepare stmt from "select a from t1";
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+alter table t1 add column b varchar(50) default NULL;
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# Test 24-b: column name
+alter table t1 change b c int;
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# Test 24-c: column type
+alter table t1 change a a varchar(10);
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# Test 24-d: column type length
+alter table t1 change a a varchar(20);
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# Test 24-e: column NULL property
+alter table t1 change a a varchar(20) NOT NULL;
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# Test 24-f: column DEFAULT
+alter table t1 change c c int DEFAULT 20;
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# Test 24-g: number of keys
+create unique index t1_a_idx on t1 (a);
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# Test 24-h: changing index uniqueness
+drop index t1_a_idx on t1;
+create index t1_a_idx on t1 (a);
+execute stmt;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a
+call p_verify_reprepare_count(0);
+SUCCESS
+
+# Cleanup
+drop table t1;
+deallocate prepare stmt;
+=====================================================================
+Testing reported bugs
+=====================================================================
+#
+# Bug#27420 A combination of PS and view operations cause
+# error + assertion on shutdown
+#
+drop table if exists t_27420_100;
+drop table if exists t_27420_101;
+drop view if exists v_27420;
+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;
+X Y
+1 1
+2 2
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop view v_27420;
+create table v_27420(X int, Y int);
+execute stmt;
+X Y
+call p_verify_reprepare_count(1);
+SUCCESS
+
+drop table v_27420;
+create table v_27420 (a int, b int, filler char(200));
+execute stmt;
+a b filler
+call p_verify_reprepare_count(1);
+SUCCESS
+
+drop table t_27420_100;
+drop table t_27420_101;
+drop table v_27420;
+deallocate prepare stmt;
+#
+# Bug#27430 Crash in subquery code when in PS and table DDL changed
+# after PREPARE
+#
+drop table if exists t_27430_1;
+drop table if exists t_27430_2;
+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;
+oref a Z
+1 1 1
+2 2 0
+3 1234 0
+4 1234 0
+call p_verify_reprepare_count(0);
+SUCCESS
+
+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;
+oref a Z
+1 1 1
+2 2 0
+3 NULL NULL
+4 NULL 0
+call p_verify_reprepare_count(1);
+SUCCESS
+
+drop table t_27430_1;
+drop table t_27430_2;
+deallocate prepare stmt;
+#
+# Bug#27690 Re-execution of prepared statement after table
+# was replaced with a view crashes
+#
+drop table if exists t_27690_1;
+drop view if exists v_27690_1;
+drop table if exists v_27690_2;
+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;
+a b a b
+1 1 1 1
+2 2 1 1
+1 1 2 2
+2 2 2 2
+execute stmt;
+a b a b
+1 1 1 1
+2 2 1 1
+1 1 2 2
+2 2 2 2
+drop table v_27690_1;
+execute stmt;
+ERROR 42S02: Table 'test.v_27690_1' doesn't exist
+execute stmt;
+ERROR 42S02: Table 'test.v_27690_1' doesn't exist
+call p_verify_reprepare_count(0);
+SUCCESS
+
+create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B;
+execute stmt;
+a b a b
+1 1 1 1
+2 2 1 1
+1 1 1 1
+2 2 1 1
+1 1 2 2
+2 2 2 2
+1 1 2 2
+2 2 2 2
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+a b a b
+1 1 1 1
+2 2 1 1
+1 1 1 1
+2 2 1 1
+1 1 2 2
+2 2 2 2
+1 1 2 2
+2 2 2 2
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t_27690_1;
+drop view v_27690_1;
+drop table v_27690_2;
+deallocate prepare stmt;
+#=====================================================================
+#
+# Bug#21294 Executing a prepared statement that executes
+# a stored function which was recreat
+#
+create function f1() returns int return 10;
+prepare stmt from "select f1()";
+execute stmt;
+f1()
+10
+drop function f1;
+create function f1() returns int return 10;
+execute stmt;
+f1()
+10
+drop function f1;
+create function f1() returns int return 20;
+execute stmt;
+f1()
+20
+call p_verify_reprepare_count(2);
+SUCCESS
+
+drop function f1;
+deallocate prepare stmt;
+#
+# Bug#12093 SP not found on second PS execution if another thread drops
+# other SP in between
+#
+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;
+connect con1,localhost,root,,;
+connection default;
+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
+connection con1;
+drop function f_12093_unrelated;
+connection default;
+# 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
+connection default;
+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
+connection con1;
+drop procedure p_12093_unrelated;
+connection default;
+# 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
+connection default;
+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
+connection con1;
+drop view v_12093_unrelated;
+connection default;
+# 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
+connection default;
+call p_verify_reprepare_count(6);
+SUCCESS
+
+disconnect con1;
+drop table t_12093;
+drop function f_12093;
+drop procedure p_12093;
+deallocate prepare stmt_sf;
+deallocate prepare stmt_sp;
+=====================================================================
+Ensure that metadata validation is performed for every type of
+SQL statement where it is needed.
+=====================================================================
+#
+# 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);
+execute stmt;
+res
+drop table t1;
+deallocate prepare stmt;
+call p_verify_reprepare_count(1);
+SUCCESS
+
+#
+# SQLCOM_CREATE_TABLE
+#
+drop table if exists t1;
+drop table if exists t2;
+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);
+SUCCESS
+
+execute stmt;
+ERROR 42S01: Table 't2' already exists
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+ERROR 42S01: Table 't2' already exists
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t2;
+create temporary table t2 (a int);
+execute stmt;
+call p_verify_reprepare_count(0);
+SUCCESS
+
+execute stmt;
+ERROR 42S01: Table 't2' already exists
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop temporary table t2;
+execute stmt;
+ERROR 42S01: Table 't2' already exists
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t2;
+execute stmt;
+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(0);
+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;
+call p_verify_reprepare_count(1);
+SUCCESS
+
+select * from t2;
+x
+drop table t2;
+execute stmt;
+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;
+create table t1 (a int);
+insert into t1 (a) values (1);
+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);
+SUCCESS
+
+execute stmt;
+Warnings:
+Note 1050 Table 't2' already exists
+call p_verify_reprepare_count(1);
+SUCCESS
+
+select * from t2;
+a
+1
+execute stmt;
+Warnings:
+Note 1050 Table 't2' already exists
+call p_verify_reprepare_count(0);
+SUCCESS
+
+select * from t2;
+a
+1
+drop table t2;
+create temporary table t2 (a varchar(10));
+execute stmt;
+Warnings:
+Note 1050 Table 't2' already exists
+select * from t2;
+a
+call p_verify_reprepare_count(1);
+SUCCESS
+
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Warnings:
+Note 1050 Table 't2' already exists
+call p_verify_reprepare_count(1);
+SUCCESS
+
+execute stmt;
+Warnings:
+Note 1050 Table 't2' already exists
+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;
+#
+# SQLCOM_UPDATE
+#
+drop table if exists t1, t2;
+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;
+#
+# SQLCOM_INSERT
+#
+drop table if exists t1, t2;
+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;
+#
+# SQLCOM_INSERT_SELECT
+#
+drop table if exists t1, t2;
+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;
+#
+# SQLCOM_REPLACE
+#
+drop table if exists t1, t2;
+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;
+#
+# SQLCOM_REPLACE_SELECT
+#
+drop table if exists t1, t2;
+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;
+#
+# SQLCOM_DELETE
+#
+drop table if exists t1, t2;
+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;
+#
+# SQLCOM_DELETE_MULTI
+#
+drop table if exists t1, t2, t3;
+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;
+#
+# SQLCOM_UPDATE_MULTI
+#
+drop table if exists t1, t2, t3;
+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;
+# Intermediate results: 8 SQLCOMs tested, 8 automatic reprepares
+call p_verify_reprepare_count(8);
+SUCCESS
+
+#
+# SQLCOM_LOAD
+#
+drop table if exists t1;
+create table t1 (a varchar(20));
+prepare stmt from "load data infile '../std_data_ln/words.dat' into table t1";
+drop table t1;
+#
+# SQLCOM_SHOW_DATABASES
+#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show databases where (1) in (select * from t1)";
+execute stmt;
+Database
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Database
+drop table t1;
+deallocate prepare stmt;
+#
+# SQLCOM_SHOW_TABLES
+#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show tables where (1) in (select * from t1)";
+execute stmt;
+Tables_in_test
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Tables_in_test
+drop table t1;
+deallocate prepare stmt;
+#
+# SQLCOM_SHOW_FIELDS
+#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show fields from t1 where (1) in (select * from t1)";
+execute stmt;
+Field Type Null Key Default Extra
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Field Type Null Key Default Extra
+drop table t1;
+deallocate prepare stmt;
+#
+# SQLCOM_SHOW_KEYS
+#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show keys from t1 where (1) in (select * from t1)";
+execute stmt;
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
+drop table t1;
+deallocate prepare stmt;
+#
+# SQLCOM_SHOW_VARIABLES
+#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show variables where (1) in (select * from t1)";
+execute stmt;
+Variable_name Value
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Variable_name Value
+drop table t1;
+deallocate prepare stmt;
+#
+# SQLCOM_SHOW_STATUS
+#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show status where (1) in (select * from t1)";
+execute stmt;
+Variable_name Value
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Variable_name Value
+drop table t1;
+deallocate prepare stmt;
+#
+# SQLCOM_SHOW_ENGINE_STATUS, SQLCOM_SHOW_ENGINE_LOGS,
+# SQLCOM_SHOW_ENGINE_MUTEX, SQLCOM_SHOW_PROCESSLIST
+#
+# Currently can not have a where clause, need to be covered
+# with tests
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show engine all status where (1) in (select * from t1)";
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1
+prepare stmt from "show engine all logs where (1) in (select * from t1)";
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1
+prepare stmt from "show engine all mutex where (1) in (select * from t1)";
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1
+prepare stmt from "show processlist where (1) in (select * from t1)";
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1
+drop table t1;
+#
+# SQLCOM_SHOW_CHARSETS
+#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show charset where (1) in (select * from t1)";
+execute stmt;
+Charset Description Default collation Maxlen
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Charset Description Default collation Maxlen
+drop table t1;
+deallocate prepare stmt;
+#
+# SQLCOM_SHOW_COLLATIONS
+#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show collation where (1) in (select * from t1)";
+execute stmt;
+Collation Charset Id Default Compiled Sortlen
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Collation Charset Id Default Compiled Sortlen
+drop table t1;
+deallocate prepare stmt;
+#
+# SQLCOM_SHOW_TABLE_STATUS
+#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show table status where (1) in (select * from t1)";
+execute stmt;
+Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
+drop table t1;
+deallocate prepare stmt;
+#
+# SQLCOM_SHOW_TRIGGERS
+#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show triggers where (1) in (select * from t1)";
+execute stmt;
+Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
+drop table t1;
+deallocate prepare stmt;
+#
+# SQLCOM_SHOW_OPEN_TABLES
+#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show open tables where (1) in (select * from t1)";
+execute stmt;
+Database Table In_use Name_locked
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Database Table In_use Name_locked
+drop table t1;
+deallocate prepare stmt;
+#
+# SQLCOM_SHOW_STATUS_PROC
+#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show procedure status where (1) in (select * from t1)";
+execute stmt;
+Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
+drop table t1;
+deallocate prepare stmt;
+#
+# SQLCOM_SHOW_STATUS_FUNC
+#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "show function status where (1) in (select * from t1)";
+execute stmt;
+Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
+drop table t1;
+create table t1 (x int);
+execute stmt;
+Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
+drop table t1;
+deallocate prepare stmt;
+#
+# SQLCOM_SHOW_EVENTS
+#
+#
+# Please see this test in ps.test, it requires not_embedded.inc
+#
+#
+# SQLCOM_SET_OPTION
+#
+drop table if exists t1;
+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;
+#
+# SQLCOM_DO
+#
+drop table if exists t1;
+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;
+#
+# SQLCOM_CALL
+#
+drop table if exists t1;
+drop procedure if exists p1;
+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;
+#
+# SQLCOM_CREATE_VIEW
+#
+drop table if exists t1;
+drop view if exists v1;
+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;
+# Intermediate result: number of reprepares matches the number
+# of tests
+call p_verify_reprepare_count(17);
+SUCCESS
+
+#
+# SQLCOM_ALTER_VIEW
+#
+drop view if exists v1;
+create view v1 as select 1;
+prepare stmt from "alter view v1 as select 2";
+drop view v1;
+# Cleanup
+#
+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;
+#
+# Additional coverage for refactoring which was made as part of work
+# on bug '27480: Extend CREATE TEMPORARY TABLES privilege to allow
+# temp table operations'.
+#
+# Check that we don't try to pre-open temporary tables for the elements
+# from prelocking list, as this can lead to unwarranted ER_CANT_REOPEN
+# errors.
+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);
+# Prelocking list for the below statement should
+# contain t1 twice - once for the INSERT and once
+# SELECT from the trigger.
+PREPARE stmt1 FROM 'INSERT INTO t1 VALUES (1)';
+EXECUTE stmt1;
+# Create temporary table which will shadow t1.
+CREATE TEMPORARY TABLE t1 (b int);
+# The below execution of statement should not fail with ER_CANT_REOPEN
+# error. Instead stmt1 should be auto-matically reprepared and succeed.
+EXECUTE stmt1;
+DEALLOCATE PREPARE stmt1;
+DROP TEMPORARY TABLE t1;
+DROP TABLE t1;
+#
+# Also check that we properly reset table list elements from UNION
+# clause of CREATE TABLE and ALTER TABLE statements.
+#
+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;
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-10702 Crash in SET STATEMENT FOR EXECUTE
+#
+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;
+a
+2048
+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;
+a
+2048
+1025
+DROP TRIGGER tr1;
+SET STATEMENT max_sort_length=2048 FOR EXECUTE stmt;
+SELECT * FROM t1;
+a
+2048
+1025
+2048
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-22 20:03:34 +0000