2
# Testing the behavior of 'PREPARE', 'DDL', 'EXECUTE' scenarios
4
# There are several subtests which are probably "superfluous" because a DDL
5
# statement before the EXECUTE <prepared stmt handle> contained a keyword
6
# or action (Example: Alter) which causes that all prepared statements using
7
# the modified object are reprepared before execution.
8
# Please do not delete these subtests if they disturb. Just disable them by
13
# There might be future optimisations of the server which decrease the amount
14
# of unneeded reprepares or skip unneeded prepare steps and than these subtests
15
# might become valuable.
17
# Every preceding ALTER TABLE seems to cause a reprepare.
18
# But if the ALTER only changed the table comment ...
20
# Created: 2008-04-18 mleich
24
drop temporary table if exists t1;
25
drop table if exists t1, t2;
26
drop procedure if exists p_verify_reprepare_count;
27
drop procedure if exists p1;
28
drop function if exists f1;
29
drop view if exists t1;
30
drop schema if exists mysqltest;
34
create procedure p_verify_reprepare_count(expected int)
36
declare old_reprepare_count int default @reprepare_count;
38
select variable_value from
39
information_schema.session_status where
40
variable_name='com_stmt_reprepare'
41
into @reprepare_count;
43
if old_reprepare_count + expected <> @reprepare_count then
44
select concat("Expected: ", expected,
45
", actual: ", @reprepare_count - old_reprepare_count)
48
select '' as "SUCCESS";
52
set @reprepare_count= 0;
56
drop table if exists t1;
59
--echo # Column added or dropped is not within the list of selected columns
60
--echo # or table comment has changed.
61
--echo # A reprepare is probably not needed.
62
create table t1 (a int, b int);
63
prepare stmt from "select a from t1";
65
call p_verify_reprepare_count(0);
66
alter table t1 add column c int;
68
call p_verify_reprepare_count(1);
70
call p_verify_reprepare_count(0);
71
alter table t1 drop column b;
73
call p_verify_reprepare_count(1);
75
call p_verify_reprepare_count(0);
76
alter table t1 comment "My best table";
78
call p_verify_reprepare_count(1);
80
call p_verify_reprepare_count(0);
82
deallocate prepare stmt;
84
--echo # Selects using the table at various positions, inser,update ...
85
--echo # + the table disappears
86
create table t1 (a int);
88
# "truncate" must have the first position (= executed as last prepared
89
# statement), because it recreates the table which has leads to reprepare
90
# (is this really needed) of all statements.
91
prepare stmt1 from "truncate t1";
92
prepare stmt2 from "select 1 as my_column from t1";
93
prepare stmt3 from "select 1 as my_column from (select * from t1) as t2";
95
"select 1 as my_column from (select 1) as t2 where exists (select 1 from t1)";
96
prepare stmt5 from "select * from (select 1 as b) as t2, t1";
97
prepare stmt6 from "select * from t1 union all select 1.5";
98
prepare stmt7 from "select 1 as my_column union all select 1 from t1";
99
prepare stmt8 from "insert into t1 values(1),(2)";
100
prepare stmt9 from "update t1 set a = 3 where a = 2";
101
prepare stmt10 from "delete from t1 where a = 1";
102
let ps_stmt_count= 10;
103
--echo # Attention: Result logging is disabled.
104
# Checks of correct results of statements are not the goal of this test.
105
let $num= $ps_stmt_count;
109
eval execute stmt$num;
113
# There was no reprepare needed, because none of the objects has changed.
114
call p_verify_reprepare_count(0);
116
let $num= $ps_stmt_count;
119
--error ER_NO_SUCH_TABLE
120
eval execute stmt$num;
123
# There was no reprepare needed, because the statement is no more applicable.
124
call p_verify_reprepare_count(0);
125
let $num= $ps_stmt_count;
128
eval deallocate prepare stmt$num;
132
--echo # Selects using the table at various positions, inser,update ...
133
--echo # + layout change (drop column) which must cause a reprepare
134
create table t1 (a int, b int);
135
insert into t1 values(1,1),(2,2),(3,3);
136
create table t2 like t1;
137
insert into t1 values(2,2);
138
prepare stmt1 from "select a,b from t1";
139
prepare stmt2 from "select a,b from (select * from t1) as t1";
140
prepare stmt3 from "select * from t1 where a = 2 and b = 2";
141
prepare stmt4 from "select * from t2 where (a,b) in (select * from t1)";
142
prepare stmt5 from "select * from t1 union select * from t2";
143
prepare stmt6 from "select * from t1 union all select * from t2";
144
prepare stmt7 from "insert into t1 set a = 4, b = 4";
145
prepare stmt8 from "insert into t1 select * from t2";
146
let ps_stmt_count= 8;
147
--echo # Attention: Result logging is disabled.
148
# Checks of correct results of statements are not the goal of this test.
149
let $num= $ps_stmt_count;
153
eval execute stmt$num;
157
call p_verify_reprepare_count(0);
158
alter table t1 drop column b;
159
--disable_abort_on_error
160
let $num= $ps_stmt_count;
163
eval execute stmt$num;
164
# A reprepare is needed, because layout change of t1 affects statement.
165
call p_verify_reprepare_count(1);
168
let $num= $ps_stmt_count;
171
eval execute stmt$num;
172
call p_verify_reprepare_count(1);
175
--echo # Why does the INSERT ... SELECT does not get a reprepare or is
176
--echo # only the counter not incremented?
178
call p_verify_reprepare_count(1);
179
--enable_abort_on_error
180
alter table t2 add column c int;
181
--error ER_WRONG_VALUE_COUNT_ON_ROW
183
call p_verify_reprepare_count(1);
184
let $num= $ps_stmt_count;
187
eval deallocate prepare stmt$num;
194
--echo # select AVG(<col>) + optimizer uses index meets loss of the index
195
create table t1 (a int, b int, primary key(b),unique index t1_unq_idx(a));
196
# We need an index which is not converted to PRIMARY KEY (becomes in
197
# case of InnoDB the key used for table clustering).
198
insert into t1 set a = 0, b = 0;
199
insert into t1 select a + 1, b + 1 from t1;
200
insert into t1 select a + 2, b + 2 from t1;
201
insert into t1 select a + 4, b + 4 from t1;
202
insert into t1 select a + 8, b + 8 from t1;
203
# "using index" optimizer strategy is intended
205
query_get_value(explain select avg(a) from t1, possible_keys, 1);
207
query_get_value(explain select avg(a) from t1, Extra, 1);
208
--echo # Optimizer strategy: Possible keys = $possible_keys , Extra = $extra
209
prepare stmt from "select avg(a) from t1";
211
call p_verify_reprepare_count(0);
213
call p_verify_reprepare_count(0);
215
alter table t1 drop index t1_unq_idx;
217
query_get_value(explain select avg(a) from t1, possible_keys, 1);
219
query_get_value(explain select avg(a) from t1, Extra, 1);
220
--echo # Optimizer strategy: Possible keys = $possible_keys , Extra = $extra
222
call p_verify_reprepare_count(1);
224
call p_verify_reprepare_count(0);
227
--echo # select AVG(<col>) + optimizer uses table scan meets a new index
228
alter table t1 add unique index t1_unq_idx(a);
230
query_get_value(explain select avg(a) from t1, possible_keys, 1);
232
query_get_value(explain select avg(a) from t1, Extra, 1);
233
--echo # Optimizer strategy: Possible keys = $possible_keys , Extra = $extra
235
call p_verify_reprepare_count(1);
237
call p_verify_reprepare_count(0);
239
deallocate prepare stmt;
243
--echo # table replaced by not updatable view - Insert
244
create table t1 (a int);
245
prepare stmt from "insert into t1 values(1)";
247
call p_verify_reprepare_count(0);
250
create view t1 as select 1;
251
--error ER_NON_INSERTABLE_TABLE
253
call p_verify_reprepare_count(1);
256
create table t2 (a int);
257
create view t1 as select * from t2 with check option;
259
call p_verify_reprepare_count(1);
261
call p_verify_reprepare_count(0);
264
deallocate prepare stmt;
269
--echo =====================================================================
270
--echo Some freestyle tests
271
--echo =====================================================================
273
create temporary table t1 as select 1 as a;
275
create procedure p1()
277
drop temporary table t1;
279
create function f1() returns int
286
prepare stmt from "select f1() as my_column, a from t1";
287
--error ER_CANT_REOPEN_TABLE
289
call p_verify_reprepare_count(0);
292
prepare stmt from "select a, f1() as my_column from t1";
293
--error ER_CANT_REOPEN_TABLE
295
call p_verify_reprepare_count(0);
298
prepare stmt from "select f1() as my_column, count(*) from t1";
299
--error ER_CANT_REOPEN_TABLE
301
call p_verify_reprepare_count(0);
304
prepare stmt from "select count(*), f1() as my_column from t1";
305
--error ER_CANT_REOPEN_TABLE
307
call p_verify_reprepare_count(0);
311
--echo # Execute fails, no drop of temporary table
312
prepare stmt from "select 1 as my_column from (select 1) as t2
313
where exists (select f1() from t1)";
315
call p_verify_reprepare_count(0);
317
call p_verify_reprepare_count(0);
320
--echo # Execute drops temporary table
321
prepare stmt from "select f1()";
323
call p_verify_reprepare_count(0);
324
--error ER_BAD_TABLE_ERROR
326
call p_verify_reprepare_count(0);
330
deallocate prepare stmt;
332
--echo # Execute fails, temporary table is not replaced by another
333
create temporary table t1 as select 1 as a;
335
create procedure p1()
337
drop temporary table t1;
338
create temporary table t1 as select 'abc' as a;
340
create function f1() returns int
346
prepare stmt from "select count(*), f1() as my_column from t1";
347
--error ER_CANT_REOPEN_TABLE
349
call p_verify_reprepare_count(0);
351
deallocate prepare stmt;
353
prepare stmt from "call p1";
356
create schema mysqltest;
358
create procedure mysqltest.p1()
360
drop schema mysqltest;
361
create schema mysqltest;
364
--error ER_SP_DOES_NOT_EXIST
366
call p_verify_reprepare_count(1);
367
--error ER_SP_DOES_NOT_EXIST
369
call p_verify_reprepare_count(0);
370
deallocate prepare stmt;
371
drop schema mysqltest;
372
drop temporary table t1;
375
# Bug#36089 drop temp table in SP called by function, crash
376
# Note: A non prepared "select 1 from t1 having count(*) = f1();" is sufficient.
379
create temporary table t1 as select 1 as a;
380
prepare stmt from "select 1 from t1 having count(*) = f1()";
382
call p_verify_reprepare_count(0);
383
deallocate prepare stmt;
384
drop temporary table t1;
391
drop temporary table if exists t1;
392
drop table if exists t1, t2;
393
drop procedure if exists p_verify_reprepare_count;
394
drop procedure if exists p1;
395
drop function if exists f1;
396
drop view if exists t1;
397
drop schema if exists mysqltest;