MySQL Bugs: #8240: select returns no rows if column = 0 (zero)

Bug #8240	select returns no rows if column = 0 (zero)
Submitted:	1 Feb 2005 16:00	Modified:	1 Feb 2005 17:15
Reporter:	Richard DeSimine	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.9	OS:	Linux (Mandrake Linux 10.1)
Assigned to:		CPU Architecture:	Any

Description:
When executing queries using Perl DBD 2.9004, the query quits after encountering any row with any data value that equals zero.

Anyone encounter this? Is there a fix? Workaround?

Thanks, Rich

How to repeat:
Given the following table named 'items'

-----------
| quantity |   (type is INTEGER)
-----------
|           2 |
-----------
|           0 |
-----------
|           6 |
-----------

"select quantity from items" correctly returns 2,0,6 when using the mysql client.

When using Perl DBD 2.9004:

"select quantity from items" returns 2 (huh?)
"select quantity+0.1 from items" returns 2.1, 0.1, 6.1

Hi.

I tried it against 4.1.9 and perl dbd:mysql 2.9004 on slackware 10 but saw all records in output. I am attaching ('Files" section) script that I used for testing. If you have ideas how to reproduce this bug, please let us know.  Could you please to check this script in your environment?

testcase

Attachment: bug8240.pl (application/x-perl, text), 380 bytes.

Thanks for the quick reply. The problem still exists. Here's the actual code, please note if the line:
 # $sth->dump_results();
is uncommented, all the data is printed, including zeros. If the line is commented all the data is printed through the first row that has a zero (in putoi). I recently converted from 4.0 to 4.1.9. Could this problem be due to having created the tables using the old database?

sub get_oidata {
  my $self        = shift;
  my $symbol    = shift;
  my $oidate     = shift;
  my $expiration = shift;

  my $dbh = $self->{dbh};

  my $strike;
  my $putoi;
  my $calloi;

  $symbol = uc $symbol;
  my $sql = "select strike, calloi, putoi from oi
   where symbol = '$symbol'
     and oidate = '$oidate'
     and expiration = '$expiration'
   order by 1
";

  my $sth = $dbh->prepare($sql) || die "problem preparing: ",$dbh->errstr,"\n";
  $sth->execute || die "Error executing $DBI::errstr\n";

 # $sth->dump_results();

  $sth->bind_col(1, \$strike);
  $sth->bind_col(2, \$calloi);
  $sth->bind_col(3, \$putoi);

  while ($sth->fetchrow_array()) {
    printf("%f %d %d\n", $strike, int($calloi), int($putoi));
  }
  $sth->finish;
}

will work if, you write:
 while (my @xyz = $sth->fetchrow_array()) {
in your loop

This might be a bug in how DBI returns an array, it seems that fetchrow_array returns the frist column in scalar context (rather tha the array member count).
So your loop exits ("correctly") when seeing 0.

check theDBI documentation, cpan bug tracker is at:
http://rt.cpan.org/NoAuth/Bugs.html?Dist=DBI