Thread

1. HAVING bug

   jose' soares <sferac@bo.nettuno.it> — 1998-10-19T10:57:54Z

   ============================================================================
    POSTGRESQL BUG REPORT TEMPLATE
   ============================================================================
   
   Your name: Jose' Soares
   Your email address:sferac@bo.nettuno.it
   
   
   System Configuration
   ---------------------
    Architecture (example: Intel Pentium): Intel Pentium
   
    Operating System (example: Linux 2.0.26 ELF)  : Linux 2.0.34 Elf
   
    PostgreSQL version (example: PostgreSQL-6.1)  : PostgreSQL-6.4-BETA2
   
     Compiler used (example:  gcc 2.7.2)           : gcc 2.7.2.1
   
   
   Please enter a FULL description of your problem:
   
   PostgreSQL tells me, the following query is not valid, but I can't see
   nothing wrong.
   
   prova=> select * from sp;
   sno  |pno  |qty
   -----+-----+---
   S1   |P1   |300
   S1   |P2   |200
   S1   |P3   |400
   S2   |P2   |400
   S3   |P2   |200
   S4   |P4   |300
   S4   |P5   |400
   (7 rows)
   
   prova=>  select sno,qty  from sp  group by sno,qty having qty=300;
   ERROR:  This is not a valid having query!
   
   I tried the same query on Informix-SE and I have the following:
   
   sno       qty
   
   S1    300.000
   S4    300.000
   
   I tried it also on Solid and I have the following:
   
   SOLID SQL Editor (teletype) v.02.20.0007
   select sno,qty  from sp  group by sno,qty having qty = 300;
   SNO                          QTY
   ---                          ---
   S1                           300.
   S4                           300.
   2 rows fetched.
   
               Jose'
   
   
   
   

2. Re: [HACKERS] HAVING bug

   Bruce Momjian <maillist@candle.pha.pa.us> — 1998-10-19T15:29:56Z

   > 
   > PostgreSQL tells me, the following query is not valid, but I can't see
   > nothing wrong.
   > 
   > prova=> select * from sp;
   > sno  |pno  |qty
   > -----+-----+---
   > S1   |P1   |300
   > S1   |P2   |200
   > S1   |P3   |400
   > S2   |P2   |400
   > S3   |P2   |200
   > S4   |P4   |300
   > S4   |P5   |400
   > (7 rows)
   > 
   > prova=>  select sno,qty  from sp  group by sno,qty having qty=300;
   > ERROR:  This is not a valid having query!
   
   HAVING is only to be used with aggregates.   In your case, qty is not an
   aggregate.
   
   At least, that is what I think is supposed to happen.
   
   > 
   > I tried the same query on Informix-SE and I have the following:
   > 
   > sno       qty
   > 
   > S1    300.000
   > S4    300.000
   > 
   > I tried it also on Solid and I have the following:
   > 
   > SOLID SQL Editor (teletype) v.02.20.0007
   > select sno,qty  from sp  group by sno,qty having qty = 300;
   > SNO                          QTY
   > ---                          ---
   > S1                           300.
   > S4                           300.
   > 2 rows fetched.
   > 
   >             Jose'
   > 
   > 
   > 
   > 
   
   
   -- 
     Bruce Momjian                        |  http://www.op.net/~candle
     maillist@candle.pha.pa.us            |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
   
   

3. RE: [HACKERS] HAVING bug

   Taral <taral@mail.utexas.edu> — 1998-10-19T17:10:44Z

   > prova=>  select sno,qty  from sp  group by sno,qty having qty=300;
   > ERROR:  This is not a valid having query!
   
   Use WHERE for conditions like this.
   
   SELECT sno, qty FROM sp WHERE qty=300 GROUP BY sno;
   
   Taral