ps_conv.inc

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

############################ ps_conv.inc ##############################
#                                                                      #
#  Tests for prepared statements: conversion of parameters             #
#                                                                      #
#  Please don't                                                        #
#  - try to execute this script in ANSI mode, because many statements  #
#    will fail due to the strict type checking                         #
#  - reuse such ugly assignments like timestamp column = float value . #
#    I included them only for controlling purposes.                    #
########################################################################

#    
# NOTE: PLEASE SEE ps_1general.test (bottom) 
#       BEFORE ADDING NEW TEST CASES HERE !!!

#
# Please be aware, that this file will be sourced by several test case files
# stored within the subdirectory 't'. So every change here will affect 
# several test cases.


# The MySQL User Variables do not support the simulation of all 
# C-API field types.
#
# - There is no method to make an explicit assignment of a type to a variable.
# - The type of the variable can be only influenced by the writing style
#   of the value.
#
# The next tests should give an example for these properties.
--disable_warnings
drop table if exists t5 ;
--enable_warnings
set @arg01= 8;
set @arg02= 8.0;
set @arg03= 80.00000000000e-1;
set @arg04= 'abc' ;
set @arg05= CAST('abc' as binary) ;
set @arg06= '1991-08-05' ;
set @arg07= CAST('1991-08-05' as date);
set @arg08= '1991-08-05 01:01:01' ;
set @arg09= CAST('1991-08-05 01:01:01' as datetime) ;
set @arg10= unix_timestamp('1991-01-01 01:01:01');
set @arg11= YEAR('1991-01-01 01:01:01');
# This first assignment to @arg<n> fixes the type of the variable
# The second assignment sets the value to NULL, but it does not change
# the numeric types.
set @arg12= 8 ;
set @arg12= NULL ;
set @arg13= 8.0 ;
set @arg13= NULL ;
set @arg14= 'abc';
set @arg14= NULL ;
set @arg15= CAST('abc' as binary) ;
set @arg15= NULL ;
create table t5 as select
  8                           as const01, @arg01 as param01,
  8.0                         as const02, @arg02 as param02,
  80.00000000000e-1           as const03, @arg03 as param03,
  'abc'                       as const04, @arg04 as param04,
  CAST('abc' as binary)       as const05, @arg05 as param05,
  '1991-08-05'                as const06, @arg06 as param06,
  CAST('1991-08-05' as date)  as const07, @arg07 as param07,
  '1991-08-05 01:01:01'       as const08, @arg08 as param08,
  CAST('1991-08-05 01:01:01'  as datetime) as const09, @arg09 as param09,
  unix_timestamp('1991-01-01 01:01:01')    as const10, @arg10 as param10,
  YEAR('1991-01-01 01:01:01') as const11, @arg11 as param11, 
  NULL                        as const12, @arg12 as param12,
                                          @arg13 as param13,
                                          @arg14 as param14,
                                          @arg15 as param15;
  
# Bug#4788 show create table provides incorrect statement
show create table t5 ;
--vertical_results
--enable_metadata
--disable_ps_protocol
select * from t5 ;
--enable_ps_protocol
--disable_metadata
--horizontal_results
drop table t5 ;

# But there seems to be also an implicit conversion of C-API
# data types to a smaller number of base data types.
# 
# Example: C-API for prepared statements
#          CREATE TABLE abc as SELECT  ? as a, ? as b, ...
#
#    MYSQL_TYPE of parameter  column type
#    MYSQL_TYPE_TINY          bigint(4)
#    MYSQL_TYPE_SHORT         bigint(6)
#    MYSQL_TYPE_FLOAT         double
#    ...
#
# So we can hope that the functionality of mysqltest + user variables
# sufficient to simulate much of the behaviour of the C-API
# vis-a-vis the server.

# The main test object is the table t9, defined as follows:
#
# eval create table t9 
# (
#   c1  tinyint, c2  smallint, c3  mediumint, c4  int,
#   c5  integer, c6  bigint, c7  float, c8  double,
#   c9  double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
#   c13 date, c14 datetime, c15 timestamp(14), c16 time,
#   c17 year, c18 tinyint, c19 bool, 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'),
#   c32 set('monday', 'tuesday', 'wednesday'),
#   primary key(c1)
# ) engine = $type ;
# We test each statement in non-prepared mode and in prepared mode
# for comparison purposes.
#
# We test the following conversions:
# BIGINT -> the rest of numeric columns
# CHAR, LONGTEXT, LONGBLOB, NULL, FLOAT, REAL, DOUBLE -> numeric columns
# FLOAT, REAL, CHAR, LONGTEXT, BINARY, BIGINT -> string
# DATETIME, TIME -> text, and back


--disable_query_log
select '------ data type conversion tests ------' as test_sequence ;
--enable_query_log
--source include/ps_renew.inc

# insert a record with many NULLs
insert into t9 set c1= 0, c15= '1991-01-01 01:01:01' ;
select * from t9 order by c1 ;

############ select @parm:= .. / select .. into @parm tests ############
--disable_query_log
select '------ select @parameter:= column ------' as test_sequence ;
--enable_query_log
# PS query to retrieve the content of the @variables
prepare full_info from "select @arg01, @arg02, @arg03, @arg04,
       @arg05, @arg06, @arg07, @arg08,
       @arg09, @arg10, @arg11, @arg12,
       @arg13, @arg14, @arg15, @arg16,
       @arg17, @arg18, @arg19, @arg20,
       @arg21, @arg22, @arg23, @arg24,
       @arg25, @arg26, @arg27, @arg28,
       @arg29, @arg30, @arg31, @arg32" ;

# non PS statement for comparison purposes
select @arg01:=  c1, @arg02:=  c2, @arg03:=  c3, @arg04:=  c4,
       @arg05:=  c5, @arg06:=  c6, @arg07:=  c7, @arg08:=  c8,
       @arg09:=  c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
       @arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
       @arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
       @arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
       @arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
       @arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
from t9 where c1= 1 ;
# get as much informations about the parameters as possible
--enable_metadata
execute full_info ;
--disable_metadata
# now the same procedure with the record containing so many NULLs
select @arg01:=  c1, @arg02:=  c2, @arg03:=  c3, @arg04:=  c4,
       @arg05:=  c5, @arg06:=  c6, @arg07:=  c7, @arg08:=  c8,
       @arg09:=  c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
       @arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
       @arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
       @arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
       @arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
       @arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
from t9 where c1= 0 ;
# get as much informations about the parameters as possible
--enable_metadata
execute full_info ;
--disable_metadata

prepare stmt1 from "select 
       @arg01:=  c1, @arg02:=  c2, @arg03:=  c3, @arg04:=  c4,
       @arg05:=  c5, @arg06:=  c6, @arg07:=  c7, @arg08:=  c8,
       @arg09:=  c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
       @arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
       @arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
       @arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
       @arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
       @arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
from t9 where c1= ?" ;
set @my_key= 1 ;
execute stmt1 using @my_key ;
# get as much informations about the parameters as possible
--enable_metadata
execute full_info ;
--disable_metadata
# now the same procedure with the record containing so many NULLs
set @my_key= 0 ;
execute stmt1 using @my_key ;
# get as much informations about the parameters as possible
--enable_metadata
execute full_info ;
--disable_metadata

# the next statement must fail
--error 1064
prepare stmt1 from "select ? := c1 from t9 where c1= 1" ;

--disable_query_log
select '------ select column, .. into @parm,.. ------' as test_sequence ;
--enable_query_log
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
       c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
       c25, c26, c27, c28, c29, c30, c31, c32
into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
     @arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
     @arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
     @arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
from t9 where c1= 1 ;
# get as much informations about the parameters as possible
--enable_metadata
execute full_info ;
--disable_metadata
# now the same procedure with the record containing so many NULLs
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
       c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
       c25, c26, c27, c28, c29, c30, c31, c32
into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
     @arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
     @arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
     @arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
from t9 where c1= 0 ;
# get as much informations about the parameters as possible
--enable_metadata
execute full_info ;
--disable_metadata

prepare stmt1 from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
       c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
       c25, c26, c27, c28, c29, c30, c31, c32
into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
     @arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
     @arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
     @arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
from t9 where c1= ?" ;
set @my_key= 1 ;
execute stmt1 using @my_key ;
# get as much informations about the parameters as possible
--enable_metadata
execute full_info ;
--disable_metadata
# now the same procedure with the record containing so many NULLs
# Bug#5034: prepared "select 1 into @arg15", second execute crashes server
set @my_key= 0 ;
execute stmt1 using @my_key ;
# get as much informations about the parameters as possible
--enable_metadata
execute full_info ;
--disable_metadata

# the next statement must fail
--error 1064
prepare stmt1 from "select c1 into ? from t9 where c1= 1" ;



######################### test of numeric types ##########################
#                                                                        #
# c1  tinyint, c2  smallint, c3  mediumint, c4  int,                     # 
# c5  integer, c6  bigint, c7  float, c8  double,                        #
# c9  double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),  #
#                                                                        #
##########################################################################
--disable_query_log
select '-- insert into numeric columns --' as test_sequence ;
--enable_query_log

######## INSERT into .. numeric columns values(BIGINT(n),BIGINT) ########
insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20 ) ;
set @arg00= 21 ;
insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                         @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
prepare stmt1 from "insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( 22, 22, 22, 22, 22, 22, 22, 22, 22, 22, 22 )" ;
execute stmt1 ;
set @arg00= 23;
prepare stmt2 from "insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values 
  (  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                    @arg00, @arg00, @arg00, @arg00 ;


######## INSERT into .. numeric columns values(DOUBLE(m,n),DOUBLE) ########
insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
  ( 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0,
    30.0, 30.0, 30.0 ) ;
set @arg00= 31.0 ;
insert into t9 
  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )