Results of WL#1856 "Conversion of client_test.c tests cases to mysqltest

if possible"
        - many new test cases
        - more and improved comments
      New files: t/ps_7ndb.test       test suite for NDB tables
                 r/ps_7ndb.result     expected results
                 include/ps_conv.inc  conversion test cases
+ review comments and fixes.


mysql-test/include/ps_create.inc:
  Rename of t_many_col_types -> t9
mysql-test/include/ps_modify.inc:
  WL#1856 Conversion of client_test.c tests cases to mysqltest if possible
  Rename: t_many_col_types -> t9
  Cleanups and comments.
  New test cases (derived from client_test.c)
mysql-test/include/ps_modify1.inc:
  WL#1856 Conversion of client_test.c tests cases to mysqltest if possible
  Rename: t_many_col_types -> t9
  Cleanups and comments.
  New test cases (derived from client_test.c)
mysql-test/include/ps_query.inc:
  WL#1856 Conversion of client_test.c tests cases to mysqltest if possible
  Rename: t_many_col_types -> t9
  Cleanups and comments.
  New test cases (derived from client_test.c)
mysql-test/include/ps_renew.inc:
  WL#1856 Conversion of client_test.c tests cases to mysqltest if possible
  Rename: t_many_col_types -> t9
mysql-test/r/ps_1general.result:
  Results updated.
mysql-test/r/ps_2myisam.result:
  Resutls updated.
mysql-test/r/ps_3innodb.result:
  Results updated.
mysql-test/r/ps_4heap.result:
  Results updated.
mysql-test/r/ps_5merge.result:
  Results updated.
mysql-test/r/ps_6bdb.result:
  Results updated.
mysql-test/t/ps_1general.test:
  WL#1856 "Conversion of client_test.c tests cases to mysqltest if
   possible": new test cases added.
mysql-test/t/ps_2myisam.test:
  WL#1856 Conversion of client_test.c tests cases to mysqltest if possible
  Call of file include/ps_conv.inc (with new test cases) and 
  fulltext test case added.
mysql-test/t/ps_3innodb.test:
  WL#1856 Conversion of client_test.c tests cases to mysqltest if possible
  Call of file include/ps_conv.inc (with new test cases) added.
mysql-test/t/ps_4heap.test:
  WL#1856 Conversion of client_test.c tests cases to mysqltest if possible
  Call of file include/ps_conv.inc (with new test cases) added.
mysql-test/t/ps_5merge.test:
  WL#1856 Conversion of client_test.c tests cases to mysqltest if possible
  Call of file include/ps_conv.inc (with new test cases) added.
mysql-test/t/ps_6bdb.test:
  WL#1856 Conversion of client_test.c tests cases to mysqltest if possible.
  Call of file include/ps_conv.inc (with new test cases) added.

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 ;