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