DBI and selecting aggregate columns with no results

thor has asked for the wisdom of the Perl Monks concerning the following question:

Greetings monks, I came across something with some DBI code today that threw me for a bit of a loop. First the code:

use warnings;
use strict;

use DBI;

my $dbh = DBI->connect(
   "dbi:ODBC:driver={SQL Server};server=localhost;database=master",
   "",
   "",
   {ChopBlanks => 1, RaiseError => 1, PrintError => 0});

{
   my $sth = $dbh->prepare(qq(
      select * from sysdatabases where 9=0
   ));
   $sth->execute();
   my $counter = 0;
   while( $sth->fetch()) {
   $counter++;
   }
   print "$counter\n";
}

{
   my $sth = $dbh->prepare(qq(
      select min(dbid) from sysdatabases where 9=0
   ));
   $sth->execute();
   my $counter = 0;
   while( $sth->fetch()) {
      $counter++;
   }
   print "$counter\n";
}
__END__
0
1
[download]

What surprises me is that when I call fetch on the second query, it doesn't return undef as a result of there being no data to retrieve. Any pointers to the friendly manual for this behavior?

thor

Feel the white light, the light within Be your own disciple, fan the sparks of will For all of us waiting, your kingdom will come

Comment on DBI and selecting aggregate columns with no results Download Code

Replies are listed 'Best First'.
Re: DBI and selecting aggregate columns with no results by RazorbladeBidet (Friar) on Mar 09, 2005 at 17:28 UTC
This is re: postgreSQL, but I think it applies to most databases... "It should be noted that except for count, these functions return a null value when no rows are selected. In particular, sum of no rows returns null, not zero as one might expect. The function coalesce may be used to substitute zero for null when necessary". So while your first select returns no rows, your aggregation returns the minimum of a null set, which is null. That's my theory anyways :) -------------- It's sad that a family can be torn apart by such a such a simple thing as a pack of wild dogs	[reply]
[OT] Re: DBI and selecting aggregate columns with no results by trammell (Priest) on Mar 09, 2005 at 18:07 UTC
You're not really having a Perl problem, but I'll respond anyhow. Here's a MySQL session that highlights what you are seeing: mysql> create table foo (i int); Query OK, 0 rows affected (0.14 sec) mysql> insert into foo values (1), (2), (3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from foo where i = 42; Empty set (0.04 sec) mysql> select min(i) from foo where i = 42; +--------+ \| min(i) \| +--------+ \| NULL \| +--------+ 1 row in set (0.04 sec) mysql> select min(i) from foo where 1 = 0; +--------+ \| min(i) \| +--------+ \| NULL \| +--------+ 1 row in set (0.00 sec)	[reply]
Re: DBI and selecting aggregate columns with no results by jZed (Prior) on Mar 09, 2005 at 20:03 UTC
SELECT without aggregate functions returns matched rows and since no rows match, there is no fetch loop and therefore the first run prints 0. SELECT with an aggregate function returns the results of that aggregate function for matched rows. Regardless of whether your DBMS returns 0 or NULL or blue, no yellow auuuugh ... as the MIN(id), it still returns something. Therefore when you do a fetch loop, there is something to fetch (even if that something is undefined or NULL), therefore the fetch loop executes and the counter is incremented and therefore the second run prints 1. update To be a bit clearer: it's the difference between `[] and [[undef]]`. With `[]` there is nothing to fetch. With `[[undef]]` there is something to fetch even though that something is nothing. If you use $dbh->selecall_arrayref() on your two queries and Data::Dumper the results you'll see that difference betweeen the returned arrayrefs. update 2 For example: `use DBI; my $dbh=DBI->connect('dbi:Pg:dbname=test1'); $dbh->do('CREATE TABLE x (id INT)'); my $results1 = $dbh->selectall_arrayref('SELECT * FROM x WHERE 1=0'); my $results2 = $dbh->selectall_arrayref('SELECT MIN(id) FROM x WHERE 1 +=0'); use Data::Dumper; print Dumper $results1; print Dumper $results2;` [download] Outputs: `$VAR1 = []; $VAR1 = [ [ undef ] ];` [download]	[reply] [d/l] [select]