From f2e036aa5fe26abfbe962f63268fd2f1301f1e27 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 1 Jul 2004 16:30:29 +0200 Subject: WL#1564 Intensive test of prepared statements via 'mysqltest' BitKeeper/etc/logging_ok: Logging to logging@openlogging.org accepted --- mysql-test/include/ps_query.inc | 587 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 587 insertions(+) create mode 100644 mysql-test/include/ps_query.inc (limited to 'mysql-test/include/ps_query.inc') diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc new file mode 100644 index 00000000000..819ec4add06 --- /dev/null +++ b/mysql-test/include/ps_query.inc @@ -0,0 +1,587 @@ +####################### ps_query.inc ######################### +# # +# Tests for prepared statements: SELECTs # +# # +############################################################## + + +# Please do not modify (INSERT/UPDATE/DELETE) the content of the tables +# t1 and t_many_col_types. +# Such tests should be done in include/ps_modify.inc + +--disable_query_log +select '------ simple select tests ------' as test_sequence ; +--enable_query_log + +##### parameter used for keyword like SELECT (must fail) +set @arg00='SELECT' ; +# mysqltest gives no output for the next statement, Why ?? +--error 1064 +@arg00 a from t1 where a=1; +--error 1064 +prepare stmt1 from ' ? a from t1 where a=1 '; + +##### parameter in select column list +## parameter is not NULL +set @arg00=1 ; +select @arg00, b from t1 where a=1 ; +prepare stmt1 from ' select ?, b from t1 where a=1 ' ; +execute stmt1 using @arg00 ; +set @arg00='lion' ; +select @arg00, b from t1 where a=1 ; +prepare stmt1 from ' select ?, b from t1 where a=1 ' ; +execute stmt1 using @arg00 ; +## parameter is NULL +set @arg00=NULL ; +select @arg00, b from t1 where a=1 ; +prepare stmt1 from ' select ?, b from t1 where a=1 ' ; +execute stmt1 using @arg00 ; +## parameter within an expression +set @arg00=1 ; +select b, a - @arg00 from t1 where a=1 ; +prepare stmt1 from ' select b, a - ? from t1 where a=1 ' ; +execute stmt1 using @arg00 ; +## parameter is within a function +# variations on 'substr' +set @arg00='MySQL' ; +select substr(@arg00,1,2) from t1 where a=1 ; +prepare stmt1 from ' select substr(?,1,2) from t1 where a=1 ' ; +execute stmt1 using @arg00 ; +set @arg00=3 ; +select substr('MySQL',@arg00,5) from t1 where a=1 ; +prepare stmt1 from ' select substr(''MySQL'',?,5) from t1 where a=1 ' ; +execute stmt1 using @arg00 ; +select substr('MySQL',1,@arg00) from t1 where a=1 ; +prepare stmt1 from ' select substr(''MySQL'',1,?) from t1 where a=1 ' ; +execute stmt1 using @arg00 ; +# variations on 'concat' +set @arg00='MySQL' ; +select a , concat(@arg00,b) from t1 ; +# BUG#3796 +prepare stmt1 from ' select a , concat(?,b) from t1 ' ; +execute stmt1 using @arg00; +# +select a , concat(b,@arg00) from t1 ; +prepare stmt1 from ' select a , concat(b,?) from t1 ' ; +execute stmt1 using @arg00; + +# variations on 'group_concat' +set @arg00='MySQL' ; +select group_concat(@arg00,b) from t1 +group by 'a' ; +prepare stmt1 from ' select group_concat(?,b) from t1 +group by ''a'' ' ; +execute stmt1 using @arg00; +# +select group_concat(b,@arg00) from t1 +group by 'a' ; +prepare stmt1 from ' select group_concat(b,?) from t1 +group by ''a'' ' ; +execute stmt1 using @arg00; + +## two parameters +set @arg00='first' ; +set @arg01='second' ; +set @arg02=NULL; +select @arg00, @arg01 from t1 where a=1 ; +prepare stmt1 from ' select ?, ? from t1 where a=1 ' ; +execute stmt1 using @arg00, @arg01 ; +# NULL as first and/or last parameter +execute stmt1 using @arg02, @arg01 ; +execute stmt1 using @arg00, @arg02 ; +execute stmt1 using @arg02, @arg02 ; +# case derived from client_test.c: test_ps_conj_select() +# for BUG#3420: select returned all rows of the table +--disable_warnings +drop table if exists new_tab ; +--enable_warnings +create table new_tab (id1 int(11) not null default '0', + value2 varchar(100), value1 varchar(100)) ; +insert into new_tab values (1,'hh','hh'),(2,'hh','hh'), + (1,'ii','ii'),(2,'ii','ii') ; +prepare stmt1 from ' select id1,value1 from new_tab where id1=? or value1=? ' ; +set @arg00=1 ; +set @arg01='hh' ; +execute stmt1 using @arg00, @arg01 ; +drop table new_tab ; +# case derived from client_test.c: test_bug1180() +# for BUG#1180 optimized away part of WHERE clause +--disable_warnings +drop table if exists new_tab ; +--enable_warnings +create table new_tab(session_id char(9) not null) ; +insert into new_tab values ('abc') ; +prepare stmt1 from ' select * from new_tab +where ?=''1111'' and session_id = ''abc'' ' ; +set @arg00='abc' ; +execute stmt1 using @arg00 ; +set @arg00='1111' ; +execute stmt1 using @arg00 ; +set @arg00='abc' ; +execute stmt1 using @arg00 ; +drop table new_tab ; + + +##### parameter used for keyword FROM (must fail) +set @arg00='FROM' ; +--error 1064 +select a @arg00 t1 where a=1 ; +--error 1064 +prepare stmt1 from ' select a ? t1 where a=1 ' ; +##### parameter used for tablename (must fail) +set @arg00='t1' ; +--error 1064 +select a from @arg00 where a=1 ; +--error 1064 +prepare stmt1 from ' select a from ? where a=1 ' ; +##### parameter used for keyword WHERE tablename (must fail) +set @arg00='WHERE' ; +--error 1064 +select a from t1 @arg00 a=1 ; +--error 1064 +prepare stmt1 from ' select a from t1 ? a=1 ' ; + +##### parameter used in where clause +# parameter is not NULL +set @arg00=1 ; +select a FROM t1 where a=@arg00 ; +prepare stmt1 from ' select a FROM t1 where a=? ' ; +execute stmt1 using @arg00 ; +set @arg00=1000 ; +# row not found +execute stmt1 using @arg00 ; +# parameter is NULL +set @arg00=NULL ; +select a FROM t1 where a=@arg00 ; +prepare stmt1 from ' select a FROM t1 where a=? ' ; +execute stmt1 using @arg00 ; +# parameter is not NULL within a function +set @arg00=4 ; +select a FROM t1 where a=sqrt(@arg00) ; +prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ; +execute stmt1 using @arg00 ; +# parameter is NULL within a function +set @arg00=NULL ; +select a FROM t1 where a=sqrt(@arg00) ; +prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ; +execute stmt1 using @arg00 ; +# parameter in IN +set @arg00=2 ; +set @arg01=3 ; +select a FROM t1 where a in (@arg00,@arg01); +prepare stmt1 from ' select a FROM t1 where a in (?,?) '; +execute stmt1 using @arg00, @arg01; +# parameter in LIKE +prepare stmt1 from ' select b FROM t1 where b like ? '; +set @arg00='two' ; +execute stmt1 using @arg00 ; +set @arg00='tw%' ; +execute stmt1 using @arg00 ; +set @arg00='%wo' ; +execute stmt1 using @arg00 ; + +##### parameter used for operator in WHERE clause (must fail) +set @arg00='>' ; +--error 1064 +select a FROM t1 where a @arg00 1 ; +--error 1064 +prepare stmt1 from ' select a FROM t1 where a ? 1 ' ; + +##### parameter used in group by clause +set @arg00=1 ; +select a,b FROM t1 where a is not NULL +AND b is not NULL group by a - @arg00 ; +prepare stmt1 from ' select a,b FROM t1 where a is not NULL +AND b is not NULL group by a - ? ' ; +execute stmt1 using @arg00 ; + +##### parameter used in having clause +set @arg00='two' ; +select a,b FROM t1 where a is not NULL +AND b is not NULL having b <> @arg00 ; +prepare stmt1 from ' select a,b FROM t1 where a is not NULL +AND b is not NULL having b <> ? ' ; +execute stmt1 using @arg00 ; + +##### parameter used in order clause +set @arg00=1 ; +select a,b FROM t1 where a is not NULL +AND b is not NULL order by a - @arg00 ; +prepare stmt1 from ' select a,b FROM t1 where a is not NULL +AND b is not NULL order by a - ? ' ; +execute stmt1 using @arg00 ; +## What is the semantic of a single parameter (integer >0) +# after order by? column number or constant +set @arg00=2 ; +select a,b from t1 order by 2 ; +prepare stmt1 from ' select a,b from t1 +order by ? '; +execute stmt1 using @arg00; + +##### parameter used in limit clause +set @arg00=1; +prepare stmt1 from ' select a,b from t1 +limit 1 '; +execute stmt1 ; +# currently (May 2004, Version 4.1) it is impossible +-- error 1064 +prepare stmt1 from ' select a,b from t1 +limit ? '; + +##### parameter used in many places +set @arg00='b' ; +set @arg01=0 ; +set @arg02=2 ; +set @arg03=2 ; +select sum(a), @arg00 from t1 where a > @arg01 +and b is not null group by substr(b,@arg02) +having sum(a) <> @arg03 ; +prepare stmt1 from ' select sum(a), ? from t1 where a > ? +and b is not null group by substr(b,?) +having sum(a) <> ? '; +execute stmt1 using @arg00, @arg01, @arg02, @arg03; + + +--disable_query_log +select '------ join tests ------' as test_sequence ; +--enable_query_log + +# no parameter +select first.a as a1, second.a as a2 +from t1 first, t1 second +where first.a = second.a ; +prepare stmt1 from ' select first.a as a1, second.a as a2 + from t1 first, t1 second + where first.a = second.a '; +execute stmt1 ; + +# some parameters +set @arg00='ABC'; +set @arg01='two'; +set @arg02='one'; +select first.a, @arg00, second.a FROM t1 first, t1 second +where @arg01 = first.b or first.a = second.a or second.b = @arg02; +prepare stmt1 from ' select first.a, ?, second.a FROM t1 first, t1 second + where ? = first.b or first.a = second.a or second.b = ? '; +execute stmt1 using @arg00, @arg01, @arg02; + + + +--disable_query_log +select '------ subquery tests ------' as test_sequence ; +--enable_query_log + +# no parameter +prepare stmt1 from ' select a, b FROM t1 outer_table where + a = (select a from t1 where b = ''two'') '; +execute stmt1 ; + +###### parameter in the outer part +set @arg00='two' ; +select a, b FROM t1 outer_table where + a = (select a from t1 where b = 'two' ) and b=@arg00 ; +prepare stmt1 from ' select a, b FROM t1 outer_table where + a = (select a from t1 where b = ''two'') and b=? '; +execute stmt1 using @arg00; +###### parameter in the inner part +set @arg00='two' ; +# Bug#4000 (only BDB tables) +select a, b FROM t1 outer_table where + a = (select a from t1 where b = @arg00 ) and b='two' ; +prepare stmt1 from ' select a, b FROM t1 outer_table where + a = (select a from t1 where b = ? ) and b=''two'' ' ; +execute stmt1 using @arg00; +set @arg00=3 ; +set @arg01='three' ; +select a,b FROM t1 where (a,b) in (select 3, 'three'); +select a FROM t1 where (a,b) in (select @arg00,@arg01); +prepare stmt1 from ' select a FROM t1 where (a,b) in (select ?, ?) '; +execute stmt1 using @arg00, @arg01; + +###### parameters in the both parts +set @arg00=1 ; +set @arg01='two' ; +set @arg02=2 ; +set @arg03='two' ; +# Bug#4000 (only BDB tables) +select a, @arg00, b FROM t1 outer_table where + b=@arg01 and a = (select @arg02 from t1 where b = @arg03 ) ; +prepare stmt1 from ' select a, ?, b FROM t1 outer_table where + b=? and a = (select ? from t1 where b = ? ) ' ; +execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; + +######## correlated subquery +# no parameter +prepare stmt1 from ' select a, b FROM t1 outer_table where + a = (select a from t1 where b = outer_table.b ) '; +# also Bug#4000 (only BDB tables) ?? +execute stmt1 ; + +###### parameter in the outer part +set @arg00='two' ; +# Bug#4000 (only BDB tables) +select a, b FROM t1 outer_table where + a = (select a from t1 where b = outer_table.b ) and b=@arg00 ; +prepare stmt1 from ' select a, b FROM t1 outer_table where + a = (select a from t1 where b = outer_table.b) and b=? '; +# also Bug#4000 (only BDB tables) ?? +execute stmt1 using @arg00; + +###### parameter in the inner part +set @arg00=2 ; +select a, b FROM t1 outer_table where + a = (select a from t1 where a = @arg00 and b = outer_table.b) and b='two' ; +prepare stmt1 from ' select a, b FROM t1 outer_table where + a = (select a from t1 where a = ? and b = outer_table.b) and b=''two'' ' ; +execute stmt1 using @arg00; + +set @arg00=2 ; +select a, b FROM t1 outer_table where + a = (select a from t1 where outer_table.a = @arg00 and a=2) and b='two' ; +prepare stmt1 from ' select a, b FROM t1 outer_table where + a = (select a from t1 where outer_table.a = ? and a=2) and b=''two'' ' ; +execute stmt1 using @arg00; + +###### parameters in the both parts +set @arg00=1 ; +set @arg01='two' ; +set @arg02=2 ; +set @arg03='two' ; +# Bug#4000 (only BDB tables) +select a, @arg00, b FROM t1 outer_table where + b=@arg01 and a = (select @arg02 from t1 where outer_table.b = @arg03 + and outer_table.a=a ) ; +prepare stmt1 from ' select a, ?, b FROM t1 outer_table where + b=? and a = (select ? from t1 where outer_table.b = ? + and outer_table.a=a ) ' ; +# also Bug#4000 (only BDB tables) ?? +execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; + +###### subquery after from +set @arg00=1 ; +set @arg01=0 ; +select a, @arg00 +from ( select a - @arg00 as a from t1 where a=@arg00 ) as t2 +where a=@arg01; +prepare stmt1 from ' select a, ? + from ( select a - ? as a from t1 where a=? ) as t2 + where a=? '; +execute stmt1 using @arg00, @arg00, @arg00, @arg01 ; + +###### heavy modified case derived from client_test.c: test_distinct() +## no parameters +--disable_warnings +drop table if exists t2 ; +--enable_warnings +create table t2 as select * from t_many_col_types; +#insert into t2 select * from t_many_col_types; +set @stmt= ' SELECT + (SELECT SUM(c1 + c12 + 0.0) FROM t2 + where (t_many_col_types.c2 - 0e-3) = t2.c2 + GROUP BY t_many_col_types.c15 LIMIT 1) as scalar_s, + exists (select 1.0e+0 from t2 + where t2.c3 * 9.0000000000 = t_many_col_types.c4) as exists_s, + c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, + (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s +FROM t_many_col_types, +(select c25 x, c32 y from t2) tt WHERE x = c25 ' ; +--enable_metadata +prepare stmt1 from @stmt ; +execute stmt1 ; +--disable_metadata +execute stmt1 ; +set @stmt= concat('explain ',@stmt); +--enable_metadata +prepare stmt1 from @stmt ; +execute stmt1 ; +--disable_metadata +execute stmt1 ; +## many parameters +set @stmt= ' SELECT + (SELECT SUM(c1+c12+?) FROM t2 where (t_many_col_types.c2-?)=t2.c2 + GROUP BY t_many_col_types.c15 LIMIT 1) as scalar_s, + exists (select ? from t2 + where t2.c3*?=t_many_col_types.c4) as exists_s, + c5*? in (select c6+? from t2) as in_s, + (c7-?, c8-?) in (select c9+?, c10+? from t2) as in_row_s +FROM t_many_col_types, +(select c25 x, c32 y from t2) tt WHERE x =c25 ' ; +set @arg00= 0.0 ; +set @arg01= 0e-3 ; +set @arg02= 1.0e+0 ; +set @arg03= 9.0000000000 ; +set @arg04= 4 ; +set @arg05= 0.3e+1 ; +set @arg06= 4 ; +set @arg07= 4 ; +set @arg08= 4.0 ; +set @arg09= 40e-1 ; +--enable_metadata +prepare stmt1 from @stmt ; +execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, + @arg07, @arg08, @arg09 ; +--disable_metadata +execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, + @arg07, @arg08, @arg09 ; +set @stmt= concat('explain ',@stmt); +--enable_metadata +prepare stmt1 from @stmt ; +execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, + @arg07, @arg08, @arg09 ; +--disable_metadata +execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, + @arg07, @arg08, @arg09 ; +drop table t2 ; + + +--disable_query_log +select '------ union tests ------' as test_sequence ; +--enable_query_log + +# no parameter +prepare stmt1 from ' select a FROM t1 where a=1 + union distinct + select a FROM t1 where a=1 '; +execute stmt1 ; +# Bug#3577: the second execute crashes mysqld +execute stmt1 ; +prepare stmt1 from ' select a FROM t1 where a=1 + union all + select a FROM t1 where a=1 '; +execute stmt1 ; + +##### everything in the first table +# one parameter as constant in the first table +set @arg00=1 ; +select @arg00 FROM t1 where a=1 +union distinct +select 1 FROM t1 where a=1; +prepare stmt1 from ' select ? FROM t1 where a=1 + union distinct + select 1 FROM t1 where a=1 ' ; +execute stmt1 using @arg00; + +##### everything in the second table +# one parameter as constant +set @arg00=1 ; +select 1 FROM t1 where a=1 +union distinct +select @arg00 FROM t1 where a=1; +prepare stmt1 from ' select 1 FROM t1 where a=1 + union distinct + select ? FROM t1 where a=1 ' ; +execute stmt1 using @arg00; + +# one parameter in every table +set @arg00='a' ; +select @arg00 FROM t1 where a=1 +union distinct +select @arg00 FROM t1 where a=1; +prepare stmt1 from ' select ? FROM t1 where a=1 + union distinct + select ? FROM t1 where a=1 '; +# BUG#3811 wrong result, prepared statement, union, +# parameter in result column list +execute stmt1 using @arg00, @arg00; +prepare stmt1 from ' select ? + union distinct + select ? '; +execute stmt1 using @arg00, @arg00; + +# many parameters +set @arg00='a' ; +set @arg01=1 ; +set @arg02='a' ; +set @arg03=2 ; +select @arg00 FROM t1 where a=@arg01 +union distinct +select @arg02 FROM t1 where a=@arg03; +prepare stmt1 from ' select ? FROM t1 where a=? + union distinct + select ? FROM t1 where a=? ' ; +execute stmt1 using @arg00, @arg01, @arg02, @arg03; + +## increased complexity + +set @arg00=1 ; +# Bug#3686 the wrong server response was 1140 Mixing of GROUP columns .. +prepare stmt1 from ' select sum(a) + 200, ? from t1 +union distinct +select sum(a) + 200, 1 from t1 +group by b ' ; +execute stmt1 using @arg00; + +set @Oporto='Oporto' ; +set @Lisboa='Lisboa' ; +set @0=0 ; +set @1=1 ; +set @2=2 ; +set @3=3 ; +set @4=4 ; +select @Oporto,@Lisboa,@0,@1,@2,@3,@4 ; + +## union + group by +select sum(a) + 200 as the_sum, @Oporto as the_town from t1 +group by b +union distinct +select sum(a) + 200, @Lisboa from t1 +group by b ; + +prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1 + group by b + union distinct + select sum(a) + 200, ? from t1 + group by b ' ; +execute stmt1 using @Oporto, @Lisboa; + + +## union + where + group by +select sum(a) + 200 as the_sum, @Oporto as the_town from t1 +where a > @1 +group by b +union distinct +select sum(a) + 200, @Lisboa from t1 +where a > @2 +group by b ; + +prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1 + where a > ? + group by b + union distinct + select sum(a) + 200, ? from t1 + where a > ? + group by b ' ; +execute stmt1 using @Oporto, @1, @Lisboa, @2; + +## union + where + group by + having +select sum(a) + 200 as the_sum, @Oporto as the_town from t1 +where a > @1 +group by b +having avg(a) > @2 +union distinct +select sum(a) + 200, @Lisboa from t1 +where a > @2 +group by b +having avg(a) > @3; + +prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1 + where a > ? + group by b + having avg(a) > ? + union distinct + select sum(a) + 200, ? from t1 + where a > ? + group by b + having avg(a) > ? '; +execute stmt1 using @Oporto, @1, @2, @Lisboa, @2, @3; + + +--disable_query_log +select '------ explain select tests ------' as test_sequence ; +--enable_query_log +prepare stmt1 from ' select * from t_many_col_types ' ; +--enable_metadata +execute stmt1; +--disable_metadata + + -- cgit v1.2.1