ps_conv.inc

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

# Numeric overflow of columns(c1, c2, c3, c4, c5, c12) with type not in 
# (FLOAT,REAL,DOUBLE) during insert
#
set @arg00= 1.11111111111111111111e+50 ;
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                        @arg00, @arg00, @arg00, @arg00, @arg00 ;
--vertical_results
--replace_result e+0 e+
execute my_select ;
--horizontal_results
--replace_result e+0 e+
execute my_delete ;
# Attention: The columns(c1,c2,c3,c4,c5,c6) do not get the overflow,
#             because the string is treated as written integer and
#             '.11111111111111111111e+50' is cut away.
set @arg00= '1.11111111111111111111e+50' ;
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                        @arg00, @arg00, @arg00, @arg00, @arg00 ;
--vertical_results
--replace_result e+0 e+
execute my_select ;
--horizontal_results
--replace_result e+0 e+
execute my_delete ;
set @arg00= -1.11111111111111111111e+50 ;
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                        @arg00, @arg00, @arg00, @arg00, @arg00 ;
--vertical_results
--replace_result e+0 e+
execute my_select ;
--horizontal_results
--replace_result e+0 e+
execute my_delete ;
# Attention: The columns(c1,c2,c3,c4,c5,c6) do not get the overflow,
#             because the string is treated as written integer and
#             '.11111111111111111111e+50' is cut away.
set @arg00= '-1.11111111111111111111e+50' ;
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                        @arg00, @arg00, @arg00, @arg00, @arg00 ;
--vertical_results
--replace_result e+0 e+
execute my_select ;
--horizontal_results
--replace_result e+0 e+
execute my_delete ;

########################## test of string types ##########################
#                                                                        # 
#   c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, #
#   c25 blob, c26 text, c27 mediumblob, c28 mediumtext, c29 longblob,    #
#   c30 longtext, c31 enum('one', 'two', 'three')                        #
#                                                                        #
##########################################################################
--disable_query_log
select '-- insert into string columns --' as test_sequence ;
--enable_query_log

######## INSERT into .. string columns values(CHAR(n),LONGTEXT) ########
--disable_query_log
insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 20, '20', '20', '20', '20', '20', '20', '20', '20', '20', '20', '20' ) ;
set @arg00= '21' ;
insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 21, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
        @arg00, @arg00, @arg00 ) ;
prepare stmt1 from "insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 22, '22', '22', '22', '22', '22', '22', '22', '22', '22', '22', '22' )" ;
execute stmt1 ;
set @arg00= '23';
prepare stmt2 from "insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values 
  ( 23, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00 ;


######## INSERT into .. string columns values(BINARY(n),LONGBLOB) ########
insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 30, CAST('30' as binary), CAST('30' as binary), CAST('30' as binary),
  CAST('30' as binary), CAST('30' as binary), CAST('30' as binary),
  CAST('30' as binary), CAST('30' as binary), CAST('30' as binary),
  CAST('30' as binary), CAST('30' as binary) ) ;
set @arg00= '31' ;
insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 31, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
        @arg00, @arg00, @arg00 ) ;
prepare stmt1 from "insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 32, CAST('32' as binary), CAST('32' as binary), CAST('32' as binary),
  CAST('32' as binary), CAST('32' as binary), CAST('32' as binary),
  CAST('32' as binary), CAST('32' as binary), CAST('32' as binary),
  CAST('32' as binary), CAST('32' as binary) )" ;
execute stmt1 ;
set @arg00= CAST('33' as binary);
prepare stmt2 from "insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values 
  ( 33, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00 ;


######## INSERT into .. string columns values(BIGINT(n),BIGINT) ########
insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 40, 40, 40, 40, 40, 40, 40, 40, 40, 40, 40, 40 ) ;
set @arg00= 41 ;
insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 41, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
        @arg00, @arg00, @arg00 ) ;
prepare stmt1 from "insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 42, 42, 42, 42, 42, 42, 42, 42, 42, 42, 42, 42 )" ;
execute stmt1 ;
set @arg00= 43;
prepare stmt2 from "insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values 
  ( 43, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00 ;


######## INSERT into .. string columns values(DOUBLE(m,n),DOUBLE) ########
insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 50, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0 ) ;
set @arg00= 51.0 ;
insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 51, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
        @arg00, @arg00, @arg00 ) ;
prepare stmt1 from "insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 52, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0 )" ;
execute stmt1 ;
set @arg00= 53.0;
prepare stmt2 from "insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values 
  ( 53, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00 ;


######## INSERT into .. string columns values(DOUBLE(m,n),DOUBLE) ########
#  typical float writing style
insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 54, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1, 5.4e+1,
    5.4e+1, 5.4e+1, 5.4e+1 ) ;
set @arg00= 5.5e+1 ;
insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 55, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
        @arg00, @arg00, @arg00 ) ;
prepare stmt1 from "insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 56, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1, 5.6e+1,
    5.6e+1, 5.6e+1, 5.6e+1 )" ;
execute stmt1 ;
set @arg00= 5.7e+1;
prepare stmt2 from "insert into t9 
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values 
  ( 57, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00 ;


######## INSERT into .. string columns values(LONGBLOB,NULL) ########
set @arg00= 'abc' ;
set @arg00= NULL ;
insert into t9
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) ;
insert into t9
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 61, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
        @arg00, @arg00, @arg00 ) ;
prepare stmt1 from "insert into t9
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )" ;
execute stmt1 ;
prepare stmt2 from "insert into t9
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                    @arg00, @arg00, @arg00, @arg00 ;

######## INSERT into .. string columns values(BIGINT,NULL) ########
set @arg00= 2 ;
set @arg00= NULL ;
insert into t9
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 71, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
        @arg00, @arg00, @arg00 ) ;
prepare stmt2 from "insert into t9
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                    @arg00, @arg00, @arg00, @arg00 ;

######## INSERT into .. string columns values(DOUBLE,NULL) ########
set @arg00= 8 ;
set @arg00= NULL ;
insert into t9
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 81, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
        @arg00, @arg00, @arg00 ) ;
prepare stmt2 from "insert into t9
  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
values
  ( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                    @arg00, @arg00, @arg00, @arg00 ;

--enable_query_log

######## SELECT of all inserted records ########
select c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30
from t9 where c1 >= 20
order by c1 ;


--disable_query_log
select '-- select .. where string column = .. --' as test_sequence ;
--enable_query_log
######## SELECT .. WHERE column(string)=value(CHAR(n)/LONGTEXT) ########
set @arg00= '20';
# c20 (char) must be extended for the comparison
select 'true' as found from t9 
where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and
  c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and
  c27= '20' and c28= '20' and c29= '20' and c30= '20' ;
select 'true' as found from t9 
where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
  c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
  c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
prepare stmt1 from "select 'true' as found from t9 
where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and
  c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and
  c27= '20' and c28= '20' and c29= '20' and c30= '20'" ;
execute stmt1 ;
prepare stmt1 from "select 'true' as found from t9 
where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
  c21= ? and c22= ? and c23= ? and c25= ? and
  c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00, @arg00 ;


######## SELECT .. WHERE column(string)=value(BINARY(n)/LONGBLOB) ########
set @arg00= CAST('20' as binary);
# c20 (char) must be extended for the comparison
select 'true' as found from t9 
where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))
                 = CAST('20' as binary) and c21= CAST('20' as binary)
  and c22= CAST('20' as binary) and c23= CAST('20' as binary) and
  c24= CAST('20' as binary) and c25= CAST('20' as binary) and
  c26= CAST('20' as binary) and c27= CAST('20' as binary) and
  c28= CAST('20' as binary) and c29= CAST('20' as binary) and
  c30= CAST('20' as binary) ;
select 'true' as found from t9 
where c1= 20 and concat(c20,substr(@arg00,1+length(c20))) = @arg00 and
  c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
  c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and
  c30= @arg00;
prepare stmt1 from "select 'true' as found from t9 
where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))
                 = CAST('20' as binary) and c21= CAST('20' as binary)
  and c22= CAST('20' as binary) and c23= CAST('20' as binary) and
  c24= CAST('20' as binary) and c25= CAST('20' as binary) and
  c26= CAST('20' as binary) and c27= CAST('20' as binary) and
  c28= CAST('20' as binary) and c29= CAST('20' as binary) and
  c30= CAST('20' as binary)" ;