bind_param incorrectly interprets scientific notation [rt.cpan.org #83489]

[mbeijen]

trafficstars

Migrated from rt.cpan.org#83489 (status was 'new')

Requestors:

• [email protected]

From [email protected] on 2013-02-20 22:13:43:

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)