MySQL Bugs: #27096: wrong conversion using Time::HiRes timestamp in prepared statement

Bug #27096	wrong conversion using Time::HiRes timestamp in prepared statement
Submitted:	13 Mar 2007 17:16	Modified:	19 Mar 2007 18:46
Reporter:	Axel Schwenke	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S2 (Serious)
Version:	4.003	OS:	Linux (Linux)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
When using a Time::HiRes timestamp as parameter for a prepared statement, the value is converted to nonsense.

Versions: Perl-5.8.3, DBI-1.57, DBD-mysql-4.003

How to repeat:
#!/usr/bin/perl -w

use DBI qw(:sql_types);
use Time::HiRes qw ( time );

my $dbh= DBI->connect("DBI:mysql:test", "root") or die;

$dbh->do("DROP TABLE IF EXISTS t1") or die;
$dbh->do("CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 CHAR(10), c3 FLOAT(16,6))") or die;

my $sth= $dbh->prepare("INSERT INTO t1 (c2, c3) VALUES (?, ?)") or die;

#
# INSERT 6 test rows
#
$sth->execute('1', 1);
$sth->execute('2.1', 2.1);
$sth->execute('"3"', "3");
$sth->execute('"4.4"', "4.4");
$sth->execute('pi', 3.1415926535);
my $pi= 3.1415926535;
$sth->execute('$pi', $pi);

#
# get current time from Time::HiRes
#
my $time= time;
printf "\$time= %.6f\n", $time;

#
# INSERT 5 rows using Time::HiRes timestamp
#
$sth->execute('$time', $time);

$sth->bind_param(1, '$time');
$sth->bind_param(2, $time, SQL_FLOAT);
$sth->execute;

$sth->bind_param(2, $time);
$sth->execute;

$sth->bind_param(2, 0.0+$time, SQL_FLOAT);
$sth->execute;

$sth->bind_param(2, 0.0+$time);
$sth->execute;

#
# explicitly convert timestamp to numerical Perl type
#
my $time2= $time+0.0;
printf "\$time2= %.6f\n", $time2;

#
# INSERT 3 rows using numerical value
#
$sth->execute('$time2', $time2);

$sth->bind_param(1, '$time2');
$sth->bind_param(2, $time2, SQL_FLOAT);
$sth->execute;

$sth->bind_param(2, $time2);
$sth->execute;

#
# explicitly convert timestamp to string
#
my $time3 = sprintf "%.6f", $time;
printf "\$time3= %s\n", $time3;

#
# INSERT 2 rows using string value
#
$sth->execute('$time3', $time3);

$sth->bind_param(1, '$time3');
$sth->bind_param(2, $time3);
$sth->execute;

#
# SELECT all rows
#
$sth= $dbh->prepare("SELECT * FROM t1");
$sth->execute;
DBI::dump_results($sth);

----

this gives:

~ $perl test.pl 
$time= 1173805804.992687
$time2= 1173805804.992687
$time3= 1173805804.992687
'1', '1', '1.000000'
'2', '2.1', '2.100000'
'3', '"3"', '3.000000'
'4', '"4.4"', '4.400000'
'5', 'pi', '3.141593'
'6', '$pi', '3.141593'
'7', '$time', '1173805824.000000'
'8', '$time', '1173805824.000000'
'9', '$time', '1173805824.000000'
'10', '$time', '1173805824.000000'
'11', '$time', '1173805824.000000'
'12', '$time2', '1173805824.000000'
'13', '$time2', '1173805824.000000'
'14', '$time2', '1173805824.000000'
'15', '$time3', '1173805824.000000'
'16', '$time3', '1173805824.000000'
16 rows

expected: 
rows 7-16 should have value 1173805804.992687

test.pl

Attachment: test.pl (application/x-perl, text), 1.63 KiB.

correction: DBI version is 1.54

Change your FLOAT to DOUBLE and it works fine. You're trying to store too much in a FLOAT. There's nothing specific to DBD::mysql about this behavior. (Hint: in the future, enable the general query log to see what is actually being sent to the server. With this, it is clear that DBD::mysql is sending the actual floating point value.)