functional-deps.patch

diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index d0c41ce..e40cc4c 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -886,10 +886,7 @@ SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
     In this example, the columns <literal>product_id</literal>,
     <literal>p.name</literal>, and <literal>p.price</literal> must be
     in the <literal>GROUP BY</> clause since they are referenced in
-    the query select list.  (Depending on how the products
-    table is set up, name and price might be fully dependent on the
-    product ID, so the additional groupings could theoretically be
-    unnecessary, though this is not implemented.)  The column
+    the query select list (but see below).  The column
     <literal>s.units</> does not have to be in the <literal>GROUP
     BY</> list since it is only used in an aggregate expression
     (<literal>sum(...)</literal>), which represents the sales
@@ -898,6 +895,18 @@ SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
    </para>
 
    <para>
+    If the products table is set up so that,
+    say, <literal>product_id</literal> is the primary key or a
+    not-null unique constraint, then it would be enough to group
+    by <literal>product_id</literal> in the above example, since name
+    and price would be <firstterm>functionally
+    dependent</firstterm><indexterm><primary>functional
+    dependency</primary></indexterm> on the product ID, and so there
+    would be no ambiguity about which name and price value to return
+    for each product ID group.
+   </para>
+
+   <para>
     In strict SQL, <literal>GROUP BY</> can only group by columns of
     the source table but <productname>PostgreSQL</productname> extends
     this to also allow <literal>GROUP BY</> to group by columns in the
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index a4d017f..d901390 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -520,9 +520,17 @@ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...]
     produces a single value computed across all the selected rows).
     When <literal>GROUP BY</literal> is present, it is not valid for
     the <command>SELECT</command> list expressions to refer to
-    ungrouped columns except within aggregate functions, since there
-    would be more than one possible value to return for an ungrouped
-    column.
+    ungrouped columns except within aggregate functions or if the
+    ungrouped column is functionally dependent on the grouped columns,
+    since there would otherwise be more than one possible value to
+    return for an ungrouped column.  A functional dependency exists if
+    the grouped columns (or a subset thereof) are the primary key or a
+    not-null unique constraint of the table containing the ungrouped
+    column.  A functional dependency also exists if the ungrouped
+    column is constrained by the <literal>WHERE</literal> clause to a
+    constant value (for example, by equality comparison with a
+    constant).  Further rules for determining functional dependencies
+    might be added in the future.
    </para>
   </refsect2>
 
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 0a69bde..bfdd7ef 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -14,6 +14,8 @@
  */
 #include "postgres.h"
 
+#include "access/heapam.h"
+#include "catalog/pg_index.h"
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
 #include "optimizer/tlist.h"
@@ -24,20 +26,23 @@
 #include "rewrite/rewriteManip.h"
 #include "utils/builtins.h"
 #include "utils/lsyscache.h"
+#include "utils/syscache.h"
 
 
 typedef struct
 {
 	ParseState *pstate;
+	Query	   *qry;
 	List	   *groupClauses;
 	bool		have_non_var_grouping;
 	int			sublevels_up;
 } check_ungrouped_columns_context;
 
-static void check_ungrouped_columns(Node *node, ParseState *pstate,
+static void check_ungrouped_columns(Node *node, ParseState *pstate, Query *qry,
 						List *groupClauses, bool have_non_var_grouping);
 static bool check_ungrouped_columns_walker(Node *node,
 							   check_ungrouped_columns_context *context);
+static bool is_functionally_dependent(List *group_clauses, Var *var, Query *qry);
 
 
 /*
@@ -408,13 +413,13 @@ parseCheckAggregates(ParseState *pstate, Query *qry)
 	clause = (Node *) qry->targetList;
 	if (hasJoinRTEs)
 		clause = flatten_join_alias_vars(root, clause);
-	check_ungrouped_columns(clause, pstate,
+	check_ungrouped_columns(clause, pstate, qry,
 							groupClauses, have_non_var_grouping);
 
 	clause = (Node *) qry->havingQual;
 	if (hasJoinRTEs)
 		clause = flatten_join_alias_vars(root, clause);
-	check_ungrouped_columns(clause, pstate,
+	check_ungrouped_columns(clause, pstate, qry,
 							groupClauses, have_non_var_grouping);
 
 	/*
@@ -535,12 +540,13 @@ parseCheckWindowFuncs(ParseState *pstate, Query *qry)
  * way more pain than the feature seems worth.
  */
 static void
-check_ungrouped_columns(Node *node, ParseState *pstate,
+check_ungrouped_columns(Node *node, ParseState *pstate, Query *qry,
 						List *groupClauses, bool have_non_var_grouping)
 {
 	check_ungrouped_columns_context context;
 
 	context.pstate = pstate;
+	context.qry = qry;
 	context.groupClauses = groupClauses;
 	context.have_non_var_grouping = have_non_var_grouping;
 	context.sublevels_up = 0;
@@ -607,16 +613,8 @@ check_ungrouped_columns_walker(Node *node,
 		 */
 		if (!context->have_non_var_grouping || context->sublevels_up != 0)
 		{
-			foreach(gl, context->groupClauses)
-			{
-				Var		   *gvar = (Var *) lfirst(gl);
-
-				if (IsA(gvar, Var) &&
-					gvar->varno == var->varno &&
-					gvar->varattno == var->varattno &&
-					gvar->varlevelsup == 0)
-					return false;		/* acceptable, we're okay */
-			}
+			if (is_functionally_dependent(context->groupClauses, var, context->qry))
+				return false;	/* acceptable, we're okay */
 		}
 
 		/* Found an ungrouped local variable; generate error message */
@@ -656,6 +654,248 @@ check_ungrouped_columns_walker(Node *node,
 }
 
 /*
+ * Check whether the attributes of the primary key or a not-null
+ * unique constraint of relid with range table index rteno appear as a
+ * subset of the group_clauses.  (If so, a functional dependency
+ * exists between the group clauses and any attribute of the relation,
+ * and so attributes of the relation can appear ungrouped.)
+ */
+static bool
+funcdeps_check_pk(List *group_clauses, Oid relid, Index rteno)
+{
+	Relation	rel;
+	ListCell   *indexoidcell;
+
+	rel = heap_open(relid, AccessShareLock);
+
+	foreach(indexoidcell, RelationGetIndexList(rel))
+	{
+		Oid         indexoid = lfirst_oid(indexoidcell);
+		HeapTuple   indexTuple;
+		Form_pg_index indexStruct;
+		int         i;
+		bool		found_col;
+		bool		found_all_cols;
+
+		indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid));
+		if (!HeapTupleIsValid(indexTuple))
+			elog(ERROR, "cache lookup failed for index %u", indexoid);
+		indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
+
+		if ((!indexStruct->indisunique && !indexStruct->indisprimary)
+			|| !indexStruct->indimmediate)
+			continue;
+
+		/*
+		 * Check that the group columns are a superset of the
+		 * primary key columns.
+		 */
+		for (i = 0; i < indexStruct->indnatts; i++)
+		{
+			HeapTuple       tp;
+			int2			attnum;
+			ListCell	   *gl;
+
+			attnum = indexStruct->indkey.values[i];
+			found_col = false;
+
+			tp = SearchSysCache2(ATTNUM,
+								 ObjectIdGetDatum(relid),
+								 Int16GetDatum(attnum));
+			if (HeapTupleIsValid(tp))
+			{
+				Form_pg_attribute att_tup = (Form_pg_attribute) GETSTRUCT(tp);
+				bool        attnotnull;
+
+				attnotnull = att_tup->attnotnull;
+				ReleaseSysCache(tp);
+				if (!attnotnull)
+					break;
+			}
+			else
+				break;
+
+			foreach(gl, group_clauses)
+			{
+				Var		   *gvar = (Var *) lfirst(gl);
+
+				if (IsA(gvar, Var) &&
+					gvar->varno == rteno &&
+					gvar->varattno == attnum &&
+					gvar->varlevelsup == 0)
+				{
+					found_col = true;
+					break;
+				}
+			}
+			if (!found_col)
+				break;
+		}
+		found_all_cols = (i == indexStruct->indnatts && found_col);
+
+		ReleaseSysCache(indexTuple);
+		if (found_all_cols)
+		{
+			heap_close(rel, NoLock);
+			return true;
+		}
+	}
+
+	heap_close(rel, NoLock);
+
+	return false;
+}
+
+/*
+ * Check if the node is an operator expression of the form var =
+ * constant (or vice versa).  (Note that this allows the var to be
+ * ungrouped because it will be guaranteed to be the same across all
+ * groups.)
+ */
+static bool
+is_var_equals_constant(Node *node, Var *var)
+{
+	OpExpr *oe;
+
+	if (!IsA(node, OpExpr))
+		return false;
+
+	oe = (OpExpr *) node;
+
+	if (list_length(oe->args) == 2
+		&& op_mergejoinable(oe->opno))
+	{
+		Node	   *n1 = linitial(oe->args);
+		Node	   *n2 = lsecond(oe->args);
+		Var		   *v;
+		Const	   *c;
+
+		if (IsA(n1, Var) && IsA(n2, Const))
+		{
+			v = (Var *) n1;
+			c = (Const *) n2;
+		}
+		else if (IsA(n2, Var) && IsA(n1, Const))
+		{
+			v = (Var *) n2;
+			c = (Const *) n1;
+		}
+		else
+			return false;
+
+		if (v->varno == var->varno
+			&& v->varattno == var->varattno
+			&& v->varlevelsup == 0
+			&& !c->constisnull)
+			return true;
+	}
+
+	return false;
+}
+
+/*
+ * Search the top-level AND components of the join tree for a match
+ * with checkfunc.
+ */
+static bool
+funcdeps_search_and_components(Node *node, bool(checkfunc)(Node *node, Var *var),  Var *var)
+{
+	if (!node)
+		return false;
+	else if (IsA(node, FromExpr))
+	{
+		FromExpr   *fe = (FromExpr *) node;
+
+		return (funcdeps_search_and_components(fe->quals, checkfunc, var)
+				|| funcdeps_search_and_components((Node *) fe->fromlist, checkfunc, var));
+	}
+	else if (IsA(node, JoinExpr))
+	{
+		JoinExpr *je = (JoinExpr *) node;
+
+		return ((je->jointype == JOIN_INNER && funcdeps_search_and_components(je->quals, checkfunc, var))
+				|| funcdeps_search_and_components(je->larg, checkfunc, var)
+				|| funcdeps_search_and_components(je->rarg, checkfunc, var));
+	}
+	else if (IsA(node, BoolExpr))
+	{
+		BoolExpr *be = (BoolExpr *) node;
+
+		if (be->boolop == AND_EXPR)
+			return funcdeps_search_and_components((Node *) be->args, checkfunc, var);
+		else
+			return false;
+	}
+	else if (IsA(node, List))
+	{
+		List	   *list = (List *) node;
+		ListCell   *cell;
+
+		foreach(cell, list)
+		{
+			Node *n = lfirst(cell);
+			bool		check;
+
+			check = funcdeps_search_and_components(n, checkfunc, var);
+			if (check)
+				return true;
+		}
+		return false;
+	}
+	else
+		return checkfunc(node, var);
+}
+
+/*
+ * Check whether var is functionally dependent on group_clauses in the
+ * context of qry.
+ *
+ * Known functional dependencies are defined in the SQL standard.
+ * This function currently only implements a subset.
+ */
+static bool
+is_functionally_dependent(List *group_clauses, Var *var, Query *qry)
+{
+	bool		check;
+	ListCell   *gl;
+	RangeTblEntry *rte;
+
+	/*
+	 * Easy case: var appears directly in group clauses.
+	 */
+	foreach(gl, group_clauses)
+	{
+		Var		   *gvar = (Var *) lfirst(gl);
+
+		if (IsA(gvar, Var) &&
+			gvar->varno == var->varno &&
+			gvar->varattno == var->varattno &&
+			gvar->varlevelsup == 0)
+			return true;
+	}
+
+	/*
+	 * Primary key of var's table is subset of group clauses.
+	 */
+	rte = rt_fetch(var->varno, qry->rtable);
+	if (rte->rtekind == RTE_RELATION)
+	{
+		check = funcdeps_check_pk(group_clauses, rte->relid, var->varno);
+		if (check)
+			return true;
+	}
+
+	/*
+	 * Var is compared with constant in top-level AND clause.
+	 */
+	check = funcdeps_search_and_components((Node *) qry->jointree, is_var_equals_constant, var);
+	if (check)
+		return true;
+
+	return false;
+}
+
+/*
  * Create expression trees for the transition and final functions
  * of an aggregate.  These are needed so that polymorphic functions
  * can be used within an aggregate --- without the expression trees,
diff --git a/src/test/regress/expected/functional_deps.out b/src/test/regress/expected/functional_deps.out
new file mode 100644
index 0000000..5391782
--- /dev/null
+++ b/src/test/regress/expected/functional_deps.out
@@ -0,0 +1,248 @@
+-- from http://www.depesz.com/index.php/2010/04/19/getting-unique-elements/
+CREATE TABLE articles (
+    id int PRIMARY KEY,
+    keywords text,
+    title text UNIQUE NOT NULL,
+    body text UNIQUE,
+    created date
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "articles_pkey" for table "articles"
+NOTICE:  CREATE TABLE / UNIQUE will create implicit index "articles_title_key" for table "articles"
+NOTICE:  CREATE TABLE / UNIQUE will create implicit index "articles_body_key" for table "articles"
+CREATE TABLE articles_in_category (
+    article_id int,
+    category_id int,
+    changed date,
+    PRIMARY KEY (article_id, category_id)
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "articles_in_category_pkey" for table "articles_in_category"
+-- test functional dependencies based on primary keys/unique constraints
+-- base tables
+-- group by primary key (OK)
+SELECT id, keywords, title, body, created
+FROM articles
+GROUP BY id;
+ id | keywords | title | body | created 
+----+----------+-------+------+---------
+(0 rows)
+
+-- group by unique not null (OK)
+SELECT id, keywords, title, body, created
+FROM articles
+GROUP BY title;
+ id | keywords | title | body | created 
+----+----------+-------+------+---------
+(0 rows)
+
+-- group by unique nullable (fail)
+SELECT id, keywords, title, body, created
+FROM articles
+GROUP BY body;
+ERROR:  column "articles.id" must appear in the GROUP BY clause or be used in an aggregate function
+LINE 1: SELECT id, keywords, title, body, created
+               ^
+-- group by something else (fail)
+SELECT id, keywords, title, body, created
+FROM articles
+GROUP BY keywords;
+ERROR:  column "articles.id" must appear in the GROUP BY clause or be used in an aggregate function
+LINE 1: SELECT id, keywords, title, body, created
+               ^
+-- multiple tables
+-- group by primary key (OK)
+SELECT a.id, a.keywords, a.title, a.body, a.created
+FROM articles AS a, articles_in_category AS aic
+WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138)
+GROUP BY a.id;
+ id | keywords | title | body | created 
+----+----------+-------+------+---------
+(0 rows)
+
+-- group by something else (fail)
+SELECT a.id, a.keywords, a.title, a.body, a.created
+FROM articles AS a, articles_in_category AS aic
+WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138)
+GROUP BY aic.article_id, aic.category_id;
+ERROR:  column "a.id" must appear in the GROUP BY clause or be used in an aggregate function
+LINE 1: SELECT a.id, a.keywords, a.title, a.body, a.created
+               ^
+-- JOIN syntax
+-- group by left table's primary key (OK)
+SELECT a.id, a.keywords, a.title, a.body, a.created
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
+WHERE aic.category_id in (14,62,70,53,138)
+GROUP BY a.id;
+ id | keywords | title | body | created 
+----+----------+-------+------+---------
+(0 rows)
+
+-- group by something else (fail)
+SELECT a.id, a.keywords, a.title, a.body, a.created
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
+WHERE aic.category_id in (14,62,70,53,138)
+GROUP BY aic.article_id, aic.category_id;
+ERROR:  column "a.id" must appear in the GROUP BY clause or be used in an aggregate function
+LINE 1: SELECT a.id, a.keywords, a.title, a.body, a.created
+               ^
+-- group by right table's (composite) primary key (OK)
+SELECT aic.changed
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
+WHERE aic.category_id in (14,62,70,53,138)
+GROUP BY aic.category_id, aic.article_id;
+ changed 
+---------
+(0 rows)
+
+-- group by right table's partial primary key (fail)
+SELECT aic.changed
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
+WHERE aic.category_id in (14,62,70,53,138)
+GROUP BY aic.article_id;
+ERROR:  column "aic.changed" must appear in the GROUP BY clause or be used in an aggregate function
+LINE 1: SELECT aic.changed
+               ^
+-- example from documentation
+CREATE TABLE products (product_id int, name text, price numeric);
+CREATE TABLE sales (product_id int, units int);
+-- OK
+SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
+    FROM products p LEFT JOIN sales s USING (product_id)
+    GROUP BY product_id, p.name, p.price;
+ product_id | name | sales 
+------------+------+-------
+(0 rows)
+
+-- fail
+SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
+    FROM products p LEFT JOIN sales s USING (product_id)
+    GROUP BY product_id;
+ERROR:  column "p.name" must appear in the GROUP BY clause or be used in an aggregate function
+LINE 1: SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
+                           ^
+ALTER TABLE products ADD PRIMARY KEY (product_id);
+NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "products_pkey" for table "products"
+-- OK now
+SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
+    FROM products p LEFT JOIN sales s USING (product_id)
+    GROUP BY product_id;
+ product_id | name | sales 
+------------+------+-------
+(0 rows)
+
+-- Drupal example, http://drupal.org/node/555530
+CREATE TABLE node (
+    nid SERIAL,
+    vid integer NOT NULL default '0',
+    type varchar(32) NOT NULL default '',
+    title varchar(128) NOT NULL default '',
+    uid integer NOT NULL default '0',
+    status integer NOT NULL default '1',
+    created integer NOT NULL default '0',
+    -- snip
+    PRIMARY KEY (nid, vid)
+);
+NOTICE:  CREATE TABLE will create implicit sequence "node_nid_seq" for serial column "node.nid"
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node"
+CREATE TABLE users (
+    uid integer NOT NULL default '0',
+    name varchar(60) NOT NULL default '',
+    pass varchar(32) NOT NULL default '',
+    -- snip
+    PRIMARY KEY (uid),
+    UNIQUE (name)
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
+NOTICE:  CREATE TABLE / UNIQUE will create implicit index "users_name_key" for table "users"
+-- OK
+SELECT u.uid, u.name FROM node n
+INNER JOIN users u ON u.uid = n.uid
+WHERE n.type = 'blog' AND n.status = 1
+GROUP BY u.uid, u.name;
+ uid | name 
+-----+------
+(0 rows)
+
+-- OK
+SELECT u.uid, u.name FROM node n
+INNER JOIN users u ON u.uid = n.uid
+WHERE n.type = 'blog' AND n.status = 1
+GROUP BY u.uid;
+ uid | name 
+-----+------
+(0 rows)
+
+-- test functional dependencies based on WHERE clause
+-- var = const (OK)
+SELECT body, keywords
+FROM articles
+WHERE keywords = 'foo bar'
+GROUP BY body;
+ body | keywords 
+------+----------
+(0 rows)
+
+-- const = var (OK)
+SELECT body, keywords
+FROM articles
+WHERE 'foo bar' = keywords
+GROUP BY body;
+ body | keywords 
+------+----------
+(0 rows)
+
+-- nested in AND (OK)
+SELECT body, keywords
+FROM articles
+WHERE keywords = 'foo bar' AND body <> ''
+GROUP BY body;
+ body | keywords 
+------+----------
+(0 rows)
+
+-- nested in OR (fail)
+SELECT body, keywords
+FROM articles
+WHERE keywords = 'foo bar' OR body <> ''
+GROUP BY body;
+ERROR:  column "articles.keywords" must appear in the GROUP BY clause or be used in an aggregate function
+LINE 1: SELECT body, keywords
+                     ^
+-- not equality (fail)
+SELECT body, keywords
+FROM articles
+WHERE keywords > 'foo bar'
+GROUP BY body;
+ERROR:  column "articles.keywords" must appear in the GROUP BY clause or be used in an aggregate function
+LINE 1: SELECT body, keywords
+                     ^
+-- base case with joins (fail)
+SELECT a.body, a.keywords, aic.category_id
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
+GROUP BY a.body, aic.category_id;
+ERROR:  column "a.keywords" must appear in the GROUP BY clause or be used in an aggregate function
+LINE 1: SELECT a.body, a.keywords, aic.category_id
+                       ^
+-- OK
+SELECT a.body, a.keywords, aic.category_id
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
+WHERE a.keywords = 'foo bar'
+GROUP BY a.body, aic.category_id;
+ body | keywords | category_id 
+------+----------+-------------
+(0 rows)
+
+-- OK
+SELECT a.body, a.keywords, aic.category_id
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id AND a.keywords = 'foo bar'
+GROUP BY a.body, aic.category_id;
+ body | keywords | category_id 
+------+----------+-------------
+(0 rows)
+
+-- fail
+SELECT a.body, a.keywords, aic.category_id
+FROM articles AS a LEFT JOIN articles_in_category AS aic ON a.id = aic.article_id AND a.keywords = 'foo bar'
+GROUP BY a.body, aic.category_id;
+ERROR:  column "a.keywords" must appear in the GROUP BY clause or be used in an aggregate function
+LINE 1: SELECT a.body, a.keywords, aic.category_id
+                       ^
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 7529777..191d1fe 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -84,7 +84,7 @@ test: rules
 # ----------
 # Another group of parallel tests
 # ----------
-test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap
+test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps
 
 # ----------
 # Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 5f185f9..e38d5f0 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -76,6 +76,7 @@ test: union
 test: case
 test: join
 test: aggregates
+test: functional_deps
 test: transactions
 ignore: random
 test: random
diff --git a/src/test/regress/sql/functional_deps.sql b/src/test/regress/sql/functional_deps.sql
new file mode 100644
index 0000000..9272878
--- /dev/null
+++ b/src/test/regress/sql/functional_deps.sql
@@ -0,0 +1,193 @@
+-- from http://www.depesz.com/index.php/2010/04/19/getting-unique-elements/
+
+CREATE TABLE articles (
+    id int PRIMARY KEY,
+    keywords text,
+    title text UNIQUE NOT NULL,
+    body text UNIQUE,
+    created date
+);
+
+CREATE TABLE articles_in_category (
+    article_id int,
+    category_id int,
+    changed date,
+    PRIMARY KEY (article_id, category_id)
+);
+
+-- test functional dependencies based on primary keys/unique constraints
+
+-- base tables
+
+-- group by primary key (OK)
+SELECT id, keywords, title, body, created
+FROM articles
+GROUP BY id;
+
+-- group by unique not null (OK)
+SELECT id, keywords, title, body, created
+FROM articles
+GROUP BY title;
+
+-- group by unique nullable (fail)
+SELECT id, keywords, title, body, created
+FROM articles
+GROUP BY body;
+
+-- group by something else (fail)
+SELECT id, keywords, title, body, created
+FROM articles
+GROUP BY keywords;
+
+-- multiple tables
+
+-- group by primary key (OK)
+SELECT a.id, a.keywords, a.title, a.body, a.created
+FROM articles AS a, articles_in_category AS aic
+WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138)
+GROUP BY a.id;
+
+-- group by something else (fail)
+SELECT a.id, a.keywords, a.title, a.body, a.created
+FROM articles AS a, articles_in_category AS aic
+WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138)
+GROUP BY aic.article_id, aic.category_id;
+
+-- JOIN syntax
+
+-- group by left table's primary key (OK)
+SELECT a.id, a.keywords, a.title, a.body, a.created
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
+WHERE aic.category_id in (14,62,70,53,138)
+GROUP BY a.id;
+
+-- group by something else (fail)
+SELECT a.id, a.keywords, a.title, a.body, a.created
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
+WHERE aic.category_id in (14,62,70,53,138)
+GROUP BY aic.article_id, aic.category_id;
+
+-- group by right table's (composite) primary key (OK)
+SELECT aic.changed
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
+WHERE aic.category_id in (14,62,70,53,138)
+GROUP BY aic.category_id, aic.article_id;
+
+-- group by right table's partial primary key (fail)
+SELECT aic.changed
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
+WHERE aic.category_id in (14,62,70,53,138)
+GROUP BY aic.article_id;
+
+
+-- example from documentation
+
+CREATE TABLE products (product_id int, name text, price numeric);
+CREATE TABLE sales (product_id int, units int);
+
+-- OK
+SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
+    FROM products p LEFT JOIN sales s USING (product_id)
+    GROUP BY product_id, p.name, p.price;
+
+-- fail
+SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
+    FROM products p LEFT JOIN sales s USING (product_id)
+    GROUP BY product_id;
+
+ALTER TABLE products ADD PRIMARY KEY (product_id);
+
+-- OK now
+SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
+    FROM products p LEFT JOIN sales s USING (product_id)
+    GROUP BY product_id;
+
+
+-- Drupal example, http://drupal.org/node/555530
+
+CREATE TABLE node (
+    nid SERIAL,
+    vid integer NOT NULL default '0',
+    type varchar(32) NOT NULL default '',
+    title varchar(128) NOT NULL default '',
+    uid integer NOT NULL default '0',
+    status integer NOT NULL default '1',
+    created integer NOT NULL default '0',
+    -- snip
+    PRIMARY KEY (nid, vid)
+);
+
+CREATE TABLE users (
+    uid integer NOT NULL default '0',
+    name varchar(60) NOT NULL default '',
+    pass varchar(32) NOT NULL default '',
+    -- snip
+    PRIMARY KEY (uid),
+    UNIQUE (name)
+);
+
+-- OK
+SELECT u.uid, u.name FROM node n
+INNER JOIN users u ON u.uid = n.uid
+WHERE n.type = 'blog' AND n.status = 1
+GROUP BY u.uid, u.name;
+
+-- OK
+SELECT u.uid, u.name FROM node n
+INNER JOIN users u ON u.uid = n.uid
+WHERE n.type = 'blog' AND n.status = 1
+GROUP BY u.uid;
+
+
+-- test functional dependencies based on WHERE clause
+
+-- var = const (OK)
+SELECT body, keywords
+FROM articles
+WHERE keywords = 'foo bar'
+GROUP BY body;
+
+-- const = var (OK)
+SELECT body, keywords
+FROM articles
+WHERE 'foo bar' = keywords
+GROUP BY body;
+
+-- nested in AND (OK)
+SELECT body, keywords
+FROM articles
+WHERE keywords = 'foo bar' AND body <> ''
+GROUP BY body;
+
+-- nested in OR (fail)
+SELECT body, keywords
+FROM articles
+WHERE keywords = 'foo bar' OR body <> ''
+GROUP BY body;
+
+-- not equality (fail)
+SELECT body, keywords
+FROM articles
+WHERE keywords > 'foo bar'
+GROUP BY body;
+
+-- base case with joins (fail)
+SELECT a.body, a.keywords, aic.category_id
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
+GROUP BY a.body, aic.category_id;
+
+-- OK
+SELECT a.body, a.keywords, aic.category_id
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id
+WHERE a.keywords = 'foo bar'
+GROUP BY a.body, aic.category_id;
+
+-- OK
+SELECT a.body, a.keywords, aic.category_id
+FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id AND a.keywords = 'foo bar'
+GROUP BY a.body, aic.category_id;
+
+-- fail
+SELECT a.body, a.keywords, aic.category_id
+FROM articles AS a LEFT JOIN articles_in_category AS aic ON a.id = aic.article_id AND a.keywords = 'foo bar'
+GROUP BY a.body, aic.category_id;