Обсуждение: Pbm with aggregates on empty output

Hello,
I found a problem. I don't know if it's a bug or a misuse from me.

Here is, in a minimal test version
(I use PostgreSQL 6.5.3 on i686-pc-linux-gnu):

1. A table, even very simple
   => create table thetable ( thevalue int4 );

2. I populate the table with several numeric values...
   => insert into thetable values(1);
   => insert into thetable values(2);
   => insert into thetable values(3);
   => insert into thetable values(4);

3. Now, I want to count my records matching a specified clause.
   My initial idea was to use an aggregate function such as sum

   => select sum(1) from thetable where thevalue > 1;
   sum
   ---
     3
   (1 row)

   It works fine and I just get what I wanted, the records count.

4. If the clause can't be verified, i.e. would generate no output

   => select * from thetable where thevalue > 10000;
   thevalue
   --------
   (0 rows)

   I expected to get a 0 value, for sum().
   In fact, the backend hangs.

   => select sum(1) from thetable where thevalue > 10000;
   pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
   We have lost the connection to the backend, so further processing is
   impossible.  Terminating.


Please, have you an idea of what's wrong with that ?
Is it a problem in the aggregate processing implementation, when the
select output is empty (or null) ?
Is it legal, for me, to do such thing ?

Thanks in advance...

Jean-Francois Rabasse

--
 Jean-François Rabasse

 Radioastronomie                    e-mail: jean-francois.rabasse@lra.ens.fr
 Département de Physique de l'ENS      tél: +33 (0)1 44 32 25 15
    24,  rue Lhomond                   fax: +33 (0)1 44 32 39 92
 F-75231   PARIS Cedex 05              www: http://www.lra.ens.fr/~rabasse/

Jean-Francois Rabasse writes:

> (I use PostgreSQL 6.5.3 on i686-pc-linux-gnu):

Time to upgrade.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

Jean-Francois Rabasse <rabasse@lra.ens.fr> writes:
>    => select sum(1) from thetable where thevalue > 10000;
>    pqReadData() -- backend closed the channel unexpectedly.

Strange ... but rather than spend any time debugging 6.5.3,
I'd suggest updating to 7.0.3.  We've fixed an awful lot of bugs
since then.

BTW, what you will actually get is a NULL result, not a zero result.
Don't blame me, blame SQL92, because that's what it specifies for SUM
of no rows :-(

            regards, tom lane