Обсуждение: Problem with a GROUP BY clause

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: mdalphin@amgen.com
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)

At 03:52 +0300 on 01/08/1999, Mark Dalphin wrote:


> 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.

We had this discussion in the past and I think you are right in saying that
under "GROUP BY", this behaviour is a bug. However, it is not really that
hard to handle. It merely returns a row with NULL values all over. Test for
NULL in the same way you test for NULL in any field that may have NULL
values. You can be sure that if a valid grouping was returned, neither
field will be null.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma