From: | jwieck(at)debis(dot)com (Jan Wieck) |
---|---|
To: | maillist(at)candle(dot)pha(dot)pa(dot)us (Bruce Momjian) |
Cc: | tgl(at)sss(dot)pgh(dot)pa(dot)us, pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] Some info about subselect/having problems |
Date: | 1999-05-10 17:07:20 |
Message-ID: | m10gtWS-000EBXC@orion.SAPserv.Hamburg.dsh.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)debis(dot)com (Jan Wieck) #
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-05-10 17:18:03 | Re: [HACKERS] DROP TABLE leaks file descriptors |
Previous Message | Bruce Momjian | 1999-05-10 17:06:36 | Re: [HACKERS] problems with parser |