MySQL Bugs: #23936: bind_param() doesn't work with SQL

Bug #23936	bind_param() doesn't work with SQL_DOUBLE datatype (at least)
Submitted:	3 Nov 2006 12:28	Modified:	2 Jan 2007 19:35
Reporter:	Steve Hay	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S2 (Serious)
Version:	4.00	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
I've found that when using the three-argument form of bind_param() with the third argument being SQL_DOUBLE, the value that gets bound is truncated to an integer value.

If I fall back to the two-argument form and let it guess for itself what datatype is intended then the value does not get truncated.  I'd rather not leave it to guess for itself, though--I prefer to be explicit and tell it what the datatype is.  It's ironic that the more explicit form is the one that fails.

Also, this only happens when using emulated prepare.  If I use server-side prepare instead then the problem goes away.  However, I'm not happy about using server-side prepare at least until bug #20559 gets fixed.

The failure occurs with DBD-mysql-3.0008.  I've also tested as far back in the 3.* series as I can go (3.0001: earlier versions don't build on Win32), and the same bug is still present.

The last version that worked correctly was the last 2.9* version (2.9008).

I see this as a serious bug.  It has already broken things for us on customers' sites.  I'm rather surprised and disappointed that there are evidently no tests in the test suite that picked this up.

I've logged it as Serious rather than Critical because there are workarounds (drop the SQL_DOUBLE or use server-side prepare), but I'm not happy with either workaround as indicated above.  The best workaround for us is to return to the old version (2.9008).

How to repeat:
Run the following program:

use strict;
use warnings;
use DBI qw(:sql_types);
my $tmp_dbh = DBI->connect(
  'dbi:mysql:database=mysql', 'root', undef,
  { AutoCommit => 1, PrintError => 0, RaiseError => 1 }
);
$tmp_dbh->do('CREATE DATABASE IF NOT EXISTS test');
$tmp_dbh->disconnect();
my $dsn = 'dbi:mysql:database=test;mysql_emulated_prepare=1';
my $dbh = DBI->connect(
  $dsn, 'root', undef,
  { AutoCommit => 1, PrintError => 0, RaiseError => 1 }
);
$dbh->do('DROP TABLE IF EXISTS foo');
$dbh->do(qq{CREATE TABLE foo (
  id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  num DOUBLE
) ENGINE=InnoDB});
my $tracefile = 'dbitrace.txt';
unlink $tracefile if -f $tracefile;
$dbh->trace(3, $tracefile);
my $sql = 'INSERT INTO foo (id, num) VALUES (NULL, ?)';
my $sth = $dbh->prepare($sql);
$sth->bind_param(1, 2.1, SQL_DOUBLE);
$sth->execute();
$sth->finish();
$sql = 'SELECT * FROM foo';
$sth = $dbh->prepare($sql);
$sth->execute();
my $rows = $sth->fetchall_arrayref({});
$sth->finish();
print "$rows->[0]{num}\n";
$dbh->disconnect();

The output from this program is:
2

It should be:
2.1

If I change the bind_param() call to just:
  $sth->bind_param(1, 2.1);
then the output is 2.1 as expected.

Alternatively, if I change the $dsn to:
  my $dsn = 'dbi:mysql:database=test;mysql_server_prepare=1';
then the output is also 2.1 as expected.

Thank you for the bug report. Verified as described:

C:\codesmaple>perl bug23936.pl
2

C:\codesmaple>

I am unable to reproduce this bug with the latest development source. I have added a regression test to the test suite, which will be in the next release (probably 4.01).

I've just tested this again with the latest release (4.00) and it still doesn't work for me, so I've re-opened the bug and updated the version field to 4.00.

The program in the original bug report still erroneously outputs 2 instead of 2.1.

Switching to server-side prepare and/or dropping the third argument to bind_param() "fixes" it, but neither constitutes a real fix.

Okay, I've verified that this does fail in 4.00, but is fixed in the current source tree (and thus the next release). Thanks for the bug report.