Sorry Tom, I missed a sentence in you previous email. My understanding of t=
he having clause is that the row should be filtered. Here is the same examp=
le with the having clause in DB2.
[gill@c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client having=
2 =3D1"
ID 2
----------- ------
0 record(s) selected.
[gill@c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client where =
2 =3D1 having 2 =3D 1"
ID 2
----------- ------
0 record(s) selected.
-jgill
-----Original Message-----
From: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Tuesday, March 08, 2005 11:15 AM
To: Gill, Jerry T.
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #1528: Rows returned that should be excluded by
WHERE clause=20
"Gill, Jerry T." <JTGill@west.com> writes:
> Just an interesting side note here, this behavior is identical to DB2. I =
am not sure if that makes it correct or not, but here is an example.
> [gill@c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client wher=
e 2 =3D1"
> ID 2
> ----------- ------
> 2 -
> 1 record(s) selected.
In the WHERE case I think there's no question that the above is correct:
WHERE is defined to filter rows before application of aggregates, so
zero rows arrive at the MAX aggregate, and that means it produces a
NULL.
But HAVING is supposed to filter after aggregation, so I think probably
there should be no row out in that case.
What does DB2 do when you say HAVING 2 =3D 1?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
