Skip to content

Commit e649796

Browse files
tglsfdctglsfdc
committed
Implement outer-level aggregates to conform to the SQL spec, with
extensions to support our historical behavior. An aggregate belongs to the closest query level of any of the variables in its argument, or the current query level if there are no variables (e.g., COUNT(*)). The implementation involves adding an agglevelsup field to Aggref, and treating outer aggregates like outer variables at planning time.
1 parent 2c93861 commit e649796

File tree

26 files changed

+607
-229
lines changed

26 files changed

+607
-229
lines changed

‎doc/src/sgml/syntax.sgml

Lines changed: 25 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.77 2003/03/25 16:15:38 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.78 2003/06/06 15:04:01 tgl Exp $
33
-->
44

55
<chapter id="sql-syntax">
@@ -1094,6 +1094,29 @@ sqrt(2)
10941094
linkend="functions-aggregate">. Other aggregate functions may be added
10951095
by the user.
10961096
</para>
1097+
1098+
<para>
1099+
An aggregate expression may only appear in the result list or
1100+
<literal>HAVING</> clause of a <command>SELECT</> command.
1101+
It is forbidden in other clauses, such as <literal>WHERE</>,
1102+
because those clauses are logically evaluated before the results
1103+
of aggregates are formed.
1104+
</para>
1105+
1106+
<para>
1107+
When an aggregate expression appears in a subquery (see
1108+
<xref linkend="sql-syntax-scalar-subqueries"> and
1109+
<xref linkend="functions-subquery">), the aggregate is normally
1110+
evaluated over the rows of the subquery. But an exception occurs
1111+
if the aggregate's argument contains only outer-level variables:
1112+
the aggregate then belongs to the nearest such outer level, and is
1113+
evaluated over the rows of that query. The aggregate expression
1114+
as a whole is then an outer reference for the subquery it appears in,
1115+
and acts as a constant over any one evaluation of that subquery.
1116+
The restriction about
1117+
appearing only in the result list or <literal>HAVING</> clause
1118+
applies with respect to the query level that the aggregate belongs to.
1119+
</para>
10971120
</sect2>
10981121

10991122
<sect2 id="sql-syntax-type-casts">
@@ -1164,7 +1187,7 @@ CAST ( <replaceable>expression</replaceable> AS <replaceable>type</replaceable>
11641187
</para>
11651188
</sect2>
11661189

1167-
<sect2>
1190+
<sect2 id="sql-syntax-scalar-subqueries">
11681191
<title>Scalar Subqueries</title>
11691192

11701193
<para>

‎src/backend/catalog/heap.c

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.245 2003/05/28 16:03:55 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.246 2003/06/06 15:04:01 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -1619,9 +1619,9 @@ AddRelationRawConstraints(Relation rel,
16191619
/*
16201620
* No subplans or aggregates, either...
16211621
*/
1622-
if (contain_subplans(expr))
1622+
if (pstate->p_hasSubLinks)
16231623
elog(ERROR, "cannot use subselect in CHECK constraint expression");
1624-
if (contain_agg_clause(expr))
1624+
if (pstate->p_hasAggs)
16251625
elog(ERROR, "cannot use aggregate function in CHECK constraint expression");
16261626

16271627
/*
@@ -1738,9 +1738,9 @@ cookDefault(ParseState *pstate,
17381738
/*
17391739
* No subplans or aggregates, either...
17401740
*/
1741-
if (contain_subplans(expr))
1741+
if (pstate->p_hasSubLinks)
17421742
elog(ERROR, "cannot use subselects in DEFAULT clause");
1743-
if (contain_agg_clause(expr))
1743+
if (pstate->p_hasAggs)
17441744
elog(ERROR, "cannot use aggregate functions in DEFAULT clause");
17451745

17461746
/*

‎src/backend/commands/tablecmds.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.73 2003/05/28 16:03:56 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.74 2003/06/06 15:04:01 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -2870,9 +2870,9 @@ AlterTableAddCheckConstraint(Relation rel, Constraint *constr)
28702870
/*
28712871
* No subplans or aggregates, either...
28722872
*/
2873-
if (contain_subplans(expr))
2873+
if (pstate->p_hasSubLinks)
28742874
elog(ERROR, "cannot use subselect in CHECK constraint expression");
2875-
if (contain_agg_clause(expr))
2875+
if (pstate->p_hasAggs)
28762876
elog(ERROR, "cannot use aggregate function in CHECK constraint expression");
28772877

28782878
/*

‎src/backend/commands/typecmds.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/typecmds.c,v 1.36 2003/05/09 23:01:45 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/typecmds.c,v 1.37 2003/06/06 15:04:01 tgl Exp $
1212
*
1313
* DESCRIPTION
1414
* The "DefineFoo" routines take the parse tree and pick out the
@@ -1720,9 +1720,9 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
17201720
/*
17211721
* No subplans or aggregates, either...
17221722
*/
1723-
if (contain_subplans(expr))
1723+
if (pstate->p_hasSubLinks)
17241724
elog(ERROR, "cannot use subselect in CHECK constraint expression");
1725-
if (contain_agg_clause(expr))
1725+
if (pstate->p_hasAggs)
17261726
elog(ERROR, "cannot use aggregate function in CHECK constraint expression");
17271727

17281728
/*

‎src/backend/executor/nodeAgg.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -45,7 +45,7 @@
4545
* Portions Copyright (c) 1994, Regents of the University of California
4646
*
4747
* IDENTIFICATION
48-
* $Header: /cvsroot/pgsql/src/backend/executor/nodeAgg.c,v 1.105 2003/05/30 20:23:10 tgl Exp $
48+
* $Header: /cvsroot/pgsql/src/backend/executor/nodeAgg.c,v 1.106 2003/06/06 15:04:01 tgl Exp $
4949
*
5050
*-------------------------------------------------------------------------
5151
*/
@@ -1179,6 +1179,9 @@ ExecInitAgg(Agg *node, EState *estate)
11791179
Datum textInitVal;
11801180
int i;
11811181

1182+
/* Planner should have assigned aggregate to correct level */
1183+
Assert(aggref->agglevelsup == 0);
1184+
11821185
/* Look for a previous duplicate aggregate */
11831186
for (i = 0; i <= aggno; i++)
11841187
{

‎src/backend/executor/nodeSubplan.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/executor/nodeSubplan.c,v 1.45 2003/04/08 23:20:01 tgl Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/executor/nodeSubplan.c,v 1.46 2003/06/06 15:04:01 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -240,7 +240,9 @@ ExecScanSubPlan(SubPlanState *node,
240240
oldcontext = MemoryContextSwitchTo(node->sub_estate->es_query_cxt);
241241

242242
/*
243-
* Set Params of this plan from parent plan correlation Vars
243+
* Set Params of this plan from parent plan correlation values.
244+
* (Any calculation we have to do is done in the parent econtext,
245+
* since the Param values don't need to have per-query lifetime.)
244246
*/
245247
pvar = node->args;
246248
foreach(lst, subplan->parParam)

‎src/backend/nodes/copyfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.251 2003/05/28 16:03:56 tgl Exp $
18+
* $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.252 2003/06/06 15:04:02 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -725,6 +725,7 @@ _copyAggref(Aggref *from)
725725
COPY_SCALAR_FIELD(aggfnoid);
726726
COPY_SCALAR_FIELD(aggtype);
727727
COPY_NODE_FIELD(target);
728+
COPY_SCALAR_FIELD(agglevelsup);
728729
COPY_SCALAR_FIELD(aggstar);
729730
COPY_SCALAR_FIELD(aggdistinct);
730731

‎src/backend/nodes/equalfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@
1818
* Portions Copyright (c) 1994, Regents of the University of California
1919
*
2020
* IDENTIFICATION
21-
* $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.194 2003/05/28 16:03:56 tgl Exp $
21+
* $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.195 2003/06/06 15:04:02 tgl Exp $
2222
*
2323
*-------------------------------------------------------------------------
2424
*/
@@ -202,6 +202,7 @@ _equalAggref(Aggref *a, Aggref *b)
202202
COMPARE_SCALAR_FIELD(aggfnoid);
203203
COMPARE_SCALAR_FIELD(aggtype);
204204
COMPARE_NODE_FIELD(target);
205+
COMPARE_SCALAR_FIELD(agglevelsup);
205206
COMPARE_SCALAR_FIELD(aggstar);
206207
COMPARE_SCALAR_FIELD(aggdistinct);
207208

‎src/backend/nodes/outfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/nodes/outfuncs.c,v 1.206 2003/05/28 16:03:56 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/nodes/outfuncs.c,v 1.207 2003/06/06 15:04:02 tgl Exp $
1212
*
1313
* NOTES
1414
* Every node type that can appear in stored rules' parsetrees *must*
@@ -613,6 +613,7 @@ _outAggref(StringInfo str, Aggref *node)
613613
WRITE_OID_FIELD(aggfnoid);
614614
WRITE_OID_FIELD(aggtype);
615615
WRITE_NODE_FIELD(target);
616+
WRITE_UINT_FIELD(agglevelsup);
616617
WRITE_BOOL_FIELD(aggstar);
617618
WRITE_BOOL_FIELD(aggdistinct);
618619
}

‎src/backend/nodes/readfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/nodes/readfuncs.c,v 1.153 2003/05/06 00:20:32 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/nodes/readfuncs.c,v 1.154 2003/06/06 15:04:02 tgl Exp $
1212
*
1313
* NOTES
1414
* Path and Plan nodes do not have any readfuncs support, because we
@@ -413,6 +413,7 @@ _readAggref(void)
413413
READ_OID_FIELD(aggfnoid);
414414
READ_OID_FIELD(aggtype);
415415
READ_NODE_FIELD(target);
416+
READ_UINT_FIELD(agglevelsup);
416417
READ_BOOL_FIELD(aggstar);
417418
READ_BOOL_FIELD(aggdistinct);
418419

‎src/backend/optimizer/plan/planner.c

Lines changed: 13 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.153 2003/05/06 00:20:32 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v 1.154 2003/06/06 15:04:02 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -75,7 +75,7 @@ planner(Query *parse, bool isCursor, int cursorOptions)
7575
double tuple_fraction;
7676
Plan *result_plan;
7777
Index save_PlannerQueryLevel;
78-
List *save_PlannerParamVar;
78+
List *save_PlannerParamList;
7979

8080
/*
8181
* The planner can be called recursively (an example is when
@@ -91,11 +91,11 @@ planner(Query *parse, bool isCursor, int cursorOptions)
9191
* subquery_planner, not here.
9292
*/
9393
save_PlannerQueryLevel = PlannerQueryLevel;
94-
save_PlannerParamVar = PlannerParamVar;
94+
save_PlannerParamList = PlannerParamList;
9595

9696
/* Initialize state for handling outer-level references and params */
9797
PlannerQueryLevel = 0; /* will be 1 in top-level subquery_planner */
98-
PlannerParamVar = NIL;
98+
PlannerParamList = NIL;
9999

100100
/* Determine what fraction of the plan is likely to be scanned */
101101
if (isCursor)
@@ -130,14 +130,14 @@ planner(Query *parse, bool isCursor, int cursorOptions)
130130
}
131131

132132
/* executor wants to know total number of Params used overall */
133-
result_plan->nParamExec = length(PlannerParamVar);
133+
result_plan->nParamExec = length(PlannerParamList);
134134

135135
/* final cleanup of the plan */
136136
set_plan_references(result_plan, parse->rtable);
137137

138138
/* restore state for outer planner, if any */
139139
PlannerQueryLevel = save_PlannerQueryLevel;
140-
PlannerParamVar = save_PlannerParamVar;
140+
PlannerParamList = save_PlannerParamList;
141141

142142
return result_plan;
143143
}
@@ -261,8 +261,7 @@ subquery_planner(Query *parse, double tuple_fraction)
261261
*
262262
* Note that both havingQual and parse->jointree->quals are in
263263
* implicitly-ANDed-list form at this point, even though they are
264-
* declared as Node *. Also note that contain_agg_clause does not
265-
* recurse into sub-selects, which is exactly what we need here.
264+
* declared as Node *.
266265
*/
267266
newHaving = NIL;
268267
foreach(lst, (List *) parse->havingQual)
@@ -397,6 +396,11 @@ preprocess_expression(Query *parse, Node *expr, int kind)
397396
if (parse->hasSubLinks)
398397
expr = SS_process_sublinks(expr, (kind == EXPRKIND_QUAL));
399398

399+
/*
400+
* XXX do not insert anything here unless you have grokked the comments
401+
* in SS_replace_correlation_vars ...
402+
*/
403+
400404
/* Replace uplevel vars with Param nodes */
401405
if (PlannerQueryLevel > 1)
402406
expr = SS_replace_correlation_vars(expr);
@@ -1356,7 +1360,7 @@ make_subplanTargetList(Query *parse,
13561360
* If we're not grouping or aggregating, nothing to do here;
13571361
* query_planner should receive the unmodified target list.
13581362
*/
1359-
if (!parse->hasAggs && !parse->groupClause && !parse->havingQual)
1363+
if (!parse->hasAggs && !parse->groupClause)
13601364
{
13611365
*need_tlist_eval = true;
13621366
return tlist;

0 commit comments

Comments
 (0)