Thread: having bug report

* subqueries containing HAVING return incorrect results

select istat from comuni where istat in (
select istat from comuni group by istat having count(istat) > 1
);
ERROR:  rewrite: aggregate column of view must be at rigth side in qual

select istat from comuni where istat in (
select istat from comuni group by istat having 1 < count(istat)
);
ERROR:  pull_var_clause: Cannot handle node type 108

______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'

José Soares <jose@sferacarta.com> writes:
> * subqueries containing HAVING return incorrect results
> select istat from comuni where istat in (
> select istat from comuni group by istat having count(istat) > 1
> );
> ERROR:  rewrite: aggregate column of view must be at rigth side in qual
> select istat from comuni where istat in (
> select istat from comuni group by istat having 1 < count(istat)
> );
> ERROR:  pull_var_clause: Cannot handle node type 108

These are both known problems (at least, I had both in my todo list).

The first one appears to be a rewriter bug --- it seems to want to
implement count(istat) as a second nested sublink, and then it falls
over because it doesn't handle "subselect op something" as opposed to
"something op subselect".  But pushing count(istat) into a subselect
is not merely inefficient, it's *wrong* in this case because then the
group by won't affect it.

The second one is a problem in the planner/optimizer; it falls over on
sublinks in HAVING clauses (of course, this particular example wouldn't
trigger the problem were it not for the upstream rewriter bug, but it's
still a planner bug).  I think union_planner's handling of sublinks
needs considerable work, but was putting it off till after 6.5.

I will work on the second problem; I think the first one is in Jan's
turf...
        regards, tom lane

<font color="#000000">Sorry. I re-sent this message because I don't see it in TODO file and I thougth it was
fixed.</font><p>TomLane ha scritto: <blockquote type="CITE">José Soares <jose@sferacarta.com> writes: <br />>
*subqueries containing HAVING return incorrect results <br />> select istat from comuni where istat in ( <br />>
selectistat from comuni group by istat having count(istat) > 1 <br />> ); <br />> ERROR:  rewrite: aggregate
columnof view must be at rigth side in qual <br />> select istat from comuni where istat in ( <br />> select
istatfrom comuni group by istat having 1 < count(istat) <br />> ); <br />> ERROR:  pull_var_clause: Cannot
handlenode type 108 <p>These are both known problems (at least, I had both in my todo list). <p>The first one appears
tobe a rewriter bug --- it seems to want to <br />implement count(istat) as a second nested sublink, and then it falls
<br/>over because it doesn't handle "subselect op something" as opposed to <br />"something op subselect".  But pushing
count(istat)into a subselect <br />is not merely inefficient, it's *wrong* in this case because then the <br />group by
won'taffect it. <p>The second one is a problem in the planner/optimizer; it falls over on <br />sublinks in HAVING
clauses(of course, this particular example wouldn't <br />trigger the problem were it not for the upstream rewriter
bug,but it's <br />still a planner bug).  I think union_planner's handling of sublinks <br />needs considerable work,
butwas putting it off till after 6.5. <p>I will work on the second problem; I think the first one is in Jan's <br
/>turf...<p>                        regards, tom lane</blockquote> Jose' <br />  

Any comments on that status of this one?

> * subqueries containing HAVING return incorrect results
> 
> select istat from comuni where istat in (
> select istat from comuni group by istat having count(istat) > 1
> );
> ERROR:  rewrite: aggregate column of view must be at rigth side in qual
> 
> select istat from comuni where istat in (
> select istat from comuni group by istat having 1 < count(istat)
> );
> ERROR:  pull_var_clause: Cannot handle node type 108
> 
> ______________________________________________________________
> PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> Jose'
> 
> 
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Any comments on that status of this one?

Those particular cases are fixed, I think, but there are still severe
problems with VIEWs that use grouping or aggregates.  I doubt we can
improve the VIEW situation much more without subselects-in-FROM.
        regards, tom lane


>> * subqueries containing HAVING return incorrect results
>> 
>> select istat from comuni where istat in (
>> select istat from comuni group by istat having count(istat) > 1
>> );
>> ERROR:  rewrite: aggregate column of view must be at rigth side in qual
>> 
>> select istat from comuni where istat in (
>> select istat from comuni group by istat having 1 < count(istat)
>> );
>> ERROR:  pull_var_clause: Cannot handle node type 108