ERROR: pull_var_clause: Cannot handle node type 108

=?iso-8859-1?Q?Jos=E9?= 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:

=?iso-8859-1?Q?Jos=E9?= 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)

=?iso-8859-1?Q?Jos=E9?= 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:

=?iso-8859-1?Q?Jos=E9?= 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�

=?iso-8859-1?Q?Jos=E9?= 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