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