1
############################ ps_conv.inc ##############################
3
# Tests for prepared statements: conversion of parameters #
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
########################################################################
13
# NOTE: PLEASE SEE ps_1general.test (bottom)
14
# BEFORE ADDING NEW TEST CASES HERE !!!
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
22
# The MySQL User Variables do not support the simulation of all
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
29
# The next tests should give an example for these properties.
31
drop table if exists t5 ;
35
set @arg03= 80.00000000000e-1;
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
53
set @arg15= CAST('abc' as binary) ;
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,
72
# Bug#4788 show create table provides incorrect statement
73
show create table t5 ;
83
# But there seems to be also an implicit conversion of C-API
84
# data types to a smaller number of base data types.
86
# Example: C-API for prepared statements
87
# CREATE TABLE abc as SELECT ? as a, ? as b, ...
89
# MYSQL_TYPE of parameter column type
90
# MYSQL_TYPE_TINY bigint(4)
91
# MYSQL_TYPE_SHORT bigint(6)
92
# MYSQL_TYPE_FLOAT double
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.
99
# The main test object is the table t9, defined as follows:
101
# eval create table t9
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'),
114
# We test each statement in non-prepared mode and in prepared mode
115
# for comparison purposes.
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
125
select '------ data type conversion tests ------' as test_sequence ;
127
--source include/ps_renew.inc
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 ;
133
############ select @parm:= .. / select .. into @parm tests ############
135
select '------ select @parameter:= column ------' as test_sequence ;
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" ;
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
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
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= ?" ;
187
execute stmt1 using @my_key ;
188
# get as much informations about the parameters as possible
192
# now the same procedure with the record containing so many NULLs
194
execute stmt1 using @my_key ;
195
# get as much informations about the parameters as possible
200
# the next statement must fail
202
prepare stmt1 from "select ? := c1 from t9 where c1= 1" ;
205
select '------ select column, .. into @parm,.. ------' as test_sequence ;
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
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
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= ?" ;
242
execute stmt1 using @my_key ;
243
# get as much informations about the parameters as possible
247
# now the same procedure with the record containing so many NULLs
248
# Bug#5034: prepared "select 1 into @arg15", second execute crashes server
250
execute stmt1 using @my_key ;
251
# get as much informations about the parameters as possible
256
# the next statement must fail
258
prepare stmt1 from "select c1 into ? from t9 where c1= 1" ;
262
######################### test of numeric types ##########################
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), #
268
##########################################################################
270
select '-- insert into numeric columns --' as test_sequence ;
273
######## INSERT into .. numeric columns values(BIGINT(n),BIGINT) ########
275
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
277
( 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20 ) ;
280
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
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 )
287
( 22, 22, 22, 22, 22, 22, 22, 22, 22, 22, 22 )" ;
290
prepare stmt2 from "insert into t9
291
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
293
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
294
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
295
@arg00, @arg00, @arg00, @arg00 ;
298
######## INSERT into .. numeric columns values(DOUBLE(m,n),DOUBLE) ########
300
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
302
( 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0,
306
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
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 )
313
( 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0,
314
32.0, 32.0, 32.0 )" ;
317
prepare stmt2 from "insert into t9
318
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
320
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
321
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
322
@arg00, @arg00, @arg00, @arg00 ;
325
######## INSERT into .. numeric columns values(CHAR(n),LONGTEXT) #########
327
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
329
( '40', '40', '40', '40', '40', '40', '40', '40',
333
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
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 )
340
( '42', '42', '42', '42', '42', '42', '42', '42',
341
'42', '42', '42' )" ;
344
prepare stmt2 from "insert into t9
345
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
347
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
348
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
349
@arg00, @arg00, @arg00, @arg00 ;
352
######## INSERT into .. numeric columns values(BINARY(n),LONGBLOB) ########
354
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
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) ;
362
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
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 )
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) )" ;
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 )
378
( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
379
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
380
@arg00, @arg00, @arg00, @arg00 ;
383
######## INSERT into .. numeric columns values(BIGINT,NULL) ########
384
# we first assign number to arg00 to set it's datatype to numeric.
388
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
390
( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
393
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
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 )
400
( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
401
NULL, NULL, NULL )" ;
403
prepare stmt2 from "insert into t9
404
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
406
( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
407
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
408
@arg00, @arg00, @arg00, @arg00 ;
411
######## INSERT into .. numeric columns values(DOUBLE,NULL) ########
415
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
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 )
422
( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
423
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
424
@arg00, @arg00, @arg00, @arg00 ;
427
######## INSERT into .. numeric columns values(LONGBLOB,NULL) ########
431
( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
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 )
438
( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
439
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
440
@arg00, @arg00, @arg00, @arg00 ;
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
451
select '-- select .. where numeric column = .. --' as test_sequence ;
453
######## SELECT .. WHERE column(numeric)=value(BIGINT(n)/BIGINT) ########
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
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 ";
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= ?
470
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
471
@arg00, @arg00, @arg00, @arg00 ;
474
######## SELECT .. WHERE column(numeric)=value(DOUBLE(m,n)/DOUBLE) ########
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
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 ";
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= ?
491
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
492
@arg00, @arg00, @arg00, @arg00 ;
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' ";
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
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= ?
512
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
513
@arg00, @arg00, @arg00, @arg00 ;
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) ";
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
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= ?
541
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
542
@arg00, @arg00, @arg00, @arg00 ;
546
#################### Some overflow experiments ################################
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. #
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. #
559
###############################################################################
561
select '-- some numeric overflow experiments --' as test_sequence ;
563
prepare my_insert from "insert into t9
564
( c21, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
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' ";
571
# Numeric overflow of columns(c1, c2, c3, c4, c5, c12) with type not in
572
# (BIGINT,FLOAT,REAL,DOUBLE) during insert
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 ;
579
--replace_result e+0 e+
582
--replace_result e+0 e+
584
set @arg00= '9223372036854775807' ;
585
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
586
@arg00, @arg00, @arg00, @arg00, @arg00 ;
588
--replace_result e+0 e+
591
--replace_result e+0 e+
593
# Use the minimum BIGINT from the manual
595
set @arg00= -9223372036854775808 ;
596
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
597
@arg00, @arg00, @arg00, @arg00, @arg00 ;
599
--replace_result e+0 e+
602
--replace_result e+0 e+
604
set @arg00= '-9223372036854775808' ;
605
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
606
@arg00, @arg00, @arg00, @arg00, @arg00 ;
608
--replace_result e+0 e+
611
--replace_result e+0 e+
614
# Numeric overflow of columns(c1, c2, c3, c4, c5, c12) with type not in
615
# (FLOAT,REAL,DOUBLE) during insert
617
set @arg00= 1.11111111111111111111e+50 ;
618
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
619
@arg00, @arg00, @arg00, @arg00, @arg00 ;
621
--replace_result e+0 e+
624
--replace_result e+0 e+
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 ;
633
--replace_result e+0 e+
636
--replace_result e+0 e+
638
set @arg00= -1.11111111111111111111e+50 ;
639
execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
640
@arg00, @arg00, @arg00, @arg00, @arg00 ;
642
--replace_result e+0 e+
645
--replace_result e+0 e+
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 ;
654
--replace_result e+0 e+
657
--replace_result e+0 e+
660
########################## test of string types ##########################
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') #
666
##########################################################################
668
select '-- insert into string columns --' as test_sequence ;
671
######## INSERT into .. string columns values(CHAR(n),LONGTEXT) ########
674
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
676
( 20, '20', '20', '20', '20', '20', '20', '20', '20', '20', '20', '20' ) ;
679
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
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 )
686
( 22, '22', '22', '22', '22', '22', '22', '22', '22', '22', '22', '22' )" ;
689
prepare stmt2 from "insert into t9
690
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
692
( 23, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
693
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
694
@arg00, @arg00, @arg00, @arg00 ;
697
######## INSERT into .. string columns values(BINARY(n),LONGBLOB) ########
699
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
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) ) ;
707
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
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 )
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) )" ;
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 )
723
( 33, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
724
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
725
@arg00, @arg00, @arg00, @arg00 ;
728
######## INSERT into .. string columns values(BIGINT(n),BIGINT) ########
730
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
732
( 40, 40, 40, 40, 40, 40, 40, 40, 40, 40, 40, 40 ) ;
735
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
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 )
742
( 42, 42, 42, 42, 42, 42, 42, 42, 42, 42, 42, 42 )" ;
745
prepare stmt2 from "insert into t9
746
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
748
( 43, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
749
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
750
@arg00, @arg00, @arg00, @arg00 ;
753
######## INSERT into .. string columns values(DOUBLE(m,n),DOUBLE) ########
755
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
757
( 50, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0, 50.0 ) ;
760
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
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 )
767
( 52, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0, 52.0 )" ;
770
prepare stmt2 from "insert into t9
771
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
773
( 53, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
774
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
775
@arg00, @arg00, @arg00, @arg00 ;
778
######## INSERT into .. string columns values(DOUBLE(m,n),DOUBLE) ########
779
# typical float writing style
781
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
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 ) ;
787
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
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 )
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 )" ;
798
prepare stmt2 from "insert into t9
799
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
801
( 57, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
802
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
803
@arg00, @arg00, @arg00, @arg00 ;
806
######## INSERT into .. string columns values(LONGBLOB,NULL) ########
810
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
812
( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) ;
814
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
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 )
821
( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )" ;
823
prepare stmt2 from "insert into t9
824
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
826
( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
827
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
828
@arg00, @arg00, @arg00, @arg00 ;
830
######## INSERT into .. string columns values(BIGINT,NULL) ########
834
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
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 )
841
( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
842
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
843
@arg00, @arg00, @arg00, @arg00 ;
845
######## INSERT into .. string columns values(DOUBLE,NULL) ########
849
( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
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 )
856
( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
857
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
858
@arg00, @arg00, @arg00, @arg00 ;
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
869
select '-- select .. where string column = .. --' as test_sequence ;
871
######## SELECT .. WHERE column(string)=value(CHAR(n)/LONGTEXT) ########
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'" ;
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 ;
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
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)" ;
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
924
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
925
@arg00, @arg00, @arg00, @arg00, @arg00 ;
928
######## SELECT .. WHERE column(string)=value(BIGINT(m,n),BIGINT) ########
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" ;
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 ;
952
######## SELECT .. WHERE column(string)=value(DOUBLE(m,n),DOUBLE) ########
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" ;
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 ;
978
######################### test of date/time columns ########################
980
# c13 date, c14 datetime, c15 timestamp(14), c16 time, c17 year #
982
############################################################################
984
select '-- insert into date/time columns --' as test_sequence ;
986
######## INSERT into .. date/time columns values(VARCHAR(19),LONGTEXT) ########
988
set @arg00= '1991-01-01 01:01:01' ;
990
( c1, c13, c14, c15, c16, c17 )
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') ;
995
( c1, c13, c14, c15, c16, c17 )
997
( 21, @arg00, @arg00, @arg00, @arg00, @arg00) ;
998
prepare stmt1 from "insert into t9
999
( c1, c13, c14, c15, c16, c17 )
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')" ;
1004
prepare stmt2 from "insert into t9
1005
( c1, c13, c14, c15, c16, c17 )
1007
( 23, ?, ?, ?, ?, ? )" ;
1008
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
1011
######## INSERT into .. date/time columns values(DATETIME,LONGBLOB) ########
1012
set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;
1014
( c1, c13, c14, c15, c16, c17 )
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)) ;
1022
( c1, c13, c14, c15, c16, c17 )
1024
( 31, @arg00, @arg00, @arg00, @arg00, @arg00) ;
1025
prepare stmt1 from "insert into t9
1026
( c1, c13, c14, c15, c16, c17 )
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))" ;
1034
prepare stmt2 from "insert into t9
1035
( c1, c13, c14, c15, c16, c17 )
1037
( 33, ?, ?, ?, ?, ? )" ;
1038
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
1041
######## INSERT into .. date/time columns values(BIGINT(n),BIGINT) ########
1042
set @arg00= 2000000000 ;
1044
( c1, c13, c14, c15, c16, c17 )
1046
( 40, 2000000000, 2000000000, 2000000000, 2000000000, 2000000000 ) ;
1048
( c1, c13, c14, c15, c16, c17 )
1050
( 41, @arg00, @arg00, @arg00, @arg00, @arg00) ;
1051
prepare stmt1 from "insert into t9
1052
( c1, c13, c14, c15, c16, c17 )
1054
( 42, 2000000000, 2000000000, 2000000000, 2000000000, 2000000000 )" ;
1056
prepare stmt2 from "insert into t9
1057
( c1, c13, c14, c15, c16, c17 )
1059
( 43, ?, ?, ?, ?, ? )" ;
1060
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
1063
######## INSERT into .. date/time columns values(DOUBLE(m,n),DOUBLE) ########
1064
set @arg00= 1.0e+10 ;
1066
( c1, c13, c14, c15, c16, c17 )
1068
( 50, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 ) ;
1070
( c1, c13, c14, c15, c16, c17 )
1072
( 51, @arg00, @arg00, @arg00, @arg00, @arg00) ;
1073
prepare stmt1 from "insert into t9
1074
( c1, c13, c14, c15, c16, c17 )
1076
( 52, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 )" ;
1078
prepare stmt2 from "insert into t9
1079
( c1, c13, c14, c15, c16, c17 )
1081
( 53, ?, ?, ?, ?, ? )" ;
1082
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
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.
1095
( c1, c13, c14, c15, c16, c17 )
1097
( 60, NULL, NULL, '1991-01-01 01:01:01',
1100
( c1, c13, c14, c15, c16, c17 )
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 )
1106
( 62, NULL, NULL, '1991-01-01 01:01:01',
1109
prepare stmt2 from "insert into t9
1110
( c1, c13, c14, c15, c16, c17 )
1112
( 63, ?, ?, '1991-01-01 01:01:01', ?, ? )" ;
1113
execute stmt2 using @arg00, @arg00, @arg00, @arg00 ;
1116
######## INSERT into .. date/time columns values(BIGINT,NULL) ########
1120
( c1, c13, c14, c15, c16, c17 )
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 )
1126
( 73, ?, ?, '1991-01-01 01:01:01', ?, ? )" ;
1127
execute stmt2 using @arg00, @arg00, @arg00, @arg00 ;
1130
######## INSERT into .. date/time columns values(DOUBLE,NULL) ########
1134
( c1, c13, c14, c15, c16, c17 )
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 )
1140
( 83, ?, ?, '1991-01-01 01:01:01', ?, ? )" ;
1141
execute stmt2 using @arg00, @arg00, @arg00, @arg00 ;
1145
######## SELECT of all inserted records ########
1146
select c1, c13, c14, c15, c16, c17 from t9 order by c1 ;
1150
select '-- select .. where date/time column = .. --' as test_sequence ;
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= '1991-01-01 01:01:01' 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= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
1161
prepare stmt1 from "select 'true' as found from t9
1162
where c1= 20 and c13= '1991-01-01 01:01:01' 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'" ;
1166
prepare stmt1 from "select 'true' as found from t9
1167
where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
1168
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
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 01:01:01' 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= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
1182
prepare stmt1 from "select 'true' as found from t9
1183
where c1= 20 and c13= CAST('1991-01-01 01:01:01' 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)" ;
1189
prepare stmt1 from "select 'true' as found from t9
1190
where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
1191
execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
1194
######## SELECT .. WHERE column(year)=value(INT(10)/BIGINT) ########
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" ;
1203
prepare stmt1 from "select 'true' as found from t9
1204
where c1= 20 and c17= ?" ;
1205
execute stmt1 using @arg00 ;
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" ;
1217
prepare stmt1 from "select 'true' as found from t9
1218
where c1= 20 and abs(c17 - ?) < 0.01" ;
1219
execute stmt1 using @arg00 ;