MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
ps_conv.inc
1 ############################ ps_conv.inc ##############################
2 # #
3 # Tests for prepared statements: conversion of parameters #
4 # #
5 # Please don't #
6 # - try to execute this script in ANSI mode, because many statements #
7 # will fail due to the strict type checking #
8 # - reuse such ugly assignments like timestamp column = float value . #
9 # I included them only for controlling purposes. #
10 ########################################################################
11 
12 #
13 # NOTE: PLEASE SEE ps_1general.test (bottom)
14 # BEFORE ADDING NEW TEST CASES HERE !!!
15 
16 #
17 # Please be aware, that this file will be sourced by several test case files
18 # stored within the subdirectory 't'. So every change here will affect
19 # several test cases.
20 
21 
22 # The MySQL User Variables do not support the simulation of all
23 # C-API field types.
24 #
25 # - There is no method to make an explicit assignment of a type to a variable.
26 # - The type of the variable can be only influenced by the writing style
27 # of the value.
28 #
29 # The next tests should give an example for these properties.
30 --disable_warnings
31 drop table if exists t5 ;
32 --enable_warnings
33 set @arg01= 8;
34 set @arg02= 8.0;
35 set @arg03= 80.00000000000e-1;
36 set @arg04= 'abc' ;
37 set @arg05= CAST('abc' as binary) ;
38 set @arg06= '1991-08-05' ;
39 set @arg07= CAST('1991-08-05' as date);
40 set @arg08= '1991-08-05 01:01:01' ;
41 set @arg09= CAST('1991-08-05 01:01:01' as datetime) ;
42 set @arg10= unix_timestamp('1991-01-01 01:01:01');
43 set @arg11= YEAR('1991-01-01 01:01:01');
44 # This first assignment to @arg<n> fixes the type of the variable
45 # The second assignment sets the value to NULL, but it does not change
46 # the numeric types.
47 set @arg12= 8 ;
48 set @arg12= NULL ;
49 set @arg13= 8.0 ;
50 set @arg13= NULL ;
51 set @arg14= 'abc';
52 set @arg14= NULL ;
53 set @arg15= CAST('abc' as binary) ;
54 set @arg15= NULL ;
55 create table t5 as select
56  8 as const01, @arg01 as param01,
57  8.0 as const02, @arg02 as param02,
58  80.00000000000e-1 as const03, @arg03 as param03,
59  'abc' as const04, @arg04 as param04,
60  CAST('abc' as binary) as const05, @arg05 as param05,
61  '1991-08-05' as const06, @arg06 as param06,
62  CAST('1991-08-05' as date) as const07, @arg07 as param07,
63  '1991-08-05 01:01:01' as const08, @arg08 as param08,
64  CAST('1991-08-05 01:01:01' as datetime) as const09, @arg09 as param09,
65  unix_timestamp('1991-01-01 01:01:01') as const10, @arg10 as param10,
66  YEAR('1991-01-01 01:01:01') as const11, @arg11 as param11,
67  NULL as const12, @arg12 as param12,
68  @arg13 as param13,
69  @arg14 as param14,
70  @arg15 as param15;
71 
72 # Bug#4788 show create table provides incorrect statement
73 show create table t5 ;
74 --vertical_results
75 --enable_metadata
76 --disable_ps_protocol
77 select * from t5 ;
78 --enable_ps_protocol
79 --disable_metadata
80 --horizontal_results
81 drop table t5 ;
82 
83 # But there seems to be also an implicit conversion of C-API
84 # data types to a smaller number of base data types.
85 #
86 # Example: C-API for prepared statements
87 # CREATE TABLE abc as SELECT ? as a, ? as b, ...
88 #
89 # MYSQL_TYPE of parameter column type
90 # MYSQL_TYPE_TINY bigint(4)
91 # MYSQL_TYPE_SHORT bigint(6)
92 # MYSQL_TYPE_FLOAT double
93 # ...
94 #
95 # So we can hope that the functionality of mysqltest + user variables
96 # sufficient to simulate much of the behaviour of the C-API
97 # vis-a-vis the server.
98 
99 # The main test object is the table t9, defined as follows:
100 #
101 # eval create table t9
102 # (
103 # c1 tinyint, c2 smallint, c3 mediumint, c4 int,
104 # c5 integer, c6 bigint, c7 float, c8 double,
105 # c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
106 # c13 date, c14 datetime, c15 timestamp(14), c16 time,
107 # c17 year, c18 tinyint, c19 bool, c20 char,
108 # c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
109 # c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
110 # c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
111 # c32 set('monday', 'tuesday', 'wednesday'),
112 # primary key(c1)
113 # ) engine = $type ;
114 # We test each statement in non-prepared mode and in prepared mode
115 # for comparison purposes.
116 #
117 # We test the following conversions:
118 # BIGINT -> the rest of numeric columns
119 # CHAR, LONGTEXT, LONGBLOB, NULL, FLOAT, REAL, DOUBLE -> numeric columns
120 # FLOAT, REAL, CHAR, LONGTEXT, BINARY, BIGINT -> string
121 # DATETIME, TIME -> text, and back
122 
123 
124 --disable_query_log
125 select '------ data type conversion tests ------' as test_sequence ;
126 --enable_query_log
127 --source include/ps_renew.inc
128 
129 # insert a record with many NULLs
130 insert into t9 set c1= 0, c15= '1991-01-01 01:01:01' ;
131 select * from t9 order by c1 ;
132 
133 ############ select @parm:= .. / select .. into @parm tests ############
134 --disable_query_log
135 select '------ select @parameter:= column ------' as test_sequence ;
136 --enable_query_log
137 # PS query to retrieve the content of the @variables
138 prepare full_info from "select @arg01, @arg02, @arg03, @arg04,
139  @arg05, @arg06, @arg07, @arg08,
140  @arg09, @arg10, @arg11, @arg12,
141  @arg13, @arg14, @arg15, @arg16,
142  @arg17, @arg18, @arg19, @arg20,
143  @arg21, @arg22, @arg23, @arg24,
144  @arg25, @arg26, @arg27, @arg28,
145  @arg29, @arg30, @arg31, @arg32" ;
146 
147 # non PS statement for comparison purposes
148 select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
149  @arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
150  @arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
151  @arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
152  @arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
153  @arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
154  @arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
155  @arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
156 from t9 where c1= 1 ;
157 # get as much informations about the parameters as possible
158 --enable_metadata
159 execute full_info ;
160 --disable_metadata
161 # now the same procedure with the record containing so many NULLs
162 select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
163  @arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
164  @arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
165  @arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
166  @arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
167  @arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
168  @arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
169  @arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
170 from t9 where c1= 0 ;
171 # get as much informations about the parameters as possible
172 --enable_metadata
173 execute full_info ;
174 --disable_metadata
175 
176 prepare stmt1 from "select
177  @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,
178  @arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,
179  @arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,
180  @arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,
181  @arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,
182  @arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,
183  @arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,
184  @arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32
185 from t9 where c1= ?" ;
186 set @my_key= 1 ;
187 execute stmt1 using @my_key ;
188 # get as much informations about the parameters as possible
189 --enable_metadata
190 execute full_info ;
191 --disable_metadata
192 # now the same procedure with the record containing so many NULLs
193 set @my_key= 0 ;
194 execute stmt1 using @my_key ;
195 # get as much informations about the parameters as possible
196 --enable_metadata
197 execute full_info ;
198 --disable_metadata
199 
200 # the next statement must fail
201 --error 1064
202 prepare stmt1 from "select ? := c1 from t9 where c1= 1" ;
203 
204 --disable_query_log
205 select '------ select column, .. into @parm,.. ------' as test_sequence ;
206 --enable_query_log
207 select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
208  c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
209  c25, c26, c27, c28, c29, c30, c31, c32
210 into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
211  @arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
212  @arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
213  @arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
214 from t9 where c1= 1 ;
215 # get as much informations about the parameters as possible
216 --enable_metadata
217 execute full_info ;
218 --disable_metadata
219 # now the same procedure with the record containing so many NULLs
220 select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
221  c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
222  c25, c26, c27, c28, c29, c30, c31, c32
223 into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
224  @arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
225  @arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
226  @arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
227 from t9 where c1= 0 ;
228 # get as much informations about the parameters as possible
229 --enable_metadata
230 execute full_info ;
231 --disable_metadata
232 
233 prepare stmt1 from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,
234  c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,
235  c25, c26, c27, c28, c29, c30, c31, c32
236 into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,
237  @arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,
238  @arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,
239  @arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32
240 from t9 where c1= ?" ;
241 set @my_key= 1 ;
242 execute stmt1 using @my_key ;
243 # get as much informations about the parameters as possible
244 --enable_metadata
245 execute full_info ;
246 --disable_metadata
247 # now the same procedure with the record containing so many NULLs
248 # Bug#5034: prepared "select 1 into @arg15", second execute crashes server
249 set @my_key= 0 ;
250 execute stmt1 using @my_key ;
251 # get as much informations about the parameters as possible
252 --enable_metadata
253 execute full_info ;
254 --disable_metadata
255 
256 # the next statement must fail
257 --error 1064
258 prepare stmt1 from "select c1 into ? from t9 where c1= 1" ;
259 
260 
261 
262 ######################### test of numeric types ##########################
263 # #
264 # c1 tinyint, c2 smallint, c3 mediumint, c4 int, #
265 # c5 integer, c6 bigint, c7 float, c8 double, #
266 # c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), #
267 # #
268 ##########################################################################
269 --disable_query_log
270 select '-- insert into numeric columns --' as test_sequence ;
271 --enable_query_log
272 
273 ######## INSERT into .. numeric columns values(BIGINT(n),BIGINT) ########
274 insert into t9
275  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
276 values
277  ( 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20 ) ;
278 set @arg00= 21 ;
279 insert into t9
280  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
281 values
282  ( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
283  @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
284 prepare stmt1 from "insert into t9
285  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
286 values
287  ( 22, 22, 22, 22, 22, 22, 22, 22, 22, 22, 22 )" ;
288 execute stmt1 ;
289 set @arg00= 23;
290 prepare stmt2 from "insert into t9
291  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
292 values
293  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
294 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
295  @arg00, @arg00, @arg00, @arg00 ;
296 
297 
298 ######## INSERT into .. numeric columns values(DOUBLE(m,n),DOUBLE) ########
299 insert into t9
300  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
301 values
302  ( 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0,
303  30.0, 30.0, 30.0 ) ;
304 set @arg00= 31.0 ;
305 insert into t9
306  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
307 values
308  ( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
309  @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
310 prepare stmt1 from "insert into t9
311  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
312 values
313  ( 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0,
314  32.0, 32.0, 32.0 )" ;
315 execute stmt1 ;
316 set @arg00= 33.0;
317 prepare stmt2 from "insert into t9
318  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
319 values
320  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
321 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
322  @arg00, @arg00, @arg00, @arg00 ;
323 
324 
325 ######## INSERT into .. numeric columns values(CHAR(n),LONGTEXT) #########
326 insert into t9
327  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
328 values
329  ( '40', '40', '40', '40', '40', '40', '40', '40',
330  '40', '40', '40' ) ;
331 set @arg00= '41' ;
332 insert into t9
333  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
334 values
335  ( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
336  @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
337 prepare stmt1 from "insert into t9
338  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
339 values
340  ( '42', '42', '42', '42', '42', '42', '42', '42',
341  '42', '42', '42' )" ;
342 execute stmt1 ;
343 set @arg00= '43';
344 prepare stmt2 from "insert into t9
345  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
346 values
347  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
348 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
349  @arg00, @arg00, @arg00, @arg00 ;
350 
351 
352 ######## INSERT into .. numeric columns values(BINARY(n),LONGBLOB) ########
353 insert into t9
354  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
355 values
356  ( CAST('50' as binary), CAST('50' as binary),
357  CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),
358  CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),
359  CAST('50' as binary), CAST('50' as binary), CAST('50' as binary) ) ;
360 set @arg00= CAST('51' as binary) ;
361 insert into t9
362  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
363 values
364  ( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
365  @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
366 prepare stmt1 from "insert into t9
367  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
368 values
369  ( CAST('52' as binary), CAST('52' as binary),
370  CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),
371  CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),
372  CAST('52' as binary), CAST('52' as binary), CAST('52' as binary) )" ;
373 execute stmt1 ;
374 set @arg00= CAST('53' as binary) ;
375 prepare stmt2 from "insert into t9
376  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
377 values
378  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
379 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
380  @arg00, @arg00, @arg00, @arg00 ;
381 
382 
383 ######## INSERT into .. numeric columns values(BIGINT,NULL) ########
384 # we first assign number to arg00 to set it's datatype to numeric.
385 set @arg00= 2 ;
386 set @arg00= NULL ;
387 insert into t9
388  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
389 values
390  ( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
391  NULL, NULL, NULL ) ;
392 insert into t9
393  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
394 values
395  ( 61, @arg00, @arg00, @arg00, @arg00, @arg00,
396  @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
397 prepare stmt1 from "insert into t9
398  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
399 values
400  ( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
401  NULL, NULL, NULL )" ;
402 execute stmt1 ;
403 prepare stmt2 from "insert into t9
404  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
405 values
406  ( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
407 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
408  @arg00, @arg00, @arg00, @arg00 ;
409 
410 
411 ######## INSERT into .. numeric columns values(DOUBLE,NULL) ########
412 set @arg00= 8.0 ;
413 set @arg00= NULL ;
414 insert into t9
415  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
416 values
417  ( 71, @arg00, @arg00, @arg00, @arg00, @arg00,
418  @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
419 prepare stmt2 from "insert into t9
420  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
421 values
422  ( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
423 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
424  @arg00, @arg00, @arg00, @arg00 ;
425 
426 
427 ######## INSERT into .. numeric columns values(LONGBLOB,NULL) ########
428 set @arg00= 'abc' ;
429 set @arg00= NULL ;
430 insert into t9
431  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
432 values
433  ( 81, @arg00, @arg00, @arg00, @arg00, @arg00,
434  @arg00, @arg00, @arg00, @arg00, @arg00 ) ;
435 prepare stmt2 from "insert into t9
436  ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
437 values
438  ( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
439 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
440  @arg00, @arg00, @arg00, @arg00 ;
441 
442 
443 
444 ######## SELECT of all inserted records ########
445 select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
446 from t9 where c1 >= 20
447 order by c1 ;
448 
449 
450 --disable_query_log
451 select '-- select .. where numeric column = .. --' as test_sequence ;
452 --enable_query_log
453 ######## SELECT .. WHERE column(numeric)=value(BIGINT(n)/BIGINT) ########
454 set @arg00= 20;
455 select 'true' as found from t9
456 where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
457  and c8= 20 and c9= 20 and c10= 20 and c12= 20;
458 select 'true' as found from t9
459 where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
460  and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
461  and c12= @arg00;
462 prepare stmt1 from "select 'true' as found from t9
463 where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20
464  and c8= 20 and c9= 20 and c10= 20 and c12= 20 ";
465 execute stmt1 ;
466 prepare stmt1 from "select 'true' as found from t9
467 where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
468  and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
469  and c12= ? ";
470 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
471  @arg00, @arg00, @arg00, @arg00 ;
472 
473 
474 ######## SELECT .. WHERE column(numeric)=value(DOUBLE(m,n)/DOUBLE) ########
475 set @arg00= 20.0;
476 select 'true' as found from t9
477 where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
478  and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0;
479 select 'true' as found from t9
480 where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
481  and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
482  and c12= @arg00;
483 prepare stmt1 from "select 'true' as found from t9
484 where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0
485  and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0 ";
486 execute stmt1 ;
487 prepare stmt1 from "select 'true' as found from t9
488 where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
489  and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
490  and c12= ? ";
491 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
492  @arg00, @arg00, @arg00, @arg00 ;
493 
494 
495 ######## SELECT .. WHERE column(numeric)=value(CHAR(n)/LONGTEXT) ########
496 select 'true' as found from t9
497 where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
498  and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20';
499 prepare stmt1 from "select 'true' as found from t9
500 where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'
501  and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20' ";
502 execute stmt1 ;
503 set @arg00= '20';
504 select 'true' as found from t9
505 where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
506  and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
507  and c12= @arg00;
508 prepare stmt1 from "select 'true' as found from t9
509 where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
510  and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
511  and c12= ? ";
512 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
513  @arg00, @arg00, @arg00, @arg00 ;
514 
515 
516 ######## SELECT .. WHERE column(numeric)=value(BINARY(n)/LONGBLOB) ########
517 select 'true' as found from t9
518 where c1= CAST('20' as binary) and c2= CAST('20' as binary) and
519  c3= CAST('20' as binary) and c4= CAST('20' as binary) and
520  c5= CAST('20' as binary) and c6= CAST('20' as binary) and
521  c7= CAST('20' as binary) and c8= CAST('20' as binary) and
522  c9= CAST('20' as binary) and c10= CAST('20' as binary) and
523  c12= CAST('20' as binary);
524 prepare stmt1 from "select 'true' as found from t9
525 where c1= CAST('20' as binary) and c2= CAST('20' as binary) and
526  c3= CAST('20' as binary) and c4= CAST('20' as binary) and
527  c5= CAST('20' as binary) and c6= CAST('20' as binary) and
528  c7= CAST('20' as binary) and c8= CAST('20' as binary) and
529  c9= CAST('20' as binary) and c10= CAST('20' as binary) and
530  c12= CAST('20' as binary) ";
531 execute stmt1 ;
532 set @arg00= CAST('20' as binary) ;
533 select 'true' as found from t9
534 where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00
535  and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00
536  and c12= @arg00;
537 prepare stmt1 from "select 'true' as found from t9
538 where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?
539  and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?
540  and c12= ? ";
541 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
542  @arg00, @arg00, @arg00, @arg00 ;
543 
544 delete from t9 ;
545 
546 #################### Some overflow experiments ################################
547 # #
548 # MySQL Manual (July 2004) #
549 # - Setting a numeric column to a value that lies outside the column's range. #
550 # The value is clipped to the closest endpoint of the range. #
551 # ... #
552 # - For example, inserting the string '1999.0e-2' into an INT, FLOAT, #
553 # DECIMAL(10,6), or YEAR column results in the values 1999, 19.9921, #
554 # 19.992100, and 1999. #
555 # That means there is an anomaly if a float value is assigned via string to #
556 # a column of type bigint. The string will be cut from the right side to #
557 # a "usable" integer value. #
558 # #
559 ###############################################################################
560 --disable_query_log
561 select '-- some numeric overflow experiments --' as test_sequence ;
562 --enable_query_log
563 prepare my_insert from "insert into t9
564  ( c21, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
565 values
566  ( 'O', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
567 prepare my_select from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12
568 from t9 where c21 = 'O' ";
569 prepare my_delete from "delete from t9 where c21 = 'O' ";
570 
571 # Numeric overflow of columns(c1, c2, c3, c4, c5, c12) with type not in
572 # (BIGINT,FLOAT,REAL,DOUBLE) during insert
573 #
574 # Use the maximum BIGINT from the manual
575 set @arg00= 9223372036854775807 ;
576 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
577  @arg00, @arg00, @arg00, @arg00, @arg00 ;
578 --vertical_results
579 --replace_result e+0 e+
580 execute my_select ;
581 --horizontal_results
582 --replace_result e+0 e+
583 execute my_delete ;
584 set @arg00= '9223372036854775807' ;
585 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
586  @arg00, @arg00, @arg00, @arg00, @arg00 ;
587 --vertical_results
588 --replace_result e+0 e+
589 execute my_select ;
590 --horizontal_results
591 --replace_result e+0 e+
592 execute my_delete ;
593 # Use the minimum BIGINT from the manual
594 #
595 set @arg00= -9223372036854775808 ;
596 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
597  @arg00, @arg00, @arg00, @arg00, @arg00 ;
598 --vertical_results
599 --replace_result e+0 e+
600 execute my_select ;
601 --horizontal_results
602 --replace_result e+0 e+
603 execute my_delete ;
604 set @arg00= '-9223372036854775808' ;
605 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
606  @arg00, @arg00, @arg00, @arg00, @arg00 ;
607 --vertical_results
608 --replace_result e+0 e+
609 execute my_select ;
610 --horizontal_results
611 --replace_result e+0 e+
612 execute my_delete ;
613 
614 # Numeric overflow of columns(c1, c2, c3, c4, c5, c12) with type not in
615 # (FLOAT,REAL,DOUBLE) during insert
616 #
617 set @arg00= 1.11111111111111111111e+50 ;
618 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
619  @arg00, @arg00, @arg00, @arg00, @arg00 ;
620 --vertical_results
621 --replace_result e+0 e+
622 execute my_select ;
623 --horizontal_results
624 --replace_result e+0 e+
625 execute my_delete ;
626 # Attention: The columns(c1,c2,c3,c4,c5,c6) do not get the overflow,
627 # because the string is treated as written integer and
628 # '.11111111111111111111e+50' is cut away.
629 set @arg00= '1.11111111111111111111e+50' ;
630 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
631  @arg00, @arg00, @arg00, @arg00, @arg00 ;
632 --vertical_results
633 --replace_result e+0 e+
634 execute my_select ;
635 --horizontal_results
636 --replace_result e+0 e+
637 execute my_delete ;
638 set @arg00= -1.11111111111111111111e+50 ;
639 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
640  @arg00, @arg00, @arg00, @arg00, @arg00 ;
641 --vertical_results
642 --replace_result e+0 e+
643 execute my_select ;
644 --horizontal_results
645 --replace_result e+0 e+
646 execute my_delete ;
647 # Attention: The columns(c1,c2,c3,c4,c5,c6) do not get the overflow,
648 # because the string is treated as written integer and
649 # '.11111111111111111111e+50' is cut away.
650 set @arg00= '-1.11111111111111111111e+50' ;
651 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
652  @arg00, @arg00, @arg00, @arg00, @arg00 ;
653 --vertical_results
654 --replace_result e+0 e+
655 execute my_select ;
656 --horizontal_results
657 --replace_result e+0 e+
658 execute my_delete ;
659 
660 ########################## test of string types ##########################
661 # #
662 # c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, #
663 # c25 blob, c26 text, c27 mediumblob, c28 mediumtext, c29 longblob, #
664 # c30 longtext, c31 enum('one', 'two', 'three') #
665 # #
666 ##########################################################################
667 --disable_query_log
668 select '-- insert into string columns --' as test_sequence ;
669 --enable_query_log
670 
671 ######## INSERT into .. string columns values(CHAR(n),LONGTEXT) ########
672 --disable_query_log
673 insert into t9
674  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
675 values
676  ( 20, '20', '20', '20', '20', '20', '20', '20', '20', '20', '20', '20' ) ;
677 set @arg00= '21' ;
678 insert into t9
679  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
680 values
681  ( 21, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
682  @arg00, @arg00, @arg00 ) ;
683 prepare stmt1 from "insert into t9
684  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
685 values
686  ( 22, '22', '22', '22', '22', '22', '22', '22', '22', '22', '22', '22' )" ;
687 execute stmt1 ;
688 set @arg00= '23';
689 prepare stmt2 from "insert into t9
690  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
691 values
692  ( 23, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
693 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
694  @arg00, @arg00, @arg00, @arg00 ;
695 
696 
697 ######## INSERT into .. string columns values(BINARY(n),LONGBLOB) ########
698 insert into t9
699  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
700 values
701  ( 30, CAST('30' as binary), CAST('30' as binary), CAST('30' as binary),
702  CAST('30' as binary), CAST('30' as binary), CAST('30' as binary),
703  CAST('30' as binary), CAST('30' as binary), CAST('30' as binary),
704  CAST('30' as binary), CAST('30' as binary) ) ;
705 set @arg00= '31' ;
706 insert into t9
707  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
708 values
709  ( 31, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
710  @arg00, @arg00, @arg00 ) ;
711 prepare stmt1 from "insert into t9
712  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
713 values
714  ( 32, CAST('32' as binary), CAST('32' as binary), CAST('32' as binary),
715  CAST('32' as binary), CAST('32' as binary), CAST('32' as binary),
716  CAST('32' as binary), CAST('32' as binary), CAST('32' as binary),
717  CAST('32' as binary), CAST('32' as binary) )" ;
718 execute stmt1 ;
719 set @arg00= CAST('33' as binary);
720 prepare stmt2 from "insert into t9
721  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
722 values
723  ( 33, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
724 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
725  @arg00, @arg00, @arg00, @arg00 ;
726 
727 
728 ######## INSERT into .. string columns values(BIGINT(n),BIGINT) ########
729 insert into t9
730  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
731 values
732  ( 40, 40, 40, 40, 40, 40, 40, 40, 40, 40, 40, 40 ) ;
733 set @arg00= 41 ;
734 insert into t9
735  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
736 values
737  ( 41, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
738  @arg00, @arg00, @arg00 ) ;
739 prepare stmt1 from "insert into t9
740  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
741 values
742  ( 42, 42, 42, 42, 42, 42, 42, 42, 42, 42, 42, 42 )" ;
743 execute stmt1 ;
744 set @arg00= 43;
745 prepare stmt2 from "insert into t9
746  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
747 values
748  ( 43, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
749 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
750  @arg00, @arg00, @arg00, @arg00 ;
751 
752 
753 ######## INSERT into .. string columns values(DOUBLE(m,n),DOUBLE) ########
754 insert into t9
755  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
756 values
757  ( 50, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0 ) ;
758 set @arg00= 51.0 ;
759 insert into t9
760  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
761 values
762  ( 51, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
763  @arg00, @arg00, @arg00 ) ;
764 prepare stmt1 from "insert into t9
765  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
766 values
767  ( 52, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0 )" ;
768 execute stmt1 ;
769 set @arg00= 53.0;
770 prepare stmt2 from "insert into t9
771  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
772 values
773  ( 53, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
774 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
775  @arg00, @arg00, @arg00, @arg00 ;
776 
777 
778 ######## INSERT into .. string columns values(DOUBLE(m,n),DOUBLE) ########
779 # typical float writing style
780 insert into t9
781  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
782 values
783  ( 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,
784  5.4e+1, 5.4e+1, 5.4e+1 ) ;
785 set @arg00= 5.5e+1 ;
786 insert into t9
787  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
788 values
789  ( 55, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
790  @arg00, @arg00, @arg00 ) ;
791 prepare stmt1 from "insert into t9
792  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
793 values
794  ( 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,
795  5.6e+1, 5.6e+1, 5.6e+1 )" ;
796 execute stmt1 ;
797 set @arg00= 5.7e+1;
798 prepare stmt2 from "insert into t9
799  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
800 values
801  ( 57, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
802 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
803  @arg00, @arg00, @arg00, @arg00 ;
804 
805 
806 ######## INSERT into .. string columns values(LONGBLOB,NULL) ########
807 set @arg00= 'abc' ;
808 set @arg00= NULL ;
809 insert into t9
810  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
811 values
812  ( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) ;
813 insert into t9
814  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
815 values
816  ( 61, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
817  @arg00, @arg00, @arg00 ) ;
818 prepare stmt1 from "insert into t9
819  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
820 values
821  ( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )" ;
822 execute stmt1 ;
823 prepare stmt2 from "insert into t9
824  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
825 values
826  ( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
827 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
828  @arg00, @arg00, @arg00, @arg00 ;
829 
830 ######## INSERT into .. string columns values(BIGINT,NULL) ########
831 set @arg00= 2 ;
832 set @arg00= NULL ;
833 insert into t9
834  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
835 values
836  ( 71, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
837  @arg00, @arg00, @arg00 ) ;
838 prepare stmt2 from "insert into t9
839  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
840 values
841  ( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
842 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
843  @arg00, @arg00, @arg00, @arg00 ;
844 
845 ######## INSERT into .. string columns values(DOUBLE,NULL) ########
846 set @arg00= 8 ;
847 set @arg00= NULL ;
848 insert into t9
849  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
850 values
851  ( 81, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
852  @arg00, @arg00, @arg00 ) ;
853 prepare stmt2 from "insert into t9
854  ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
855 values
856  ( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
857 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
858  @arg00, @arg00, @arg00, @arg00 ;
859 
860 --enable_query_log
861 
862 ######## SELECT of all inserted records ########
863 select c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30
864 from t9 where c1 >= 20
865 order by c1 ;
866 
867 
868 --disable_query_log
869 select '-- select .. where string column = .. --' as test_sequence ;
870 --enable_query_log
871 ######## SELECT .. WHERE column(string)=value(CHAR(n)/LONGTEXT) ########
872 set @arg00= '20';
873 # c20 (char) must be extended for the comparison
874 select 'true' as found from t9
875 where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and
876  c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and
877  c27= '20' and c28= '20' and c29= '20' and c30= '20' ;
878 select 'true' as found from t9
879 where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
880  c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
881  c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
882 prepare stmt1 from "select 'true' as found from t9
883 where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and
884  c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and
885  c27= '20' and c28= '20' and c29= '20' and c30= '20'" ;
886 execute stmt1 ;
887 prepare stmt1 from "select 'true' as found from t9
888 where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
889  c21= ? and c22= ? and c23= ? and c25= ? and
890  c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
891 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
892  @arg00, @arg00, @arg00, @arg00, @arg00 ;
893 
894 
895 ######## SELECT .. WHERE column(string)=value(BINARY(n)/LONGBLOB) ########
896 set @arg00= CAST('20' as binary);
897 # c20 (char) must be extended for the comparison
898 select 'true' as found from t9
899 where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))
900  = CAST('20' as binary) and c21= CAST('20' as binary)
901  and c22= CAST('20' as binary) and c23= CAST('20' as binary) and
902  c24= CAST('20' as binary) and c25= CAST('20' as binary) and
903  c26= CAST('20' as binary) and c27= CAST('20' as binary) and
904  c28= CAST('20' as binary) and c29= CAST('20' as binary) and
905  c30= CAST('20' as binary) ;
906 select 'true' as found from t9
907 where c1= 20 and concat(c20,substr(@arg00,1+length(c20))) = @arg00 and
908  c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
909  c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and
910  c30= @arg00;
911 prepare stmt1 from "select 'true' as found from t9
912 where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))
913  = CAST('20' as binary) and c21= CAST('20' as binary)
914  and c22= CAST('20' as binary) and c23= CAST('20' as binary) and
915  c24= CAST('20' as binary) and c25= CAST('20' as binary) and
916  c26= CAST('20' as binary) and c27= CAST('20' as binary) and
917  c28= CAST('20' as binary) and c29= CAST('20' as binary) and
918  c30= CAST('20' as binary)" ;
919 execute stmt1 ;
920 prepare stmt1 from "select 'true' as found from t9
921 where c1= 20 and concat(c20,substr(?,1+length(c20))) = ? and c21= ? and
922  c22= ? and c23= ? and c25= ? and c26= ? and c27= ? and c28= ? and
923  c29= ? and c30= ?";
924 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
925  @arg00, @arg00, @arg00, @arg00, @arg00 ;
926 
927 
928 ######## SELECT .. WHERE column(string)=value(BIGINT(m,n),BIGINT) ########
929 set @arg00= 20;
930 # c20 (char) must be extended for the comparison
931 select 'true' as found from t9
932 where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and
933  c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and
934  c27= 20 and c28= 20 and c29= 20 and c30= 20 ;
935 select 'true' as found from t9
936 where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
937  c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
938  c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
939 prepare stmt1 from "select 'true' as found from t9
940 where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and
941  c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and
942  c27= 20 and c28= 20 and c29= 20 and c30= 20" ;
943 execute stmt1 ;
944 prepare stmt1 from "select 'true' as found from t9
945 where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
946  c21= ? and c22= ? and c23= ? and c25= ? and
947  c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
948 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
949  @arg00, @arg00, @arg00, @arg00, @arg00 ;
950 
951 
952 ######## SELECT .. WHERE column(string)=value(DOUBLE(m,n),DOUBLE) ########
953 set @arg00= 20.0;
954 # c20 (char) must be extended for the comparison
955 select 'true' as found from t9
956 where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and
957  c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and
958  c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0 ;
959 select 'true' as found from t9
960 where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and
961  c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and
962  c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;
963 prepare stmt1 from "select 'true' as found from t9
964 where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and
965  c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and
966  c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0" ;
967 execute stmt1 ;
968 prepare stmt1 from "select 'true' as found from t9
969 where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and
970  c21= ? and c22= ? and c23= ? and c25= ? and
971  c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;
972 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
973  @arg00, @arg00, @arg00, @arg00, @arg00 ;
974 
975 delete from t9 ;
976 
977 
978 ######################### test of date/time columns ########################
979 # #
980 # c13 date, c14 datetime, c15 timestamp(14), c16 time, c17 year #
981 # #
982 ############################################################################
983 --disable_query_log
984 select '-- insert into date/time columns --' as test_sequence ;
985 --enable_query_log
986 ######## INSERT into .. date/time columns values(VARCHAR(19),LONGTEXT) ########
987 --disable_query_log
988 set @arg00= '1991-01-01 01:01:01' ;
989 insert into t9
990  ( c1, c13, c14, c15, c16, c17 )
991 values
992  ( 20, '1991-01-01 01:01:01', '1991-01-01 01:01:01', '1991-01-01 01:01:01',
993  '1991-01-01 01:01:01', '1991-01-01 01:01:01') ;
994 insert into t9
995  ( c1, c13, c14, c15, c16, c17 )
996 values
997  ( 21, @arg00, @arg00, @arg00, @arg00, @arg00) ;
998 prepare stmt1 from "insert into t9
999  ( c1, c13, c14, c15, c16, c17 )
1000 values
1001  ( 22, '1991-01-01 01:01:01', '1991-01-01 01:01:01', '1991-01-01 01:01:01',
1002  '1991-01-01 01:01:01', '1991-01-01 01:01:01')" ;
1003 execute stmt1 ;
1004 prepare stmt2 from "insert into t9
1005  ( c1, c13, c14, c15, c16, c17 )
1006 values
1007  ( 23, ?, ?, ?, ?, ? )" ;
1008 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
1009 
1010 
1011 ######## INSERT into .. date/time columns values(DATETIME,LONGBLOB) ########
1012 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
1013 insert into t9
1014  ( c1, c13, c14, c15, c16, c17 )
1015 values
1016  ( 30, CAST('1991-01-01 01:01:01' as datetime),
1017  CAST('1991-01-01 01:01:01' as datetime),
1018  CAST('1991-01-01 01:01:01' as datetime),
1019  CAST('1991-01-01 01:01:01' as datetime),
1020  CAST('1991-01-01 01:01:01' as datetime)) ;
1021 insert into t9
1022  ( c1, c13, c14, c15, c16, c17 )
1023 values
1024  ( 31, @arg00, @arg00, @arg00, @arg00, @arg00) ;
1025 prepare stmt1 from "insert into t9
1026  ( c1, c13, c14, c15, c16, c17 )
1027 values
1028  ( 32, CAST('1991-01-01 01:01:01' as datetime),
1029  CAST('1991-01-01 01:01:01' as datetime),
1030  CAST('1991-01-01 01:01:01' as datetime),
1031  CAST('1991-01-01 01:01:01' as datetime),
1032  CAST('1991-01-01 01:01:01' as datetime))" ;
1033 execute stmt1 ;
1034 prepare stmt2 from "insert into t9
1035  ( c1, c13, c14, c15, c16, c17 )
1036 values
1037  ( 33, ?, ?, ?, ?, ? )" ;
1038 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
1039 
1040 
1041 ######## INSERT into .. date/time columns values(BIGINT(n),BIGINT) ########
1042 set @arg00= 2000000000 ;
1043 insert into t9
1044  ( c1, c13, c14, c15, c16, c17 )
1045 values
1046  ( 40, 2000000000, 2000000000, 2000000000, 2000000000, 2000000000 ) ;
1047 insert into t9
1048  ( c1, c13, c14, c15, c16, c17 )
1049 values
1050  ( 41, @arg00, @arg00, @arg00, @arg00, @arg00) ;
1051 prepare stmt1 from "insert into t9
1052  ( c1, c13, c14, c15, c16, c17 )
1053 values
1054  ( 42, 2000000000, 2000000000, 2000000000, 2000000000, 2000000000 )" ;
1055 execute stmt1 ;
1056 prepare stmt2 from "insert into t9
1057  ( c1, c13, c14, c15, c16, c17 )
1058 values
1059  ( 43, ?, ?, ?, ?, ? )" ;
1060 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
1061 
1062 
1063 ######## INSERT into .. date/time columns values(DOUBLE(m,n),DOUBLE) ########
1064 set @arg00= 1.0e+10 ;
1065 insert into t9
1066  ( c1, c13, c14, c15, c16, c17 )
1067 values
1068  ( 50, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 ) ;
1069 insert into t9
1070  ( c1, c13, c14, c15, c16, c17 )
1071 values
1072  ( 51, @arg00, @arg00, @arg00, @arg00, @arg00) ;
1073 prepare stmt1 from "insert into t9
1074  ( c1, c13, c14, c15, c16, c17 )
1075 values
1076  ( 52, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 )" ;
1077 execute stmt1 ;
1078 prepare stmt2 from "insert into t9
1079  ( c1, c13, c14, c15, c16, c17 )
1080 values
1081  ( 53, ?, ?, ?, ?, ? )" ;
1082 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
1083 
1084 
1085 ######## INSERT into .. date/time columns values(LONGBLOB,NULL) ########
1086 # Attention: c15 is timestamp and the manual says:
1087 # The first TIMESTAMP column in table row automatically is updated
1088 # to the current timestamp when the value of any other column in the
1089 # row is changed, unless the TIMESTAMP column explicitly is assigned
1090 # a value other than NULL.
1091 # That's why a fixed NOT NULL value is inserted.
1092 set @arg00= 'abc' ;
1093 set @arg00= NULL ;
1094 insert into t9
1095  ( c1, c13, c14, c15, c16, c17 )
1096 values
1097  ( 60, NULL, NULL, '1991-01-01 01:01:01',
1098  NULL, NULL) ;
1099 insert into t9
1100  ( c1, c13, c14, c15, c16, c17 )
1101 values
1102  ( 61, @arg00, @arg00, '1991-01-01 01:01:01', @arg00, @arg00) ;
1103 prepare stmt1 from "insert into t9
1104  ( c1, c13, c14, c15, c16, c17 )
1105 values
1106  ( 62, NULL, NULL, '1991-01-01 01:01:01',
1107  NULL, NULL)" ;
1108 execute stmt1 ;
1109 prepare stmt2 from "insert into t9
1110  ( c1, c13, c14, c15, c16, c17 )
1111 values
1112  ( 63, ?, ?, '1991-01-01 01:01:01', ?, ? )" ;
1113 execute stmt2 using @arg00, @arg00, @arg00, @arg00 ;
1114 
1115 
1116 ######## INSERT into .. date/time columns values(BIGINT,NULL) ########
1117 set @arg00= 8 ;
1118 set @arg00= NULL ;
1119 insert into t9
1120  ( c1, c13, c14, c15, c16, c17 )
1121 values
1122  ( 71, @arg00, @arg00, '1991-01-01 01:01:01', @arg00, @arg00) ;
1123 prepare stmt2 from "insert into t9
1124  ( c1, c13, c14, c15, c16, c17 )
1125 values
1126  ( 73, ?, ?, '1991-01-01 01:01:01', ?, ? )" ;
1127 execute stmt2 using @arg00, @arg00, @arg00, @arg00 ;
1128 
1129 
1130 ######## INSERT into .. date/time columns values(DOUBLE,NULL) ########
1131 set @arg00= 8.0 ;
1132 set @arg00= NULL ;
1133 insert into t9
1134  ( c1, c13, c14, c15, c16, c17 )
1135 values
1136  ( 81, @arg00, @arg00, '1991-01-01 01:01:01', @arg00, @arg00) ;
1137 prepare stmt2 from "insert into t9
1138  ( c1, c13, c14, c15, c16, c17 )
1139 values
1140  ( 83, ?, ?, '1991-01-01 01:01:01', ?, ? )" ;
1141 execute stmt2 using @arg00, @arg00, @arg00, @arg00 ;
1142 
1143 --enable_query_log
1144 
1145 ######## SELECT of all inserted records ########
1146 select c1, c13, c14, c15, c16, c17 from t9 order by c1 ;
1147 
1148 
1149 --disable_query_log
1150 select '-- select .. where date/time column = .. --' as test_sequence ;
1151 --enable_query_log
1152 ######## SELECT .. WHERE column(date/time/..)=value(CHAR(n)/LONGTEXT) ########
1153 set @arg00= '1991-01-01 01:01:01' ;
1154 select 'true' as found from t9
1155 where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
1156  c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
1157  c17= '1991-01-01 01:01:01' ;
1158 select 'true' as found from t9
1159 where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
1160  and c17= @arg00 ;
1161 prepare stmt1 from "select 'true' as found from t9
1162 where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
1163  c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
1164  c17= '1991-01-01 01:01:01'" ;
1165 execute stmt1 ;
1166 prepare stmt1 from "select 'true' as found from t9
1167 where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
1168 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
1169 
1170 
1171 ######## SELECT .. WHERE column(date/time/..)=value(DATETIME/LONGBLOB) ########
1172 set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
1173 select 'true' as found from t9
1174 where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
1175  c14= CAST('1991-01-01 01:01:01' as datetime) and
1176  c15= CAST('1991-01-01 01:01:01' as datetime) and
1177  c16= CAST('1991-01-01 01:01:01' as datetime) and
1178  c17= CAST('1991-01-01 01:01:01' as datetime) ;
1179 select 'true' as found from t9
1180 where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
1181  and c17= @arg00 ;
1182 prepare stmt1 from "select 'true' as found from t9
1183 where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and
1184  c14= CAST('1991-01-01 01:01:01' as datetime) and
1185  c15= CAST('1991-01-01 01:01:01' as datetime) and
1186  c16= CAST('1991-01-01 01:01:01' as datetime) and
1187  c17= CAST('1991-01-01 01:01:01' as datetime)" ;
1188 execute stmt1 ;
1189 prepare stmt1 from "select 'true' as found from t9
1190 where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
1191 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
1192 
1193 
1194 ######## SELECT .. WHERE column(year)=value(INT(10)/BIGINT) ########
1195 set @arg00= 1991 ;
1196 select 'true' as found from t9
1197 where c1= 20 and c17= 1991 ;
1198 select 'true' as found from t9
1199 where c1= 20 and c17= @arg00 ;
1200 prepare stmt1 from "select 'true' as found from t9
1201 where c1= 20 and c17= 1991" ;
1202 execute stmt1 ;
1203 prepare stmt1 from "select 'true' as found from t9
1204 where c1= 20 and c17= ?" ;
1205 execute stmt1 using @arg00 ;
1206 
1207 
1208 ######## SELECT .. WHERE column(year)=value(DOUBLE(m,n)/DOUBLE) ########
1209 set @arg00= 1.991e+3 ;
1210 select 'true' as found from t9
1211 where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;
1212 select 'true' as found from t9
1213 where c1= 20 and abs(c17 - @arg00) < 0.01 ;
1214 prepare stmt1 from "select 'true' as found from t9
1215 where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;
1216 execute stmt1 ;
1217 prepare stmt1 from "select 'true' as found from t9
1218 where c1= 20 and abs(c17 - ?) < 0.01" ;
1219 execute stmt1 using @arg00 ;
1220