diff options
Diffstat (limited to 'mysql-test/main/ps_ddl.test')
| -rw-r--r-- | mysql-test/main/ps_ddl.test | 2285 |
1 files changed, 2285 insertions, 0 deletions
diff --git a/mysql-test/main/ps_ddl.test b/mysql-test/main/ps_ddl.test new file mode 100644 index 00000000000..90226d379bf --- /dev/null +++ b/mysql-test/main/ps_ddl.test @@ -0,0 +1,2285 @@ +# +# 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 |; +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| +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; +call p_verify_reprepare_count(1); +select @message; + +--echo Test 6-e: removing a relevant trigger + +drop trigger t1_bi; + +set @val=12; +execute stmt using @val; +call p_verify_reprepare_count(1); +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; +call p_verify_reprepare_count(1); +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; + +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(1); + +--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 ;| +create procedure p1(out x int) select max(a) from t1 into x; + +prepare stmt from "select * from v1"; +execute stmt; +execute stmt; +call p_verify_reprepare_count(0); +drop procedure p1; +create procedure p1(out x int) select max(a) from t2 into x; +--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; +call p_verify_reprepare_count(1); +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); +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; + +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)); +--error ER_UNSUPPORTED_PS +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; +--error ER_UNSUPPORTED_PS +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 # |