Bug #24256 Another failure in bind_param() with SQL_DOUBLE datatype
Submitted: 13 Nov 2006 12:22 Modified: 2 Jan 2007 22:46
Reporter: Steve Hay Email Updates:
Status: Closed Impact on me:
None 
Category:Connectors: DBD::mysql ( Perl ) Severity:S2 (Serious)
Version:3.0008 OS:Windows (Windows XP SP2)
Assigned to: CPU Architecture:Any

[13 Nov 2006 12:22] Steve Hay
Description:
I've found another bug in the three-argument form of bind_param() with the third argument being SQL_DOUBLE.  This may be related to bug #23936, but the effect here is slightly different.

The problem is that the execute() method fails whenever a negative value has been bound.  (The other bug related to double values getting truncated to integer values when inserted/updated, but here the SQL execution fails completely and no data is inserted/updated at all.)

As with the other bug, the problem only occurs when the datatype is specified in the bind_param() call, and only when using emulated prepare statements.

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, -1, SQL_DOUBLE);
$sth->execute();
$sth->finish();
$dbh->disconnect();

This program produces the error message:

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 at test.pl line 27.
[14 Nov 2006 7:51] MySQL Verification Team
Thank you for the bug report.

C:\codesmaple>perl bug24256.pl
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for
 the right syntax to use near ')' at line 1 at bug24256.pl line 27.
[2 Jan 2007 22:46] Jim Winstead
Like Bug #23936, this is fixed in the DBD::mysql source tree (and in the next release, probably 4.001). I've added a regression test to the test suite.

Thanks for the bug report.