José Soares <jose@sferacarta.com> writes:
> Tom Lane ha scritto:
>> I take it you are using 6.4, because 6.5 generates different failure
>> messages. But it's not any less broken :-(. The rewriter seems to have
>> a bunch of bugs associated with aggregate functions in HAVING clauses of
>> sub-selects.
> You are right Tom. I installed v6.5.1 and now the message is different, but I
> can't understand it again:
> hygea=> select oid,nome from prova where nome in (select nome from prova
> group by nome having 1<count(*));
> ERROR: SELECT/HAVING requires aggregates to be valid
Well, like I said, it's broken. What's actually going on is that the
rewriter is mistakenly deciding that the count(*) needs to be pushed
down into another level of subselect:
select oid,nome from prova where nome in
(select nome from prova group by nome having 1 <
(select count(*) from prova));
whereupon the optimizer quite rightly complains that there is no
aggregate function visible in the mid-level HAVING clause.
This pushing-down is probably the right thing for some scenarios
involving aggregate functions introduced by views, but it's surely
dead wrong in the example as given. I don't currently understand
the rewriter well enough to know when it should happen or not happen.
I might take a swipe at fixing it though if Jan doesn't step up to bat
soon --- this class of bugs has been generating complaints for a good
while.
regards, tom lane