Re: BUG #1528: Rows returned that should be excluded by WHERE clause - Mailing list pgsql-bugs

"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,
buthere is an example. 
> [gill@c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client where 2 =1"

> 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 = 1?

            regards, tom lane