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 product_id, p.name, and p.price must be in the 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 s.units does not have to be in the GROUP BY list since it is only used in an aggregate expression (sum(...)), which represents the sales @@ -898,6 +895,18 @@ SELECT product_id, p.name, (sum(s.units) * p.price) AS sales + If the products table is set up so that, + say, product_id is the primary key or a + not-null unique constraint, then it would be enough to group + by product_id in the above example, since name + and price would be functionally + dependentfunctional + dependency on the product ID, and so there + would be no ambiguity about which name and price value to return + for each product ID group. + + + In strict SQL, GROUP BY can only group by columns of the source table but PostgreSQL extends this to also allow 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 expression [, ...] produces a single value computed across all the selected rows). When GROUP BY is present, it is not valid for the SELECT 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 WHERE 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. 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;