From a1627a1d64da72723d61e1ff033bf00ea05a82d0 Mon Sep 17 00:00:00 2001 From: "Marc G. Fournier" Date: Wed, 5 Aug 1998 04:49:19 +0000 Subject: [PATCH] From: David Hartwig I have attached a patch to allow GROUP BY and/or ORDER BY function or expressions. Note worthy items: 1. The expression or function need not be in the target list. Example: SELECT name FROM foo GROUP BY lower(name); 2. Simplified the grammar to use expressions only. 3. Cleaned up earlier patch in this area to make use of existing utility functions. 3. Reduced some of the members in the SortGroupBy parse node. The original data members were redundant with the new expression node. (MUST do a "make clean" now) 4. Added a new parse node "JoinUsing". The JOIN USING clause was overloading this SortGroupBy structure. With the afore mentioned reduction of members, the two clauses lost all their commonality. 5. A bug still exist where, if a function or expression is GROUPed BY, and an aggregate function does not include a attribute from the expression or function, the backend crashes. (or something like that) The bug pre-dates this patch. Example: SELECT lower(a) AS lowcase, count(b) FROM foo GROUP BY lowcase; *** BOOM *** --Also when not in target list SELECT count(b) FROM foo GROUP BY lower(a); *** BOOM AGAIN *** --- src/backend/parser/gram.y | 81 ++------- src/backend/parser/parse_clause.c | 248 ++++++++++++++++---------- src/backend/parser/parse_target.c | 25 +-- src/include/nodes/nodes.h | 5 +- src/include/nodes/parsenodes.h | 17 +- src/include/parser/parse_target.h | 7 +- src/test/regress/expected/select_implicit.out | 168 +++++++++++++++-- src/test/regress/sql/select_implicit.sql | 79 +++++++- 8 files changed, 435 insertions(+), 195 deletions(-) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index db4c9344d4..6f4854c395 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.20 1998/08/04 17:37:48 momjian Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.21 1998/08/05 04:49:08 scrappy Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -103,6 +103,7 @@ Oid param_type(int t); /* used in parse_expr.c */ DefElem *defelt; ParamString *param; SortGroupBy *sortgroupby; + JoinUsing *joinusing; IndexElem *ielem; RangeVar *range; RelExpr *relexp; @@ -162,7 +163,7 @@ Oid param_type(int t); /* used in parse_expr.c */ sort_clause, sortby_list, index_params, index_list, name_list, from_clause, from_list, opt_array_bounds, nest_array_bounds, expr_list, attrs, res_target_list, res_target_list2, - def_list, opt_indirection, group_clause, groupby_list, TriggerFuncArgs + def_list, opt_indirection, group_clause, TriggerFuncArgs %type func_return %type set_opt @@ -171,7 +172,7 @@ Oid param_type(int t); /* used in parse_expr.c */ %type union_clause, select_list %type join_list -%type +%type join_using %type opt_union %type opt_table @@ -211,7 +212,6 @@ Oid param_type(int t); /* used in parse_expr.c */ %type CreateAsElement %type NumericOnly, FloatOnly, IntegerOnly %type event_object, attr -%type groupby %type sortby %type index_elem, func_index %type from_val @@ -2517,28 +2517,10 @@ sortby_list: sortby { $$ = lcons($1, NIL); } | sortby_list ',' sortby { $$ = lappend($1, $3); } ; -sortby: ColId OptUseOp +sortby: a_expr OptUseOp { $$ = makeNode(SortGroupBy); - $$->resno = 0; - $$->range = NULL; - $$->name = $1; - $$->useOp = $2; - } - | ColId '.' ColId OptUseOp - { - $$ = makeNode(SortGroupBy); - $$->resno = 0; - $$->range = $1; - $$->name = $3; - $$->useOp = $4; - } - | Iconst OptUseOp - { - $$ = makeNode(SortGroupBy); - $$->resno = $1; - $$->range = NULL; - $$->name = NULL; + $$->node = $1; $$->useOp = $2; } ; @@ -2570,40 +2552,10 @@ name_list: name { $$ = lappend($1,makeString($3)); } ; -group_clause: GROUP BY groupby_list { $$ = $3; } +group_clause: GROUP BY expr_list { $$ = $3; } | /*EMPTY*/ { $$ = NIL; } ; -groupby_list: groupby { $$ = lcons($1, NIL); } - | groupby_list ',' groupby { $$ = lappend($1, $3); } - ; - -groupby: ColId - { - $$ = makeNode(SortGroupBy); - $$->resno = 0; - $$->range = NULL; - $$->name = $1; - $$->useOp = NULL; - } - | ColId '.' ColId - { - $$ = makeNode(SortGroupBy); - $$->resno = 0; - $$->range = $1; - $$->name = $3; - $$->useOp = NULL; - } - | Iconst - { - $$ = makeNode(SortGroupBy); - $$->resno = $1; - $$->range = NULL; - $$->name = NULL; - $$->useOp = NULL; - } - ; - having_clause: HAVING a_expr { $$ = $2; @@ -2688,28 +2640,33 @@ join_list: join_using { $$ = lcons($1, NIL); } ; join_using: ColId - { - $$ = makeNode(SortGroupBy); + /* Changed from SortGroupBy parse node to new JoinUsing node. + * SortGroupBy no longer needs these structure members. + * + * Once, acknowledged, this comment can be removed by the + * developer(s) working on the JOIN clause. + * + * - daveh@insightdist.com 1998-07-31 + */ + { + $$ = makeNode(JoinUsing); $$->resno = 0; $$->range = NULL; $$->name = $1; - $$->useOp = NULL; } | ColId '.' ColId { - $$ = makeNode(SortGroupBy); + $$ = makeNode(JoinUsing); $$->resno = 0; $$->range = $1; $$->name = $3; - $$->useOp = NULL; } | Iconst { - $$ = makeNode(SortGroupBy); + $$ = makeNode(JoinUsing); $$->resno = $1; $$->range = NULL; $$->name = NULL; - $$->useOp = NULL; } ; diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 0e122999cb..98eecb319a 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -7,7 +7,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/parse_clause.c,v 1.22 1998/08/02 13:34:26 thomas Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/parse_clause.c,v 1.23 1998/08/05 04:49:09 scrappy Exp $ * *------------------------------------------------------------------------- */ @@ -28,9 +28,15 @@ #include "parser/parse_coerce.h" + +#define ORDER_CLAUSE 0 +#define GROUP_CLAUSE 1 + +static char *clauseText[] = {"ORDER", "GROUP"}; + static TargetEntry * -find_targetlist_entry(ParseState *pstate, - SortGroupBy *sortgroupby, List *tlist); +findTargetlistEntry(ParseState *pstate, Node *node, List *tlist, int clause); + static void parseFromClause(ParseState *pstate, List *frmList); @@ -65,7 +71,7 @@ makeRangeTable(ParseState *pstate, char *relname, List *frmList) /* * transformWhereClause - * transforms the qualification and make sure it is of type Boolean - * + * */ Node * transformWhereClause(ParseState *pstate, Node *a_expr) @@ -128,130 +134,182 @@ parseFromClause(ParseState *pstate, List *frmList) } /* - * find_targetlist_entry - + * findTargetlistEntry - * returns the Resdom in the target list matching the specified varname - * and range + * and range. If none exist one is created. + * + * Rewritten for ver 6.4 to handle expressions in the GROUP/ORDER BY clauses. + * - daveh@insightdist.com 1998-07-31 * */ static TargetEntry * -find_targetlist_entry(ParseState *pstate, SortGroupBy *sortgroupby, List *tlist) +findTargetlistEntry(ParseState *pstate, Node *node, List *tlist, int clause) { - List *i; - int real_rtable_pos = 0, - target_pos = 0; + List *l; + int rtable_pos = 0, + target_pos = 0, + targetlist_pos = 0; TargetEntry *target_result = NULL; + Value *val = NULL; + char *relname = NULL; + char *name = NULL; + Node *expr = NULL; + int relCnt = 0; + + /* Pull out some values before looping thru target list */ + switch(nodeTag(node)) + { + case T_Attr: + relname = ((Attr*)node)->relname; + val = (Value *)lfirst(((Attr*)node)->attrs); + name = strVal(val); + rtable_pos = refnameRangeTablePosn(pstate, relname, NULL); + relCnt = length(pstate->p_rtable); + break; + + case T_Ident: + name = ((Ident*)node)->name; + relCnt = length(pstate->p_rtable); + break; + + case T_A_Const: + val = &((A_Const*)node)->val; + + if (nodeTag(val) != T_Integer) + elog(ERROR, "Illegal Constant in %s BY", clauseText[clause]); + target_pos = intVal(val); + break; + + case T_FuncCall: + case T_A_Expr: + expr = transformExpr(pstate, node, EXPR_COLUMN_FIRST); + break; + + default: + elog(ERROR, "Illegal %s BY node = %d", clauseText[clause], nodeTag(node)); + } - if (sortgroupby->range != NULL) - real_rtable_pos = refnameRangeTablePosn(pstate, sortgroupby->range, NULL); - - foreach(i, tlist) + /* + * Loop through target entries and try to match to node + */ + foreach(l, tlist) { - TargetEntry *target = (TargetEntry *) lfirst(i); + TargetEntry *target = (TargetEntry *) lfirst(l); Resdom *resnode = target->resdom; Var *var = (Var *) target->expr; char *resname = resnode->resname; int test_rtable_pos = var->varno; - /* no name specified? then must have been a column number instead... */ - if (sortgroupby->name == NULL) + ++targetlist_pos; + + switch(nodeTag(node)) { - if (sortgroupby->resno == ++target_pos) + case T_Attr: + if (strcmp(resname, name) == 0 && rtable_pos == test_rtable_pos) { - target_result = target; - break; + /* Check for only 1 table & ORDER BY -ambiguity does not matter here */ + if (clause == ORDER_CLAUSE && relCnt == 1) + return target; + + if (target_result != NULL) + elog(ERROR, "%s BY '%s' is ambiguous", clauseText[clause], name); + else + target_result = target; + /* Stay in loop to check for ambiguity */ } - } - /* otherwise, try to match name... */ - else - { - /* same name? */ - if (strcmp(resname, sortgroupby->name) == 0) + break; + + case T_Ident: + if (strcmp(resname, name) == 0) { - if (sortgroupby->range != NULL) - { - if (real_rtable_pos == test_rtable_pos) - { - if (target_result != NULL) - elog(ERROR, "ORDER/GROUP BY '%s' is ambiguous", sortgroupby->name); - else - target_result = target; - } - } + /* Check for only 1 table & ORDER BY -ambiguity does not matter here */ + if (clause == ORDER_CLAUSE && relCnt == 1) + return target; + + if (target_result != NULL) + elog(ERROR, "%s BY '%s' is ambiguous", clauseText[clause], name); else - { - if (target_result != NULL) - elog(ERROR, "ORDER/GROUP BY '%s' is ambiguous", sortgroupby->name); - else - target_result = target; - } + target_result = target; + /* Stay in loop to check for ambiguity */ } - } - } + break; + case T_A_Const: + if (target_pos == targetlist_pos) + { + /* Can't be ambigious and we got what we came for */ + return target; + } + break; - /* No name specified and no target found? - * Then must have been an out-of-range column number instead... - * - thomas 1998-07-09 - */ - if ((sortgroupby->name == NULL) && (target_result == NULL)) - { - elog(ERROR, "ORDER/GROUP BY position %d is not in target list", - sortgroupby->resno); - } + case T_FuncCall: + case T_A_Expr: + if (equal(expr, target->expr)) + { + /* Check for only 1 table & ORDER BY -ambiguity does not matter here */ + if (clause == ORDER_CLAUSE) + return target; + + if (target_result != NULL) + elog(ERROR, "GROUP BY has ambiguous expression"); + else + target_result = target; + } + break; + default: + elog(ERROR, "Illegal %s BY node = %d", clauseText[clause], nodeTag(node)); + } + } - /* BEGIN add missing target entry hack. - * - * Prior to this hack, this function returned NIL if no target_result. - * Thus, ORDER/GROUP BY required the attributes be in the target list. - * Now it constructs a new target entry which is appended to the end of - * the target list. This target is set to be resjunk = TRUE so that + /* + * If no matches, construct a new target entry which is appended to the end + * of the target list. This target is set to be resjunk = TRUE so that * it will not be projected into the final tuple. - * daveh@insightdist.com 5/20/98 */ - if ((target_result == NULL) && (sortgroupby->name != NULL)) { - - List *p_target = tlist; - TargetEntry *tent = makeNode(TargetEntry); - - if (sortgroupby->range != NULL) { - Attr *missingAttr = (Attr *)makeNode(Attr); - missingAttr->type = T_Attr; - - missingAttr->relname = palloc(strlen(sortgroupby->range) + 1); - strcpy(missingAttr->relname, sortgroupby->range); - - missingAttr->attrs = lcons(makeString(sortgroupby->name), NIL); - - tent = transformTargetIdent(pstate, (Node *)missingAttr, tent, - &missingAttr->relname, NULL, - missingAttr->relname, TRUE); - } - else + if (target_result == NULL) + { + switch(nodeTag(node)) { - Ident *missingIdent = (Ident *)makeNode(Ident); - missingIdent->type = T_Ident; + case T_Attr: + target_result = transformTargetIdent(pstate, node, makeNode(TargetEntry), + &((Attr*)node)->relname, NULL, + ((Attr*)node)->relname, TRUE); + lappend(tlist, target_result); + break; - missingIdent->name = palloc(strlen(sortgroupby->name) + 1); - strcpy(missingIdent->name, sortgroupby->name); + case T_Ident: + target_result = transformTargetIdent(pstate, node, makeNode(TargetEntry), + &((Ident*)node)->name, NULL, + ((Ident*)node)->name, TRUE); + lappend(tlist, target_result); + break; - tent = transformTargetIdent(pstate, (Node *)missingIdent, tent, - &missingIdent->name, NULL, - missingIdent->name, TRUE); - } + case T_A_Const: + /* + * If we got this far, then must have been an out-of-range column number + */ + elog(ERROR, "%s BY position %d is not in target list", clauseText[clause], target_pos); + break; + + case T_FuncCall: + case T_A_Expr: + target_result = MakeTargetlistExpr(pstate, "resjunk", expr, FALSE, TRUE); + lappend(tlist, target_result); + break; - /* Add to the end of the target list */ - while (lnext(p_target) != NIL) { - p_target = lnext(p_target); + default: + elog(ERROR, "Illegal %s BY node = %d", clauseText[clause], nodeTag(node)); + break; } - lnext(p_target) = lcons(tent, NIL); - target_result = tent; } - /* END add missing target entry hack. */ return target_result; } + + + /* * transformGroupClause - * transform a Group By clause @@ -269,7 +327,7 @@ transformGroupClause(ParseState *pstate, List *grouplist, List *targetlist) TargetEntry *restarget; Resdom *resdom; - restarget = find_targetlist_entry(pstate, lfirst(grouplist), targetlist); + restarget = findTargetlistEntry(pstate, lfirst(grouplist), targetlist, GROUP_CLAUSE); grpcl->entry = restarget; resdom = restarget->resdom; @@ -328,7 +386,7 @@ printf("transformSortClause: entering\n"); TargetEntry *restarget; Resdom *resdom; - restarget = find_targetlist_entry(pstate, sortby, targetlist); + restarget = findTargetlistEntry(pstate, sortby->node, targetlist, ORDER_CLAUSE); #ifdef PARSEDEBUG printf("transformSortClause: find sorting operator for type %d\n", diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 7de957935d..58dd3a28b3 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -7,7 +7,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/parse_target.c,v 1.19 1998/07/20 19:53:52 momjian Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/parse_target.c,v 1.20 1998/08/05 04:49:11 scrappy Exp $ * *------------------------------------------------------------------------- */ @@ -32,11 +32,7 @@ static List *ExpandAllTables(ParseState *pstate); static char *FigureColname(Node *expr, Node *resval); -static TargetEntry * -MakeTargetlistExpr(ParseState *pstate, - char *colname, - Node *expr, - List *arrayRef); + Node * SizeTargetExpr(ParseState *pstate, Node *expr, @@ -129,7 +125,7 @@ printf("transformTargetIdent- transform type %d to %d\n", { expr = coerce_type(pstate, node, attrtype_id, attrtype_target); expr = transformExpr(pstate, expr, EXPR_COLUMN_FIRST); - tent = MakeTargetlistExpr(pstate, *resname, expr, FALSE); + tent = MakeTargetlistExpr(pstate, *resname, expr, FALSE, FALSE); expr = tent->expr; } else @@ -293,7 +289,7 @@ printf("transformTargetList: decode T_Expr\n"); constval->val.str = save_str; tent = MakeTargetlistExpr(pstate, res->name, (Node *) make_const(constval), - NULL); + NULL, FALSE); pfree(save_str); } else @@ -326,7 +322,7 @@ printf("transformTargetList: decode T_Expr\n"); } res->name = colname; tent = MakeTargetlistExpr(pstate, res->name, expr, - res->indirection); + res->indirection, FALSE); } break; } @@ -570,12 +566,17 @@ printf("SizeTargetExpr: no conversion function for sizing\n"); * For type mismatches between expressions and targets, use the same * techniques as for function and operator type coersion. * - thomas 1998-05-08 + * + * Added resjunk flag and made extern so that it can be use by GROUP/ + * ORDER BY a function or expersion not in the target_list + * - daveh@insightdist.com 1998-07-31 */ -static TargetEntry * +TargetEntry * MakeTargetlistExpr(ParseState *pstate, char *colname, Node *expr, - List *arrayRef) + List *arrayRef, + int16 resjunk) { Oid type_id, attrtype; @@ -698,7 +699,7 @@ printf("MakeTargetlistExpr: attrtypmod is %d\n", (int4) attrtypmod); colname, (Index) 0, (Oid) 0, - 0); + resjunk); tent = makeTargetEntry(resnode, expr); diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 1a172d83e0..a73907f257 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -6,7 +6,7 @@ * * Copyright (c) 1994, Regents of the University of California * - * $Id: nodes.h,v 1.25 1998/07/18 04:22:45 momjian Exp $ + * $Id: nodes.h,v 1.26 1998/08/05 04:49:12 scrappy Exp $ * *------------------------------------------------------------------------- */ @@ -211,7 +211,8 @@ typedef enum NodeTag T_RangeTblEntry, T_SortClause, T_GroupClause, - T_SubSelect + T_SubSelect, + T_JoinUsing } NodeTag; /* diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index bb6a657c0b..730a4acde8 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -6,7 +6,7 @@ * * Copyright (c) 1994, Regents of the University of California * - * $Id: parsenodes.h,v 1.52 1998/07/26 04:31:29 scrappy Exp $ + * $Id: parsenodes.h,v 1.53 1998/08/05 04:49:13 scrappy Exp $ * *------------------------------------------------------------------------- */ @@ -648,7 +648,7 @@ typedef struct TypeName char *name; /* name of the type */ bool timezone; /* timezone specified? */ bool setof; /* is a set? */ - int32 typmod; /* type modifier */ + int16 typmod; /* type modifier */ List *arrayBounds; /* array bounds */ } TypeName; @@ -789,11 +789,20 @@ typedef struct RelExpr typedef struct SortGroupBy { NodeTag type; + char *useOp; /* operator to use */ + Node *node; /* Expression */ +} SortGroupBy; + +/* + * JoinUsing - for join using clause + */ +typedef struct JoinUsing +{ + NodeTag type; int resno; /* target number */ char *range; char *name; /* name of column to sort on */ - char *useOp; /* operator to use */ -} SortGroupBy; +} JoinUsing; /* * RangeVar - range variable, used in from clauses diff --git a/src/include/parser/parse_target.h b/src/include/parser/parse_target.h index d270b6803b..b3fc020fde 100644 --- a/src/include/parser/parse_target.h +++ b/src/include/parser/parse_target.h @@ -6,7 +6,7 @@ * * Copyright (c) 1994, Regents of the University of California * - * $Id: parse_target.h,v 1.8 1998/07/08 14:18:45 thomas Exp $ + * $Id: parse_target.h,v 1.9 1998/08/05 04:49:15 scrappy Exp $ * *------------------------------------------------------------------------- */ @@ -35,5 +35,10 @@ transformTargetIdent(ParseState *pstate, extern Node * CoerceTargetExpr(ParseState *pstate, Node *expr, Oid type_id, Oid attrtype); +TargetEntry * MakeTargetlistExpr(ParseState *pstate, + char *colname, + Node *expr, + List *arrayRef, + int16 resjunk); #endif /* PARSE_TARGET_H */ diff --git a/src/test/regress/expected/select_implicit.out b/src/test/regress/expected/select_implicit.out index 42af780a61..22df795676 100644 --- a/src/test/regress/expected/select_implicit.out +++ b/src/test/regress/expected/select_implicit.out @@ -4,28 +4,32 @@ QUERY: INSERT INTO test_missing_target VALUES (1, 2, 'AAAA'); QUERY: INSERT INTO test_missing_target VALUES (2, 2, 'AAAA'); QUERY: INSERT INTO test_missing_target VALUES (3, 3, 'BBBB'); QUERY: INSERT INTO test_missing_target VALUES (4, 3, 'BBBB'); -QUERY: INSERT INTO test_missing_target VALUES (5, 3, 'BBBB'); -QUERY: INSERT INTO test_missing_target VALUES (6, 4, 'CCCC'); -QUERY: INSERT INTO test_missing_target VALUES (7, 4, 'CCCC'); +QUERY: INSERT INTO test_missing_target VALUES (5, 3, 'bbbb'); +QUERY: INSERT INTO test_missing_target VALUES (6, 4, 'cccc'); +QUERY: INSERT INTO test_missing_target VALUES (7, 4, 'cccc'); QUERY: INSERT INTO test_missing_target VALUES (8, 4, 'CCCC'); QUERY: INSERT INTO test_missing_target VALUES (9, 4, 'CCCC'); QUERY: SELECT c, count(*) FROM test_missing_target GROUP BY test_missing_target.c; c |count --------+----- AAAA | 2 -BBBB | 3 -CCCC | 4 +BBBB | 2 +CCCC | 2 XXXX | 1 -(4 rows) +bbbb | 1 +cccc | 2 +(6 rows) QUERY: SELECT count(*) FROM test_missing_target GROUP BY test_missing_target.c; count ----- 2 - 3 - 4 + 2 + 2 1 -(4 rows) + 1 + 2 +(6 rows) QUERY: SELECT count(*) FROM test_missing_target GROUP BY a ORDER BY b; ERROR: parser: illegal use of aggregates or non-group column in target list @@ -56,9 +60,9 @@ AAAA AAAA BBBB BBBB -BBBB -CCCC -CCCC +bbbb +cccc +cccc CCCC CCCC (10 rows) @@ -82,17 +86,54 @@ QUERY: SELECT c, count(*) FROM test_missing_target GROUP BY 1; c |count --------+----- AAAA | 2 -BBBB | 3 -CCCC | 4 +BBBB | 2 +CCCC | 2 XXXX | 1 -(4 rows) +bbbb | 1 +cccc | 2 +(6 rows) QUERY: SELECT c, count(*) FROM test_missing_target GROUP BY 3; -ERROR: ORDER/GROUP BY position 3 is not in target list +ERROR: GROUP BY position 3 is not in target list QUERY: SELECT count(*) FROM test_missing_target x, test_missing_target y WHERE x.a = y.a GROUP BY b ORDER BY b; ERROR: Column b is ambiguous +QUERY: SELECT a, a FROM test_missing_target + ORDER BY a; +a|a +-+- +0|0 +1|1 +2|2 +3|3 +4|4 +5|5 +6|6 +7|7 +8|8 +9|9 +(10 rows) + +QUERY: SELECT a/2, a/2 FROM test_missing_target + ORDER BY a/2; +?column?|?column? +--------+-------- + 0| 0 + 0| 0 + 1| 1 + 1| 1 + 2| 2 + 2| 2 + 3| 3 + 3| 3 + 4| 4 + 4| 4 +(10 rows) + +QUERY: SELECT a/2, a/2 FROM test_missing_target + GROUP BY a/2; +ERROR: GROUP BY has ambiguous expression QUERY: SELECT x.b, count(*) FROM test_missing_target x, test_missing_target y WHERE x.a = y.a GROUP BY x.b; @@ -128,5 +169,100 @@ count 4 (4 rows) +QUERY: SELECT a%2, count(a) FROM test_missing_target GROUP BY test_missing_target.a%2; +?column?|count +--------+----- + 0| 5 + 1| 5 +(2 rows) + +QUERY: /* + NOTE: as of 1998-08-01 a bug was detected unrelated to this feature which + requires the aggragate function argument to be the same as some non-agragate + in the target list. (i.e. count(*) and count(b) crash the backend.) +*/ +SELECT count(c) FROM test_missing_target GROUP BY lower(test_missing_target.c); +count +----- + 2 + 3 + 4 + 1 +(4 rows) + +QUERY: SELECT count(a) FROM test_missing_target GROUP BY a ORDER BY b; +ERROR: parser: illegal use of aggregates or non-group column in target list +QUERY: SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2; +count +----- + 1 + 5 + 4 +(3 rows) + +QUERY: SELECT lower(test_missing_target.c), count(c) + FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c); +lower |count +--------+----- +aaaa | 2 +bbbb | 3 +cccc | 4 +xxxx | 1 +(4 rows) + +QUERY: SELECT a FROM test_missing_target ORDER BY upper(c); +a +- +1 +2 +3 +4 +5 +6 +7 +8 +9 +0 +(10 rows) + +QUERY: SELECT count(b) FROM test_missing_target + GROUP BY (b + 1) / 2 ORDER BY (b + 1) / 2 desc; +count +----- + 7 + 3 +(2 rows) + +QUERY: SELECT count(x.a) FROM test_missing_target x, test_missing_target y + WHERE x.a = y.a + GROUP BY b/2 ORDER BY b/2; +ERROR: Column b is ambiguous +QUERY: SELECT x.b/2, count(x.b) FROM test_missing_target x, test_missing_target y + WHERE x.a = y.a + GROUP BY x.b/2; +?column?|count +--------+----- + 0| 1 + 1| 5 + 2| 4 +(3 rows) + +QUERY: SELECT count(b) FROM test_missing_target x, test_missing_target y + WHERE x.a = y.a + GROUP BY x.b/2; +ERROR: Column b is ambiguous +QUERY: SELECT count(x.b) INTO TABLE test_missing_target3 +FROM test_missing_target x, test_missing_target y + WHERE x.a = y.a + GROUP BY x.b/2; +QUERY: SELECT * FROM test_missing_target3; +count +----- + 1 + 5 + 4 +(3 rows) + QUERY: DROP TABLE test_missing_target; QUERY: DROP TABLE test_missing_target2; +QUERY: DROP TABLE test_missing_target3; diff --git a/src/test/regress/sql/select_implicit.sql b/src/test/regress/sql/select_implicit.sql index 44e06a95b6..a867b9b556 100644 --- a/src/test/regress/sql/select_implicit.sql +++ b/src/test/regress/sql/select_implicit.sql @@ -13,9 +13,9 @@ INSERT INTO test_missing_target VALUES (1, 2, 'AAAA'); INSERT INTO test_missing_target VALUES (2, 2, 'AAAA'); INSERT INTO test_missing_target VALUES (3, 3, 'BBBB'); INSERT INTO test_missing_target VALUES (4, 3, 'BBBB'); -INSERT INTO test_missing_target VALUES (5, 3, 'BBBB'); -INSERT INTO test_missing_target VALUES (6, 4, 'CCCC'); -INSERT INTO test_missing_target VALUES (7, 4, 'CCCC'); +INSERT INTO test_missing_target VALUES (5, 3, 'bbbb'); +INSERT INTO test_missing_target VALUES (6, 4, 'cccc'); +INSERT INTO test_missing_target VALUES (7, 4, 'cccc'); INSERT INTO test_missing_target VALUES (8, 4, 'CCCC'); INSERT INTO test_missing_target VALUES (9, 4, 'CCCC'); @@ -59,6 +59,21 @@ SELECT count(*) FROM test_missing_target x, test_missing_target y WHERE x.a = y.a GROUP BY b ORDER BY b; +-- order w/ target under ambigious condition +-- failure NOT expected +SELECT a, a FROM test_missing_target + ORDER BY a; + +-- order expression w/ target under ambigious condition +-- failure NOT expected +SELECT a/2, a/2 FROM test_missing_target + ORDER BY a/2; + +-- group expression w/ target under ambigious condition +-- failure expected +SELECT a/2, a/2 FROM test_missing_target + GROUP BY a/2; + -- group w/ existing GROUP BY target under ambigious condition SELECT x.b, count(*) FROM test_missing_target x, test_missing_target y WHERE x.a = y.a @@ -77,7 +92,65 @@ FROM test_missing_target x, test_missing_target y GROUP BY x.b; SELECT * FROM test_missing_target2; + +-- Functions and expressions + +-- w/ existing GROUP BY target +SELECT a%2, count(a) FROM test_missing_target GROUP BY test_missing_target.a%2; +/* + NOTE: as of 1998-08-01 a bug was detected unrelated to this feature which + requires the aggragate function argument to be the same as some non-agragate + in the target list. (i.e. count(*) and count(b) crash the backend.) +*/ + +-- w/o existing GROUP BY target using a relation name in GROUP BY clause +SELECT count(c) FROM test_missing_target GROUP BY lower(test_missing_target.c); + +-- w/o existing GROUP BY target and w/o existing a different ORDER BY target +-- failure expected +SELECT count(a) FROM test_missing_target GROUP BY a ORDER BY b; + +-- w/o existing GROUP BY target and w/o existing same ORDER BY target +SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2; + +-- w/ existing GROUP BY target using a relation name in target +SELECT lower(test_missing_target.c), count(c) + FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c); + +-- w/o existing GROUP BY target +SELECT a FROM test_missing_target ORDER BY upper(c); + +-- w/o existing ORDER BY target +SELECT count(b) FROM test_missing_target + GROUP BY (b + 1) / 2 ORDER BY (b + 1) / 2 desc; + +-- group w/o existing GROUP BY and ORDER BY target under ambigious condition +-- failure expected +SELECT count(x.a) FROM test_missing_target x, test_missing_target y + WHERE x.a = y.a + GROUP BY b/2 ORDER BY b/2; + +-- group w/ existing GROUP BY target under ambigious condition +SELECT x.b/2, count(x.b) FROM test_missing_target x, test_missing_target y + WHERE x.a = y.a + GROUP BY x.b/2; + +-- group w/o existing GROUP BY target under ambigious condition +SELECT count(b) FROM test_missing_target x, test_missing_target y + WHERE x.a = y.a + GROUP BY x.b/2; + +-- group w/o existing GROUP BY target under ambigious condition +-- into a table +SELECT count(x.b) INTO TABLE test_missing_target3 +FROM test_missing_target x, test_missing_target y + WHERE x.a = y.a + GROUP BY x.b/2; +SELECT * FROM test_missing_target3; + -- Cleanup DROP TABLE test_missing_target; DROP TABLE test_missing_target2; +DROP TABLE test_missing_target3; + -- 2.11.0