Tom, > the issue is clearly that the known-false HAVING clause is pushed down > inside the aggregation, as though it were WHERE. ÂThe existing code > pushes down HAVING to WHERE if the clause contains no aggregates, but > evidently this is too simplistic. ÂWhat are the correct conditions for > pushing down HAVING clauses to WHERE?
When the HAVING clause refers to a unaltered GROUP BY column; that is, one whose contents are not aggregated, calculated, or aliased. I can't think of any other condition which would be permissable. I would guess that the reason why that test case bombs is that the planner detects that "2" is not aggregates, calculated, or aliased and assumes that it's a GROUP BY column. The real problem with this query is that we have a constant column which is always in existance, thus producing a single row when run without the HAVING clause. Personally, I've always felt that the SQL committee made a mistake in having aggregates of no rows produce a single null output row; it leads to wierdness like this here. Hopefully someone can back that up with an ANSI-SQL reference ... -- Josh Berkus Aglio Database Solutions San Francisco ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])