* 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'



Wed, 16 Jun 1999 15:54:44 +0200
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
having bug report
=?iso-8859-1?Q?Jos=E9?= 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

Wed, 16 Jun 1999 11:06:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] having bug report 
Sorry. I re-sent this message because I don't see it in TODO file and I
thougth it was fixed.

Tom Lane ha scritto:

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

Jose'



Sorry. I re-sent this message because I don't see
it in TODO file and I thougth it was fixed.
Tom Lane ha scritto:
=?iso-8859-1?Q?Jos=E9?= 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
Jose'
Wed, 16 Jun 1999 17:44:48 +0200
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
Re: [HACKERS] having bug report

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, Pennsylvania 19026

Mon, 29 Nov 1999 17:27:30 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] having bug report
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

Mon, 29 Nov 1999 21:15:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] having bug report 