1###################### ps_general.test ####################### 2# # 3# basic and miscellaneous tests for prepared statements # 4# # 5############################################################## 6 7# 8# NOTE: PLEASE SEE THE DETAILED DESCRIPTION AT THE BOTTOM OF THIS FILE 9# BEFORE ADDING NEW TEST CASES HERE !!! 10 11--disable_warnings 12drop table if exists t5, t6, t7, t8; 13drop database if exists mysqltest ; 14 15# Cleanup from other tests 16drop database if exists client_test_db; 17drop database if exists testtets; 18drop table if exists t1Aa,t2Aa,v1Aa,v2Aa; 19drop view if exists t1Aa,t2Aa,v1Aa,v2Aa; 20--enable_warnings 21 22--disable_query_log 23select '------ basic tests ------' as test_sequence ; 24--enable_query_log 25 26let $type= 'MYISAM' ; 27# create the tables (t1 and t9) used in many tests 28--source include/ps_create.inc 29# insert data into these tables 30--source include/ps_renew.inc 31 32 33################ The basic functions ################ 34 35# 1. PREPARE stmt_name FROM <preparable statement>; 36# <preparable statement> ::= 37# 'literal_stmt' | 38# @variable_ref_stmt. 39# The statement may contain question marks as placeholders for parameters. 40# 41# Bind a statement name to a string containing a SQL statement and 42# send it to the server. The server will parse the statement and 43# reply with "Query Ok" or an error message. 44# 45PREPARE stmt FROM ' select * from t1 where a = ? ' ; 46 47# 2. EXECUTE stmt_name [USING @var [, @var ]]; 48# Current values of supplied variables are used as parameters. 49# 50# Send the server the order to execute the statement and supply values 51# for the input parameters needed. 52# If no error occurs the server reply will be identical to the reply for 53# the query used in PREPARE with question marks replaced with values of 54# the input variables. 55# 56SET @var= 2 ; 57EXECUTE stmt USING @var ; 58# The non prepared statement with the same server reply would be: 59select * from t1 where a = @var ; 60 61# 3. DEALLOCATE PREPARE stmt_name; 62# 63# Send the server the order to drop the parse informations. 64# The server will reply with "Query Ok" or an error message. 65DEALLOCATE PREPARE stmt ; 66 67################ PREPARE ################ 68# prepare without parameter 69prepare stmt1 from ' select 1 as my_col ' ; 70# prepare with parameter 71prepare stmt1 from ' select ? as my_col ' ; 72# prepare must fail (incomplete statements/wrong syntax) 73--error ER_PARSE_ERROR 74prepare ; 75--error ER_PARSE_ERROR 76prepare stmt1 ; 77--error ER_PARSE_ERROR 78prepare stmt1 from ; 79--error ER_PARSE_ERROR 80prepare_garbage stmt1 from ' select 1 ' ; 81--error ER_PARSE_ERROR 82prepare stmt1 from_garbage ' select 1 ' ; 83--error ER_PARSE_ERROR 84prepare stmt1 from ' select_garbage 1 ' ; 85--error ER_PARSE_ERROR 86prepare from ' select 1 ' ; 87--error ER_PARSE_ERROR 88prepare stmt1 ' select 1 ' ; 89--error ER_PARSE_ERROR 90prepare ? from ' select ? as my_col ' ; 91# statement in variable 92set @arg00='select 1 as my_col'; 93prepare stmt1 from @arg00; 94# prepare must fail (query variable is empty) 95set @arg00=''; 96--error ER_EMPTY_QUERY 97prepare stmt1 from @arg00; 98set @arg00=NULL; 99# prepare must fail (query variable is NULL) 100--error ER_PARSE_ERROR 101prepare stmt1 from @arg01; 102 103prepare stmt1 from ' select * from t1 where a <= 2 ' ; 104# prepare must fail (column x does not exist) 105--error ER_BAD_FIELD_ERROR 106prepare stmt1 from ' select * from t1 where x <= 2 ' ; 107# cases derived from client_test.c: test_null() 108# prepare must fail (column x does not exist) 109--error ER_BAD_FIELD_ERROR 110prepare stmt1 from ' insert into t1(a,x) values(?,?) ' ; 111--error ER_BAD_FIELD_ERROR 112prepare stmt1 from ' insert into t1(x,a) values(?,?) ' ; 113--disable_warnings 114drop table if exists not_exist ; 115--enable_warnings 116# prepare must fail (table does not exist) 117--error ER_NO_SUCH_TABLE 118prepare stmt1 from ' select * from not_exist where a <= 2 ' ; 119 120# case derived from client_test.c: test_prepare_syntax() 121# prepare must fail (incomplete statement) 122--error ER_PARSE_ERROR 123prepare stmt1 from ' insert into t1 values(? ' ; 124--error ER_PARSE_ERROR 125prepare stmt1 from ' select a, b from t1 126 where a=? and where ' ; 127 128################ EXECUTE ################ 129# execute must fail (statement never_prepared never prepared) 130--error ER_UNKNOWN_STMT_HANDLER 131execute never_prepared ; 132# execute must fail (prepare stmt1 just failed, 133# but there was a successful prepare of stmt1 before) 134prepare stmt1 from ' select * from t1 where a <= 2 ' ; 135--error ER_NO_SUCH_TABLE 136prepare stmt1 from ' select * from not_exist where a <= 2 ' ; 137--error ER_UNKNOWN_STMT_HANDLER 138execute stmt1 ; 139 140# drop the table between prepare and execute 141create table t5 142( 143 a int primary key, 144 b char(30), 145 c int 146); 147insert into t5( a, b, c) values( 1, 'original table', 1); 148prepare stmt2 from ' select * from t5 ' ; 149execute stmt2 ; 150drop table t5 ; 151# execute must fail (table was dropped after prepare) 152--error ER_NO_SUCH_TABLE 153execute stmt2 ; 154# cases derived from client_test.c: test_select_prepare() 155# 1. drop + create table (same column names/types/order) 156# between prepare and execute 157create table t5 158( 159 a int primary key, 160 b char(30), 161 c int 162); 163insert into t5( a, b, c) values( 9, 'recreated table', 9); 164execute stmt2 ; 165drop table t5 ; 166# 2. drop + create table (same column names/types but different order) 167# between prepare and execute 168create table t5 169( 170 a int primary key, 171 c int, 172 b char(30) 173); 174insert into t5( a, b, c) values( 9, 'recreated table', 9); 175execute stmt2 ; 176drop table t5 ; 177# 3. drop + create table (same column names/types/order+extra column) 178# between prepare and execute 179create table t5 180( 181 a int primary key, 182 b char(30), 183 c int, 184 d timestamp default '2008-02-23 09:23:45' 185); 186insert into t5( a, b, c) values( 9, 'recreated table', 9); 187execute stmt2 ; 188drop table t5 ; 189# 4. drop + create table (same column names/types, different order + 190# additional column) between prepare and execute 191create table t5 192( 193 a int primary key, 194 d timestamp default '2008-02-23 09:23:45', 195 b char(30), 196 c int 197); 198insert into t5( a, b, c) values( 9, 'recreated table', 9); 199execute stmt2 ; 200drop table t5 ; 201# 5. drop + create table (same column names/order, different types) 202# between prepare and execute 203create table t5 204( 205 a timestamp default '2004-02-29 18:01:59', 206 b char(30), 207 c int 208); 209insert into t5( b, c) values( 'recreated table', 9); 210execute stmt2 ; 211drop table t5 ; 212# 6. drop + create table (same column types/order, different names) 213# between prepare and execute 214create table t5 215( 216 f1 int primary key, 217 f2 char(30), 218 f3 int 219); 220insert into t5( f1, f2, f3) values( 9, 'recreated table', 9); 221execute stmt2 ; 222drop table t5 ; 223 224# execute without parameter 225prepare stmt1 from ' select * from t1 where a <= 2 ' ; 226execute stmt1 ; 227# execute with parameter 228set @arg00=1 ; 229set @arg01='two' ; 230prepare stmt1 from ' select * from t1 where a <= ? ' ; 231execute stmt1 using @arg00; 232# execute must fail (too small number of parameters) 233--error ER_WRONG_ARGUMENTS 234execute stmt1 ; 235# execute must fail (too big number of parameters) 236--error ER_WRONG_ARGUMENTS 237execute stmt1 using @arg00, @arg01; 238# execute must fail (parameter is not set) 239execute stmt1 using @not_set; 240 241################ DEALLOCATE ################ 242# deallocate must fail (the statement 'never_prepared' was never prepared) 243--error ER_UNKNOWN_STMT_HANDLER 244deallocate prepare never_prepared ; 245# deallocate must fail (prepare stmt1 just failed, 246# but there was a successful prepare before) 247prepare stmt1 from ' select * from t1 where a <= 2 ' ; 248--error ER_NO_SUCH_TABLE 249prepare stmt1 from ' select * from not_exist where a <= 2 ' ; 250--error ER_UNKNOWN_STMT_HANDLER 251deallocate prepare stmt1; 252create table t5 253( 254 a int primary key, 255 b char(10) 256); 257prepare stmt2 from ' select a,b from t5 where a <= 2 ' ; 258drop table t5 ; 259# deallocate prepared statement where the table was dropped after prepare 260deallocate prepare stmt2; 261 262## parallel use of more than one prepared statement handlers 263# switch between different queries 264prepare stmt1 from ' select a from t1 where a <= 2 ' ; 265prepare stmt2 from ' select b from t1 where a <= 2 ' ; 266execute stmt2 ; 267execute stmt1 ; 268# switch between statement handlers of the same query 269prepare stmt1 from ' select a from t1 where a <= 2 ' ; 270prepare stmt2 from ' select a from t1 where a <= 2 ' ; 271execute stmt2 ; 272execute stmt1 ; 273deallocate prepare stmt1 ; 274# Will the deallocate of stmt1 with the same query affect stmt2 ? 275execute stmt2 ; 276 277--disable_query_log 278select '------ show and misc tests ------' as test_sequence ; 279--enable_query_log 280 281--disable_warnings 282drop table if exists t2; 283--enable_warnings 284create table t2 285( 286 a int primary key, b char(10) 287); 288 289################ SHOW COMMANDS ################ 290prepare stmt4 from ' show databases '; 291execute stmt4; 292prepare stmt4 from ' show tables from test like ''t2%'' '; 293execute stmt4; 294prepare stmt4 from ' show columns from t2 where field in (select ?) '; 295SET @arg00="a"; 296execute stmt4 using @arg00; 297SET @arg00="b"; 298execute stmt4 using @arg00; 299SET @arg00=1; 300execute stmt4 using @arg00; 301 302prepare stmt4 from ' show columns from t2 from test like ''a%'' '; 303execute stmt4; 304create index t2_idx on t2(b); 305prepare stmt4 from ' show index from t2 from test '; 306execute stmt4; 307prepare stmt4 from ' show table status from test like ''t2%'' '; 308# egalize date and time values 309--replace_column 8 # 12 # 13 # 14 # 19 # 310# Bug#4288 : prepared statement 'show table status ..', wrong output on execute 311execute stmt4; 312# try the same with the big table 313prepare stmt4 from ' show table status from test like ''t9%'' '; 314# egalize date and time values 315--replace_column 8 # 12 # 13 # 14 # 19 # 316# Bug#4288 317execute stmt4; 318prepare stmt4 from ' show status like ''Threads_running'' '; 319--replace_column 2 # 320execute stmt4; 321prepare stmt4 from ' show variables like ''sql_mode'' '; 322execute stmt4; 323prepare stmt4 from ' show engine myisam logs '; 324execute stmt4; 325prepare stmt4 from ' show grants for user '; 326prepare stmt4 from ' show create table t2 '; 327prepare stmt4 from ' show master status '; 328prepare stmt4 from ' show master logs '; 329prepare stmt4 from ' show slave status '; 330prepare stmt4 from ' show warnings limit 20 '; 331prepare stmt4 from ' show errors limit 20 '; 332prepare stmt4 from ' show storage engines '; 333# The output depends upon the precise order in which 334# storage engines are registered, so we switch off the output. 335--disable_result_log 336execute stmt4; 337--enable_result_log 338 339################ MISC STUFF ################ 340## get a warning and an error 341# cases derived from client_test.c: test_warnings(), test_errors() 342--disable_warnings 343drop table if exists t5; 344--enable_warnings 345prepare stmt1 from ' drop table if exists t5 ' ; 346execute stmt1 ; 347prepare stmt1 from ' drop table t5 ' ; 348--error ER_BAD_TABLE_ERROR 349execute stmt1 ; 350 351## SELECT @@version 352# cases derived from client_test.c: test_select_version() 353# 354# TODO: Metadata check is temporary disabled here, because metadata of 355# this statement also depends on @@version contents and you can't apply 356# replace_column and replace_result to it. It will be enabled again when 357# support of replace_column and replace_result on metadata will be 358# implemented. 359# 360#--enable_metadata 361prepare stmt1 from ' SELECT @@version ' ; 362# egalize the version 363--replace_column 1 <version> 364execute stmt1 ; 365#--disable_metadata 366 367## do @var:= and set @var= 368# cases derived from client_test.c: test_do_set() 369prepare stmt_do from ' do @var:= (1 in (select a from t1)) ' ; 370prepare stmt_set from ' set @var= (1 in (select a from t1)) ' ; 371let $1= 3 ; 372while ($1) 373{ 374 execute stmt_do ; 375 --disable_query_log 376 select @var as 'content of @var is:' ; 377 --enable_query_log 378 execute stmt_set ; 379 --disable_query_log 380 select @var as 'content of @var is:' ; 381 --enable_query_log 382 dec $1 ; 383} 384# the same test with a table containing one column and 'select *' 385--disable_warnings 386drop table if exists t5 ; 387--enable_warnings 388create table t5 (a int) ; 389prepare stmt_do from ' do @var:= (1 in (select a from t5)) ' ; 390prepare stmt_set from ' set @var= (1 in (select a from t5)) ' ; 391let $1= 3 ; 392while ($1) 393{ 394 execute stmt_do ; 395 --disable_query_log 396 select @var as 'content of @var is:' ; 397 --enable_query_log 398 execute stmt_set ; 399 --disable_query_log 400 select @var as 'content of @var is:' ; 401 --enable_query_log 402 dec $1 ; 403} 404drop table t5 ; 405deallocate prepare stmt_do ; 406deallocate prepare stmt_set ; 407 408## nonsense like prepare of prepare,execute or deallocate 409--error ER_UNSUPPORTED_PS 410prepare stmt1 from ' prepare stmt2 from '' select 1 '' ' ; 411--error ER_UNSUPPORTED_PS 412prepare stmt1 from ' execute stmt2 ' ; 413--error ER_UNSUPPORTED_PS 414prepare stmt1 from ' deallocate prepare never_prepared ' ; 415 416## We don't support alter view as prepared statements 417--error ER_UNSUPPORTED_PS 418prepare stmt1 from 'alter view v1 as select 2'; 419 420## switch the database connection 421--error ER_UNSUPPORTED_PS 422prepare stmt4 from ' use test ' ; 423 424## create/drop database 425prepare stmt3 from ' create database mysqltest '; 426create database mysqltest ; 427prepare stmt3 from ' drop database mysqltest '; 428drop database mysqltest ; 429 430#### table related commands 431## describe 432prepare stmt3 from ' describe t2 '; 433execute stmt3; 434drop table t2 ; 435--error ER_NO_SUCH_TABLE 436execute stmt3; 437## lock/unlock 438--error ER_UNSUPPORTED_PS 439prepare stmt3 from ' lock tables t1 read ' ; 440--error ER_UNSUPPORTED_PS 441prepare stmt3 from ' unlock tables ' ; 442## Load/Unload table contents 443 444--let $datafile = $MYSQLTEST_VARDIR/tmp/data.txt 445--error 0,1 446--remove_file $datafile 447 448--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> 449--error ER_UNSUPPORTED_PS 450eval prepare stmt1 from ' load data infile ''$datafile'' 451 into table t1 fields terminated by ''\t'' '; 452--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> 453eval prepare stmt1 from ' select * into outfile ''$datafile'' from t1 '; 454 execute stmt1 ; 455## 456prepare stmt1 from ' optimize table t1 ' ; 457prepare stmt1 from ' analyze table t1 ' ; 458prepare stmt1 from ' checksum table t1 ' ; 459prepare stmt1 from ' repair table t1 ' ; 460 461--remove_file $datafile 462 463## handler 464--error ER_UNSUPPORTED_PS 465prepare stmt1 from ' handler t1 open '; 466 467 468## commit/rollback 469prepare stmt3 from ' commit ' ; 470prepare stmt3 from ' rollback ' ; 471 472 473## switch the sql_mode 474prepare stmt4 from ' SET sql_mode=ansi '; 475execute stmt4; 476# check if the sql_mode is now ansi 477select 'a' || 'b' ; 478prepare stmt4 from ' SET sql_mode="" '; 479execute stmt4; 480# check if the sql_mode is not ansi 481select '2' || '3' ; 482# Will a switch of the sqlmode affect the execution of already prepared 483# statements ? 484prepare stmt5 from ' select ''2'' || ''3'' ' ; 485execute stmt5; 486SET sql_mode=ansi; 487execute stmt5; 488SET sql_mode=""; 489 490prepare stmt1 from ' flush local privileges ' ; 491prepare stmt1 from ' reset query cache ' ; 492prepare stmt1 from ' KILL 0 '; 493 494## simple explain 495# cases derived from client_test.c: test_explain_bug() 496prepare stmt1 from ' explain select a from t1 order by b '; 497# PS protocol gives slightly different metadata 498--disable_ps_protocol 499--enable_metadata 500--replace_result 4096 4_OR_8_K 8192 4_OR_8_K 501execute stmt1; 502--disable_metadata 503SET @arg00=1 ; 504prepare stmt1 from ' explain select a from t1 where a > ? order by b '; 505--enable_metadata 506--replace_result 4096 4_OR_8_K 8192 4_OR_8_K 507execute stmt1 using @arg00; 508--disable_metadata 509--enable_ps_protocol 510 511## parameters with probably problematic characters (quote, double quote) 512# cases derived from client_test.c: test_logs() 513# try if 514--disable_warnings 515drop table if exists t2; 516--enable_warnings 517create table t2 (id smallint, name varchar(20)) ; 518prepare stmt1 from ' insert into t2 values(?, ?) ' ; 519set @id= 9876 ; 520set @arg00= 'MySQL - Open Source Database' ; 521set @arg01= "'" ; 522set @arg02= '"' ; 523set @arg03= "my'sql'" ; 524set @arg04= 'my"sql"' ; 525insert into t2 values ( @id , @arg00 ); 526insert into t2 values ( @id , @arg01 ); 527insert into t2 values ( @id , @arg02 ); 528insert into t2 values ( @id , @arg03 ); 529insert into t2 values ( @id , @arg04 ); 530prepare stmt1 from ' select * from t2 where id= ? and name= ? '; 531execute stmt1 using @id, @arg00 ; 532execute stmt1 using @id, @arg01 ; 533execute stmt1 using @id, @arg02 ; 534execute stmt1 using @id, @arg03 ; 535execute stmt1 using @id, @arg04 ; 536drop table t2; 537 538################ CREATE/DROP/ALTER/RENAME TESTS ################ 539--disable_query_log 540select '------ create/drop/alter/rename tests ------' as test_sequence ; 541--enable_query_log 542 543--disable_warnings 544drop table if exists t2, t3; 545--enable_warnings 546 547## DROP TABLE 548prepare stmt_drop from ' drop table if exists t2 ' ; 549--disable_warnings 550execute stmt_drop; 551--enable_warnings 552 553## CREATE TABLE 554prepare stmt_create from ' create table t2 ( 555 a int primary key, b char(10)) '; 556execute stmt_create; 557prepare stmt3 from ' create table t3 like t2 '; 558execute stmt3; 559drop table t3; 560 561## CREATE TABLE .. SELECT 562set @arg00=1; 563prepare stmt3 from ' create table t3 (m int) select ? as m ' ; 564# Bug#4280 server hangs, prepared "create table .. as select ? .." 565execute stmt3 using @arg00; 566select m from t3; 567drop table t3; 568 569prepare stmt3 from ' create index t2_idx on t2(b) '; 570prepare stmt3 from ' drop index t2_idx on t2 ' ; 571prepare stmt3 from ' alter table t2 drop primary key '; 572 573## RENAME TABLE 574--disable_warnings 575drop table if exists new_t2; 576--enable_warnings 577prepare stmt3 from ' rename table t2 to new_t2 '; 578execute stmt3; 579--error ER_TABLE_EXISTS_ERROR 580execute stmt3; 581rename table new_t2 to t2; 582drop table t2; 583## RENAME more than on TABLE within one statement 584# cases derived from client_test.c: test_rename() 585prepare stmt1 from ' rename table t5 to t6, t7 to t8 ' ; 586create table t5 (a int) ; 587--error ER_NO_SUCH_TABLE 588execute stmt1 ; 589create table t7 (a int) ; 590# rename, t5 -> t6 and t7 -> t8 591execute stmt1 ; 592# rename must fail, t5 and t7 does not exist t6 and t8 already exist 593--error ER_TABLE_EXISTS_ERROR 594execute stmt1 ; 595rename table t6 to t5, t8 to t7 ; 596# rename, t5 -> t6 and t7 -> t8 597execute stmt1 ; 598drop table t6, t8 ; 599 600 601################ BIG STATEMENT TESTS ################ 602--disable_query_log 603select '------ big statement tests ------' as test_sequence ; 604--enable_query_log 605# The following tests use huge numbers of lines, characters or parameters 606# per prepared statement. 607# I assume the server and also the client (mysqltest) are stressed. 608# 609# Attention: The limits used are NOT derived from the manual 610# or other sources. 611 612## many lines ( 50 ) 613let $my_stmt= select 'ABC' as my_const_col from t1 where 6141 = 1 AND 6151 = 1 AND 6161 = 1 AND 6171 = 1 AND 6181 = 1 AND 6191 = 1 AND 6201 = 1 AND 6211 = 1 AND 6221 = 1 AND 6231 = 1 AND 6241 = 1 AND 6251 = 1 AND 6261 = 1 AND 6271 = 1 AND 6281 = 1 AND 6291 = 1 AND 6301 = 1 AND 6311 = 1 AND 6321 = 1 AND 6331 = 1 AND 6341 = 1 AND 6351 = 1 AND 6361 = 1 AND 6371 = 1 AND 6381 = 1 AND 6391 = 1 AND 6401 = 1 AND 6411 = 1 AND 6421 = 1 AND 6431 = 1 AND 6441 = 1 AND 6451 = 1 AND 6461 = 1 AND 6471 = 1 AND 6481 = 1 AND 6491 = 1 AND 6501 = 1 AND 6511 = 1 AND 6521 = 1 AND 6531 = 1 AND 6541 = 1 AND 6551 = 1 AND 6561 = 1 AND 6571 = 1 AND 6581 = 1 AND 6591 = 1 AND 6601 = 1 AND 6611 = 1 AND 6621 = 1 ; 663eval ($my_stmt) ; 664eval prepare stmt1 from "$my_stmt" ; 665execute stmt1 ; 666execute stmt1 ; 667 668## many characters ( about 1400 ) 669 670let $my_stmt= select 'ABC' as my_const_col FROM t1 WHERE 671'1234567890123456789012345678901234567890123456789012345678901234567890' 672= '1234567890123456789012345678901234567890123456789012345678901234567890' AND 673'1234567890123456789012345678901234567890123456789012345678901234567890' 674= '1234567890123456789012345678901234567890123456789012345678901234567890' AND 675'1234567890123456789012345678901234567890123456789012345678901234567890' 676= '1234567890123456789012345678901234567890123456789012345678901234567890' AND 677'1234567890123456789012345678901234567890123456789012345678901234567890' 678= '1234567890123456789012345678901234567890123456789012345678901234567890' AND 679'1234567890123456789012345678901234567890123456789012345678901234567890' 680= '1234567890123456789012345678901234567890123456789012345678901234567890' AND 681'1234567890123456789012345678901234567890123456789012345678901234567890' 682= '1234567890123456789012345678901234567890123456789012345678901234567890' AND 683'1234567890123456789012345678901234567890123456789012345678901234567890' 684= '1234567890123456789012345678901234567890123456789012345678901234567890' AND 685'1234567890123456789012345678901234567890123456789012345678901234567890' 686= '1234567890123456789012345678901234567890123456789012345678901234567890' AND 687'1234567890123456789012345678901234567890123456789012345678901234567890' 688= '1234567890123456789012345678901234567890123456789012345678901234567890' ; 689eval ($my_stmt) ; 690eval prepare stmt1 from "$my_stmt" ; 691execute stmt1 ; 692execute stmt1 ; 693 694 695## many parameters ( 50 ) 696--disable_query_log 697set @arg00= 1; 698set @arg01= 1; 699set @arg02= 1; 700set @arg03= 1; 701set @arg04= 1; 702set @arg05= 1; 703set @arg06= 1; 704set @arg07= 1; 705set @arg10= 1; 706set @arg11= 1; 707set @arg12= 1; 708set @arg13= 1; 709set @arg14= 1; 710set @arg15= 1; 711set @arg16= 1; 712set @arg17= 1; 713set @arg20= 1; 714set @arg21= 1; 715set @arg22= 1; 716set @arg23= 1; 717set @arg24= 1; 718set @arg25= 1; 719set @arg26= 1; 720set @arg27= 1; 721set @arg30= 1; 722set @arg31= 1; 723set @arg32= 1; 724set @arg33= 1; 725set @arg34= 1; 726set @arg35= 1; 727set @arg36= 1; 728set @arg37= 1; 729set @arg40= 1; 730set @arg41= 1; 731set @arg42= 1; 732set @arg43= 1; 733set @arg44= 1; 734set @arg45= 1; 735set @arg46= 1; 736set @arg47= 1; 737set @arg50= 1; 738set @arg51= 1; 739set @arg52= 1; 740set @arg53= 1; 741set @arg54= 1; 742set @arg55= 1; 743set @arg56= 1; 744set @arg57= 1; 745set @arg60= 1; 746set @arg61= 1; 747--enable_query_log 748 749select 'ABC' as my_const_col FROM t1 WHERE 750@arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and 751@arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and 752@arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and 753@arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and 754@arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and 755@arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and 756@arg00=@arg00 ; 757prepare stmt1 from ' select ''ABC'' as my_const_col FROM t1 WHERE 758 ? = ? and ? = ? and ? = ? and ? = ? and 759 ? = ? and ? = ? and ? = ? and ? = ? and 760 ? = ? and ? = ? and ? = ? and ? = ? and 761 ? = ? and ? = ? and ? = ? and ? = ? and 762 ? = ? and ? = ? and ? = ? and ? = ? and 763 ? = ? and ? = ? and ? = ? and ? = ? and 764 ? = ? ' ; 765execute stmt1 using 766@arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 767@arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 768@arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 769@arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 770@arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 771@arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 772@arg00, @arg00; 773execute stmt1 using 774@arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, 775@arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16, @arg17, 776@arg20, @arg21, @arg22, @arg23, @arg24, @arg25, @arg26, @arg27, 777@arg30, @arg31, @arg32, @arg33, @arg34, @arg35, @arg36, @arg37, 778@arg40, @arg41, @arg42, @arg43, @arg44, @arg45, @arg46, @arg47, 779@arg50, @arg51, @arg52, @arg53, @arg54, @arg55, @arg56, @arg57, 780@arg60, @arg61 ; 781 782# cases derived from client_test.c: test_mem_overun() 783--disable_warnings 784drop table if exists t5 ; 785--enable_warnings 786 787set @col_num= 1000 ; 788 789--disable_query_log 790set @string= 'create table t5( ' ; 791let $1=`select @col_num - 1` ; 792while ($1) 793{ 794 eval set @string= concat(@string, 'c$1 int,') ; 795 dec $1 ; 796} 797set @string= concat(@string, 'c0 int)' ); 798--enable_query_log 799select @string as "" ; 800prepare stmt1 from @string ; 801execute stmt1 ; 802 803--disable_query_log 804set @string= 'insert into t5 values(' ; 805let $1=`select @col_num - 1` ; 806while ($1) 807{ 808 eval set @string= concat(@string, '1 ,') ; 809 dec $1 ; 810} 811eval set @string= concat(@string, '1 )') ; 812--enable_query_log 813select @string as "" ; 814prepare stmt1 from @string ; 815execute stmt1 ; 816 817prepare stmt1 from ' select * from t5 ' ; 818--enable_metadata 819# prevent too long lines 820--vertical_results 821--disable_result_log 822execute stmt1 ; 823--enable_result_log 824--disable_metadata 825--horizontal_results 826 827drop table t1, t5, t9; 828 829--echo # 830--echo # testcase for bug#11765413 - Crash with dependent subquery and 831--echo # prepared statement 832create table t1 (c1 int); 833insert into t1 values (1); 834prepare stmt1 from "select 1 from t1 where 1=(select 1 from t1 having c1)"; 835execute stmt1; 836drop prepare stmt1; 837drop table t1; 838 839##### RULES OF THUMB TO PRESERVE THE SYSTEMATICS OF THE PS TEST CASES ##### 840# 841# 0. You don't have the time to 842# - read and pay attention to these rules of thumb 843# - accept that QA may move your test case to a different place 844# (I will not change your code!!) . 845# Please append your test case to 846# t/ps.test 847# 848# 1. You have more time and want to get as much value from you test case as 849# possible. Please try to make the following decisions: 850# 851# Will the execution or result of the sub test case depend on the 852# properties of a storage engine ? 853# 854# NO --> alter t/ps_1general.test (Example: Command with syntax error) 855# If you need a table, please try to use 856# t1 - very simple table 857# t9 - table with nearly all available column types 858# whenever possible. 859# 860# The structure and the content of these tables can be found in 861# include/ps_create.inc CREATE TABLE ... 862# include/ps_renew.inc DELETE all rows and INSERT some rows 863# 864# Both tables are managed by the same storage engine. 865# The type of the storage engine is stored in the variable 866# '$type' . In ps_1general.test $type is set to 'MYISAM'. 867# 868# Please feel free to source ps_create.inc or ps_renew.inc 869# whenever you think it helps. But please restore the original 870# state of these tables after your tests, because the following 871# statements may depend on it. 872# 873# YES 874# | 875# | 876# Is it possible to apply the sub test case to all table types ? 877# YES --> alter include/ps_query.inc (for SELECTs) 878# include/ps_modify.inc (for INSERT/UPDATE/DELETE) 879# include/ps_modify1.inc (also for INSERT/UPDATE/DELETE, 880# but t/ps_5merge.test will not source that file) 881# Please try to find an appropriate place within the file. 882# It would be nice if we have some systematics in the 883# order of the sub test cases (if possible). 884# 885# Please be aware, that 886# include: ps_query.inc, ps_modify.inc, ps_modify1.inc 887# will be sourced by several test case files stored within the 888# subdirectory 't'. So every change here will affect several test 889# cases. 890# 891# NO 892# | 893# | 894# Append the sub test case to the appropriate 895# ps_<number><table type>.test . 896# 897# 2. The current structure of the PS tests 898# 899# t/ps_1general.test Check of basic PS features, SHOW commands and DDL 900# The tests should not depend on the table type. 901# 902# t/ps_2myisam Check of PS on tables of type MYISAM . 903# t/ps_3innodb Check of PS on tables of type InnoDB . 904# ... 905# t/ps_6bdb Check of PS on tables of type BDB . 906# All storage engine related tests use the variable $type to hold the 907# name of the storage engine. 908# 909# include/ps_query.inc test cases with SELECT/... 910# These test cases should not modify the content or 911# the structure (DROP/ALTER..) of the tables 912# 't1' and 't9'. 913# include/ps_modify.inc test cases with INSERT/UPDATE/... 914# These test cases should not modify the structure 915# (DROP/ALTER..) of the tables 916# 't1' and 't9'. 917# These two test sequences will be applied to all table types . 918# 919# include/ps_modify1.inc test cases with INSERT/UPDATE/... 920# This test sequences will be applied to all table types 921# except MERGE tables. 922# 923# include/ps_create.inc DROP and CREATE of the tables 924# 't1' and 't9' . 925# include/ps_renew.inc DELETE all rows and INSERT some rows, that means 926# recreate the original content of these tables. 927# Please do not alter the commands concerning these two tables. 928# 929# Please feel free and encouraged to exploit the current code sharing 930# mechanism of the 'ps_<number><table type>' test cases. It is an convenient 931# way to check all storage engines. 932# 933# Thank you for reading these rules of thumb. 934# 935# Matthias 936 937# End of 4.1 tests 938 939# 940# MDEV-10318 unset params in --ps --embedded 941# 942--error ER_PARSE_ERROR,2031 943select ?+1; 944 945