MySQL Bugs: #16149: Invalid result is returned when the column contains negative values

Bug #16149	Invalid result is returned when the column contains negative values
Submitted:	3 Jan 2006 10:09	Modified:	18 Jun 2006 21:21
Reporter:	Bhuvaneswaran Arumugam	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Linux (Redhat Enterprise Linux 3)
Assigned to:		CPU Architecture:	Any

Description:
Consider a column of type int. It contains negative values and we want to receive the values using select query. In mysql 5.0.15, when i run the query from sql prompt, i receive expected result. But, I receive invalid result when i run the query from perl using DBD::mysql module. The same script returns correct result when it is run against mysql 4.1.7.

Consider the following perl script:

<script>
#!/usr/bin/perl

use DBI;
use Data::Dumper;

my $db_user = 'my_db_user';
my $db_pass = 'my_db_pass';
my $db_name = 'my_db_name';
my $db_host = 'localhost';
my $db_port = 3306; 
my $dbi = DBI->connect("dbi:mysql:$db_name", $db_user, $db_pass, {AutoCommit => 0});

my ($sql, $sth, $HashRef); 
$sql = "select metric_name_id, metric_name from metric_name where 1 = 1 and project_based = 1 order by metric_name limit 1";
$sth = $dbi->prepare ($sql);
$sth->execute();

print Dumper $sth->fetchall_arrayref();
$dbi->disconnect();
</script>

When i run the above script using RHEL3, DBI 1.50, DBD::mysql 3.0002_4 and mysql 5.0.15, i receive the following output:

$VAR1 = [
          [
            4294967292,
            'Issues By Status'
          ]
        ];

When i run the same script using RH9.0, DBI 1.43, DBD::mysql 2.9003 and mysql 4.1.7, i receive the following output. It is the expected output as well:

$VAR1 = [
          [
            '-4',
            'Issues By Status'
          ]
        ];

Regards,
Bhuvaneswaran

How to repeat:
It can be repeated by running the script.

Thank you for a problem report. Please, send the results of SHOW CREATE TABLE metric_name statement.

Sure. The output of SHOW CREATE TABLE metric_name is as follows:

CREATE TABLE `metric_name` (
  `metric_name_id` int(11) NOT NULL auto_increment,
  `metric_name` varchar(255) NOT NULL default '',
  `metric_category` varchar(255) NOT NULL default '',
  `metric_name_type` enum('NUMBER','STRING','PERCENT') NOT NULL default 'NUMBER',
  `project_based` tinyint(1) NOT NULL default '0',
  `metric_attr_type_id` int(11) default NULL,
  PRIMARY KEY  (`metric_name_id`),
  UNIQUE KEY `metric_name` (`metric_name`),
  KEY `metric_attr_type_id` (`metric_attr_type_id`),
  KEY `i_mn_metric_name` (`metric_name`),
  CONSTRAINT `metric_name_ibfk_1` FOREIGN KEY (`metric_attr_type_id`) REFERENCES `metric_attr_type` (`metric_attr_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Should you require more info, please let me know.

I need SHOW CREATE TABLE metric_attr_type results (because of that foreign key). Some test data may be also useful. Have you tried to repeat with the newer version, 5.0.18?

Yep, i have tested in 5.0.18 and the problem is repeated. Here is the output of SHOW CREATE TABLE metric_attr_type. 

CREATE TABLE `metric_attr_type` (
  `metric_attr_type_id` int(11) NOT NULL auto_increment,
  `metric_attr_type_name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`metric_attr_type_id`),
  KEY `i_man_metric_attr_type` (`metric_attr_type_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

FWIW, the problem is repeatable even if the table donot contain reference to any other table.

was not able to reproduce it... Could you check attached testcase?

./bug16149.pl
$VAR1 = [
          [
            '-4',
            'testing..'
          ]
        ];
$VAR1 = [
          [
            '5.0.18-max'
          ]
        ];

testcase I used

Attachment: bug16149.pl (application/x-perl, text), 1.26 KiB.

I used DBD::mysql  3.0002 from cpan

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Please, try to repeat with a newer version of server, 5.0.21, using your test case or http://bugs.mysql.com/file.php?id=2755 Alexey uploaded, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".