Thread: HAVING bug

============================================================================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'

> 
> 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,
Pennsylvania19026
 

> 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