ps_query.inc

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

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 ;

# Bug#8807
prepare stmt1 from 'select c4 FROM t9 where
    c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ;
execute stmt1 using @arg01, @arg02;

######## 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 ) order by a ';
# also Bug#4000 (only BDB tables)
# Bug#4106 : ndb table, query with correlated subquery, wrong result
execute stmt1 ;
# test case derived from client_test.c: test_subqueries_ref
let $1= 3 ;
while ($1)
{
  prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
                           (SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
  execute stmt1 ;
  deallocate prepare stmt1 ;
  dec $1 ;
}


###### 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 ;

###### subquery  in select list
# test case derived from client_test.c: test_create_drop
--disable_warnings
drop table if exists t2 ;
--enable_warnings
create table t2 as select * from t1;
prepare stmt1 from ' select a in (select a from t2) from t1 ' ;
execute stmt1 ;
# test case derived from client_test.c: test_selecttmp()
--disable_warnings
drop table if exists t5, t6, t7 ;
--enable_warnings
create table t5 (a int , b int) ;
create table t6 like t5 ;
create table t7 like t5 ;
insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7),
                      (2, -1), (3, 10) ;
insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1) ;
insert into t7 values (3, 3), (2, 2), (1, 1) ;
prepare stmt1 from ' select a, (select count(distinct t5.b) as sum from t5, t6
                     where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
                     group by t5.a order by sum limit 1) from t7 ' ;
let $1= 3 ;
while ($1)
{
  execute stmt1 ;
  dec $1 ;
}
drop table t5, t6, t7 ;


###### heavy modified case derived from client_test.c: test_distinct()
--disable_warnings
drop table if exists t2 ;
--enable_warnings
create table t2 as select * from t9;
## unusual and complex SELECT without parameters
set @stmt= ' SELECT
   (SELECT SUM(c1 + c12 + 0.0) FROM t2 
    where (t9.c2 - 0e-3) = t2.c2
    GROUP BY t9.c15 LIMIT 1) as scalar_s,
   exists (select 1.0e+0 from t2 
           where t2.c3 * 9.0000000000 = t9.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 t9,
(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
--enable_metadata
prepare stmt1 from @stmt ;
#
# Result log was disabled upon test case failure in the optimized build.
#
--disable_result_log
execute stmt1 ;
--disable_metadata
execute stmt1 ;
## now expand the terrible SELECT to EXPLAIN SELECT
set @stmt= concat('explain ',@stmt);
--enable_metadata
prepare stmt1 from @stmt ;
execute stmt1 ;
--disable_metadata
# Bug#4271 prepared explain complex select, second executes crashes the server
execute stmt1 ;
## many parameters
## replace the constants of the complex SELECT with parameters
set @stmt= ' SELECT
   (SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
    GROUP BY t9.c15 LIMIT 1) as scalar_s,
   exists (select ? from t2 
           where t2.c3*?=t9.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 t9,
(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 ;
## now expand the terrible SELECT to EXPLAIN SELECT
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 ;
--enable_result_log
drop table t2 ;


##### test case derived from client_test.c: test_bug4079()
--error 1242
select 1 < (select a from t1) ;
prepare stmt1 from ' select 1 < (select a from t1) ' ;
--error 1242
execute stmt1 ;
# Bug#5066 embedded server, select after failed subquery provides wrong result
#          (two additional records, all column values NULL)
select 1 as my_col ;

################ union tests ################
--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 ;
# test case derived from client_test.c: test_bad_union()
--error 1222
prepare stmt1 from ' SELECT 1, 2 union SELECT 1 ' ;
--error 1222
prepare stmt1 from ' SELECT 1 union SELECT 1, 2 ' ;
--error 1222
prepare stmt1 from ' SELECT * from t1 union SELECT 1 ' ;
--error 1222
prepare stmt1 from ' SELECT 1 union SELECT * from t1 ' ;


##### 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;


################ explain select tests ################
--disable_query_log
select '------ explain select tests ------' as test_sequence ;
--enable_query_log
--disable_metadata
# table with many column types
prepare stmt1 from ' explain select * from t9 ' ;
--enable_metadata
execute stmt1;
--disable_metadata