diff options
Diffstat (limited to 'mysql-test/t/ps_ddl.test')
| -rw-r--r-- | mysql-test/t/ps_ddl.test | 2285 |
1 files changed, 0 insertions, 2285 deletions
diff --git a/mysql-test/t/ps_ddl.test b/mysql-test/t/ps_ddl.test deleted file mode 100644 index 90226d379bf..00000000000 --- a/mysql-test/t/ps_ddl.test +++ /dev/null @@ -1,2285 +0,0 @@ -# -# 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 # |