Sorry Tom, I missed a sentence in you previous email. My understanding of the having clause is that the row should be filtered. Here is the same example with the having clause in DB2.
[EMAIL PROTECTED] gill]$ db2 "select 2 as id, max(apn3) from phoenix.client having 2 =1" ID 2 ----------- ------ 0 record(s) selected. [EMAIL PROTECTED] gill]$ db2 "select 2 as id, max(apn3) from phoenix.client where 2 =1 having 2 = 1" ID 2 ----------- ------ 0 record(s) selected. -jgill -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 "Gill, Jerry T." <[EMAIL PROTECTED]> 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. > [EMAIL PROTECTED] 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 ---------------------------(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 ---------------------------(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
