summaryrefslogtreecommitdiff
path: root/mysql-test/include
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/include')
-rw-r--r--mysql-test/include/ps_conv.inc1202
-rw-r--r--mysql-test/include/ps_create.inc4
-rw-r--r--mysql-test/include/ps_modify.inc128
-rw-r--r--mysql-test/include/ps_modify1.inc11
-rw-r--r--mysql-test/include/ps_query.inc203
-rw-r--r--mysql-test/include/ps_renew.inc9
6 files changed, 1514 insertions, 43 deletions
diff --git a/mysql-test/include/ps_conv.inc b/mysql-test/include/ps_conv.inc
new file mode 100644
index 00000000000..f61fb7db1c9
--- /dev/null
+++ b/mysql-test/include/ps_conv.inc
@@ -0,0 +1,1202 @@
+############################ 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
+select * from t5 ;
+--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 bit, 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 )
+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 ;
+execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
+ @arg00, @arg00, @arg00, @arg00, @arg00 ;
+--vertical_results
+execute my_select ;
+--horizontal_results
+execute my_delete ;
+set @arg00= '9223372036854775807' ;
+execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
+ @arg00, @arg00, @arg00, @arg00, @arg00 ;
+--vertical_results
+execute my_select ;
+--horizontal_results
+execute my_delete ;
+# Use the minimum BIGINT from the manual
+#
+set @arg00= -9223372036854775808 ;
+execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
+ @arg00, @arg00, @arg00, @arg00, @arg00 ;
+--vertical_results
+execute my_select ;
+--horizontal_results
+execute my_delete ;
+set @arg00= '-9223372036854775808' ;
+execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
+ @arg00, @arg00, @arg00, @arg00, @arg00 ;
+--vertical_results
+execute my_select ;
+--horizontal_results
+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 ;
+execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
+ @arg00, @arg00, @arg00, @arg00, @arg00 ;
+--vertical_results
+execute my_select ;
+--horizontal_results
+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
+execute my_select ;
+--horizontal_results
+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
+execute my_select ;
+--horizontal_results
+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
+execute my_select ;
+--horizontal_results
+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)" ;
+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(14), 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) ########
+--disable_query_log
+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 ;
+
+
+######## 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 ;
+
+--enable_query_log
+
+######## 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= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+ c15= '1991-01-01 01:01:01' and c16= '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= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+ and c17= @arg00 ;
+prepare stmt1 from "select 'true' as found from t9
+where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+ c15= '1991-01-01 01:01:01' and c16= '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= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+execute stmt1 using @arg00, @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 01:01:01' 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
+ c16= 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= @arg00 and c14= @arg00 and c15= @arg00 and c16= @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 datetime) and
+ c14= CAST('1991-01-01 01:01:01' as datetime) and
+ c15= CAST('1991-01-01 01:01:01' as datetime) and
+ c16= 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= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+execute stmt1 using @arg00, @arg00, @arg00, @arg00, @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 c17= 1.991e+3 ;
+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= 1.991e+3" ;
+execute stmt1 ;
+prepare stmt1 from "select 'true' as found from t9
+where c1= 20 and c17= ?" ;
+execute stmt1 using @arg00 ;
+
diff --git a/mysql-test/include/ps_create.inc b/mysql-test/include/ps_create.inc
index 77844c99d77..dfc9c494b46 100644
--- a/mysql-test/include/ps_create.inc
+++ b/mysql-test/include/ps_create.inc
@@ -19,7 +19,7 @@
#---- Please do not alter the following table definitions -------#
--disable_warnings
-drop table if exists t1, t_many_col_types ;
+drop table if exists t1, t9 ;
--enable_warnings
eval create table t1
@@ -28,7 +28,7 @@ eval create table t1
primary key(a)
) engine = $type ;
-eval create table t_many_col_types
+eval create table t9
(
c1 tinyint, c2 smallint, c3 mediumint, c4 int,
c5 integer, c6 bigint, c7 float, c8 double,
diff --git a/mysql-test/include/ps_modify.inc b/mysql-test/include/ps_modify.inc
index ba7ccefdd98..9cf11709e69 100644
--- a/mysql-test/include/ps_modify.inc
+++ b/mysql-test/include/ps_modify.inc
@@ -14,12 +14,12 @@
# several test cases.
#
# Please do not modify the structure (DROP/ALTER..) of the tables
-# 't1' and 't_many_col_types'.
+# 't1' and 't9'.
#
# But you are encouraged to use these two tables within your statements
-# whenever possible.
-# t1 - very simple table
-# t_many_col_types - table with nearly all available column types
+# (DELETE/UPDATE/...) whenever possible.
+# t1 - very simple table
+# t9 - table with nearly all available column types
#
# The structure and the content of these tables can be found in
# include/ps_create.inc CREATE TABLE ...
@@ -124,17 +124,48 @@ set @arg04=2;
--disable_warnings
drop table if exists t2;
--enable_warnings
+# t2 will be of table type 'MYISAM'
create table t2 as select a,b from t1 ;
prepare stmt1 from 'update t1 set a=? where b=?
and a in (select ? from t2
where b = ? or a = ?)';
+--enable_info
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
+--disable_info
select a,b from t1 where a = @arg00 ;
prepare stmt1 from 'update t1 set a=? where b=?
and a not in (select ? from t2
where b = ? or a = ?)';
+--enable_info
execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
-select a,b from t1 order by a;
+--disable_info
+select a,b from t1 order by a ;
+drop table t2 ;
+# t2 is now of table type '$type'
+# The test battery for table type 'MERGE' gets here only a 'MYISAM' table
+#
+# Test UPDATE with SUBQUERY in prepared mode
+#
+eval create table t2
+(
+ a int, b varchar(30),
+ primary key(a)
+) engine = $type ;
+insert into t2(a,b) select a, b from t1 ;
+prepare stmt1 from 'update t1 set a=? where b=?
+ and a in (select ? from t2
+ where b = ? or a = ?)';
+--enable_info
+execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
+--disable_info
+select a,b from t1 where a = @arg00 ;
+prepare stmt1 from 'update t1 set a=? where b=?
+ and a not in (select ? from t2
+ where b = ? or a = ?)';
+--enable_info
+execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
+--disable_info
+select a,b from t1 order by a ;
drop table t2 ;
## update with parameters in limit
@@ -179,6 +210,46 @@ set @arg01='eight' ;
prepare stmt1 from 'insert into t1 values(?, ? )';
execute stmt1 using @arg00, @arg01 ;
select a,b from t1 where b = @arg01;
+# cases derived from client_test.c: test_null()
+set @NULL= null ;
+set @arg00= 'abc' ;
+# execute must fail, because first column is primary key (-> not null)
+--error 1048
+execute stmt1 using @NULL, @NULL ;
+--error 1048
+execute stmt1 using @NULL, @NULL ;
+--error 1048
+execute stmt1 using @NULL, @arg00 ;
+--error 1048
+execute stmt1 using @NULL, @arg00 ;
+let $1 = 2;
+while ($1)
+{
+ eval set @arg01= 10000 + $1 ;
+ execute stmt1 using @arg01, @arg00 ;
+ dec $1;
+}
+select * from t1 where a > 10000 order by a ;
+delete from t1 where a > 10000 ;
+let $1 = 2;
+while ($1)
+{
+ eval set @arg01= 10000 + $1 ;
+ execute stmt1 using @arg01, @NULL ;
+ dec $1;
+}
+select * from t1 where a > 10000 order by a ;
+delete from t1 where a > 10000 ;
+let $1 = 10;
+while ($1)
+{
+ eval set @arg01= 10000 + $1 ;
+ execute stmt1 using @arg01, @arg01 ;
+ dec $1;
+}
+select * from t1 where a > 10000 order by a ;
+delete from t1 where a > 10000 ;
+
## insert with two rows in values part
set @arg00=81 ;
@@ -208,6 +279,22 @@ set @arg01=1 ;
--error 1062
execute stmt1 using @arg00, @arg01;
+## insert, autoincrement column and ' SELECT LAST_INSERT_ID() '
+# cases derived from client_test.c: test_bug3117()
+--disable_warnings
+drop table if exists t2 ;
+--enable_warnings
+# The test battery for table type 'MERGE' gets here only a 'MYISAM' table
+eval create table t2 (id int auto_increment primary key)
+ENGINE= $type ;
+prepare stmt1 from ' select last_insert_id() ' ;
+insert into t2 values (NULL) ;
+execute stmt1 ;
+insert into t2 values (NULL) ;
+# bug#3117
+execute stmt1 ;
+drop table t2 ;
+
## many parameters
set @1000=1000 ;
set @x1000_2="x1000_2" ;
@@ -237,3 +324,34 @@ delete from t1 where a >= 1000 ;
## replace
--error 1295
prepare stmt1 from ' replace into t1 (a,b) select 100, ''hundred'' ';
+
+
+## multi table statements
+--disable_query_log
+select '------ multi table tests ------' as test_sequence ;
+--enable_query_log
+# cases derived from client_test.c: test_multi
+delete from t1 ;
+delete from t9 ;
+insert into t1(a,b) values (1, 'one'), (2, 'two'), (3, 'three') ;
+insert into t9 (c1,c21)
+ values (1, 'one'), (2, 'two'), (3, 'three') ;
+prepare stmt_delete from " delete t1, t9
+ from t1, t9 where t1.a=t9.c1 and t1.b='updated' ";
+prepare stmt_update from " update t1, t9
+ set t1.b='updated', t9.c21='updated'
+ where t1.a=t9.c1 and t1.a=? ";
+prepare stmt_select1 from " select a, b from t1 order by a" ;
+prepare stmt_select2 from " select c1, c21 from t9 order by c1" ;
+set @arg00= 1 ;
+let $1= 3 ;
+while ($1)
+{
+ execute stmt_update using @arg00 ;
+ execute stmt_delete ;
+ execute stmt_select1 ;
+ execute stmt_select2 ;
+ set @arg00= @arg00 + 1 ;
+ dec $1 ;
+}
+
diff --git a/mysql-test/include/ps_modify1.inc b/mysql-test/include/ps_modify1.inc
index da3bea80669..5fba7faa59a 100644
--- a/mysql-test/include/ps_modify1.inc
+++ b/mysql-test/include/ps_modify1.inc
@@ -20,12 +20,12 @@
# several test cases.
#
# Please do not modify the structure (DROP/ALTER..) of the tables
-# 't1' and 't_many_col_types'.
+# 't1' and 't9'.
#
# But you are encouraged to use these two tables within your statements
# (DELETE/UPDATE/...) whenever possible.
-# t1 - very simple table
-# t_many_col_types - table with nearly all available column types
+# t1 - very simple table
+# t9 - table with nearly all available column types
#
# The structure and the content of these tables can be found in
# include/ps_create.inc CREATE TABLE ...
@@ -41,6 +41,11 @@
#-------- Please be very carefull when editing behind this line ----------#
+--source include/ps_renew.inc
+#
+# add a NULL row to t1: this row is used only in this test
+insert into t1 values(0,NULL) ;
+
## big insert select statements
set @duplicate='duplicate ' ;
set @1000=1000 ;
diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc
index 9c80c7a040c..3ebe3c7b5a1 100644
--- a/mysql-test/include/ps_query.inc
+++ b/mysql-test/include/ps_query.inc
@@ -15,13 +15,13 @@
#
# Please do not modify (INSERT/UPDATE/DELETE) the content or the
# structure (DROP/ALTER..) of the tables
-# 't1' and 't_many_col_types'.
+# 't1' and 't9'.
# Such tests should be done in include/ps_modify.inc .
#
# But you are encouraged to use these two tables within your SELECT statements
# whenever possible.
-# t1 - very simple table
-# t_many_col_types - table with nearly all available column types
+# t1 - very simple table
+# t9 - table with nearly all available column types
#
# The structure and the content of these tables can be found in
# include/ps_create.inc CREATE TABLE ...
@@ -38,10 +38,18 @@
#-------- Please be very carefull when editing behind this line ----------#
+################ simple select tests ################
--disable_query_log
select '------ simple select tests ------' as test_sequence ;
--enable_query_log
+##### many column types, but no parameter
+# heavy modified case derived from client_test.c: test_func_fields()
+prepare stmt1 from ' select * from t9 ' ;
+--enable_metadata
+execute stmt1;
+--disable_metadata
+
##### parameter used for keyword like SELECT (must fail)
set @arg00='SELECT' ;
# mysqltest gives no output for the next statement, Why ??
@@ -70,6 +78,17 @@ 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 ;
+# case derived from client_test.c: test_ps_null_param()
+set @arg00=null ;
+select @arg00 as my_col ;
+prepare stmt1 from ' select ? as my_col';
+execute stmt1 using @arg00 ;
+select @arg00 + 1 as my_col ;
+prepare stmt1 from ' select ? + 1 as my_col';
+execute stmt1 using @arg00 ;
+select 1 + @arg00 as my_col ;
+prepare stmt1 from ' select 1 + ? as my_col';
+execute stmt1 using @arg00 ;
## parameter is within a function
# variations on 'substr'
set @arg00='MySQL' ;
@@ -86,7 +105,7 @@ execute stmt1 using @arg00 ;
# variations on 'concat'
set @arg00='MySQL' ;
select a , concat(@arg00,b) from t1 ;
-# BUG#3796
+# BUG#3796 Prepared statement, select concat(<parameter>,<column>),wrong result
prepare stmt1 from ' select a , concat(?,b) from t1 ' ;
execute stmt1 using @arg00;
#
@@ -122,25 +141,25 @@ 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 ;
+drop table if exists t5 ;
--enable_warnings
-create table new_tab (id1 int(11) not null default '0',
+create table t5 (id1 int(11) not null default '0',
value2 varchar(100), value1 varchar(100)) ;
-insert into new_tab values (1,'hh','hh'),(2,'hh','hh'),
+insert into t5 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=? ' ;
+prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? ' ;
set @arg00=1 ;
set @arg01='hh' ;
execute stmt1 using @arg00, @arg01 ;
-drop table new_tab ;
+drop table t5 ;
# 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 ;
+drop table if exists t5 ;
--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
+create table t5(session_id char(9) not null) ;
+insert into t5 values ('abc') ;
+prepare stmt1 from ' select * from t5
where ?=''1111'' and session_id = ''abc'' ' ;
set @arg00='abc' ;
execute stmt1 using @arg00 ;
@@ -148,7 +167,7 @@ set @arg00='1111' ;
execute stmt1 using @arg00 ;
set @arg00='abc' ;
execute stmt1 using @arg00 ;
-drop table new_tab ;
+drop table t5 ;
##### parameter used for keyword FROM (must fail)
@@ -200,6 +219,12 @@ 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;
+# case derived from client_test.c: test_bug1500()
+set @arg00= 'one' ;
+set @arg01= 'two' ;
+set @arg02= 'five' ;
+prepare stmt1 from ' select b FROM t1 where b in (?,?,?) ' ;
+execute stmt1 using @arg00, @arg01, @arg02 ;
# parameter in LIKE
prepare stmt1 from ' select b FROM t1 where b like ? ';
set @arg00='two' ;
@@ -208,6 +233,24 @@ set @arg00='tw%' ;
execute stmt1 using @arg00 ;
set @arg00='%wo' ;
execute stmt1 using @arg00 ;
+# case derived from client_test.c: test_ps_null_param():
+# second part, comparisions with NULL placeholders in prepared
+# mode
+set @arg00=null ;
+insert into t9 set c1= 0, c5 = NULL ;
+select c5 from t9 where c5 > NULL ;
+prepare stmt1 from ' select c5 from t9 where c5 > ? ';
+execute stmt1 using @arg00 ;
+select c5 from t9 where c5 < NULL ;
+prepare stmt1 from ' select c5 from t9 where c5 < ? ';
+execute stmt1 using @arg00 ;
+select c5 from t9 where c5 = NULL ;
+prepare stmt1 from ' select c5 from t9 where c5 = ? ';
+execute stmt1 using @arg00 ;
+select c5 from t9 where c5 <=> NULL ;
+prepare stmt1 from ' select c5 from t9 where c5 <=> ? ';
+execute stmt1 using @arg00 ;
+delete from t9 where c1= 0 ;
##### parameter used for operator in WHERE clause (must fail)
set @arg00='>' ;
@@ -276,6 +319,7 @@ having sum(a) <> ? ';
execute stmt1 using @arg00, @arg01, @arg02, @arg03;
+################ join tests ################
--disable_query_log
select '------ join tests ------' as test_sequence ;
--enable_query_log
@@ -301,8 +345,39 @@ prepare stmt1 from ' select first.a, ?, second.a FROM t1 first, t1 second
order by second.a, first.a';
execute stmt1 using @arg00, @arg01, @arg02;
+# test case derived from client_test.c: test_join()
+--disable_warnings
+drop table if exists t2 ;
+--enable_warnings
+create table t2 as select * from t1 ;
+set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) ' ;
+set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) ' ;
+set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) ' ;
+let $1= 9 ;
+while ($1)
+{
+ --disable_query_log
+ eval select @query$1 as 'the join statement is:' ;
+ --enable_query_log
+ eval prepare stmt1 from @query$1 ;
+ let $2= 3 ;
+ while ($2)
+ {
+ execute stmt1 ;
+ dec $2 ;
+ }
+ dec $1 ;
+}
+drop table t2 ;
+################ subquery tests ################
--disable_query_log
select '------ subquery tests ------' as test_sequence ;
--enable_query_log
@@ -350,8 +425,20 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
# 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) ??
+# 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' ;
@@ -360,7 +447,7 @@ 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) ??
+# also Bug#4000 (only BDB tables)
execute stmt1 using @arg00;
###### parameter in the inner part
@@ -390,7 +477,7 @@ select a, @arg00, b FROM t1 outer_table where
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) ??
+# also Bug#4000 (only BDB tables)
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
###### subquery after from
@@ -404,43 +491,76 @@ prepare stmt1 from ' select a, ?
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()
-## 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;
+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 (t_many_col_types.c2 - 0e-3) = t2.c2
- GROUP BY t_many_col_types.c15 LIMIT 1) as scalar_s,
+ 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 = t_many_col_types.c4) as exists_s,
+ 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 t_many_col_types,
+FROM t9,
(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
--enable_metadata
prepare stmt1 from @stmt ;
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 (t_many_col_types.c2-?)=t2.c2
- GROUP BY t_many_col_types.c15 LIMIT 1) as scalar_s,
+ (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*?=t_many_col_types.c4) as exists_s,
+ 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 t_many_col_types,
+FROM t9,
(select c25 x, c32 y from t2) tt WHERE x =c25 ' ;
set @arg00= 0.0 ;
set @arg01= 0e-3 ;
@@ -459,6 +579,7 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
--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 ;
@@ -470,6 +591,17 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
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
@@ -485,6 +617,16 @@ 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
@@ -612,10 +754,13 @@ prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
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
-prepare stmt1 from ' select * from t_many_col_types ' ;
+--disable_metadata
+# table with many column types
+prepare stmt1 from ' explain select * from t9 ' ;
--enable_metadata
execute stmt1;
--disable_metadata
diff --git a/mysql-test/include/ps_renew.inc b/mysql-test/include/ps_renew.inc
index 1441638f257..f50b5d58485 100644
--- a/mysql-test/include/ps_renew.inc
+++ b/mysql-test/include/ps_renew.inc
@@ -1,6 +1,6 @@
################ include/ps_renew.inc #################
# #
-# renew the content of t1 and t_many_col_types #
+# renew the content of t1 and t9 #
# #
#######################################################
@@ -13,8 +13,8 @@ insert into t1 values (3,'three');
insert into t1 values (4,'four');
commit ;
-delete from t_many_col_types ;
-insert into t_many_col_types
+delete from t9 ;
+insert into t9
set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
c10= 1, c11= 1, c12 = 1,
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
@@ -23,7 +23,7 @@ set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';
-insert into t_many_col_types
+insert into t9
set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
c10= 9, c11= 9, c12 = 9,
c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',
@@ -32,3 +32,4 @@ set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,
c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',
c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',
c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
+commit ;
View Lorry Controller Status