Thread: Re: [HACKERS] Some info about subselect/having problems

Anyone on this one?


> I have been chasing some of the various bug reports involving HAVING
> clauses in sub-SELECTs.  A couple of examples are:
> 
> select name from t1 where name in
> (select name from t1 group by name having count(*) = 2);
> 
> ERROR:  rewrite: aggregate column of view must be at rigth side in qual
> 
> select name from t1 where name in
> (select name from t1 group by name having 2 = count(*));
> 
> ERROR:  This could have been done in a where clause!!
> 
> 
> I think that both of these errors are at least partially the fault of
> rewriteHandler.c.  The first message is coming from
> modifyAggrefMakeSublink().  It looks like the code simply doesn't bother
> to handle the case where the aggregate is on the left-hand side ---
> is there a reason for that?
> 
> The second one is more subtle.  What is happening is that in the rewrite
> step, modifyAggrefQual() scans the outer WHERE clause all the way down
> into the sub-SELECT, where it finds an occurrence of count(*) and
> replaces it by a parameter.  The reported error comes when later
> processing of the sub-SELECT finds that its having clause contains no
> aggregate functions anymore.
> 
> modifyAggrefQual()'s behavior would be correct if we wanted to assume
> that the count() aggregate is associated with the *outer* SELECT and
> is being propagated into the inner select as a constant.  But that's
> not the most reasonable reading of this query, IMHO (unless it is
> mandated by some requirement of SQL92?).  Even more to the point, the
> rest of the parser thinks that aggregates are not allowed in WHERE
> clauses:
> 
> select name from t1 where 2 = count(*);
> ERROR:  Aggregates not allowed in WHERE clause
> 
> which agrees with my understanding of the semantics.  So why is
> modifyAggrefQual() searching the outer select's WHERE clause in the
> first place?
> 
> This leads to a definitional question: should it be possible to refer
> to an aggregate on the outer SELECT inside a sub-SELECT, and if so how?
> I tried
> 
> select name from t1 as outer1 group by name having name in
> (select name from t1 as inner1 having
>  count(inner1.name) = count(outer1.name) );
> ERROR:  Illegal use of aggregates or non-group column in target list
> 
> but as you can see, the system did not take the hint.
> 
> So, several probable bugs in rewrite:
>  * omitted support for aggregate on lefthand side
>  * shouldn't be looking for aggregates in WHERE clause
>  * should be distinguishing which level of query an aggregate is
>    associated with
> 
> But I'm not familiar enough with rewrite to want to start hacking on it.
> Anyone?
> 
>             regards, tom lane
> 
> 


--  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 wrote:
>
>
> Anyone on this one?
>
>
> > I have been chasing some of the various bug reports involving HAVING
> > clauses in sub-SELECTs.  A couple of examples are:
> >
> > select name from t1 where name in
> > (select name from t1 group by name having count(*) = 2);
> >
> > ERROR:  rewrite: aggregate column of view must be at rigth side in qual
> >
> > select name from t1 where name in
> > (select name from t1 group by name having 2 = count(*));
> >
> > ERROR:  This could have been done in a where clause!!
> >
> >
> > I think that both of these errors are at least partially the fault of
> > rewriteHandler.c.  The first message is coming from
> > modifyAggrefMakeSublink().  It looks like the code simply doesn't bother
> > to handle the case where the aggregate is on the left-hand side ---
> > is there a reason for that?

    Yes. The SubLink node needs an Expr on the left-hand side. At
    the time I implemented the  modifyAggrefMakeSublink()  (which
    is  still  something  I don't like because it's bogus when it
    comes to user defined  GROUP  BY  clauses),  the  pg_operator
    class  was  in  a  very  bad state WRT the negator/commutator
    operators. Now that pg_operator is fixed, we could  swap  the
    sides and use the negator instead. But...

> >
> > The second one is more subtle.  What is happening is that in the rewrite
> > step, modifyAggrefQual() scans the outer WHERE clause all the way down
> > into the sub-SELECT, where it finds an occurrence of count(*) and
> > replaces it by a parameter.  The reported error comes when later
> > processing of the sub-SELECT finds that its having clause contains no
> > aggregate functions anymore.
> >
> > modifyAggrefQual()'s behavior would be correct if we wanted to assume
> > that the count() aggregate is associated with the *outer* SELECT and
> > is being propagated into the inner select as a constant.  But that's
> > not the most reasonable reading of this query, IMHO (unless it is
> > mandated by some requirement of SQL92?).  Even more to the point, the
> > rest of the parser thinks that aggregates are not allowed in WHERE
> > clauses:
> >
> > select name from t1 where 2 = count(*);
> > ERROR:  Aggregates not allowed in WHERE clause
> >
> > which agrees with my understanding of the semantics.  So why is
> > modifyAggrefQual() searching the outer select's WHERE clause in the
> > first place?

    Right  so  far.  The  searching is done because the aggregate
    could be the result of a previous view rewrite.

      CREATE VIEW v1 AS SELECT a, count(b) AS n FROM t1
      GROUP BY a;

      SELECT * FROM v1 WHERE 2 = n;

    Again this one is bogus (doing it in a join with some totally
    different  grouping).  It  was  just  a  first  step  to make
    something working. Again the final solution would only  be  a
    subselecting RTE.

    Aggregates  in  views are still a good way to show the limits
    of the rewrite system.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #