mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-01-15 19:42:28 +01:00
Code Issues Projects Releases Packages Wiki Activity Actions
mariadb/mysql-test/include/ps_conv.inc
Lena Startseva 0a5e4a0191 MDEV-31005: Make working cursor-protocol 
Updated tests: cases with bugs or which cannot be run
with the cursor-protocol were excluded with
"--disable_cursor_protocol"/"--enable_cursor_protocol"

Fix for v.10.5
2024-09-18 18:39:26 +07:00

1250 lines
49 KiB
PHP
Raw Permalink Blame History

############################ 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
--disable_query_log
SET TIME_ZONE= '+03:00';
--enable_query_log
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 ;
eval create table t5 engine = MyISAM 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
--disable_view_protocol
select * from t5 ;
--enable_view_protocol
--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, 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 information 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 information 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 information 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 information 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_cursor_protocol
--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 information 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 information 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= ?" ;
--enable_cursor_protocol
set @my_key= 1 ;
execute stmt1 using @my_key ;
# get as much information 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 information 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 )
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
( 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0,
32.0, 32.0, 32.0 )" ;
execute stmt1 ;
set @arg00= 33.0;
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(CHAR(n),LONGTEXT) #########
insert into t9
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
( '40', '40', '40', '40', '40', '40', '40', '40',
'40', '40', '40' ) ;
set @arg00= '41' ;
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
( '42', '42', '42', '42', '42', '42', '42', '42',
'42', '42', '42' )" ;
execute stmt1 ;
set @arg00= '43';
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(BINARY(n),LONGBLOB) ########
insert into t9
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
( CAST('50' as binary), CAST('50' as binary),
CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),
CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),
CAST('50' as binary), CAST('50' as binary), CAST('50' as binary) ) ;
set @arg00= CAST('51' as binary) ;
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
( CAST('52' as binary), CAST('52' as binary),
CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),
CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),
CAST('52' as binary), CAST('52' as binary), CAST('52' as binary) )" ;
execute stmt1 ;
set @arg00= CAST('53' as binary) ;
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(BIGINT,NULL) ########
# we first assign number to arg00 to set it's datatype to numeric.
set @arg00= 2 ;
set @arg00= NULL ;
insert into t9
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL ) ;
insert into t9
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
( 61, @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
( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL )" ;
execute stmt1 ;
prepare stmt2 from "insert into t9
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
@arg00, @arg00, @arg00, @arg00 ;
######## INSERT into .. numeric columns values(DOUBLE,NULL) ########
set @arg00= 8.0 ;
set @arg00= NULL ;
insert into t9
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
( 71, @arg00, @arg00, @arg00, @arg00, @arg00,
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
prepare stmt2 from "insert into t9
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
@arg00, @arg00, @arg00, @arg00 ;
######## INSERT into .. numeric columns values(LONGBLOB,NULL) ########
set @arg00= 'abc' ;
set @arg00= NULL ;
insert into t9
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
( 81, @arg00, @arg00, @arg00, @arg00, @arg00,
@arg00, @arg00, @arg00, @arg00, @arg00 ) ;
prepare stmt2 from "insert into t9
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
@arg00, @arg00, @arg00, @arg00 ;
######## SELECT of all inserted records ########
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
from t9 where c1 >= 20
order by c1 ;
--disable_query_log
select '-- select .. where numeric column = .. --' as test_sequence ;
--enable_query_log
######## SELECT .. WHERE column(numeric)=value(BIGINT(n)/BIGINT) ########
set @arg00= 20;
select 'true' as found from t9
where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
and c8= 20 and c9= 20 and c10= 20 and c12= 20;
select 'true' as found from t9
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
and c12= @arg00;
prepare stmt1 from "select 'true' as found from t9
where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
and c8= 20 and c9= 20 and c10= 20 and c12= 20 ";
execute stmt1 ;
prepare stmt1 from "select 'true' as found from t9
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
and c12= ? ";
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
@arg00, @arg00, @arg00, @arg00 ;
######## SELECT .. WHERE column(numeric)=value(DOUBLE(m,n)/DOUBLE) ########
set @arg00= 20.0;
select 'true' as found from t9
where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0;
select 'true' as found from t9
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
and c12= @arg00;
prepare stmt1 from "select 'true' as found from t9
where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0 ";
execute stmt1 ;
prepare stmt1 from "select 'true' as found from t9
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
and c12= ? ";
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
@arg00, @arg00, @arg00, @arg00 ;
######## SELECT .. WHERE column(numeric)=value(CHAR(n)/LONGTEXT) ########
select 'true' as found from t9
where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20';
prepare stmt1 from "select 'true' as found from t9
where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20' ";
execute stmt1 ;
set @arg00= '20';
select 'true' as found from t9
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
and c12= @arg00;
prepare stmt1 from "select 'true' as found from t9
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
and c12= ? ";
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
@arg00, @arg00, @arg00, @arg00 ;
######## SELECT .. WHERE column(numeric)=value(BINARY(n)/LONGBLOB) ########
select 'true' as found from t9
where c1= CAST('20' as binary) and c2= CAST('20' as binary) and
c3= CAST('20' as binary) and c4= CAST('20' as binary) and
c5= CAST('20' as binary) and c6= CAST('20' as binary) and
c7= CAST('20' as binary) and c8= CAST('20' as binary) and
c9= CAST('20' as binary) and c10= CAST('20' as binary) and
c12= CAST('20' as binary);
prepare stmt1 from "select 'true' as found from t9
where c1= CAST('20' as binary) and c2= CAST('20' as binary) and
c3= CAST('20' as binary) and c4= CAST('20' as binary) and
c5= CAST('20' as binary) and c6= CAST('20' as binary) and
c7= CAST('20' as binary) and c8= CAST('20' as binary) and
c9= CAST('20' as binary) and c10= CAST('20' as binary) and
c12= CAST('20' as binary) ";
execute stmt1 ;
set @arg00= CAST('20' as binary) ;
select 'true' as found from t9
where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
and c12= @arg00;
prepare stmt1 from "select 'true' as found from t9
where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
and c12= ? ";
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
@arg00, @arg00, @arg00, @arg00 ;
delete from t9 ;
#################### Some overflow experiments ################################
# #
# MySQL Manual (July 2004) #
# - Setting a numeric column to a value that lies outside the column's range. #
# The value is clipped to the closest endpoint of the range. #
# ... #
# - For example, inserting the string '1999.0e-2' into an INT, FLOAT, #
# DECIMAL(10,6), or YEAR column results in the values 1999, 19.9921, #
# 19.992100, and 1999. #
# That means there is an anomaly if a float value is assigned via string to #
# a column of type bigint. The string will be cut from the right side to #
# a "usable" integer value. #
# #
###############################################################################
--disable_query_log
select '-- some numeric overflow experiments --' as test_sequence ;
--enable_query_log
prepare my_insert from "insert into t9
( c21, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
( 'O', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
prepare my_select from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
from t9 where c21 = 'O' ";
prepare my_delete from "delete from t9 where c21 = 'O' ";
# Numeric overflow of columns(c1, c2, c3, c4, c5, c12) with type not in
# (BIGINT,FLOAT,REAL,DOUBLE) during insert
#
# Use the maximum BIGINT from the manual
set @arg00= 9223372036854775807 ;
set statement sql_mode = '' for
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= '9223372036854775807' ;
set statement sql_mode = '' for
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 ;
# Use the minimum BIGINT from the manual
#
set @arg00= -9223372036854775808 ;
set statement sql_mode = '' for
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= '-9223372036854775808' ;
set statement sql_mode = '' for
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 ;
# Numeric overflow of columns(c1, c2, c3, c4, c5, c12) with type not in
# (FLOAT,REAL,DOUBLE) during insert
#
set @arg00= 1.11111111111111111111e+50 ;
set statement sql_mode = '' for
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' ;
set statement sql_mode = '' for
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 ;
set statement sql_mode = '' for
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' ;
set statement sql_mode = '' for
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) ########
set sql_mode = '';
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 ;
set sql_mode = default;
######## 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 ;
######## 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)" ;
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(BIGINT(m,n),BIGINT) ########
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(DOUBLE(m,n),DOUBLE) ########
set @arg00= 20.0;
# c20 (char) must be extended for the comparison
select 'true' as found from t9
where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and
c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and
c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0 ;
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.0,1+length(c20)))= 20.0 and c21= 20.0 and
c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and
c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0" ;
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 ;
delete from t9 ;
######################### test of date/time columns ########################
# #
# c13 date, c14 datetime, c15 timestamp, c16 time, c17 year #
# #
############################################################################
--disable_query_log
select '-- insert into date/time columns --' as test_sequence ;
--enable_query_log
######## INSERT into .. date/time columns values(VARCHAR(19),LONGTEXT) ########
set sql_mode = '';
set @arg00= '1991-01-01 01:01:01' ;
insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 20, '1991-01-01 01:01:01', '1991-01-01 01:01:01', '1991-01-01 01:01:01',
'1991-01-01 01:01:01', '1991-01-01 01:01:01') ;
insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 21, @arg00, @arg00, @arg00, @arg00, @arg00) ;
prepare stmt1 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 22, '1991-01-01 01:01:01', '1991-01-01 01:01:01', '1991-01-01 01:01:01',
'1991-01-01 01:01:01', '1991-01-01 01:01:01')" ;
execute stmt1 ;
prepare stmt2 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 23, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
######## INSERT into .. date/time columns values(DATETIME,LONGBLOB) ########
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 30, CAST('1991-01-01 01:01:01' as datetime),
CAST('1991-01-01 01:01:01' as datetime),
CAST('1991-01-01 01:01:01' as datetime),
CAST('1991-01-01 01:01:01' as datetime),
CAST('1991-01-01 01:01:01' as datetime)) ;
insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 31, @arg00, @arg00, @arg00, @arg00, @arg00) ;
prepare stmt1 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 32, CAST('1991-01-01 01:01:01' as datetime),
CAST('1991-01-01 01:01:01' as datetime),
CAST('1991-01-01 01:01:01' as datetime),
CAST('1991-01-01 01:01:01' as datetime),
CAST('1991-01-01 01:01:01' as datetime))" ;
execute stmt1 ;
prepare stmt2 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 33, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
######## INSERT into .. date/time columns values(BIGINT(n),BIGINT) ########
set @arg00= 2000000000 ;
insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 40, 2000000000, 2000000000, 2000000000, 2000000000, 2000000000 ) ;
insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 41, @arg00, @arg00, @arg00, @arg00, @arg00) ;
prepare stmt1 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 42, 2000000000, 2000000000, 2000000000, 2000000000, 2000000000 )" ;
execute stmt1 ;
prepare stmt2 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 43, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
######## INSERT into .. date/time columns values(DOUBLE(m,n),DOUBLE) ########
set @arg00= 1.0e+10 ;
insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 50, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 ) ;
insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 51, @arg00, @arg00, @arg00, @arg00, @arg00) ;
prepare stmt1 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 52, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 )" ;
execute stmt1 ;
prepare stmt2 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 53, ?, ?, ?, ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
set sql_mode = default;
######## INSERT into .. date/time columns values(LONGBLOB,NULL) ########
# Attention: c15 is timestamp and the manual says:
# The first TIMESTAMP column in table row automatically is updated
# to the current timestamp when the value of any other column in the
# row is changed, unless the TIMESTAMP column explicitly is assigned
# a value other than NULL.
# That's why a fixed NOT NULL value is inserted.
set @arg00= 'abc' ;
set @arg00= NULL ;
insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 60, NULL, NULL, '1991-01-01 01:01:01',
NULL, NULL) ;
insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 61, @arg00, @arg00, '1991-01-01 01:01:01', @arg00, @arg00) ;
prepare stmt1 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 62, NULL, NULL, '1991-01-01 01:01:01',
NULL, NULL)" ;
execute stmt1 ;
prepare stmt2 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 63, ?, ?, '1991-01-01 01:01:01', ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00 ;
######## INSERT into .. date/time columns values(BIGINT,NULL) ########
set @arg00= 8 ;
set @arg00= NULL ;
insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 71, @arg00, @arg00, '1991-01-01 01:01:01', @arg00, @arg00) ;
prepare stmt2 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 73, ?, ?, '1991-01-01 01:01:01', ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00 ;
######## INSERT into .. date/time columns values(DOUBLE,NULL) ########
set @arg00= 8.0 ;
set @arg00= NULL ;
insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 81, @arg00, @arg00, '1991-01-01 01:01:01', @arg00, @arg00) ;
prepare stmt2 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
values
( 83, ?, ?, '1991-01-01 01:01:01', ?, ? )" ;
execute stmt2 using @arg00, @arg00, @arg00, @arg00 ;
######## SELECT of all inserted records ########
select c1, c13, c14, c15, c16, c17 from t9 order by c1 ;
--disable_query_log
select '-- select .. where date/time column = .. --' as test_sequence ;
--enable_query_log
######## SELECT .. WHERE column(date/time/..)=value(CHAR(n)/LONGTEXT) ########
set @arg00= '1991-01-01 01:01:01' ;
select 'true' as found from t9
where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
c15= '1991-01-01 01:01:01' and
c17= '1991-01-01 01:01:01' ;
select 'true' as found from t9
where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00
and c17= @arg00 ;
prepare stmt1 from "select 'true' as found from t9
where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
c15= '1991-01-01 01:01:01' and
c17= '1991-01-01 01:01:01'" ;
execute stmt1 ;
prepare stmt1 from "select 'true' as found from t9
where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c17= ?" ;
execute stmt1 using @arg00, @arg00, @arg00, @arg00 ;
######## SELECT .. WHERE column(date/time/..)=value(DATETIME/LONGBLOB) ########
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c17= CAST('1991-01-01 01:01:01' as datetime) ;
select 'true' as found from t9
where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00
and c17= @arg00 ;
prepare stmt1 from "select 'true' as found from t9
where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
c14= CAST('1991-01-01 01:01:01' as datetime) and
c15= CAST('1991-01-01 01:01:01' as datetime) and
c17= CAST('1991-01-01 01:01:01' as datetime)" ;
execute stmt1 ;
prepare stmt1 from "select 'true' as found from t9
where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c17= ?" ;
execute stmt1 using @arg00, @arg00, @arg00, @arg00 ;
######## SELECT .. WHERE column(date/time/..)=value(CHAR(n)/LONGTEXT) ########
set @arg00= '01:01:01' ;
select 'true' as found from t9 where c1= 20 and c16= '01:01:01' ;
select 'true' as found from t9 where c1= 20 and c16= @arg00 ;
prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= '01:01:01'" ;
execute stmt1 ;
prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= ?" ;
execute stmt1 using @arg00 ;
######## SELECT .. WHERE column(date/time/..)=value(DATETIME/LONGBLOB) ########
set @arg00= CAST('01:01:01' as time) ;
select 'true' as found from t9 where c1= 20 and c16= CAST('01:01:01' as time) ;
select 'true' as found from t9 where c1= 20 and c16= @arg00 ;
prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= CAST('01:01:01' as time)" ;
execute stmt1 ;
prepare stmt1 from "select 'true' as found from t9 where c1= 20 and c16= ?" ;
execute stmt1 using @arg00 ;
######## SELECT .. WHERE column(year)=value(INT(10)/BIGINT) ########
set @arg00= 1991 ;
select 'true' as found from t9
where c1= 20 and c17= 1991 ;
select 'true' as found from t9
where c1= 20 and c17= @arg00 ;
prepare stmt1 from "select 'true' as found from t9
where c1= 20 and c17= 1991" ;
execute stmt1 ;
prepare stmt1 from "select 'true' as found from t9
where c1= 20 and c17= ?" ;
execute stmt1 using @arg00 ;
######## SELECT .. WHERE column(year)=value(DOUBLE(m,n)/DOUBLE) ########
set @arg00= 1.991e+3 ;
select 'true' as found from t9
where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
select 'true' as found from t9
where c1= 20 and abs(c17 - @arg00) < 0.01 ;
prepare stmt1 from "select 'true' as found from t9
where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
execute stmt1 ;
prepare stmt1 from "select 'true' as found from t9
where c1= 20 and abs(c17 - ?) < 0.01" ;
execute stmt1 using @arg00 ;
Reference in a new issue View git blame Copy permalink