Recognize functional dependency on primary keys. This allows a table's

other columns to be referenced without listing them in GROUP BY, so long as
the primary key column(s) are listed in GROUP BY.

Eventually we should also allow functional dependency on a UNIQUE constraint
when the columns are marked NOT NULL, but that has to wait until NOT NULL
constraints are represented in pg_constraint, because we need to have
pg_constraint OIDs for all the conditions needed to ensure functional
dependency.

Peter Eisentraut, reviewed by Alex Hunsaker and Tom Lane

1 files changed, 53 insertions, 10 deletions

diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index c36c5fb08c6..a7c34bc8930 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -8,12 +8,13 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/parse_agg.c,v 1.93 2010/03/17 16:52:38 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/parse_agg.c,v 1.94 2010/08/07 02:44:07 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
 #include "postgres.h"
 
+#include "catalog/pg_constraint.h"
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
 #include "optimizer/tlist.h"
@@ -29,13 +30,16 @@
 typedef struct
 {
 	ParseState *pstate;
+	Query	   *qry;
 	List	   *groupClauses;
 	bool		have_non_var_grouping;
+	List	  **func_grouped_rels;
 	int			sublevels_up;
 } check_ungrouped_columns_context;
 
-static void check_ungrouped_columns(Node *node, ParseState *pstate,
-						List *groupClauses, bool have_non_var_grouping);
+static void check_ungrouped_columns(Node *node, ParseState *pstate, Query *qry,
+						List *groupClauses, bool have_non_var_grouping,
+						List **func_grouped_rels);
 static bool check_ungrouped_columns_walker(Node *node,
 							   check_ungrouped_columns_context *context);
 
@@ -293,6 +297,7 @@ parseCheckAggregates(ParseState *pstate, Query *qry)
 {
 	List	   *groupClauses = NIL;
 	bool		have_non_var_grouping;
+	List	   *func_grouped_rels = NIL;
 	ListCell   *l;
 	bool		hasJoinRTEs;
 	bool		hasSelfRefRTEs;
@@ -408,14 +413,16 @@ parseCheckAggregates(ParseState *pstate, Query *qry)
 	clause = (Node *) qry->targetList;
 	if (hasJoinRTEs)
 		clause = flatten_join_alias_vars(root, clause);
-	check_ungrouped_columns(clause, pstate,
-							groupClauses, have_non_var_grouping);
+	check_ungrouped_columns(clause, pstate, qry,
+							groupClauses, have_non_var_grouping,
+							&func_grouped_rels);
 
 	clause = (Node *) qry->havingQual;
 	if (hasJoinRTEs)
 		clause = flatten_join_alias_vars(root, clause);
-	check_ungrouped_columns(clause, pstate,
-							groupClauses, have_non_var_grouping);
+	check_ungrouped_columns(clause, pstate, qry,
+							groupClauses, have_non_var_grouping,
+							&func_grouped_rels);
 
 	/*
 	 * Per spec, aggregates can't appear in a recursive term.
@@ -535,14 +542,17 @@ parseCheckWindowFuncs(ParseState *pstate, Query *qry)
  * way more pain than the feature seems worth.
  */
 static void
-check_ungrouped_columns(Node *node, ParseState *pstate,
-						List *groupClauses, bool have_non_var_grouping)
+check_ungrouped_columns(Node *node, ParseState *pstate, Query *qry,
+						List *groupClauses, bool have_non_var_grouping,
+						List **func_grouped_rels)
 {
 	check_ungrouped_columns_context context;
 
 	context.pstate = pstate;
+	context.qry = qry;
 	context.groupClauses = groupClauses;
 	context.have_non_var_grouping = have_non_var_grouping;
+	context.func_grouped_rels = func_grouped_rels;
 	context.sublevels_up = 0;
 	check_ungrouped_columns_walker(node, &context);
 }
@@ -619,10 +629,43 @@ check_ungrouped_columns_walker(Node *node,
 			}
 		}
 
-		/* Found an ungrouped local variable; generate error message */
+		/*
+		 * Check whether the Var is known functionally dependent on the GROUP
+		 * BY columns.  If so, we can allow the Var to be used, because the
+		 * grouping is really a no-op for this table.  However, this deduction
+		 * depends on one or more constraints of the table, so we have to add
+		 * those constraints to the query's constraintDeps list, because it's
+		 * not semantically valid anymore if the constraint(s) get dropped.
+		 * (Therefore, this check must be the last-ditch effort before raising
+		 * error: we don't want to add dependencies unnecessarily.)
+		 *
+		 * Because this is a pretty expensive check, and will have the same
+		 * outcome for all columns of a table, we remember which RTEs we've
+		 * already proven functional dependency for in the func_grouped_rels
+		 * list.  This test also prevents us from adding duplicate entries
+		 * to the constraintDeps list.
+		 */
+		if (list_member_int(*context->func_grouped_rels, var->varno))
+			return false;				/* previously proven acceptable */
+
 		Assert(var->varno > 0 &&
 			   (int) var->varno <= list_length(context->pstate->p_rtable));
 		rte = rt_fetch(var->varno, context->pstate->p_rtable);
+		if (rte->rtekind == RTE_RELATION)
+		{
+			if (check_functional_grouping(rte->relid,
+										  var->varno,
+										  0,
+										  context->groupClauses,
+										  &context->qry->constraintDeps))
+			{
+				*context->func_grouped_rels =
+					lappend_int(*context->func_grouped_rels, var->varno);
+				return false;			/* acceptable */
+			}
+		}
+
+		/* Found an ungrouped local variable; generate error message */
 		attname = get_rte_attribute_name(rte, var->varattno);
 		if (context->sublevels_up == 0)
 			ereport(ERROR,