Thread: error on HAVING clause
To report any other bug, fill out the form below and e-mail it to
pgsql-bugs@postgresql.org.
============================================================================
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.31 Elf
PostgreSQL version (example: PostgreSQL-6.1) : PostgreSQL-snapshot april 6, 1998
Compiler used (example: gcc 2.7.2) : gcc 2.7.2.1
Please enter a FULL description of your problem:
------------------------------------------------
COUNT(*) doesn't work with HAVING
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
SELECT PNO
FROM SP
GROUP BY PNO
HAVING COUNT(PNO) > 1;
pno
-----
P1
P2
P4
P5
(4 rows)
SELECT PNO
FROM SP
GROUP BY PNO
HAVING COUNT(*) > 1;
PQexec() -- Request was sent to backend, but backend closed the channel before responding.
This probably means the backend terminated abnormally before or while processing the request.
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
??
> Please enter a FULL description of your problem: > ------------------------------------------------ > > COUNT(*) doesn't work with HAVING > > > Please describe a way to repeat the problem. Please try to provide a > concise reproducible example, if at all possible: > > ---------------------------------------------------------------------- > > SELECT PNO > FROM SP > GROUP BY PNO > HAVING COUNT(PNO) > 1; > > pno > ----- > P1 > P2 > P4 > P5 > (4 rows) > > > SELECT PNO > FROM SP > GROUP BY PNO > HAVING COUNT(*) > 1; > > PQexec() -- Request was sent to backend, but backend closed the channel before responding. > This probably means the backend terminated abnormally before or while processing the request. Appreciate your report. Hopefully we can fix it by the 6.3.2 final release. If not, we will have to remove the feature until 6.4. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
On Thu, 9 Apr 1998, Bruce Momjian wrote:
Don't worry about time Bruce. I'm not in a hurry.
HAVING is an important feature. Finally SELECT statement is complete.
I would like to show you another thing about HAVING.
prova=> select sno,qty from sp group by sno,qty having qty = 300;
sno |qty
-----+---
S1 |100
S1 |200
S1 |300
S1 |400
S2 |300
S2 |400
S3 |200
S4 |200
S4 |300
S4 |400
(10 rows)
prova=> select oid,sno,qty from sp group by sno,qty having qty = 300;
oid|sno |qty
------+-----+---
147004|S1 |100
147001|S1 |200
147000|S1 |300
147002|S1 |400
147006|S2 |300
147007|S2 |400
147008|S3 |200
147009|S4 |200
147010|S4 |300
147011|S4 |400
(10 rows)
Solid give me another result. Who are rigth ?
SOLID SQL Editor (teletype) v.02.20.0007
select sno,qty from sp group by sno,qty having qty = 300;
SNO QTY
--- ---
S1 300.
S2 300.
S4 300.
3 rows fetched.
Maybe this one is illegal, but it give me a strange output:
prova=> select oid,sno,qty from sp having qty = 300;
| | <---------where is the title ????
------+-----+---
147000|S1 |300
147001|S1 |200
147002|S1 |400
147003|S1 |200
147004|S1 |100
147005|S1 |100
147006|S2 |300
147007|S2 |400
147008|S3 |200
147009|S4 |200
147010|S4 |300
147011|S4 |400
(12 rows)
Jose'
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
On Thu, 9 Apr 1998, Bruce Momjian wrote:
Don't worry about time Bruce. I'm not in a hurry.
HAVING is an important feature. Finally SELECT statement is complete.
I would like to show you another thing about HAVING.
prova=> select sno,qty from sp group by sno,qty having qty = 300;
sno |qty
-----+---
S1 |100
S1 |200
S1 |300
S1 |400
S2 |300
S2 |400
S3 |200
S4 |200
S4 |300
S4 |400
(10 rows)
prova=> select oid,sno,qty from sp group by sno,qty having qty = 300;
oid|sno |qty
------+-----+---
147004|S1 |100
147001|S1 |200
147000|S1 |300
147002|S1 |400
147006|S2 |300
147007|S2 |400
147008|S3 |200
147009|S4 |200
147010|S4 |300
147011|S4 |400
(10 rows)
Solid give me another result. Who are rigth ?
SOLID SQL Editor (teletype) v.02.20.0007
select sno,qty from sp group by sno,qty having qty = 300;
SNO QTY
--- ---
S1 300.
S2 300.
S4 300.
3 rows fetched.
Maybe this one is illegal, but it give me a strange output:
prova=> select oid,sno,qty from sp having qty = 300;
| | <---------where is the title ????
------+-----+---
147000|S1 |300
147001|S1 |200
147002|S1 |400
147003|S1 |200
147004|S1 |100
147005|S1 |100
147006|S2 |300
147007|S2 |400
147008|S3 |200
147009|S4 |200
147010|S4 |300
147011|S4 |400
(12 rows)
Jose'
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
> > On Thu, 9 Apr 1998, Bruce Momjian wrote: > > Don't worry about time Bruce. I'm not in a hurry. > HAVING is an important feature. Finally SELECT statement is complete. > I would like to show you another thing about HAVING. Honestly, I don't know who is right. You are not using an aggregate in the HAVING, so I have no idea how it is supposed to be handled. > > prova=> select sno,qty from sp group by sno,qty having qty = 300; > sno |qty > -----+--- > S1 |100 > S1 |200 > S1 |300 > S1 |400 > S2 |300 > S2 |400 > S3 |200 > S4 |200 > S4 |300 > S4 |400 > (10 rows) > > prova=> select oid,sno,qty from sp group by sno,qty having qty = 300; > oid|sno |qty > ------+-----+--- > 147004|S1 |100 > 147001|S1 |200 > 147000|S1 |300 > 147002|S1 |400 > 147006|S2 |300 > 147007|S2 |400 > 147008|S3 |200 > 147009|S4 |200 > 147010|S4 |300 > 147011|S4 |400 > (10 rows) > > Solid give me another result. Who are rigth ? > > SOLID SQL Editor (teletype) v.02.20.0007 > select sno,qty from sp group by sno,qty having qty = 300; > SNO QTY > --- --- > S1 300. > S2 300. > S4 300. > 3 rows fetched. > > Maybe this one is illegal, but it give me a strange output: > > prova=> select oid,sno,qty from sp having qty = 300; > | | <---------where is the title ???? > ------+-----+--- > 147000|S1 |300 > 147001|S1 |200 > 147002|S1 |400 > 147003|S1 |200 > 147004|S1 |100 > 147005|S1 |100 > 147006|S2 |300 > 147007|S2 |400 > 147008|S3 |200 > 147009|S4 |200 > 147010|S4 |300 > 147011|S4 |400 > (12 rows) > Jose' > > -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Question is, if we can't get it fixed completely, does it work well enough for us to keep it in 6.3.2? > > On Thu, 9 Apr 1998, Bruce Momjian wrote: > > Don't worry about time Bruce. I'm not in a hurry. > HAVING is an important feature. Finally SELECT statement is complete. > I would like to show you another thing about HAVING. > > prova=> select sno,qty from sp group by sno,qty having qty = 300; > sno |qty > -----+--- > S1 |100 > S1 |200 > S1 |300 > S1 |400 > S2 |300 > S2 |400 > S3 |200 > S4 |200 > S4 |300 > S4 |400 > (10 rows) > > prova=> select oid,sno,qty from sp group by sno,qty having qty = 300; > oid|sno |qty > ------+-----+--- > 147004|S1 |100 > 147001|S1 |200 > 147000|S1 |300 > 147002|S1 |400 > 147006|S2 |300 > 147007|S2 |400 > 147008|S3 |200 > 147009|S4 |200 > 147010|S4 |300 > 147011|S4 |400 > (10 rows) > > Solid give me another result. Who are rigth ? > > SOLID SQL Editor (teletype) v.02.20.0007 > select sno,qty from sp group by sno,qty having qty = 300; > SNO QTY > --- --- > S1 300. > S2 300. > S4 300. > 3 rows fetched. > > Maybe this one is illegal, but it give me a strange output: > > prova=> select oid,sno,qty from sp having qty = 300; > | | <---------where is the title ???? > ------+-----+--- > 147000|S1 |300 > 147001|S1 |200 > 147002|S1 |400 > 147003|S1 |200 > 147004|S1 |100 > 147005|S1 |100 > 147006|S2 |300 > 147007|S2 |400 > 147008|S3 |200 > 147009|S4 |200 > 147010|S4 |300 > 147011|S4 |400 > (12 rows) > Jose' > > -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)