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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026