ps_1general.test 27.4 KB
Newer Older
1 2 3 4 5 6
###################### ps_general.test #######################
#                                                            #
#   basic and miscellaneous tests for prepared statements    #
#                                                            #
##############################################################

7 8 9
#    
# NOTE: PLEASE SEE THE DETAILED DESCRIPTION AT THE BOTTOM OF THIS FILE
#       BEFORE ADDING NEW TEST CASES HERE !!!
10

monty@mysql.com's avatar
monty@mysql.com committed
11 12 13
--disable_warnings
drop table if exists t5, t6, t7, t8;
drop database if exists mysqltest ;
monty@mysql.com's avatar
monty@mysql.com committed
14

konstantin@mysql.com's avatar
konstantin@mysql.com committed
15
# Cleanup from other tests
16
drop database if exists client_test_db;
konstantin@mysql.com's avatar
konstantin@mysql.com committed
17 18 19
drop database if exists testtets;
drop table if exists t1Aa,t2Aa,v1Aa,v2Aa;
drop view if exists t1Aa,t2Aa,v1Aa,v2Aa;
monty@mysql.com's avatar
monty@mysql.com committed
20 21
--enable_warnings

22 23 24 25 26
--disable_query_log
select '------ basic tests ------' as test_sequence ;
--enable_query_log

let $type= 'MYISAM' ;
27
# create the tables (t1 and t9) used in many tests
28 29 30 31 32
--source include/ps_create.inc
# insert data into these tables
--source include/ps_renew.inc


33
################ The basic functions ################
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

# 1. PREPARE stmt_name FROM <preparable statement>;
#    <preparable statement> ::=
#     'literal_stmt' |
#     @variable_ref_stmt.
#    The statement may contain question marks as placeholders for parameters.
#
#    Bind a statement name to a string containing a SQL statement and
#    send it to the server. The server will parse the statement and
#    reply with "Query Ok" or an error message.
#
PREPARE stmt FROM ' select * from t1 where a = ? ' ;

# 2. EXECUTE stmt_name [USING @var [, @var ]];
#    Current values of supplied variables are used as parameters.
#
#    Send the server the order to execute the statement and supply values
#    for the input parameters needed.
#    If no error occurs the server reply will be identical to the reply for
#    the query used in PREPARE with question marks replaced with values of
#    the input variables.
#
SET @var= 2 ;
EXECUTE stmt USING @var ;
#    The non prepared statement with the same server reply would be:
select * from t1 where a = @var ;

# 3. DEALLOCATE PREPARE stmt_name;
#
#    Send the server the order to drop the parse informations.
#    The server will reply with "Query Ok" or an error message.
DEALLOCATE PREPARE stmt ;

67
################ PREPARE ################
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103
# prepare without parameter
prepare stmt1 from ' select 1 as my_col ' ;
# prepare with parameter
prepare stmt1 from ' select ? as my_col ' ;
# prepare must fail (incomplete statements/wrong syntax)
--error 1064
prepare ;
--error 1064
prepare stmt1 ;
--error 1064
prepare stmt1 from ;
--error 1064
prepare_garbage stmt1 from ' select 1 ' ;
--error 1064
prepare stmt1 from_garbage ' select 1 ' ;
--error 1064
prepare stmt1 from ' select_garbage 1 ' ;
--error 1064
prepare from ' select 1 ' ;
--error 1064
prepare stmt1 ' select 1 ' ;
--error 1064
prepare ? from ' select ? as my_col ' ;
# statement in variable
set @arg00='select 1 as my_col';
prepare stmt1 from @arg00;
# prepare must fail (query variable is empty)
set @arg00='';
--error 1065
prepare stmt1 from @arg00;
set @arg00=NULL;
# prepare must fail (query variable is NULL)
--error 1064
prepare stmt1 from @arg01;

prepare stmt1 from ' select * from t1 where a <= 2 ' ;
104
# prepare must fail (column x does not exist)
105 106
--error 1054
prepare stmt1 from ' select * from t1 where x <= 2 ' ;
107 108 109 110 111 112
# cases derived from client_test.c: test_null()
# prepare must fail (column x does not exist)
--error 1054
prepare stmt1 from ' insert into t1(a,x) values(?,?) ' ;
--error 1054
prepare stmt1 from ' insert into t1(x,a) values(?,?) ' ;
113 114 115 116 117 118 119 120 121 122 123 124 125 126 127
--disable_warnings
drop table if exists not_exist ;
--enable_warnings
# prepare must fail (table does not exist)
--error 1146
prepare stmt1 from ' select * from not_exist where a <= 2 ' ;

# case derived from client_test.c: test_prepare_syntax()
# prepare must fail (incomplete statement)
--error 1064
prepare stmt1 from ' insert into t1 values(? ' ;
--error 1064
prepare stmt1 from ' select a, b from t1
                     where a=? and where ' ;

128
################ EXECUTE ################
129 130 131 132 133 134 135 136 137 138 139 140
# execute must fail (statement never_prepared never prepared)
--error 1243
execute never_prepared ;
# execute must fail (prepare stmt1 just failed,
#         but there was a successful prepare of stmt1 before)
prepare stmt1 from ' select * from t1 where a <= 2 ' ;
--error 1146
prepare stmt1 from ' select * from not_exist where a <= 2 ' ;
--error 1243
execute stmt1 ;

# drop the table between prepare and execute
141
create table t5
142 143 144 145 146
(
  a int primary key,
  b char(30),
  c int
);
147 148
insert into t5( a, b, c) values( 1, 'original table', 1);
prepare stmt2 from ' select * from t5 ' ;
149
execute stmt2 ;
150
drop table t5 ;
151 152 153 154 155 156
# execute must fail (table was dropped after prepare)
--error 1146
execute stmt2 ;
# cases derived from client_test.c: test_select_prepare()
# 1. drop + create table (same column names/types/order) 
# between prepare and execute
157
create table t5
158 159 160 161 162
(
  a int primary key,
  b char(30),
  c int
);
163
insert into t5( a, b, c) values( 9, 'recreated table', 9);
164
execute stmt2 ;
165
drop table t5 ;
166 167
# 2. drop + create table (same column names/types but different order)
# between prepare and execute
168
create table t5
169 170 171 172 173
(
  a int primary key,
  c int,
  b char(30)
);
174
insert into t5( a, b, c) values( 9, 'recreated table', 9);
175
execute stmt2 ;
176
drop table t5 ;
177 178
# 3. drop + create table (same column names/types/order+extra column) 
# between prepare and execute
179
create table t5
180 181 182 183 184 185
(
  a int primary key,
  b char(30),
  c int,
  d timestamp default current_timestamp
);
186
insert into t5( a, b, c) values( 9, 'recreated table', 9);
187
execute stmt2 ;
188
drop table t5 ;
189 190
# 4. drop + create table (same column names/types, different order +
# additional column) between prepare and execute
191
create table t5
192 193 194 195 196 197
(
  a int primary key,
  d timestamp default current_timestamp,
  b char(30),
  c int
);
198
insert into t5( a, b, c) values( 9, 'recreated table', 9);
199
execute stmt2 ;
200
drop table t5 ;
201 202
# 5. drop + create table (same column names/order, different types)
# between prepare and execute
203
create table t5
204 205 206 207 208
(
  a timestamp default '2004-02-29 18:01:59',
  b char(30),
  c int
);
209
insert into t5( b, c) values( 'recreated table', 9);
210
execute stmt2 ;
211
drop table t5 ;
212 213
# 6. drop + create table (same column types/order, different names) 
# between prepare and execute
214
create table t5
215 216 217 218 219
(
  f1 int primary key,
  f2 char(30),
  f3 int
);
220
insert into t5( f1, f2, f3) values( 9, 'recreated table', 9);
221 222
--error 1054
execute stmt2 ;
223
drop table t5 ;
224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241

# execute without parameter
prepare stmt1 from ' select * from t1 where a <= 2 ' ;
execute stmt1 ;
# execute with parameter
set @arg00=1 ;
set @arg01='two' ;
prepare stmt1 from ' select * from t1 where a <= ? ' ;
execute stmt1 using @arg00;
# execute must fail (too small number of parameters)
--error 1210
execute stmt1 ;
# execute must fail (too big number of parameters)
--error 1210
execute stmt1 using @arg00, @arg01;
# execute must fail (parameter is not set)
execute stmt1 using @not_set;

242 243
################ DEALLOCATE ################
# deallocate must fail (the statement 'never_prepared' was never prepared)
244 245 246 247 248 249 250 251 252
--error 1243
deallocate prepare never_prepared ;
# deallocate must fail (prepare stmt1 just failed,
#         but there was a successful prepare before)
prepare stmt1 from ' select * from t1 where a <= 2 ' ;
--error 1146
prepare stmt1 from ' select * from not_exist where a <= 2 ' ;
--error 1243
deallocate prepare stmt1;
253
create table t5
254 255 256 257
(
  a int primary key,
  b char(10)
);
258 259
prepare stmt2 from ' select a,b from t5 where a <= 2 ' ;
drop table t5 ;
260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289
# deallocate prepared statement where the table was dropped after prepare
deallocate prepare stmt2;

## parallel use of more than one prepared statement handlers
# switch between different queries
prepare stmt1 from ' select a from t1 where a <= 2 ' ;
prepare stmt2 from ' select b from t1 where a <= 2 ' ;
execute stmt2 ;
execute stmt1 ;
# switch between statement handlers of the same query
prepare stmt1 from ' select a from t1 where a <= 2 ' ;
prepare stmt2 from ' select a from t1 where a <= 2 ' ;
execute stmt2 ;
execute stmt1 ;
deallocate prepare stmt1 ;
# Will the deallocate of stmt1 with the same query affect stmt2 ?
execute stmt2 ;

--disable_query_log
select '------ show and misc tests ------' as test_sequence ;
--enable_query_log

--disable_warnings
drop table if exists t2;
--enable_warnings
create table t2 
(
  a int primary key, b char(10)
);

290
################ SHOW COMMANDS ################
291 292 293 294
prepare stmt4 from ' show databases ';
execute stmt4;
prepare stmt4 from ' show tables from test like ''t2%'' ';
execute stmt4;
295 296 297 298 299 300 301 302
prepare stmt4 from ' show columns from t2 where field in (select ?) ';
SET @arg00="a";
execute stmt4 using @arg00;
SET @arg00="b";
execute stmt4 using @arg00;
SET @arg00=1;
execute stmt4 using @arg00;

303 304 305 306 307 308 309
prepare stmt4 from ' show columns from t2 from test like ''a%'' ';
execute stmt4;
create index t2_idx on t2(b);
prepare stmt4 from ' show index from t2 from test ';
execute stmt4;
prepare stmt4 from ' show table status from test like ''t2%'' ';
# egalize date and time values
310
--replace_column 8 # 12 # 13 # 14 #
311 312 313
# Bug#4288 : prepared statement 'show table status ..', wrong output on execute
execute stmt4;
# try the same with the big table
314
prepare stmt4 from ' show table status from test like ''t9%'' ';
315
# egalize date and time values
316
--replace_column 8 # 12 # 13 # 14 #
317 318 319
# Bug#4288
execute stmt4;
prepare stmt4 from ' show status like ''Threads_running'' ';
320
--replace_column 2 #
321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348
execute stmt4;
prepare stmt4 from ' show variables like ''sql_mode'' ';
execute stmt4;
prepare stmt4 from ' show engine bdb logs ';
# The output depends on the history (actions of the bdb engine).
# That is the reason why, we switch the output here off.
#   (The real output will be tested in ps_6bdb.test)
# --replace_result $MYSQL_TEST_DIR TEST_DIR
--disable_result_log
execute stmt4;
--enable_result_log
prepare stmt4 from ' show grants for user ';
--error 1295
prepare stmt4 from ' show create table t2 ';
--error 1295
prepare stmt4 from ' show master status ';
--error 1295
prepare stmt4 from ' show master logs ';
--error 1295
prepare stmt4 from ' show slave status ';
--error 1295
prepare stmt4 from ' show warnings limit 20 ';
--error 1295
prepare stmt4 from ' show errors limit 20 ';
prepare stmt4 from ' show storage engines ';
--replace_column 2 YES/NO
execute stmt4;

349
################ MISC STUFF ################
350 351 352
## get a warning and an error
# cases derived from client_test.c: test_warnings(), test_errors()
--disable_warnings
353
drop table if exists t5;
354
--enable_warnings
355
prepare stmt1 from ' drop table if exists t5 ' ;
356
execute stmt1 ;
357
prepare stmt1 from ' drop table t5 ' ;
358 359 360
--error 1051
execute stmt1 ;

361 362
## SELECT @@version
# cases derived from client_test.c: test_select_version()
363 364 365 366 367 368 369 370
#
# TODO: Metadata check is temporary disabled here, because metadata of 
# this statement also depends on @@version contents and you can't apply
# replace_column and replace_result to it. It will be enabled again when 
# support of replace_column and replace_result on metadata will be
# implemented.
#
#--enable_metadata
371 372 373 374
prepare stmt1 from ' SELECT @@version ' ;
# egalize the version
--replace_column 1 <version>
execute stmt1 ;
375
#--disable_metadata
376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417

## do @var:= and set @var=
# cases derived from client_test.c: test_do_set()
prepare stmt_do from ' do @var:=  (1 in (select a from t1)) ' ;
prepare stmt_set from ' set @var= (1 in (select a from t1)) ' ;
let $1= 3 ;
while ($1)
{
  execute stmt_do ;
  --disable_query_log
  select @var as 'content of @var is:' ;
  --enable_query_log
  execute stmt_set ;
  --disable_query_log
  select @var as 'content of @var is:' ;
  --enable_query_log
  dec $1 ;
}
# the same test with a table containing one column and 'select *'
--disable_warnings
drop table if exists t5 ;
--enable_warnings
create table t5 (a int) ;
prepare stmt_do from ' do @var:=  (1 in (select a from t5)) ' ;
prepare stmt_set from ' set @var= (1 in (select a from t5)) ' ;
let $1= 3 ;
while ($1)
{
  execute stmt_do ;
  --disable_query_log
  select @var as 'content of @var is:' ;
  --enable_query_log
  execute stmt_set ;
  --disable_query_log
  select @var as 'content of @var is:' ;
  --enable_query_log
  dec $1 ;
}
drop table t5 ;
deallocate prepare stmt_do ;
deallocate prepare stmt_set ;

418 419 420 421 422 423 424 425 426 427 428 429 430 431
## nonsense like prepare of prepare,execute or deallocate
--error 1064
prepare stmt1 from ' prepare stmt2 from '' select 1 ''  ' ;
--error 1064
prepare stmt1 from ' execute stmt2 ' ;
--error 1064
prepare stmt1 from ' deallocate prepare never_prepared ' ;

## switch the database connection
--error 1295
prepare stmt4 from ' use test ' ;

## create/drop database
--error 1295
432 433
prepare stmt3 from ' create database mysqltest ';
create database mysqltest ;
434
--error 1295
435 436
prepare stmt3 from ' drop database mysqltest ';
drop database mysqltest ;
437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481

#### table related commands
## describe
prepare stmt3 from ' describe t2 ';
execute stmt3;
drop table t2 ;
--error 1146
execute stmt3;
## lock/unlock
--error 1295
prepare stmt3 from ' lock tables t1 read ' ;
--error 1295
prepare stmt3 from ' unlock tables ' ;
## Load/Unload table contents
--error 1295
prepare stmt1 from ' load data infile ''data.txt''
into table t1 fields terminated by ''\t'' ';
prepare stmt1 from ' select * into outfile ''data.txt'' from t1 ';
execute stmt1 ;
## 
prepare stmt1 from ' optimize table t1 ' ;
prepare stmt1 from ' analyze table t1 ' ;
--error 1295
prepare stmt1 from ' checksum table t1 ' ;
prepare stmt1 from ' repair table t1 ' ;
--error 1295
prepare stmt1 from ' restore table t1 from ''data.txt'' ' ;
## handler
--error 1295
prepare stmt1 from ' handler t1 open ';


## commit/rollback
prepare stmt3 from ' commit ' ;
prepare stmt3 from ' rollback ' ;


## switch the sql_mode
prepare stmt4 from ' SET sql_mode=ansi ';
execute stmt4;
# check if the sql_mode is now ansi
select 'a' || 'b' ;
prepare stmt4 from ' SET sql_mode="" ';
execute stmt4;
# check if the sql_mode is not ansi
482
select '2' || '3' ;
483 484
# Will a switch of the sqlmode affect the execution of already prepared 
# statements ?
485
prepare stmt5 from ' select ''2'' || ''3'' ' ;
486 487 488 489 490 491 492 493 494 495 496 497 498 499 500
execute stmt5;
SET sql_mode=ansi;
execute stmt5;
SET sql_mode="";

--error 1295
prepare stmt1 from ' flush local privileges ' ;
--error 1295
prepare stmt1 from ' reset query cache ' ;
--error 1295
prepare stmt1 from ' KILL 0 ';

## simple explain
# cases derived from client_test.c: test_explain_bug()
prepare stmt1 from ' explain select a from t1 order by b ';
501 502
# PS protocol gives slightly different metadata
--disable_ps_protocol
503 504 505 506 507 508 509 510
--enable_metadata
execute stmt1;
--disable_metadata
SET @arg00=1 ;
prepare stmt1 from ' explain select a from t1 where a > ? order by b ';
--enable_metadata
execute stmt1 using @arg00;
--disable_metadata
511
--enable_ps_protocol
512

513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540
## parameters with probably problematic characters (quote, double  quote)
# cases derived from client_test.c: test_logs()
# try if 
--disable_warnings
drop table if exists t2;
--enable_warnings
create table t2 (id smallint, name varchar(20)) ;
prepare stmt1 from ' insert into t2 values(?, ?) ' ;
set @id= 9876 ;
set @arg00= 'MySQL - Open Source Database' ;
set @arg01= "'" ;
set @arg02= '"' ;
set @arg03= "my'sql'" ;
set @arg04= 'my"sql"' ;
insert into t2 values ( @id , @arg00 );
insert into t2 values ( @id , @arg01 );
insert into t2 values ( @id , @arg02 );
insert into t2 values ( @id , @arg03 );
insert into t2 values ( @id , @arg04 );
prepare stmt1 from ' select * from t2 where id= ? and name= ? ';
execute stmt1 using @id, @arg00 ;
execute stmt1 using @id, @arg01 ;
execute stmt1 using @id, @arg02 ;
execute stmt1 using @id, @arg03 ;
execute stmt1 using @id, @arg04 ;
drop table t2;

################ CREATE/DROP/ALTER/RENAME TESTS ################
541 542 543 544 545 546 547 548
--disable_query_log
select '------ create/drop/alter/rename tests ------' as test_sequence ;
--enable_query_log

--disable_warnings
drop table if exists t2, t3;
--enable_warnings

549
## DROP TABLE
550 551 552 553 554
prepare stmt_drop from ' drop table if exists t2 ' ;
--disable_warnings
execute stmt_drop;
--enable_warnings

555
## CREATE TABLE
556 557 558 559 560 561 562
prepare stmt_create from ' create table t2 (
                             a int primary key, b char(10)) ';
execute stmt_create;
prepare stmt3 from ' create table t3 like t2 ';
execute stmt3;
drop table t3;

563
## CREATE TABLE .. SELECT
564 565 566 567 568 569 570 571 572 573
set @arg00=1;
prepare stmt3 from ' create table t3 (m int) select ? as m ' ;
# Bug#4280 server hangs, prepared "create table .. as select ? .."
execute stmt3 using @arg00;
select m from t3;
drop table t3;

prepare stmt3 from ' create index t2_idx on t2(b) ';
prepare stmt3 from ' drop index t2_idx on t2 ' ; 
prepare stmt3 from ' alter table t2 drop primary key ';
574 575

## RENAME TABLE
576 577 578 579 580 581 582 583 584
--disable_warnings
drop table if exists new_t2;
--enable_warnings
prepare stmt3 from ' rename table t2 to new_t2 ';
execute stmt3;
--error 1050
execute stmt3;
rename table new_t2 to t2;
drop table t2;
585 586 587 588
## RENAME more than on TABLE within one statement
# cases derived from client_test.c: test_rename()
prepare stmt1 from ' rename table t5 to t6, t7 to t8 ' ;
create table t5 (a int) ;
monty@mysql.com's avatar
monty@mysql.com committed
589
# rename must fail, t7 does not exist
590
# Clean up the filename here because embedded server reports whole path
591
--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / t7.frm t7
592 593 594 595 596 597 598 599 600 601 602 603 604
--error 1017
execute stmt1 ;
create table t7 (a int) ;
# rename, t5 -> t6 and t7 -> t8
execute stmt1 ;
# rename must fail, t5 and t7 does not exist t6 and t8 already exist
--error 1050
execute stmt1 ;
rename table t6 to t5, t8 to t7 ;
# rename, t5 -> t6 and t7 -> t8
execute stmt1 ;
drop table t6, t8 ;

605

606
################ BIG STATEMENT TESTS ################
607 608 609
--disable_query_log
select '------ big statement tests ------' as test_sequence ;
--enable_query_log
610 611 612 613
# The following tests use huge numbers of lines, characters or parameters
# per prepared statement.
# I assume the server and also the client (mysqltest) are stressed.
#
614 615 616 617
# Attention: The limits used are NOT derived from the manual
#            or other sources.

## many lines ( 50 )
618
let $my_stmt= select 'ABC' as my_const_col from t1 where
619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 AND
1 = 1 ;
668 669
eval ($my_stmt) ;
eval prepare stmt1 from "$my_stmt" ;
670 671 672 673 674
execute stmt1 ;
execute stmt1 ;

## many characters ( about 1400 )

675
let $my_stmt= select 'ABC' as my_const_col FROM t1 WHERE
676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693
'1234567890123456789012345678901234567890123456789012345678901234567890'
= '1234567890123456789012345678901234567890123456789012345678901234567890' AND
'1234567890123456789012345678901234567890123456789012345678901234567890'
= '1234567890123456789012345678901234567890123456789012345678901234567890' AND
'1234567890123456789012345678901234567890123456789012345678901234567890'
= '1234567890123456789012345678901234567890123456789012345678901234567890' AND
'1234567890123456789012345678901234567890123456789012345678901234567890'
= '1234567890123456789012345678901234567890123456789012345678901234567890' AND
'1234567890123456789012345678901234567890123456789012345678901234567890'
= '1234567890123456789012345678901234567890123456789012345678901234567890' AND
'1234567890123456789012345678901234567890123456789012345678901234567890'
= '1234567890123456789012345678901234567890123456789012345678901234567890' AND
'1234567890123456789012345678901234567890123456789012345678901234567890'
= '1234567890123456789012345678901234567890123456789012345678901234567890' AND
'1234567890123456789012345678901234567890123456789012345678901234567890'
= '1234567890123456789012345678901234567890123456789012345678901234567890' AND
'1234567890123456789012345678901234567890123456789012345678901234567890'
= '1234567890123456789012345678901234567890123456789012345678901234567890' ;
694 695
eval ($my_stmt) ;
eval prepare stmt1 from "$my_stmt" ;
696 697 698 699 700
execute stmt1 ;
execute stmt1 ;


## many parameters ( 50 )
701
--disable_query_log
702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751
set @arg00= 1;
set @arg01= 1; 
set @arg02= 1;
set @arg03= 1; 
set @arg04= 1;
set @arg05= 1; 
set @arg06= 1;
set @arg07= 1;
set @arg10= 1;
set @arg11= 1; 
set @arg12= 1;
set @arg13= 1; 
set @arg14= 1;
set @arg15= 1; 
set @arg16= 1;
set @arg17= 1; 
set @arg20= 1;
set @arg21= 1; 
set @arg22= 1;
set @arg23= 1; 
set @arg24= 1;
set @arg25= 1; 
set @arg26= 1;
set @arg27= 1; 
set @arg30= 1;
set @arg31= 1; 
set @arg32= 1;
set @arg33= 1; 
set @arg34= 1;
set @arg35= 1; 
set @arg36= 1;
set @arg37= 1; 
set @arg40= 1;
set @arg41= 1; 
set @arg42= 1;
set @arg43= 1; 
set @arg44= 1;
set @arg45= 1; 
set @arg46= 1;
set @arg47= 1; 
set @arg50= 1;
set @arg51= 1; 
set @arg52= 1;
set @arg53= 1; 
set @arg54= 1;
set @arg55= 1; 
set @arg56= 1;
set @arg57= 1; 
set @arg60= 1;
set @arg61= 1;
752
--enable_query_log
753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786

select 'ABC' as my_const_col FROM t1 WHERE
@arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and
@arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and
@arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and
@arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and
@arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and
@arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and @arg00=@arg00 and
@arg00=@arg00 ;
prepare stmt1 from ' select ''ABC'' as my_const_col FROM t1 WHERE
 ? = ?  and  ? = ?  and  ? = ?  and  ? = ?  and
 ? = ?  and  ? = ?  and  ? = ?  and  ? = ?  and
 ? = ?  and  ? = ?  and  ? = ?  and  ? = ?  and
 ? = ?  and  ? = ?  and  ? = ?  and  ? = ?  and
 ? = ?  and  ? = ?  and  ? = ?  and  ? = ?  and
 ? = ?  and  ? = ?  and  ? = ?  and  ? = ?  and
 ? = ?  ' ;
execute stmt1 using 
@arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
@arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
@arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
@arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
@arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
@arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
@arg00, @arg00;
execute stmt1 using 
@arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, 
@arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16, @arg17,
@arg20, @arg21, @arg22, @arg23, @arg24, @arg25, @arg26, @arg27, 
@arg30, @arg31, @arg32, @arg33, @arg34, @arg35, @arg36, @arg37, 
@arg40, @arg41, @arg42, @arg43, @arg44, @arg45, @arg46, @arg47, 
@arg50, @arg51, @arg52, @arg53, @arg54, @arg55, @arg56, @arg57, 
@arg60, @arg61 ;

787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831
# cases derived from client_test.c: test_mem_overun()
--disable_warnings
drop table if exists t5 ;
--enable_warnings

set @col_num= 1000 ;

--disable_query_log
set @string= 'create table t5( ' ;
let $1=`select @col_num - 1` ;
while ($1)
{
  eval set @string= concat(@string, 'c$1 int,') ;
  dec $1 ;
}
set @string= concat(@string, 'c0 int)' );
--enable_query_log
select @string as "" ;
prepare stmt1 from @string ;
execute stmt1 ;

--disable_query_log
set @string= 'insert into t5 values(' ;
let $1=`select @col_num - 1` ;
while ($1)
{
  eval set @string= concat(@string, '1 ,') ;
  dec $1 ;
}
eval set @string= concat(@string, '1 )') ;
--enable_query_log
select @string as "" ;
prepare stmt1 from @string ;
execute stmt1 ;

prepare stmt1 from ' select * from t5 ' ;
--enable_metadata
# prevent too long lines
--vertical_results
--disable_result_log
execute stmt1 ;
--enable_result_log
--disable_metadata
--horizontal_results

832
drop table t1, t5, t9;
833

834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851
##### RULES OF THUMB TO PRESERVE THE SYSTEMATICS OF THE PS TEST CASES #####
#
# 0. You  don't have the time to 
#    - read and pay attention to these rules of thumb
#    - accept that QA may move your test case to a different place
#      (I will not change your code!!) .
#    Please append your test case to
#        t/ps.test
#
# 1. You have more time and want to get as much value from you test case as
#    possible. Please try to make the following decisions:
#
#    Will the execution or result of the sub test case depend on the
#    properties of a storage engine ?
#
#      NO   --> alter t/ps_1general.test (Example: Command with syntax error)
#               If you need a table, please try to use
#               t1               - very simple table
852
#               t9 - table with nearly all available column types
853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906
#               whenever possible.
#
#               The structure and the content of these tables can be found in
#               include/ps_create.inc  CREATE TABLE ...
#               include/ps_renew.inc   DELETE all rows and INSERT some rows
#
#               Both tables are managed by the same storage engine.
#               The type of the storage engine is stored in the variable 
#               '$type' .  In ps_1general.test $type is set to 'MYISAM'.
#    
#               Please feel free to source ps_create.inc or ps_renew.inc
#               whenever you think it helps. But please restore the original
#               state of these tables after your tests, because the following
#               statements may depend on it.
#
#      YES
#       |
#       |
#    Is it possible to apply the sub test case to all table types ?
#      YES  --> alter include/ps_query.inc   (for SELECTs)
#                     include/ps_modify.inc  (for INSERT/UPDATE/DELETE)
#                     include/ps_modify1.inc (also for INSERT/UPDATE/DELETE,
#                                but t/ps_5merge.test will not source that file)
#               Please try to find an appropriate place within the file.
#               It would be nice if we have some systematics in the
#               order of the sub test cases (if possible).
#
#               Please be aware, that
#                  include: ps_query.inc, ps_modify.inc, ps_modify1.inc
#               will be sourced by several test case files stored within the 
#               subdirectory 't'. So every change here will affect several test
#               cases.
#
#      NO
#       |
#       |
#    Append the sub test case to the appropriate 
#                  ps_<number><table type>.test  .
#
# 2. The current structure of the PS tests
#
#    t/ps_1general.test     Check of basic PS features, SHOW commands and DDL
#                           The tests should not depend on the table type.
#
#    t/ps_2myisam           Check of PS on tables of type MYISAM .
#    t/ps_3innodb           Check of PS on tables of type InnoDB .
#    ...
#    t/ps_6bdb              Check of PS on tables of type BDB .
#         All storage engine related tests use the variable $type to hold the
#         name of the storage engine.        
#
#    include/ps_query.inc   test cases with SELECT/... 
#                           These test cases should not modify the content or
#                           the structure (DROP/ALTER..) of the tables
907
#                           't1' and 't9'.
908 909 910
#    include/ps_modify.inc  test cases with INSERT/UPDATE/... 
#                           These test cases should not modify the structure 
#                           (DROP/ALTER..) of the tables
911
#                           't1' and 't9'.
912 913 914 915 916 917 918
#         These two test sequences will be applied to all table types .
#
#    include/ps_modify1.inc test cases with INSERT/UPDATE/...
#         This test sequences will be applied to all table types 
#         except MERGE tables.
#
#    include/ps_create.inc  DROP and CREATE of the tables 
919
#                             't1' and 't9' .
920 921 922 923 924 925 926 927 928 929 930
#    include/ps_renew.inc   DELETE all rows and INSERT some rows, that means
#                           recreate the original content of these tables.
#         Please do not alter the commands concerning these two tables.
#
#  Please feel free and encouraged to exploit the current code sharing
#  mechanism of the 'ps_<number><table type>' test cases. It is an convenient
#  way to check all storage engines.
#
#  Thank you for reading these rules of thumb.
#
#     Matthias
931 932

# End of 4.1 tests