Hi,
I am running Postgresql ver 6.5.1 on an SGI (Irix).
I am trying to write a query to return some top scoring items, using a
GROUP BY clause, however, I am having trouble with "empty" results. For
example:
htg=> SELECT LocusID, Score FROM Match
htg-> WHERE SeqID=4358;
locusid|score
-------+-----
(0 rows)
In the case of SeqID=4358, there are no matching LocusIDs, so 0 rows are
returned. This is fine. There may be cases, however, when there are many
matching LocusIDs, so I use a GROUP BY clause. This usually works, but in cases
like the above one, where there are no matching LocusIDs, it returns the wrong
result. Ie:
htg=> SELECT LocusID, max(Score) FROM Match
htg-> WHERE SeqID=4358
htg-> GROUP BY LocusID;
locusid|max
-------+---
|
(1 row)
There is no row, yet Postgresql reports there is "one" which it returns as
empty. This also makes the Perl DBI::DBD interface unhappy. It seems to return
an undefined value which can't even be tested with "if(! defined $value)"!
Unless I really misunderstand "GROUP BY", I think this is a bug.
Mark
--
Mark Dalphin email: [EMAIL PROTECTED]
Mail Stop: 29-2-A phone: +1-805-447-4951 (work)
One Amgen Center Drive +1-805-375-0680 (home)
Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)
