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])
