Bug #37315 bind_param incorrectly interprets scientific notation
Submitted: 10 Jun 2008 18:17 Modified: 20 Feb 2013 22:14
Reporter: Andrew Feren Email Updates:
Status: Unsupported Impact on me:
None 
Category:Connectors: DBD::mysql ( Perl ) Severity:S3 (Non-critical)
Version:4.005, 4.007 OS:Any
Assigned to: CPU Architecture:Any
Tags: bind_param

[10 Jun 2008 18:17] Andrew Feren
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;
[10 Jun 2008 19:52] Valeriy Kravchuk
Thank you for a problem report. What exact version of DBD::mysql do you use and what exact version of MySQL server you are working with?
[10 Jun 2008 20:22] Andrew Feren
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)
[10 Jun 2008 21:00] Andrew Feren
Upgraded DBD on Ubuntu to 4.007 and I still see the same issue.
[11 Jul 2008 20:17] Sveta Smirnova
Thank you for the report.

Verified as described.
[20 Feb 2013 22:14] Sveta Smirnova
Thank you for the report.

We don't work on DBD::mysql bugs anymore. All its bugs should go to CPAN: https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql

I re-submitted your report to https://rt.cpan.org/Public/Bug/Display.html?id=83489
Please subscribe to the new report on CPAN and work with DBD::mysql developers in case if they need additional details.