Tom,
> the issue is clearly that the known-false HAVING clause is pushed down
> inside the aggregation, as though it were WHERE. =C2=A0The existing code
> pushes down HAVING to WHERE if the clause contains no aggregates, but
> evidently this is too simplistic. =C2=A0What are the correct conditions f=
or
> pushing down HAVING clauses to WHERE?
When the HAVING clause refers to a unaltered GROUP BY column; that is, one=
=20
whose contents are not aggregated, calculated, or aliased. I can't think o=
f=20
any other condition which would be permissable. I would guess that the=20
reason why that test case bombs is that the planner detects that "2" is not=
=20
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=
=20
always in existance, thus producing a single row when run without the HAVIN=
G=20
clause. Personally, I've always felt that the SQL committee made a mistake=
=20
in having aggregates of no rows produce a single null output row; it leads =
to=20
wierdness like this here.=20=20=20
Hopefully someone can back that up with an ANSI-SQL reference ...
--=20
Josh Berkus
Aglio Database Solutions
San Francisco