Обсуждение: 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