Re: [HACKERS] ERROR: pull_var_clause: Cannot handle node type 108

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