| Subject: | Fetchrow_hashref doesn't work when you are selecting multiple agregates on the same value. |
Attached is an updated 06build.t file for SQL::Statement, it demonstrates a problem with using fetchrow_hashref to get values from a query that uses aggregate functions.
It turns out that if you select multiple aggregates from one value, for example:
max(foo) as m, avg(foo) as a, sum(foo) as s
... and then attempt to get the results using fetchrow hashref, only the last value will exist in the hash. Additionally, the key for the value in the hash will be whatever the first value should have been. For the example above, we would get
{ 'm' => (the sum of 'foo') }
#!/usr/bin/perl -w
$|=1;
use strict;
use Test::More;
use lib qw( ../lib );
use vars qw($DEBUG);
eval { require DBI; require DBD::File; };
if ($@) {
plan skip_all => "No DBI or DBD::File available";
}
else {
plan tests => 7;
}
use SQL::Statement; printf "SQL::Statement v.%s\n", $SQL::Statement::VERSION;
my $dbh=DBI->connect('dbi:File(RaiseError=1):');
$dbh->do($_) for <DATA>;
my $sth=$dbh->prepare("
SELECT SUM(sales), MAX(sales) FROM biz
");
ok('2700~1000^' eq query2str($sth),'AGGREGATE FUNCTIONS WITHOUT GROUP BY');
$sth=$dbh->prepare("
SELECT region,SUM(sales), MAX(sales) FROM biz GROUP BY region
");
ok('West~2000~1000^East~700~700^' eq query2str($sth),'GROUP BY one column');
$sth=$dbh->prepare("
SELECT region,store,SUM(sales), MAX(sales) FROM biz GROUP BY region,store
");
ok('West~Los Angeles~1500~1000^West~San Diego~500~500^East~Boston~700~700^'
eq query2str($sth),'GROUP BY several columns');
$sth=$dbh->prepare("
SELECT region,store,SUM(sales) as s, MAX(sales) as m FROM biz GROUP BY region,store
");
$sth->execute;
my $data = $sth->fetchrow_hashref();
use Data::Dumper;
diag Dumper($data);
is( $data->{region}, 'West' );
is( $data->{store}, 'Los Angeles' );
is( $data->{s}, 1500 );
is( $data->{m}, 1000 );
sub query2str {
my($sth)=@_;
$sth->execute;
my $str='';
while (my $r=$sth->fetch) {
$str .= sprintf "%s^",join('~',map { defined $_ ? $_ : 'undef' } @$r);
}
return $str unless $DEBUG;
printf "%s\n",join',',@{$sth->{NAME}};
print "<$str>\n";
return $str;
}
__END__
CREATE TEMP TABLE biz (region TEXT, store TEXT, sales INTEGER)
INSERT INTO biz VALUES ('West','Los Angeles',1000 )
INSERT INTO biz VALUES ('West','San Diego' ,500 )
INSERT INTO biz VALUES ('West','Los Angeles',500 )
INSERT INTO biz VALUES ('East','Boston' ,700 )