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

From Josh Berkus
Subject Re: [pgsql-bugs] Daily digest v1.1387 (8 messages)
Date
Msg-id 200503090949.30909.josh@agliodbs.com
Whole thread Raw
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: Neil Conway
Date:
Subject: Re: Fault when return strings over 256 characters in PLpgSQL
Next
From: Tom Lane
Date:
Subject: Re: BUG #1528: Rows returned that should be excluded by WHERE clause