Edit File: ps_ddl.result
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; 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| set @reprepare_count= 0; flush status; ===================================================================== Part 1: NOTHING -> TABLE transitions ===================================================================== prepare stmt from "select * from t1"; ERROR 42S02: Table 'test.t1' doesn't exist ===================================================================== Part 2: NOTHING -> TEMPORARY TABLE transitions ===================================================================== ===================================================================== Part 3: NOTHING -> VIEW transitions ===================================================================== ===================================================================== Part 4: TABLE -> NOTHING transitions ===================================================================== # Test 4-a: select ... from <table> create table t1 (a int); prepare stmt from "select * from t1"; execute stmt; a call p_verify_reprepare_count(0); SUCCESS execute stmt; a call p_verify_reprepare_count(0); SUCCESS drop table t1; execute stmt; ERROR 42S02: Table 'test.t1' doesn't exist call p_verify_reprepare_count(0); SUCCESS execute stmt; ERROR 42S02: Table 'test.t1' doesn't exist call p_verify_reprepare_count(0); SUCCESS deallocate prepare stmt; # Test 4-b: TABLE -> NOTHING by renaming the table create table t1 (a int); prepare stmt from "select * from t1"; execute stmt; a call p_verify_reprepare_count(0); SUCCESS execute stmt; a call p_verify_reprepare_count(0); SUCCESS rename table t1 to t2; execute stmt; ERROR 42S02: Table 'test.t1' doesn't exist call p_verify_reprepare_count(0); SUCCESS execute stmt; ERROR 42S02: Table 'test.t1' doesn't exist call p_verify_reprepare_count(0); SUCCESS deallocate prepare stmt; drop table t2; ===================================================================== Part 5: TABLE -> TABLE (DDL) transitions ===================================================================== create table t1 (a int); prepare stmt from "select a from t1"; execute stmt; a call p_verify_reprepare_count(0); SUCCESS execute stmt; a call p_verify_reprepare_count(0); SUCCESS alter table t1 add column (b int); execute stmt; a call p_verify_reprepare_count(1); SUCCESS execute stmt; a call p_verify_reprepare_count(0); SUCCESS drop table t1; deallocate prepare stmt; ===================================================================== Part 6: TABLE -> TABLE (TRIGGER) transitions ===================================================================== # Test 6-a: adding a relevant trigger create table t1 (a int); prepare stmt from "insert into t1 (a) value (?)"; set @val=1; execute stmt using @val; call p_verify_reprepare_count(0); SUCCESS create trigger t1_bi before insert on t1 for each row set @message= new.a; set @val=2; execute stmt using @val; call p_verify_reprepare_count(1); SUCCESS select @message; @message 2 set @val=3; execute stmt using @val; call p_verify_reprepare_count(0); SUCCESS select @message; @message 3 prepare stmt from "insert into t1 (a) value (?)"; set @val=4; execute stmt using @val; call p_verify_reprepare_count(0); SUCCESS select @message; @message 4 # Test 6-b: adding an irrelevant trigger create trigger t1_bd before delete on t1 for each row set @message= old.a; set @val=5; execute stmt using @val; call p_verify_reprepare_count(1); SUCCESS select @message; @message 5 set @val=6; execute stmt using @val; call p_verify_reprepare_count(0); SUCCESS select @message; @message 6 prepare stmt from "insert into t1 (a) value (?)"; set @val=7; execute stmt using @val; call p_verify_reprepare_count(0); SUCCESS select @message; @message 7 # Test 6-c: changing a relevant trigger drop trigger t1_bi; create trigger t1_bi before insert on t1 for each row set @message= concat("new trigger: ", new.a); set @val=8; execute stmt using @val; call p_verify_reprepare_count(1); SUCCESS select @message; @message new trigger: 8 set @val=9; execute stmt using @val; call p_verify_reprepare_count(0); SUCCESS select @message; @message new trigger: 9 prepare stmt from "insert into t1 (a) value (?)"; set @val=10; execute stmt using @val; call p_verify_reprepare_count(0); SUCCESS select @message; @message new trigger: 10 # Test 6-d: changing an irrelevant trigger drop trigger t1_bd; set @val=11; execute stmt using @val; call p_verify_reprepare_count(1); SUCCESS select @message; @message new trigger: 11 Test 6-e: removing a relevant trigger drop trigger t1_bi; set @val=12; execute stmt using @val; call p_verify_reprepare_count(1); SUCCESS select @message; @message new trigger: 11 set @val=13; execute stmt using @val; call p_verify_reprepare_count(0); SUCCESS select @message; @message new trigger: 11 prepare stmt from "insert into t1 (a) value (?)"; set @val=14; execute stmt using @val; call p_verify_reprepare_count(0); SUCCESS select @message; @message new trigger: 11 select * from t1 order by a; a 1 2 3 4 5 6 7 8 9 10 11 12 13 14 drop table t1; deallocate prepare stmt; ===================================================================== Part 7: TABLE -> TABLE (TRIGGER dependencies) transitions ===================================================================== # Test 7-a: dependent PROCEDURE has changed # create table t1 (a int); create trigger t1_ai after insert on t1 for each row call p1(new.a); create procedure p1(a int) begin end; prepare stmt from "insert into t1 (a) values (?)"; set @var= 1; execute stmt using @var; drop procedure p1; create procedure p1 (a int) begin end; set @var= 2; execute stmt using @var; # Cleanup drop procedure p1; call p_verify_reprepare_count(1); SUCCESS # Test 7-b: dependent FUNCTION has changed # # Note, this scenario is supported, subject of Bug#12093 # drop trigger t1_ai; create trigger t1_ai after insert on t1 for each row select f1(new.a+1) into @var; create function f1 (a int) returns int return a; prepare stmt from "insert into t1(a) values (?)"; set @var=3; execute stmt using @var; select @var; @var 4 drop function f1; create function f1 (a int) returns int return 0; execute stmt using @var; call p_verify_reprepare_count(1); SUCCESS drop function f1; deallocate prepare stmt; # Test 7-c: dependent VIEW has changed # # Note, this scenario is not functioning correctly, see # Bug#33255 Trigger using views and view ddl : corrupted triggers # and Bug #33000 Triggers do not detect changes in meta-data. # drop trigger t1_ai; create table t2 (a int unique); create table t3 (a int unique); create view v1 as select a from t2; create trigger t1_ai after insert on t1 for each row insert into v1 (a) values (new.a); # Demonstrate that the same bug is present # without prepared statements insert into t1 (a) values (5); select * from t2; a 5 select * from t3; a drop view v1; create view v1 as select a from t3; insert into t1 (a) values (6); ERROR 42S02: Table 'test.t2' doesn't exist flush table t1; insert into t1 (a) values (6); select * from t2; a 5 select * from t3; a 6 prepare stmt from "insert into t1 (a) values (?)"; set @var=7; execute stmt using @var; call p_verify_reprepare_count(0); SUCCESS select * from t3; a 6 7 select * from t2; a 5 drop view v1; create view v1 as select a from t2; set @var=8; # XXX: bug, the SQL statement in the trigger is still # pointing at table 't3', since the view was expanded # at first statement execution. # Since the view definition is inlined in the statement # at prepare, changing the view definition does not cause # repreparation. # Repreparation of the main statement doesn't cause repreparation # of trigger statements. execute stmt using @var; call p_verify_reprepare_count(0); SUCCESS # # Sic: the insert went into t3, even though the view now # points at t2. This is because neither the merged view # nor its prelocking list are affected by view DDL # The binary log is of course wrong, since it is not # using prepared statements # select * from t2; a 5 select * from t3; a 6 7 8 flush table t1; set @var=9; execute stmt using @var; call p_verify_reprepare_count(1); SUCCESS select * from t2; a 5 9 select * from t3; a 6 7 8 drop view v1; drop table t1,t2,t3; # Test 7-d: dependent TABLE has changed create table t1 (a int); create trigger t1_ai after insert on t1 for each row insert into t2 (a) values (new.a); create table t2 (a int); prepare stmt from "insert into t1 (a) values (?)"; set @var=1; execute stmt using @var; alter table t2 add column comment varchar(255); set @var=2; # Since the dependent table is tracked in the prelocked # list of the prepared statement, invalidation happens # and the statement is re-prepared. This is an unnecessary # side effect, since the statement that *is* dependent # on t2 definition is inside the trigger, and it is currently # not reprepared (see the previous test case). execute stmt using @var; call p_verify_reprepare_count(1); SUCCESS select * from t1; a 1 2 select * from t2; a comment 1 NULL 2 NULL drop table t1,t2; # Test 7-e: dependent TABLE TRIGGER has changed create table t1 (a int); create trigger t1_ai after insert on t1 for each row insert into t2 (a) values (new.a); create table t2 (a int unique); create trigger t2_ai after insert on t2 for each row insert into t3 (a) values (new.a); create table t3 (a int unique); create table t4 (a int unique); insert into t1 (a) values (1); select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.a=t3.a); a a a 1 1 1 drop trigger t2_ai; create trigger t2_ai after insert on t2 for each row insert into t4 (a) values (new.a); insert into t1 (a) values (2); select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a); a a a 2 2 2 prepare stmt from "insert into t1 (a) values (?)"; set @var=3; execute stmt using @var; select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a); a a a 2 2 2 3 3 3 drop trigger t2_ai; create trigger t2_ai after insert on t2 for each row insert into t3 (a) values (new.a); set @var=4; execute stmt using @var; call p_verify_reprepare_count(1); SUCCESS select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.a=t3.a); a a a 1 1 1 4 4 4 select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a); a a a 2 2 2 3 3 3 drop table t1, t2, t3, t4; deallocate prepare stmt; ===================================================================== Part 8: TABLE -> TEMPORARY TABLE transitions ===================================================================== # Test 8-a: base table used recreated as temporary table create table t1 (a int); prepare stmt from "select * from t1"; execute stmt; a drop table t1; create temporary table t1 (a int); execute stmt; a call p_verify_reprepare_count(1); SUCCESS execute stmt; a call p_verify_reprepare_count(0); SUCCESS drop table t1; deallocate prepare stmt; # Test 8-b: temporary table has precedence over base table with same name create table t1 (a int); prepare stmt from 'select count(*) from t1'; execute stmt; count(*) 0 call p_verify_reprepare_count(0); SUCCESS execute stmt; count(*) 0 call p_verify_reprepare_count(0); SUCCESS create temporary table t1 AS SELECT 1; execute stmt; count(*) 1 call p_verify_reprepare_count(1); SUCCESS execute stmt; count(*) 1 call p_verify_reprepare_count(0); SUCCESS deallocate prepare stmt; drop temporary table t1; drop table t1; ===================================================================== Part 9: TABLE -> VIEW transitions ===================================================================== create table t1 (a int); prepare stmt from "select * from t1"; execute stmt; a call p_verify_reprepare_count(0); SUCCESS drop table t1; create table t2 (a int); create view t1 as select * from t2; execute stmt; a call p_verify_reprepare_count(1); SUCCESS drop view t1; drop table t2; deallocate prepare stmt; ===================================================================== Part 10: TEMPORARY TABLE -> NOTHING transitions ===================================================================== create temporary table t1 (a int); prepare stmt from "select * from t1"; execute stmt; a call p_verify_reprepare_count(0); SUCCESS drop temporary table t1; execute stmt; ERROR 42S02: Table 'test.t1' doesn't exist call p_verify_reprepare_count(0); SUCCESS deallocate prepare stmt; ===================================================================== Part 11: TEMPORARY TABLE -> TABLE transitions ===================================================================== # Test 11-a: temporary table replaced by base table create table t1 (a int); insert into t1 (a) value (1); create temporary table t1 (a int); prepare stmt from "select * from t1"; execute stmt; a call p_verify_reprepare_count(0); SUCCESS drop temporary table t1; execute stmt; a 1 call p_verify_reprepare_count(1); SUCCESS select * from t1; a 1 drop table t1; deallocate prepare stmt; # Test 11-b: temporary table has precedence over base table with same name # temporary table disappears create table t1 (a int); create temporary table t1 as select 1 as a; prepare stmt from "select count(*) from t1"; execute stmt; count(*) 1 call p_verify_reprepare_count(0); SUCCESS execute stmt; count(*) 1 call p_verify_reprepare_count(0); SUCCESS drop temporary table t1; execute stmt; count(*) 0 call p_verify_reprepare_count(1); SUCCESS execute stmt; count(*) 0 call p_verify_reprepare_count(0); SUCCESS deallocate prepare stmt; drop table t1; ===================================================================== Part 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions ===================================================================== create temporary table t1 (a int); prepare stmt from "select a from t1"; execute stmt; a call p_verify_reprepare_count(0); SUCCESS drop temporary table t1; create temporary table t1 (a int, b int); execute stmt; a call p_verify_reprepare_count(1); SUCCESS select * from t1; a b drop temporary table t1; deallocate prepare stmt; ===================================================================== Part 13: TEMPORARY TABLE -> VIEW transitions ===================================================================== create temporary table t1 (a int); create table t2 (a int); prepare stmt from "select * from t1"; execute stmt; a call p_verify_reprepare_count(0); SUCCESS drop temporary table t1; create view t1 as select * from t2; execute stmt; a call p_verify_reprepare_count(1); SUCCESS drop view t1; drop table t2; deallocate prepare stmt; ===================================================================== Part 14: VIEW -> NOTHING transitions ===================================================================== create table t2 (a int); create view t1 as select * from t2; prepare stmt from "select * from t1"; execute stmt; a drop view t1; execute stmt; ERROR 42S02: Table 'test.t1' doesn't exist call p_verify_reprepare_count(0); SUCCESS execute stmt; ERROR 42S02: Table 'test.t1' doesn't exist call p_verify_reprepare_count(0); SUCCESS drop table t2; deallocate prepare stmt; ===================================================================== Part 15: VIEW -> TABLE transitions ===================================================================== create table t2 (a int); create view t1 as select * from t2; prepare stmt from "select * from t1"; execute stmt; a call p_verify_reprepare_count(0); SUCCESS drop view t1; create table t1 (a int); execute stmt; a call p_verify_reprepare_count(1); SUCCESS drop table t2; drop table t1; deallocate prepare stmt; ===================================================================== Part 16: VIEW -> TEMPORARY TABLE transitions ===================================================================== # # Test 1: Merged view # create table t2 (a int); insert into t2 (a) values (1); create view t1 as select * from t2; prepare stmt from "select * from t1"; execute stmt; a 1 call p_verify_reprepare_count(0); SUCCESS create temporary table t1 (a int); execute stmt; a 1 call p_verify_reprepare_count(0); SUCCESS drop view t1; execute stmt; ERROR 42S02: Table 'test.t1' doesn't exist call p_verify_reprepare_count(0); SUCCESS drop table t2; drop temporary table t1; deallocate prepare stmt; # # Test 2: Materialized view # create table t2 (a int); insert into t2 (a) values (1); create algorithm = temptable view t1 as select * from t2; prepare stmt from "select * from t1"; execute stmt; a 1 call p_verify_reprepare_count(0); SUCCESS create temporary table t1 (a int); execute stmt; a 1 call p_verify_reprepare_count(0); SUCCESS drop view t1; execute stmt; ERROR 42S02: Table 'test.t1' doesn't exist call p_verify_reprepare_count(0); SUCCESS drop table t2; drop temporary table t1; deallocate prepare stmt; # # Test 3: View referencing an Information schema table # create view t1 as select table_name from information_schema.views; prepare stmt from "select * from t1"; execute stmt; table_name t1 call p_verify_reprepare_count(0); SUCCESS create temporary table t1 (a int); execute stmt; table_name t1 call p_verify_reprepare_count(0); SUCCESS drop view t1; execute stmt; table_name call p_verify_reprepare_count(0); SUCCESS drop temporary table t1; deallocate prepare stmt; ===================================================================== Part 17: VIEW -> VIEW (DDL) transitions ===================================================================== create table t2 (a int); insert into t2 values (10), (20), (30); create view t1 as select a, 2*a as b, 3*a as c from t2; select * from t1; a b c 10 20 30 20 40 60 30 60 90 prepare stmt from "select * from t1"; execute stmt; a b c 10 20 30 20 40 60 30 60 90 drop view t1; create view t1 as select a, 2*a as b, 5*a as c from t2; select * from t1; a b c 10 20 50 20 40 100 30 60 150 # Currently a different result from conventional statements. # A view is inlined once at prepare, later on view DDL # does not affect prepared statement and it is not re-prepared. # This is reported in Bug#36002 Prepared statements: if a view # used in a statement is replaced, bad data execute stmt; a b c 10 20 30 20 40 60 30 60 90 call p_verify_reprepare_count(0); SUCCESS flush table t2; execute stmt; a b c 10 20 50 20 40 100 30 60 150 call p_verify_reprepare_count(1); SUCCESS drop table t2; drop view t1; deallocate prepare stmt; ===================================================================== Part 18: VIEW -> VIEW (VIEW dependencies) transitions ===================================================================== # Part 18a: dependent function has changed create table t1 (a int); insert into t1 (a) values (1), (2), (3); create function f1() returns int return (select max(a) from t1); create view v1 as select f1(); prepare stmt from "select * from v1"; execute stmt; f1() 3 execute stmt; f1() 3 call p_verify_reprepare_count(0); SUCCESS drop function f1; create function f1() returns int return 2; # XXX: Used to be another manifestation of Bug#12093. # We only used to get a different error # message because the non-existing procedure error is masked # by the view. execute stmt; f1() 2 execute stmt; f1() 2 call p_verify_reprepare_count(1); SUCCESS # Part 18b: dependent procedure has changed (referred to via a function) create table t2 (a int); insert into t2 (a) values (4), (5), (6); drop function f1; create function f1() returns int begin declare x int; call p1(x); return x; end| create procedure p1(out x int) select max(a) from t1 into x; prepare stmt from "select * from v1"; execute stmt; f1() 3 execute stmt; f1() 3 call p_verify_reprepare_count(0); SUCCESS drop procedure p1; create procedure p1(out x int) select max(a) from t2 into x; # XXX: used to be a bug. The prelocked list was not invalidated # and we kept opening table t1, whereas the procedure # is now referring to table t2 execute stmt; f1() 6 call p_verify_reprepare_count(1); SUCCESS flush table t1; execute stmt; f1() 6 call p_verify_reprepare_count(0); SUCCESS execute stmt; f1() 6 # Test 18-c: dependent VIEW has changed drop view v1; create view v2 as select a from t1; create view v1 as select * from v2; prepare stmt from "select * from v1"; execute stmt; a 1 2 3 execute stmt; a 1 2 3 call p_verify_reprepare_count(0); SUCCESS drop view v2; create view v2 as select a from t2; execute stmt; a 1 2 3 execute stmt; a 1 2 3 call p_verify_reprepare_count(0); SUCCESS flush table t1; execute stmt; a 4 5 6 call p_verify_reprepare_count(1); SUCCESS execute stmt; a 4 5 6 # Test 18-d: dependent TABLE has changed drop view v2; create table v2 as select * from t1; execute stmt; a 1 2 3 call p_verify_reprepare_count(1); SUCCESS execute stmt; a 1 2 3 call p_verify_reprepare_count(0); SUCCESS drop table v2; create table v2 (a int unique) as select * from t2; execute stmt; a 4 5 6 call p_verify_reprepare_count(1); SUCCESS execute stmt; a 4 5 6 call p_verify_reprepare_count(0); SUCCESS # Test 18-e: dependent TABLE trigger has changed prepare stmt from "insert into v1 (a) values (?)"; set @var= 7; execute stmt using @var; call p_verify_reprepare_count(0); SUCCESS create trigger v2_bi before insert on v2 for each row set @message="v2_bi"; set @var=8; execute stmt using @var; call p_verify_reprepare_count(1); SUCCESS select @message; @message v2_bi drop trigger v2_bi; set @message=null; set @var=9; execute stmt using @var; call p_verify_reprepare_count(1); SUCCESS select @message; @message NULL create trigger v2_bi after insert on v2 for each row set @message="v2_ai"; set @var= 10; execute stmt using @var; call p_verify_reprepare_count(1); SUCCESS select @message; @message v2_ai select * from v1; a 4 5 6 7 8 9 10 # Cleanup drop table if exists t1, t2, v1, v2; drop view if exists v1, v2; drop function f1; drop procedure p1; deallocate prepare stmt; ===================================================================== Part 19: Special tables (INFORMATION_SCHEMA) ===================================================================== prepare stmt from "select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE from INFORMATION_SCHEMA.ROUTINES where routine_name='p1'"; create procedure p1() select "hi there"; execute stmt; ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE test p1 PROCEDURE execute stmt; ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE test p1 PROCEDURE drop procedure p1; create procedure p1() select "hi there, again"; execute stmt; ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE test p1 PROCEDURE execute stmt; ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE test p1 PROCEDURE call p_verify_reprepare_count(0); SUCCESS drop procedure p1; deallocate prepare stmt; ===================================================================== Part 20: Special tables (log tables) ===================================================================== prepare stmt from "select * from mysql.general_log where argument='IMPOSSIBLE QUERY STRING'"; execute stmt; execute stmt; execute stmt; execute stmt; call p_verify_reprepare_count(0); SUCCESS deallocate prepare stmt; ===================================================================== Part 21: Special tables (system tables) ===================================================================== prepare stmt from "select type, db, name from mysql.proc where name='p1'"; create procedure p1() select "hi there"; execute stmt; type db name PROCEDURE test p1 execute stmt; type db name PROCEDURE test p1 drop procedure p1; create procedure p1() select "hi there, again"; execute stmt; type db name PROCEDURE test p1 execute stmt; type db name PROCEDURE test p1 call p_verify_reprepare_count(0); SUCCESS drop procedure p1; deallocate prepare stmt; ===================================================================== Part 22: Special tables (views temp tables) ===================================================================== create table t1 (a int); create algorithm=temptable view v1 as select a*a as a2 from t1; # Using a temporary table internally should not confuse the prepared # statement code, and should not raise ER_PS_INVALIDATED errors show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`a` * `t1`.`a`) AS `a2` from `t1` latin1 latin1_swedish_ci prepare stmt from "select * from v1"; insert into t1 values (1), (2), (3); execute stmt; a2 1 4 9 execute stmt; a2 1 4 9 insert into t1 values (4), (5), (6); execute stmt; a2 1 4 9 16 25 36 execute stmt; a2 1 4 9 16 25 36 call p_verify_reprepare_count(0); SUCCESS drop table t1; drop view v1; ===================================================================== Part 23: Special statements ===================================================================== # SQLCOM_ALTER_TABLE: create table t1 (a int); prepare stmt from "alter table t1 add column b int"; execute stmt; drop table t1; create table t1 (a1 int, a2 int); # t1 has changed, and it's does not lead to reprepare execute stmt; alter table t1 drop column b; execute stmt; alter table t1 drop column b; execute stmt; call p_verify_reprepare_count(0); SUCCESS drop table t1; # SQLCOM_REPAIR: create table t1 (a int); insert into t1 values (1), (2), (3); prepare stmt from "repair table t1"; execute stmt; Table Op Msg_type Msg_text test.t1 repair status OK execute stmt; Table Op Msg_type Msg_text test.t1 repair status OK drop table t1; create table t1 (a1 int, a2 int); insert into t1 values (1, 10), (2, 20), (3, 30); # t1 has changed, and it's does not lead to reprepare execute stmt; Table Op Msg_type Msg_text test.t1 repair status OK alter table t1 add column b varchar(50) default NULL; execute stmt; Table Op Msg_type Msg_text test.t1 repair status OK call p_verify_reprepare_count(0); SUCCESS alter table t1 drop column b; execute stmt; Table Op Msg_type Msg_text test.t1 repair status OK call p_verify_reprepare_count(0); SUCCESS # SQLCOM_ANALYZE: prepare stmt from "analyze table t1"; execute stmt; Table Op Msg_type Msg_text test.t1 analyze status OK drop table t1; create table t1 (a1 int, a2 int); insert into t1 values (1, 10), (2, 20), (3, 30); # t1 has changed, and it's not a problem execute stmt; Table Op Msg_type Msg_text test.t1 analyze status OK alter table t1 add column b varchar(50) default NULL; execute stmt; Table Op Msg_type Msg_text test.t1 analyze status OK alter table t1 drop column b; execute stmt; Table Op Msg_type Msg_text test.t1 analyze status OK call p_verify_reprepare_count(0); SUCCESS # SQLCOM_OPTIMIZE: prepare stmt from "optimize table t1"; execute stmt; Table Op Msg_type Msg_text test.t1 optimize status Table is already up to date drop table t1; create table t1 (a1 int, a2 int); insert into t1 values (1, 10), (2, 20), (3, 30); # t1 has changed, and it's not a problem execute stmt; Table Op Msg_type Msg_text test.t1 optimize status OK alter table t1 add column b varchar(50) default NULL; execute stmt; Table Op Msg_type Msg_text test.t1 optimize status OK alter table t1 drop column b; execute stmt; Table Op Msg_type Msg_text test.t1 optimize status OK call p_verify_reprepare_count(0); SUCCESS drop table t1; # SQLCOM_SHOW_CREATE_PROC: prepare stmt from "show create procedure p1"; execute stmt; ERROR 42000: PROCEDURE p1 does not exist execute stmt; ERROR 42000: PROCEDURE p1 does not exist create procedure p1() begin end; execute stmt; execute stmt; drop procedure p1; create procedure p1(x int, y int) begin end; execute stmt; execute stmt; drop procedure p1; execute stmt; ERROR 42000: PROCEDURE p1 does not exist execute stmt; ERROR 42000: PROCEDURE p1 does not exist call p_verify_reprepare_count(0); SUCCESS # SQLCOM_SHOW_CREATE_FUNC: prepare stmt from "show create function f1"; execute stmt; ERROR 42000: FUNCTION f1 does not exist execute stmt; ERROR 42000: FUNCTION f1 does not exist create function f1() returns int return 0; execute stmt; execute stmt; drop function f1; create function f1(x int, y int) returns int return x+y; execute stmt; execute stmt; drop function f1; execute stmt; ERROR 42000: FUNCTION f1 does not exist execute stmt; ERROR 42000: FUNCTION f1 does not exist call p_verify_reprepare_count(0); SUCCESS # SQLCOM_SHOW_CREATE_TRIGGER: create table t1 (a int); prepare stmt from "show create trigger t1_bi"; execute stmt; ERROR HY000: Trigger does not exist execute stmt; ERROR HY000: Trigger does not exist create trigger t1_bi before insert on t1 for each row set @message= "t1_bi"; execute stmt; execute stmt; drop trigger t1_bi; create trigger t1_bi before insert on t1 for each row set @message= "t1_bi (2)"; execute stmt; execute stmt; drop trigger t1_bi; execute stmt; ERROR HY000: Trigger does not exist execute stmt; ERROR HY000: Trigger does not exist call p_verify_reprepare_count(0); SUCCESS drop table t1; deallocate prepare stmt; ===================================================================== Part 24: Testing the strength of TABLE_SHARE version ===================================================================== # Test 24-a: number of columns create table t1 (a int); prepare stmt from "select a from t1"; execute stmt; a call p_verify_reprepare_count(0); SUCCESS alter table t1 add column b varchar(50) default NULL; execute stmt; a call p_verify_reprepare_count(1); SUCCESS execute stmt; a call p_verify_reprepare_count(0); SUCCESS # Test 24-b: column name alter table t1 change b c int; execute stmt; a call p_verify_reprepare_count(1); SUCCESS execute stmt; a call p_verify_reprepare_count(0); SUCCESS # Test 24-c: column type alter table t1 change a a varchar(10); execute stmt; a call p_verify_reprepare_count(1); SUCCESS execute stmt; a call p_verify_reprepare_count(0); SUCCESS # Test 24-d: column type length alter table t1 change a a varchar(20); execute stmt; a call p_verify_reprepare_count(1); SUCCESS execute stmt; a call p_verify_reprepare_count(0); SUCCESS # Test 24-e: column NULL property alter table t1 change a a varchar(20) NOT NULL; execute stmt; a call p_verify_reprepare_count(1); SUCCESS execute stmt; a call p_verify_reprepare_count(0); SUCCESS # Test 24-f: column DEFAULT alter table t1 change c c int DEFAULT 20; execute stmt; a call p_verify_reprepare_count(1); SUCCESS execute stmt; a call p_verify_reprepare_count(0); SUCCESS # Test 24-g: number of keys create unique index t1_a_idx on t1 (a); execute stmt; a call p_verify_reprepare_count(1); SUCCESS execute stmt; a call p_verify_reprepare_count(0); SUCCESS # Test 24-h: changing index uniqueness drop index t1_a_idx on t1; create index t1_a_idx on t1 (a); execute stmt; a call p_verify_reprepare_count(1); SUCCESS execute stmt; a call p_verify_reprepare_count(0); SUCCESS # Cleanup drop table t1; deallocate prepare stmt; ===================================================================== Testing reported bugs ===================================================================== # # Bug#27420 A combination of PS and view operations cause # error + assertion on shutdown # drop table if exists t_27420_100; drop table if exists t_27420_101; drop view if exists v_27420; create table t_27420_100(a int); insert into t_27420_100 values (1), (2); create table t_27420_101(a int); insert into t_27420_101 values (1), (2); create view v_27420 as select t_27420_100.a X, t_27420_101.a Y from t_27420_100, t_27420_101 where t_27420_100.a=t_27420_101.a; prepare stmt from "select * from v_27420"; execute stmt; X Y 1 1 2 2 call p_verify_reprepare_count(0); SUCCESS drop view v_27420; create table v_27420(X int, Y int); execute stmt; X Y call p_verify_reprepare_count(1); SUCCESS drop table v_27420; create table v_27420 (a int, b int, filler char(200)); execute stmt; a b filler call p_verify_reprepare_count(1); SUCCESS drop table t_27420_100; drop table t_27420_101; drop table v_27420; deallocate prepare stmt; # # Bug#27430 Crash in subquery code when in PS and table DDL changed # after PREPARE # drop table if exists t_27430_1; drop table if exists t_27430_2; create table t_27430_1 (a int not null, oref int not null, key(a)); insert into t_27430_1 values (1, 1), (1, 1234), (2, 3), (2, 1234), (3, 1234); create table t_27430_2 (a int not null, oref int not null); insert into t_27430_2 values (1, 1), (2, 2), (1234, 3), (1234, 4); prepare stmt from "select oref, a, a in (select a from t_27430_1 where oref=t_27430_2.oref) Z from t_27430_2"; execute stmt; oref a Z 1 1 1 2 2 0 3 1234 0 4 1234 0 call p_verify_reprepare_count(0); SUCCESS drop table t_27430_1, t_27430_2; create table t_27430_1 (a int, oref int, key(a)); insert into t_27430_1 values (1, 1), (1, NULL), (2, 3), (2, NULL), (3, NULL); create table t_27430_2 (a int, oref int); insert into t_27430_2 values (1, 1), (2,2), (NULL, 3), (NULL, 4); execute stmt; oref a Z 1 1 1 2 2 0 3 NULL NULL 4 NULL 0 call p_verify_reprepare_count(1); SUCCESS drop table t_27430_1; drop table t_27430_2; deallocate prepare stmt; # # Bug#27690 Re-execution of prepared statement after table # was replaced with a view crashes # drop table if exists t_27690_1; drop view if exists v_27690_1; drop table if exists v_27690_2; create table t_27690_1 (a int, b int); insert into t_27690_1 values (1,1),(2,2); create table v_27690_1 as select * from t_27690_1; create table v_27690_2 as select * from t_27690_1; prepare stmt from "select * from v_27690_1, v_27690_2"; execute stmt; a b a b 1 1 1 1 2 2 1 1 1 1 2 2 2 2 2 2 execute stmt; a b a b 1 1 1 1 2 2 1 1 1 1 2 2 2 2 2 2 drop table v_27690_1; execute stmt; ERROR 42S02: Table 'test.v_27690_1' doesn't exist execute stmt; ERROR 42S02: Table 'test.v_27690_1' doesn't exist call p_verify_reprepare_count(0); SUCCESS create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B; execute stmt; a b a b 1 1 1 1 2 2 1 1 1 1 1 1 2 2 1 1 1 1 2 2 2 2 2 2 1 1 2 2 2 2 2 2 call p_verify_reprepare_count(1); SUCCESS execute stmt; a b a b 1 1 1 1 2 2 1 1 1 1 1 1 2 2 1 1 1 1 2 2 2 2 2 2 1 1 2 2 2 2 2 2 call p_verify_reprepare_count(0); SUCCESS drop table t_27690_1; drop view v_27690_1; drop table v_27690_2; deallocate prepare stmt; #===================================================================== # # Bug#21294 Executing a prepared statement that executes # a stored function which was recreat # create function f1() returns int return 10; prepare stmt from "select f1()"; execute stmt; f1() 10 drop function f1; create function f1() returns int return 10; execute stmt; f1() 10 drop function f1; create function f1() returns int return 20; execute stmt; f1() 20 call p_verify_reprepare_count(2); SUCCESS drop function f1; deallocate prepare stmt; # # Bug#12093 SP not found on second PS execution if another thread drops # other SP in between # drop table if exists t_12093; drop function if exists f_12093; drop function if exists f_12093_unrelated; drop procedure if exists p_12093; drop view if exists v_12093_unrelated; create table t_12093 (a int); create function f_12093() returns int return (select count(*) from t_12093); create procedure p_12093(a int) select * from t_12093; create function f_12093_unrelated() returns int return 2; create procedure p_12093_unrelated() begin end; create view v_12093_unrelated as select * from t_12093; prepare stmt_sf from 'select f_12093();'; prepare stmt_sp from 'call p_12093(f_12093())'; execute stmt_sf; f_12093() 0 execute stmt_sp; a drop function f_12093_unrelated; # XXX: used to be a bug execute stmt_sf; f_12093() 0 # XXX: used to be a bug execute stmt_sp; a # XXX: used to be a bug execute stmt_sf; f_12093() 0 # XXX: used to be a bug execute stmt_sp; a prepare stmt_sf from 'select f_12093();'; prepare stmt_sp from 'call p_12093(f_12093())'; execute stmt_sf; f_12093() 0 execute stmt_sp; a drop procedure p_12093_unrelated; # XXX: used to be a bug execute stmt_sf; f_12093() 0 # XXX: used to be a bug execute stmt_sp; a # XXX: used to be a bug execute stmt_sf; f_12093() 0 # XXX: used to be a bug execute stmt_sp; a prepare stmt_sf from 'select f_12093();'; prepare stmt_sp from 'call p_12093(f_12093())'; execute stmt_sf; f_12093() 0 execute stmt_sp; a drop view v_12093_unrelated; # XXX: used to be a bug execute stmt_sf; f_12093() 0 # XXX: used to be a bug execute stmt_sp; a # XXX: used to be a bug execute stmt_sf; f_12093() 0 # XXX: used to be a bug execute stmt_sp; a call p_verify_reprepare_count(6); SUCCESS drop table t_12093; drop function f_12093; drop procedure p_12093; deallocate prepare stmt_sf; deallocate prepare stmt_sp; ===================================================================== Ensure that metadata validation is performed for every type of SQL statement where it is needed. ===================================================================== # # SQLCOM_SELECT # drop table if exists t1; create table t1 (a int); prepare stmt from "select 1 as res from dual where (1) in (select * from t1)"; drop table t1; create table t1 (x int); execute stmt; res drop table t1; deallocate prepare stmt; call p_verify_reprepare_count(1); SUCCESS # # SQLCOM_CREATE_TABLE # drop table if exists t1; drop table if exists t2; create table t1 (a int); prepare stmt from 'create table t2 as select * from t1'; execute stmt; drop table t2; execute stmt; drop table t2; execute stmt; call p_verify_reprepare_count(0); SUCCESS execute stmt; ERROR 42S01: Table 't2' already exists call p_verify_reprepare_count(1); SUCCESS execute stmt; ERROR 42S01: Table 't2' already exists call p_verify_reprepare_count(0); SUCCESS drop table t2; create temporary table t2 (a int); execute stmt; call p_verify_reprepare_count(0); SUCCESS execute stmt; ERROR 42S01: Table 't2' already exists call p_verify_reprepare_count(1); SUCCESS drop temporary table t2; execute stmt; ERROR 42S01: Table 't2' already exists call p_verify_reprepare_count(0); SUCCESS drop table t2; execute stmt; call p_verify_reprepare_count(0); SUCCESS drop table t2; create view t2 as select 1; execute stmt; Got one of the listed errors call p_verify_reprepare_count(1); SUCCESS execute stmt; Got one of the listed errors call p_verify_reprepare_count(0); SUCCESS drop view t2; drop table t1; create table t1 (x varchar(20)); execute stmt; call p_verify_reprepare_count(1); SUCCESS select * from t2; x drop table t2; execute stmt; call p_verify_reprepare_count(0); SUCCESS drop table t2; alter table t1 add column y decimal(10,3); execute stmt; call p_verify_reprepare_count(1); SUCCESS select * from t2; x y drop table t2; execute stmt; call p_verify_reprepare_count(0); SUCCESS drop table t1; deallocate prepare stmt; create table t1 (a int); insert into t1 (a) values (1); prepare stmt from "create temporary table if not exists t2 as select * from t1"; execute stmt; drop table t2; execute stmt; call p_verify_reprepare_count(0); SUCCESS execute stmt; Warnings: Note 1050 Table 't2' already exists call p_verify_reprepare_count(1); SUCCESS select * from t2; a 1 execute stmt; Warnings: Note 1050 Table 't2' already exists call p_verify_reprepare_count(0); SUCCESS select * from t2; a 1 drop table t2; create temporary table t2 (a varchar(10)); execute stmt; Warnings: Note 1050 Table 't2' already exists select * from t2; a call p_verify_reprepare_count(1); SUCCESS drop table t1; create table t1 (x int); execute stmt; Warnings: Note 1050 Table 't2' already exists call p_verify_reprepare_count(1); SUCCESS execute stmt; Warnings: Note 1050 Table 't2' already exists call p_verify_reprepare_count(0); SUCCESS drop table t1; drop temporary table t2; drop table t2; deallocate prepare stmt; create table t1 (a int); prepare stmt from "create table t2 like t1"; execute stmt; call p_verify_reprepare_count(0); SUCCESS drop table t2; execute stmt; call p_verify_reprepare_count(0); SUCCESS drop table t2; drop table t1; execute stmt; ERROR 42S02: Table 'test.t1' doesn't exist call p_verify_reprepare_count(0); SUCCESS execute stmt; ERROR 42S02: Table 'test.t1' doesn't exist call p_verify_reprepare_count(0); SUCCESS create table t1 (x char(17)); execute stmt; call p_verify_reprepare_count(1); SUCCESS drop table t2; execute stmt; call p_verify_reprepare_count(0); SUCCESS drop table t2; alter table t1 add column y time; execute stmt; call p_verify_reprepare_count(1); SUCCESS select * from t2; x y drop table t2; execute stmt; call p_verify_reprepare_count(0); SUCCESS drop table t1; drop table t2; deallocate prepare stmt; # # SQLCOM_UPDATE # drop table if exists t1, t2; create table t1 (a int); create table t2 (a int); prepare stmt from "update t2 set a=a+1 where (1) in (select * from t1)"; execute stmt; drop table t1; create table t1 (x int); execute stmt; drop table t1, t2; deallocate prepare stmt; # # SQLCOM_INSERT # drop table if exists t1, t2; create table t1 (a int); create table t2 (a int); prepare stmt from "insert into t2 set a=((1) in (select * from t1))"; execute stmt; drop table t1; create table t1 (x int); execute stmt; drop table t1, t2; deallocate prepare stmt; # # SQLCOM_INSERT_SELECT # drop table if exists t1, t2; create table t1 (a int); create table t2 (a int); prepare stmt from "insert into t2 select * from t1"; execute stmt; drop table t1; create table t1 (x int); execute stmt; drop table t1, t2; deallocate prepare stmt; # # SQLCOM_REPLACE # drop table if exists t1, t2; create table t1 (a int); create table t2 (a int); prepare stmt from "replace t2 set a=((1) in (select * from t1))"; execute stmt; drop table t1; create table t1 (x int); execute stmt; drop table t1, t2; deallocate prepare stmt; # # SQLCOM_REPLACE_SELECT # drop table if exists t1, t2; create table t1 (a int); create table t2 (a int); prepare stmt from "replace t2 select * from t1"; execute stmt; drop table t1; create table t1 (x int); execute stmt; drop table t1, t2; deallocate prepare stmt; # # SQLCOM_DELETE # drop table if exists t1, t2; create table t1 (a int); create table t2 (a int); prepare stmt from "delete from t2 where (1) in (select * from t1)"; execute stmt; drop table t1; create table t1 (x int); execute stmt; drop table t1, t2; deallocate prepare stmt; # # SQLCOM_DELETE_MULTI # drop table if exists t1, t2, t3; create table t1 (a int); create table t2 (a int); create table t3 (a int); prepare stmt from "delete t2, t3 from t2, t3 where (1) in (select * from t1)"; execute stmt; drop table t1; create table t1 (x int); execute stmt; drop table t1, t2, t3; deallocate prepare stmt; # # SQLCOM_UPDATE_MULTI # drop table if exists t1, t2, t3; create table t1 (a int); create table t2 (a int); create table t3 (a int); prepare stmt from "update t2, t3 set t3.a=t2.a, t2.a=null where (1) in (select * from t1)"; drop table t1; create table t1 (x int); execute stmt; drop table t1, t2, t3; deallocate prepare stmt; # Intermediate results: 8 SQLCOMs tested, 8 automatic reprepares call p_verify_reprepare_count(8); SUCCESS # # SQLCOM_LOAD # drop table if exists t1; create table t1 (a varchar(20)); prepare stmt from "load data infile '../std_data_ln/words.dat' into table t1"; ERROR HY000: This command is not supported in the prepared statement protocol yet drop table t1; # # SQLCOM_SHOW_DATABASES # drop table if exists t1; create table t1 (a int); prepare stmt from "show databases where (1) in (select * from t1)"; execute stmt; Database drop table t1; create table t1 (x int); execute stmt; Database drop table t1; deallocate prepare stmt; # # SQLCOM_SHOW_TABLES # drop table if exists t1; create table t1 (a int); prepare stmt from "show tables where (1) in (select * from t1)"; execute stmt; Tables_in_test drop table t1; create table t1 (x int); execute stmt; Tables_in_test drop table t1; deallocate prepare stmt; # # SQLCOM_SHOW_FIELDS # drop table if exists t1; create table t1 (a int); prepare stmt from "show fields from t1 where (1) in (select * from t1)"; execute stmt; Field Type Null Key Default Extra drop table t1; create table t1 (x int); execute stmt; Field Type Null Key Default Extra drop table t1; deallocate prepare stmt; # # SQLCOM_SHOW_KEYS # drop table if exists t1; create table t1 (a int); prepare stmt from "show keys from t1 where (1) in (select * from t1)"; execute stmt; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment drop table t1; create table t1 (x int); execute stmt; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment drop table t1; deallocate prepare stmt; # # SQLCOM_SHOW_VARIABLES # drop table if exists t1; create table t1 (a int); prepare stmt from "show variables where (1) in (select * from t1)"; execute stmt; Variable_name Value drop table t1; create table t1 (x int); execute stmt; Variable_name Value drop table t1; deallocate prepare stmt; # # SQLCOM_SHOW_STATUS # drop table if exists t1; create table t1 (a int); prepare stmt from "show status where (1) in (select * from t1)"; execute stmt; Variable_name Value drop table t1; create table t1 (x int); execute stmt; Variable_name Value drop table t1; deallocate prepare stmt; # # SQLCOM_SHOW_ENGINE_STATUS, SQLCOM_SHOW_ENGINE_LOGS, # SQLCOM_SHOW_ENGINE_MUTEX, SQLCOM_SHOW_PROCESSLIST # # Currently can not have a where clause, need to be covered # with tests drop table if exists t1; create table t1 (a int); prepare stmt from "show engine all status where (1) in (select * from t1)"; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1 prepare stmt from "show engine all logs where (1) in (select * from t1)"; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1 prepare stmt from "show engine all mutex where (1) in (select * from t1)"; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1 prepare stmt from "show processlist where (1) in (select * from t1)"; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1 drop table t1; # # SQLCOM_SHOW_CHARSETS # drop table if exists t1; create table t1 (a int); prepare stmt from "show charset where (1) in (select * from t1)"; execute stmt; Charset Description Default collation Maxlen drop table t1; create table t1 (x int); execute stmt; Charset Description Default collation Maxlen drop table t1; deallocate prepare stmt; # # SQLCOM_SHOW_COLLATIONS # drop table if exists t1; create table t1 (a int); prepare stmt from "show collation where (1) in (select * from t1)"; execute stmt; Collation Charset Id Default Compiled Sortlen drop table t1; create table t1 (x int); execute stmt; Collation Charset Id Default Compiled Sortlen drop table t1; deallocate prepare stmt; # # SQLCOM_SHOW_TABLE_STATUS # drop table if exists t1; create table t1 (a int); prepare stmt from "show table status where (1) in (select * from t1)"; execute stmt; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment drop table t1; create table t1 (x int); execute stmt; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment drop table t1; deallocate prepare stmt; # # SQLCOM_SHOW_TRIGGERS # drop table if exists t1; create table t1 (a int); prepare stmt from "show triggers where (1) in (select * from t1)"; execute stmt; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation drop table t1; create table t1 (x int); execute stmt; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation drop table t1; deallocate prepare stmt; # # SQLCOM_SHOW_OPEN_TABLES # drop table if exists t1; create table t1 (a int); prepare stmt from "show open tables where (1) in (select * from t1)"; execute stmt; Database Table In_use Name_locked drop table t1; create table t1 (x int); execute stmt; Database Table In_use Name_locked drop table t1; deallocate prepare stmt; # # SQLCOM_SHOW_STATUS_PROC # drop table if exists t1; create table t1 (a int); prepare stmt from "show procedure status where (1) in (select * from t1)"; execute stmt; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation drop table t1; create table t1 (x int); execute stmt; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation drop table t1; deallocate prepare stmt; # # SQLCOM_SHOW_STATUS_FUNC # drop table if exists t1; create table t1 (a int); prepare stmt from "show function status where (1) in (select * from t1)"; execute stmt; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation drop table t1; create table t1 (x int); execute stmt; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation drop table t1; deallocate prepare stmt; # # SQLCOM_SHOW_EVENTS # # # Please see this test in ps.test, it requires not_embedded.inc # # # SQLCOM_SET_OPTION # drop table if exists t1; create table t1 (a int); prepare stmt from "set @a=((1) in (select * from t1))"; execute stmt; drop table t1; create table t1 (x int); execute stmt; drop table t1; deallocate prepare stmt; # # SQLCOM_DO # drop table if exists t1; create table t1 (a int); prepare stmt from "do ((1) in (select * from t1))"; execute stmt; drop table t1; create table t1 (x int); execute stmt; drop table t1; deallocate prepare stmt; # # SQLCOM_CALL # drop table if exists t1; drop procedure if exists p1; create procedure p1(a int) begin end; create table t1 (a int); prepare stmt from "call p1((1) in (select * from t1))"; execute stmt; drop table t1; create table t1 (x int); execute stmt; drop table t1; drop procedure p1; deallocate prepare stmt; # # SQLCOM_CREATE_VIEW # drop table if exists t1; drop view if exists v1; create table t1 (a int); prepare stmt from "create view v1 as select * from t1"; execute stmt; drop view v1; drop table t1; create table t1 (x int); execute stmt; drop view v1; drop table t1; deallocate prepare stmt; # Intermediate result: number of reprepares matches the number # of tests call p_verify_reprepare_count(17); SUCCESS # # SQLCOM_ALTER_VIEW # drop view if exists v1; create view v1 as select 1; prepare stmt from "alter view v1 as select 2"; ERROR HY000: This command is not supported in the prepared statement protocol yet drop view v1; # Cleanup # drop temporary table if exists t1, t2, t3; drop table if exists t1, t2, t3, v1, v2; drop procedure if exists p_verify_reprepare_count; drop procedure if exists p1; drop function if exists f1; drop view if exists v1, v2;
Back to File Manager