| Subject: | bind_param incorrectly interprets scientific notation |
Originally reported by Andrew Feren at
http://bugs.mysql.com/bug.php?id=37315
Description:
I have a DB with a DECIMAL column. If I use bind_param to insert a
value formated using scientific notation. The value inserted is off by
several orders of magnitude (the exponent specified in the scientific
notation).
example
7.1e-03 will get inserted as 7.1 not as .007
If I insert the same value ("7.1e-03") using execute with an arg list I
get the expected result.
How to repeat:
#!perl -w
use strict;
# DB modules
use DBI qw(:sql_types);
my $dbH = DBI->connect("DBI:mysql:database=test;host=localhost;port=3306",
'root','');
$dbH->do(qq {
CREATE TABLE IF NOT EXISTS test.float_test (
input VARCHAR(15) NOT NULL,
result DECIMAL(15,3) NOT NULL,
expected VARCHAR(15) NOT NULL,
ins_type VARCHAR(15) NOT NULL
) ENGINE = MyISAM
});
my $sci_v = '7.1e-03';
#reformat as 0.xxx
my $float_v = sprintf('%06f', $sci_v); # This is my current workaround
my $sth_execute = $dbH->prepare( qq{
INSERT INTO test.float_test
(input, result, expected, ins_type)
VALUES
($float_v,?,'0.007','execute'),
($sci_v, ?,'0.007','execute')
});
$sth_execute->execute($float_v, $sci_v);
my $sth_bind = $dbH->prepare( qq{
INSERT INTO test.float_test
(input, result, expected, ins_type)
VALUES
($float_v,?,'0.007','bind_param'),
($sci_v, ?,'0.007','bind_param')
});
$sth_bind->bind_param(1, $float_v, SQL_VARCHAR);
$sth_bind->bind_param(2, $sci_v, SQL_DECIMAL);
$sth_bind->execute();
# to see the row that failed.
# SELECT * FROM test.float_test WHERE result != expected;
Tested on several Servers and DBI versions. I first ran across this
several months ago and I no longer have a complete list, but I have most
recently seen this on.
MySQL
5.0.45-community-nt
5.0.51a-3ubuntu5.1
DBD::mysql
4.005 (on ubuntu)
4.005 (on windows)
4.006 (on windows)