Thread: Status report: subselect + grouping problems

Status report: subselect + grouping problems

From
Tom Lane
Date:
I have cleaned up some problems seen in the parser and planner when
a query involves both subselects and grouping ("grouping" = GROUP BY,
HAVING, and/or aggregate functions).  I am not sure whether I've
fixed everything in those modules, though, because all my remaining
test cases are falling over due to rewriter bugs.  For example:

CREATE TABLE t1 (name text, value float8);
CREATE

SELECT name FROM t1 WHERE name IN
(select name from t1 group by name having 2 = count(*));
ERROR:  SELECT/HAVING requires aggregates to be valid

The trouble in this case is that the rewriter pushes count(*) down
into a third level of sub-select, which the planner quite rightly
decides is a constant with respect to the second level, whereupon
there don't seem to be any aggregates in the second-level subselect.

I'm not sufficiently clear about what the rewriter is trying to do here
to risk trying to fix it.  I think the problem is a mistaken application
of modifyAggrefMakeSublink(), whose comment claims*    Create a sublink node for a qualification expression that*
usesan aggregate column of a view
 
There is no view nor aggregate column in sight here, but the routine
is getting invoked anyway.  Jan, any thoughts?
        regards, tom lane