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

Started by Bruce Momjianover 26 years ago2 messages

maillist@candle.pha.pa.us

over 26 years ago

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

Import Notes

Reply to msg id not found: 27044.924469564@sss.pgh.pa.us

Noname

jwieck@debis.com

over 26 years ago

In reply to: Bruce Momjian (#1)

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) #