Re: [pgsql-bugs] Daily digest v1.1387 (8 messages)

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