Thread
-
Re: [HACKERS] Some info about subselect/having problems
Bruce Momjian <maillist@candle.pha.pa.us> — 1999-05-10T16:11:09Z
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, Pennsylvania 19026
-
Re: [HACKERS] Some info about subselect/having problems
Jan Wieck <jwieck@debis.com> — 1999-05-10T17:07:20Z
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) #