Обсуждение: ERROR: pull_var_clause: Cannot handle node type 108
What does it mean? prova=> select nome from prova group by nome having count(*) > 1; nome Carlos Henrique Jose (3 rows) prova=> select oid,* from prova where nome in (select nome from prova group by nome having 1 < count(*)); ERROR: pull_var_clause: Cannot handle node type 108 prova=> select * from prova where nome in (select nome from prova group by nome having count(*) > 1); ERROR: rewrite: aggregate column of view must be at rigth side in qual José
José Soares <jose@sferacarta.com> writes:
> What does it mean?
> prova=> select nome from prova group by nome having count(*) > 1;
> [ OK ]
> prova=> select oid,* from prova where nome in (select nome from prova
> group by nome having 1 < count(*));
> ERROR: pull_var_clause: Cannot handle node type 108
> prova=> select * from prova where nome in (select nome from prova group
> by nome having count(*) > 1);
> ERROR: rewrite: aggregate column of view must be at rigth side in qual
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. Or maybe it's just several manifestations of the same bug.
I have notes about this problem but do not understand it well enough to
fix it. Perhaps Jan has a clue about it...
regards, tom lane
Tom Lane ha scritto: > José Soares <jose@sferacarta.com> writes: > > What does it mean? > > prova=> select nome from prova group by nome having count(*) > 1; > > [ OK ] > > > prova=> select oid,* from prova where nome in (select nome from prova > > group by nome having 1 < count(*)); > > ERROR: pull_var_clause: Cannot handle node type 108 > > > prova=> select * from prova where nome in (select nome from prova group > > by nome having count(*) > 1); > > ERROR: rewrite: aggregate column of view must be at rigth side in qual > > I take it you are using 6.4, because 6.5 generates different failure This is my ver: hygea=> select version(); version ------------------------------------------------------------------- PostgreSQL 6.5.0 on i586-pc-linux-gnulibc1, compiled by gcc 2.7.2.1 (1 row) > 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. Or maybe it's just several manifestations of the same bug. > I have notes about this problem but do not understand it well enough to > fix it. Perhaps Jan has a clue about it... > > regards, tom lane
José Soares <jose@sferacarta.com> writes:
>> I take it you are using 6.4, because 6.5 generates different failure
> This is my ver:
> hygea=> select version();
> version
> -------------------------------------------------------------------
> PostgreSQL 6.5.0 on i586-pc-linux-gnulibc1, compiled by gcc 2.7.2.1
> (1 row)
6.5 prerelease maybe? I'm fairly sure that 6.5 release does not have
the "pull_var_clause: Cannot handle node type" message; but it was a
late change.
"select version()" is just about useless for determining what you
are dealing with if you use CVS updates or snapshots, because the
version number only gets changed at official release times.
regards, tom lane
Tom Lane ha scritto: > José Soares <jose@sferacarta.com> writes: > > What does it mean? > > prova=> select nome from prova group by nome having count(*) > 1; > > [ OK ] > > > prova=> select oid,* from prova where nome in (select nome from prova > > group by nome having 1 < count(*)); > > ERROR: pull_var_clause: Cannot handle node type 108 > > > prova=> select * from prova where nome in (select nome from prova group > > by nome having count(*) > 1); > > ERROR: rewrite: aggregate column of view must be at rigth side in qual > > 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. Or maybe it's just several manifestations of the same bug. > I have notes about this problem but do not understand it well enough to > fix it. Perhaps Jan has a clue about it... > > regards, tom lane You are right Tom. I installed v6.5.1 and now the message is different, but I can't understand it again: hygea=> select nome from prova group by nome having 1<count(*); nome ------ carlos jose (2 rows) 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 José
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