1# 2# Testing the behavior of 'PREPARE', 'DDL', 'EXECUTE' scenarios 3# 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 9# if (0) 10# { 11# <tests to disable> 12# }. 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. 16# Example: 17# Every preceding ALTER TABLE seems to cause a reprepare. 18# But if the ALTER only changed the table comment ... 19# 20# Created: 2008-04-18 mleich 21# 22 23--disable_warnings 24drop temporary table if exists t1; 25drop table if exists t1, t2; 26drop procedure if exists p_verify_reprepare_count; 27drop procedure if exists p1; 28drop function if exists f1; 29drop view if exists t1; 30drop schema if exists mysqltest; 31--enable_warnings 32 33delimiter |; 34create procedure p_verify_reprepare_count(expected int) 35begin 36 declare old_reprepare_count int default @reprepare_count; 37 38 select variable_value from 39 information_schema.session_status where 40 variable_name='com_stmt_reprepare' 41 into @reprepare_count; 42 43 if old_reprepare_count + expected <> @reprepare_count then 44 select concat("Expected: ", expected, 45 ", actual: ", @reprepare_count - old_reprepare_count) 46 as "ERROR"; 47 else 48 select '' as "SUCCESS"; 49 end if; 50end| 51delimiter ;| 52set @reprepare_count= 0; 53flush status; 54 55--disable_warnings 56drop table if exists t1; 57--disable_warnings 58 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. 62create table t1 (a int, b int); 63prepare stmt from "select a from t1"; 64execute stmt; 65call p_verify_reprepare_count(0); 66alter table t1 add column c int; 67execute stmt; 68call p_verify_reprepare_count(1); 69execute stmt; 70call p_verify_reprepare_count(0); 71alter table t1 drop column b; 72execute stmt; 73call p_verify_reprepare_count(1); 74execute stmt; 75call p_verify_reprepare_count(0); 76alter table t1 comment "My best table"; 77execute stmt; 78call p_verify_reprepare_count(1); 79execute stmt; 80call p_verify_reprepare_count(0); 81drop table t1; 82deallocate prepare stmt; 83 84--echo # Selects using the table at various positions, inser,update ... 85--echo # + the table disappears 86create table t1 (a int); 87# Attention: 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. 91prepare stmt1 from "truncate t1"; 92prepare stmt2 from "select 1 as my_column from t1"; 93prepare stmt3 from "select 1 as my_column from (select * from t1) as t2"; 94prepare stmt4 from 95"select 1 as my_column from (select 1) as t2 where exists (select 1 from t1)"; 96prepare stmt5 from "select * from (select 1 as b) as t2, t1"; 97prepare stmt6 from "select * from t1 union all select 1.5"; 98prepare stmt7 from "select 1 as my_column union all select 1 from t1"; 99prepare stmt8 from "insert into t1 values(1),(2)"; 100prepare stmt9 from "update t1 set a = 3 where a = 2"; 101prepare stmt10 from "delete from t1 where a = 1"; 102let 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. 105let $num= $ps_stmt_count; 106while ($num) 107{ 108 --disable_result_log 109 eval execute stmt$num; 110 --enable_result_log 111 dec $num; 112} 113# There was no reprepare needed, because none of the objects has changed. 114call p_verify_reprepare_count(0); 115drop table t1; 116let $num= $ps_stmt_count; 117while ($num) 118{ 119 --error ER_NO_SUCH_TABLE 120 eval execute stmt$num; 121 dec $num; 122} 123# There was no reprepare needed, because the statement is no more applicable. 124call p_verify_reprepare_count(0); 125let $num= $ps_stmt_count; 126while ($num) 127{ 128 eval deallocate prepare stmt$num; 129 dec $num; 130} 131 132--echo # Selects using the table at various positions, inser,update ... 133--echo # + layout change (drop column) which must cause a reprepare 134create table t1 (a int, b int); 135insert into t1 values(1,1),(2,2),(3,3); 136create table t2 like t1; 137insert into t1 values(2,2); 138prepare stmt1 from "select a,b from t1"; 139prepare stmt2 from "select a,b from (select * from t1) as t1"; 140prepare stmt3 from "select * from t1 where a = 2 and b = 2"; 141prepare stmt4 from "select * from t2 where (a,b) in (select * from t1)"; 142prepare stmt5 from "select * from t1 union select * from t2"; 143prepare stmt6 from "select * from t1 union all select * from t2"; 144prepare stmt7 from "insert into t1 set a = 4, b = 4"; 145prepare stmt8 from "insert into t1 select * from t2"; 146let 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. 149let $num= $ps_stmt_count; 150while ($num) 151{ 152 --disable_result_log 153 eval execute stmt$num; 154 --enable_result_log 155 dec $num; 156} 157call p_verify_reprepare_count(0); 158alter table t1 drop column b; 159--disable_abort_on_error 160let $num= $ps_stmt_count; 161while ($num) 162{ 163 eval execute stmt$num; 164 # A reprepare is needed, because layout change of t1 affects statement. 165 call p_verify_reprepare_count(1); 166 dec $num; 167} 168let $num= $ps_stmt_count; 169while ($num) 170{ 171 eval execute stmt$num; 172 call p_verify_reprepare_count(1); 173 dec $num; 174} 175eval execute stmt8; 176call p_verify_reprepare_count(1); 177--enable_abort_on_error 178alter table t2 add column c int; 179--error ER_WRONG_VALUE_COUNT_ON_ROW 180eval execute stmt8; 181call p_verify_reprepare_count(1); 182let $num= $ps_stmt_count; 183while ($num) 184{ 185 eval deallocate prepare stmt$num; 186 dec $num; 187} 188drop table t1; 189drop table t2; 190 191 192--echo # select AVG(<col>) + optimizer uses index meets loss of the index 193create table t1 (a int, b int, primary key(b),unique index t1_unq_idx(a)); 194# We need an index which is not converted to PRIMARY KEY (becomes in 195# case of InnoDB the key used for table clustering). 196insert into t1 set a = 0, b = 0; 197insert into t1 select a + 1, b + 1 from t1; 198insert into t1 select a + 2, b + 2 from t1; 199insert into t1 select a + 4, b + 4 from t1; 200insert into t1 select a + 8, b + 8 from t1; 201# "using index" optimizer strategy is intended 202let $possible_keys= 203 query_get_value(explain select avg(a) from t1, possible_keys, 1); 204let $extra= 205 query_get_value(explain select avg(a) from t1, Extra, 1); 206--echo # Optimizer strategy: Possible keys = $possible_keys , Extra = $extra 207prepare stmt from "select avg(a) from t1"; 208execute stmt; 209call p_verify_reprepare_count(0); 210execute stmt; 211call p_verify_reprepare_count(0); 212 213alter table t1 drop index t1_unq_idx; 214let $possible_keys= 215 query_get_value(explain select avg(a) from t1, possible_keys, 1); 216let $extra= 217 query_get_value(explain select avg(a) from t1, Extra, 1); 218--echo # Optimizer strategy: Possible keys = $possible_keys , Extra = $extra 219execute stmt; 220call p_verify_reprepare_count(1); 221execute stmt; 222call p_verify_reprepare_count(0); 223 224 225--echo # select AVG(<col>) + optimizer uses table scan meets a new index 226alter table t1 add unique index t1_unq_idx(a); 227let $possible_keys= 228 query_get_value(explain select avg(a) from t1, possible_keys, 1); 229let $extra= 230 query_get_value(explain select avg(a) from t1, Extra, 1); 231--echo # Optimizer strategy: Possible keys = $possible_keys , Extra = $extra 232execute stmt; 233call p_verify_reprepare_count(1); 234execute stmt; 235call p_verify_reprepare_count(0); 236 237deallocate prepare stmt; 238drop table t1; 239 240 241--echo # table replaced by not updatable view - Insert 242create table t1 (a int); 243prepare stmt from "insert into t1 values(1)"; 244execute stmt; 245call p_verify_reprepare_count(0); 246 247drop table t1; 248create view t1 as select 1; 249--error ER_NON_INSERTABLE_TABLE 250execute stmt; 251call p_verify_reprepare_count(1); 252 253drop view t1; 254create table t2 (a int); 255create view t1 as select * from t2 with check option; 256execute stmt; 257call p_verify_reprepare_count(1); 258execute stmt; 259call p_verify_reprepare_count(0); 260select * from t1; 261 262deallocate prepare stmt; 263drop view t1; 264drop table t2; 265 266 267--echo ===================================================================== 268--echo Some freestyle tests 269--echo ===================================================================== 270 271create temporary table t1 as select 1 as a; 272delimiter |; 273create procedure p1() 274begin 275 drop temporary table t1; 276end| 277create function f1() returns int 278begin 279 call p1(); 280 return 1; 281end| 282delimiter ;| 283 284prepare stmt from "select f1() as my_column, a from t1"; 285--error ER_CANT_REOPEN_TABLE 286execute stmt; 287call p_verify_reprepare_count(0); 288select * from t1; 289 290prepare stmt from "select a, f1() as my_column from t1"; 291--error ER_CANT_REOPEN_TABLE 292execute stmt; 293call p_verify_reprepare_count(0); 294select * from t1; 295 296prepare stmt from "select f1() as my_column, count(*) from t1"; 297--error ER_CANT_REOPEN_TABLE 298execute stmt; 299call p_verify_reprepare_count(0); 300select * from t1; 301 302prepare stmt from "select count(*), f1() as my_column from t1"; 303--error ER_CANT_REOPEN_TABLE 304execute stmt; 305call p_verify_reprepare_count(0); 306select * from t1; 307 308 309--echo # Execute fails, no drop of temporary table 310prepare stmt from "select 1 as my_column from (select 1) as t2 311 where exists (select f1() from t1)"; 312execute stmt; 313call p_verify_reprepare_count(0); 314execute stmt; 315call p_verify_reprepare_count(0); 316select * from t1; 317 318--echo # Execute drops temporary table 319prepare stmt from "select f1()"; 320execute stmt; 321call p_verify_reprepare_count(0); 322--error ER_BAD_TABLE_ERROR 323execute stmt; 324call p_verify_reprepare_count(0); 325 326drop function f1; 327drop procedure p1; 328deallocate prepare stmt; 329 330--echo # Execute fails, temporary table is not replaced by another 331create temporary table t1 as select 1 as a; 332delimiter |; 333create procedure p1() 334begin 335 drop temporary table t1; 336 create temporary table t1 as select 'abc' as a; 337end| 338create function f1() returns int 339begin 340 call p1(); 341 return 1; 342end| 343delimiter ;| 344prepare stmt from "select count(*), f1() as my_column from t1"; 345--error ER_CANT_REOPEN_TABLE 346execute stmt; 347call p_verify_reprepare_count(0); 348select * from t1; 349deallocate prepare stmt; 350 351prepare stmt from "call p1"; 352execute stmt; 353drop procedure p1; 354create schema mysqltest; 355delimiter |; 356create procedure mysqltest.p1() 357begin 358 drop schema mysqltest; 359 create schema mysqltest; 360end| 361delimiter ;| 362--error ER_SP_DOES_NOT_EXIST 363execute stmt; 364call p_verify_reprepare_count(0); 365--error ER_SP_DOES_NOT_EXIST 366execute stmt; 367call p_verify_reprepare_count(0); 368deallocate prepare stmt; 369drop schema mysqltest; 370drop temporary table t1; 371 372 373# Bug#36089 drop temp table in SP called by function, crash 374# Note: A non prepared "select 1 from t1 having count(*) = f1();" is sufficient. 375if (0) 376{ 377create temporary table t1 as select 1 as a; 378prepare stmt from "select 1 from t1 having count(*) = f1()"; 379execute stmt; 380call p_verify_reprepare_count(0); 381deallocate prepare stmt; 382drop temporary table t1; 383} 384 385 386--echo # Cleanup 387--echo # 388--disable_warnings 389drop temporary table if exists t1; 390drop table if exists t1, t2; 391drop procedure if exists p_verify_reprepare_count; 392drop procedure if exists p1; 393drop function if exists f1; 394drop view if exists t1; 395drop schema if exists mysqltest; 396--enable_warnings 397