On mån, 2010-06-07 at 21:33 +0300, Peter Eisentraut wrote:
> I have developed a patch that partially implements the "functional
> dependency" feature that allows some columns to be omitted from the
> GROUP BY clause if it can be shown that the columns are functionally
> dependent on the columns in the group by clause and therefore
> guaranteed to be unique per group.
Second version:
I stripped out all checks except the primary key/unique constraint
checks.
Views whose existence depends on one of those constraints get a
dependency recorded. This depends on the patch currently in the commit
fest to record not null constraints in pg_constraint, so that the
dependencies on not-null constraints can be recorded.
I haven't done any caching of index lookups yet. Some testing with
1600-column tables didn't show any effect. I'll test this a little
more.
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 74021e8..1d02472 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -520,8 +520,12 @@ 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
+ 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.
</para>
</refsect2>
diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c
index d7a06bc..dae51ea 100644
--- a/src/backend/commands/view.c
+++ b/src/backend/commands/view.c
@@ -16,6 +16,7 @@
#include "access/heapam.h"
#include "access/xact.h"
+#include "catalog/dependency.h"
#include "catalog/namespace.h"
#include "commands/defrem.h"
#include "commands/tablecmds.h"
@@ -474,6 +475,27 @@ DefineView(ViewStmt *stmt, const char *queryString)
*/
CommandCounterIncrement();
+ if (list_length(viewParse->dependencies) > 0)
+ {
+ ObjectAddress myself, referenced;
+ ListCell *lc;
+
+ myself.classId = RelationRelationId;
+ myself.objectId = viewOid;
+ myself.objectSubId = 0;
+
+ foreach(lc, viewParse->dependencies)
+ {
+ Oid index_relid = lfirst_oid(lc);
+
+ referenced.classId = RelationRelationId;
+ referenced.objectId = index_relid;
+ referenced.objectSubId = 0;
+
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+ }
+ }
+
/*
* The range table of 'viewParse' does not contain entries for the "OLD"
* and "NEW" relations. So... add them!
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index e770e89..948df43 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2253,6 +2253,7 @@ _copyQuery(Query *from)
COPY_NODE_FIELD(limitCount);
COPY_NODE_FIELD(rowMarks);
COPY_NODE_FIELD(setOperations);
+ COPY_NODE_FIELD(dependencies);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 5d83727..98a1249 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -877,6 +877,7 @@ _equalQuery(Query *a, Query *b)
COMPARE_NODE_FIELD(limitCount);
COMPARE_NODE_FIELD(rowMarks);
COMPARE_NODE_FIELD(setOperations);
+ COMPARE_NODE_FIELD(dependencies);
return true;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index e7dae4b..ff6d0ad 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2006,6 +2006,7 @@ _outQuery(StringInfo str, Query *node)
WRITE_NODE_FIELD(limitCount);
WRITE_NODE_FIELD(rowMarks);
WRITE_NODE_FIELD(setOperations);
+ WRITE_NODE_FIELD(dependencies);
}
static void
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index bc6e2a6..79edcce 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -218,6 +218,7 @@ _readQuery(void)
READ_NODE_FIELD(limitCount);
READ_NODE_FIELD(rowMarks);
READ_NODE_FIELD(setOperations);
+ READ_NODE_FIELD(dependencies);
READ_DONE();
}
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 0a69bde..3f66888 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 funcdeps_check_pk(List *group_clauses, Oid relid, Index rteno, Oid *index_relid);
/*
@@ -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;
@@ -617,6 +623,19 @@ check_ungrouped_columns_walker(Node *node,
gvar->varlevelsup == 0)
return false; /* acceptable, we're okay */
}
+
+ /* Check whether primary key of var's table is subset of group clauses. */
+ rte = rt_fetch(var->varno, context->pstate->p_rtable);
+ if (rte->rtekind == RTE_RELATION)
+ {
+ Oid index_relid;
+
+ if (funcdeps_check_pk(context->groupClauses, rte->relid, var->varno, &index_relid))
+ {
+ context->qry->dependencies = lappend_oid(context->qry->dependencies, index_relid);
+ return false;
+ }
+ }
}
/* Found an ungrouped local variable; generate error message */
@@ -656,6 +675,100 @@ 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, Oid *index_relid)
+{
+ 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);
+ *index_relid = indexoid;
+ return true;
+ }
+ }
+
+ heap_close(rel, NoLock);
+
+ 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/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index b591073..746f4ca 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -146,6 +146,9 @@ typedef struct Query
Node *setOperations; /* set-operation tree if this is top level of
* a UNION/INTERSECT/EXCEPT query */
+ List *dependencies; /* list of index OIDs that are
+ * required for the query to be
+ * valid */
} Query;
diff --git a/src/test/regress/expected/functional_deps.out b/src/test/regress/expected/functional_deps.out
new file mode 100644
index 0000000..87db5cb
--- /dev/null
+++ b/src/test/regress/expected/functional_deps.out
@@ -0,0 +1,192 @@
+-- from http://www.depesz.com/index.php/2010/04/19/getting-unique-elements/
+CREATE TABLE articles (
+ id int CONSTRAINT articles_pkey 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)
+
+-- views and dependencies
+-- fail
+CREATE VIEW fdv1 AS
+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 2: SELECT id, keywords, title, body, created
+ ^
+-- OK
+CREATE VIEW fdv1 AS
+SELECT id, keywords, title, body, created
+FROM articles
+GROUP BY id;
+-- fail
+ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT;
+ERROR: cannot drop constraint articles_pkey on table articles because other objects depend on it
+DETAIL: view fdv1 depends on index articles_pkey
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
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..5f0aaf8
--- /dev/null
+++ b/src/test/regress/sql/functional_deps.sql
@@ -0,0 +1,157 @@
+-- from http://www.depesz.com/index.php/2010/04/19/getting-unique-elements/
+
+CREATE TABLE articles (
+ id int CONSTRAINT articles_pkey 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;
+
+
+-- views and dependencies
+
+-- fail
+CREATE VIEW fdv1 AS
+SELECT id, keywords, title, body, created
+FROM articles
+GROUP BY body;
+
+-- OK
+CREATE VIEW fdv1 AS
+SELECT id, keywords, title, body, created
+FROM articles
+GROUP BY id;
+
+-- fail
+ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
