Thread

1. Pbm with aggregates on empty output

   Jean-Francois Rabasse <rabasse@lra.ens.fr> — 2001-03-06T17:08:59Z

   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/
   
   
   

2. Re: Pbm with aggregates on empty output

   Peter Eisentraut <peter_e@gmx.net> — 2001-03-07T20:51:00Z

   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/
   
   
   

3. Re: Pbm with aggregates on empty output

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-08T00:42:40Z

   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