On Mon, Oct 24, 2011 at 20:52, Erik Rijkers <e...@xs4all.nl> wrote: > On Wed, October 19, 2011 15:01, Kerem Kat wrote: >> Adding CORRESPONDING to Set Operations >> Initial patch, filename: corresponding_clause_v2.patch > > I had a quick look at the behaviour of this patch. > > Btw, the examples in your email were typoed (one select is missing): > >> SELECT 1 a, 2 b, 3 c UNION CORRESPONDING 4 b, 5 d, 6 c, 7 f; > should be: > SELECT 1 a, 2 b, 3 c UNION CORRESPONDING select 4 b, 5 d, 6 c, 7 f; > > and > >> SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) 4 b, 5 d, 6 c, 7 f; > should be: > SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) select 4 b, 5 d, 6 c, 7 f; >>
Yes you are correct, mea culpa. > > > > But there is also a small bug, I think: the order in the CORRESPONDING BY > list should be followed, > according to the standard (foundation, p. 408): > > "2) If <corresponding column list> is specified, then let SL be a <select > list> of those <column > name>s explicitly appearing in the <corresponding column list> in the order > that these > <column name>s appear in the <corresponding column list>. Every <column name> > in the > <corresponding column list> shall be a <column name> of both T1 and T2." > > That would make this wrong, I think: > > SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c,b) select 5 d, 6 c, 7 f, 4 b ; > > b | c > ---+--- > 2 | 3 > 4 | 6 > (2 rows) > > i.e., I think it should show columns in the order c, b (and not b, c); the > order of the > CORRESPONDING BY phrase. > > (but maybe I'm misreading the text of the standard; I find it often difficult > to follow) > It wasn't a misread, I checked the draft, in my version same explanation is at p.410. I have corrected the ordering of the targetlists of subqueries. And added 12 regression tests for column list ordering. Can you confirm that the order has changed for you? > > Thanks, > > > Erik Rijkers > > Regards, Kerem KAT
*** a/doc/src/sgml/queries.sgml --- b/doc/src/sgml/queries.sgml *************** *** 1225,1230 **** --- 1225,1233 ---- <primary>EXCEPT</primary> </indexterm> <indexterm zone="queries-union"> + <primary>CORRESPONDING</primary> + </indexterm> + <indexterm zone="queries-union"> <primary>set union</primary> </indexterm> <indexterm zone="queries-union"> *************** *** 1241,1249 **** The results of two queries can be combined using the set operations union, intersection, and difference. The syntax is <synopsis> ! <replaceable>query1</replaceable> UNION <optional>ALL</optional> <replaceable>query2</replaceable> ! <replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <replaceable>query2</replaceable> ! <replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <replaceable>query2</replaceable> </synopsis> <replaceable>query1</replaceable> and <replaceable>query2</replaceable> are queries that can use any of --- 1244,1252 ---- The results of two queries can be combined using the set operations union, intersection, and difference. The syntax is <synopsis> ! <replaceable>query1</replaceable> UNION <optional>ALL</optional> <optional>CORRESPONDING <optional>BY (<replaceable>select_list</replaceable>)</optional></optional> <replaceable>query2</replaceable> ! <replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <optional>CORRESPONDING <optional>BY (<replaceable>select_list</replaceable>)</optional></optional> <replaceable>query2</replaceable> ! <replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <optional>CORRESPONDING <optional>BY (<replaceable>select_list</replaceable>)</optional></optional> <replaceable>query2</replaceable> </synopsis> <replaceable>query1</replaceable> and <replaceable>query2</replaceable> are queries that can use any of *************** *** 1283,1288 **** --- 1286,1299 ---- </para> <para> + <literal>CORRESPONDING</> returns all columns that are in both <replaceable>query1</> and <replaceable>query2</> with the same name. + </para> + + <para> + <literal>CORRESPONDING BY</> returns all columns in the column list that are also in both <replaceable>query1</> and <replaceable>query2</> with the same name. + </para> + + <para> In order to calculate the union, intersection, or difference of two queries, the two queries must be <quote>union compatible</quote>, which means that they return the same number of columns and *** a/doc/src/sgml/sql.sgml --- b/doc/src/sgml/sql.sgml *************** *** 859,865 **** [ WHERE <replaceable class="PARAMETER">condition</replaceable> ] [ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ] [ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ] ! [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="PARAMETER">select</replaceable> ] [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ] [ OFFSET <replaceable class="PARAMETER">start</replaceable> ] --- 859,865 ---- [ WHERE <replaceable class="PARAMETER">condition</replaceable> ] [ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ] [ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ] ! [ { UNION | INTERSECT | EXCEPT } [ ALL ] [ CORRESPONDING [ BY ( <replaceable class="PARAMETER">expression</replaceable> ) ] ] <replaceable class="PARAMETER">select</replaceable> ] [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ] [ OFFSET <replaceable class="PARAMETER">start</replaceable> ] *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** *** 2507,2512 **** --- 2507,2513 ---- COPY_NODE_FIELD(lockingClause); COPY_SCALAR_FIELD(op); COPY_SCALAR_FIELD(all); + COPY_NODE_FIELD(correspondingClause); COPY_NODE_FIELD(larg); COPY_NODE_FIELD(rarg); *************** *** 2522,2527 **** --- 2523,2530 ---- COPY_SCALAR_FIELD(all); COPY_NODE_FIELD(larg); COPY_NODE_FIELD(rarg); + COPY_NODE_FIELD(correspondingColumns); + COPY_SCALAR_FIELD(hasCorrespondingBy); COPY_NODE_FIELD(colTypes); COPY_NODE_FIELD(colTypmods); COPY_NODE_FIELD(colCollations); *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** *** 982,987 **** --- 982,988 ---- COMPARE_NODE_FIELD(lockingClause); COMPARE_SCALAR_FIELD(op); COMPARE_SCALAR_FIELD(all); + COMPARE_NODE_FIELD(correspondingClause); COMPARE_NODE_FIELD(larg); COMPARE_NODE_FIELD(rarg); *************** *** 995,1000 **** --- 996,1003 ---- COMPARE_SCALAR_FIELD(all); COMPARE_NODE_FIELD(larg); COMPARE_NODE_FIELD(rarg); + COMPARE_NODE_FIELD(correspondingColumns); + COMPARE_SCALAR_FIELD(hasCorrespondingBy); COMPARE_NODE_FIELD(colTypes); COMPARE_NODE_FIELD(colTypmods); COMPARE_NODE_FIELD(colCollations); *** a/src/backend/nodes/nodeFuncs.c --- b/src/backend/nodes/nodeFuncs.c *************** *** 2894,2899 **** --- 2894,2901 ---- return true; if (walker(stmt->lockingClause, context)) return true; + if (walker(stmt->correspondingClause, context)) + return true; if (walker(stmt->larg, context)) return true; if (walker(stmt->rarg, context)) *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** *** 2032,2037 **** --- 2032,2038 ---- WRITE_NODE_FIELD(lockingClause); WRITE_ENUM_FIELD(op, SetOperation); WRITE_BOOL_FIELD(all); + WRITE_NODE_FIELD(correspondingClause); WRITE_NODE_FIELD(larg); WRITE_NODE_FIELD(rarg); } *************** *** 2296,2301 **** --- 2297,2304 ---- WRITE_BOOL_FIELD(all); WRITE_NODE_FIELD(larg); WRITE_NODE_FIELD(rarg); + WRITE_NODE_FIELD(correspondingColumns); + WRITE_BOOL_FIELD(hasCorrespondingBy); WRITE_NODE_FIELD(colTypes); WRITE_NODE_FIELD(colTypmods); WRITE_NODE_FIELD(colCollations); *** a/src/backend/nodes/readfuncs.c --- b/src/backend/nodes/readfuncs.c *************** *** 342,347 **** --- 342,349 ---- READ_BOOL_FIELD(all); READ_NODE_FIELD(larg); READ_NODE_FIELD(rarg); + READ_NODE_FIELD(correspondingColumns); + READ_BOOL_FIELD(hasCorrespondingBy); READ_NODE_FIELD(colTypes); READ_NODE_FIELD(colTypmods); READ_NODE_FIELD(colCollations); *** a/src/backend/parser/analyze.c --- b/src/backend/parser/analyze.c *************** *** 54,59 **** --- 54,62 ---- static Query *transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt); static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt, bool isTopLevel, List **targetlist); + static SelectStmt *createSubqueryForCorresponding(List* outputColumns, + SelectStmt* main_arg); + static List *determineMatchingColumns(List *ltargetlist, List *rtargetlist); static void determineRecursiveColTypes(ParseState *pstate, Node *larg, List *nrtargetlist); static void applyColumnNames(List *dst, List *src); *************** *** 1665,1670 **** --- 1668,1875 ---- &rtargetlist); /* + * If CORRESPONDING is specified, syntax and column name validities checked, + * column filtering is done by a subquery later on. + */ + if(stmt->correspondingClause == NIL) + { + // No CORRESPONDING clause, no operation needed for column filtering. + op->correspondingColumns = stmt->correspondingClause; + op->hasCorrespondingBy = false; + } + else if(linitial(stmt->correspondingClause) == NULL) + { + // CORRESPONDING clause, find matching column names from both tables. If there are none then it is a syntax error. + + Query *largQuery; + Query *rargQuery; + List *matchingColumns; + + /* Analyze left query to resolve column names. */ + largQuery = parse_sub_analyze((Node *) stmt->larg, pstate, NULL, false); + + /* Analyze right query to resolve column names. */ + rargQuery = parse_sub_analyze((Node *) stmt->rarg, pstate, NULL, false); + + /* Find matching columns from both queries. */ + matchingColumns = determineMatchingColumns(largQuery->targetList, + rargQuery->targetList); + + op->correspondingColumns = matchingColumns; + op->hasCorrespondingBy = false; + + /* If matchingColumns is empty, there is an error. At least one column in the select lists must have the same name. */ + if(list_length(matchingColumns) == 0) + { + ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("%s queries with a CORRESPONDING clause must have at least one column with the same name", + context))); + } + + + // Create subquery for larg, selecting column names from matchingColumns. + stmt->larg = createSubqueryForCorresponding(matchingColumns, stmt->larg); + + // Assign newly generated query to original left query. + op->larg = transformSetOperationTree(pstate, stmt->larg, + false, + <argetlist); + + // Create subquery for rarg, selecting column names from matchingColumns. + stmt->rarg = createSubqueryForCorresponding(matchingColumns, stmt->rarg); + + // Assign newly generated query to original right query. + op->rarg = transformSetOperationTree(pstate, stmt->rarg, + false, + &rtargetlist); + } + else + { + // CORRESPONDING BY clause, find matching column names from both tables + // and intersect them with BY(...) column list. If there are none + // then it is a syntax error. + + Query *largQuery; + Query *rargQuery; + List *matchingColumns; + List *matchingColumnsFiltered; + ListCell *corrtl; + ListCell *mctl; + + /* Analyze left query to resolve column names. */ + largQuery = parse_sub_analyze((Node *) stmt->larg, pstate, NULL, false); + + /* Analyze right query to resolve column names. */ + rargQuery = parse_sub_analyze((Node *) stmt->rarg, pstate, NULL, false); + + /* + * Find matching columns from both queries. + * In CORRESPONDING BY, column names will be removed from + * matchingColumns if they are not in the BY clause. + * All columns in the BY clause must be in matchingColumns, + * otherwise raise syntax error in BY clause. + */ + + matchingColumns = determineMatchingColumns(largQuery->targetList, + rargQuery->targetList); + + /* + * Every column name in correspondingClause must be in matchingColumns, + * otherwise it is a syntax error. + */ + foreach(corrtl, stmt->correspondingClause) + { + Node* corrtle = lfirst(corrtl); + if (IsA(corrtle, ColumnRef) && + list_length(((ColumnRef *) corrtle)->fields) == 1 && + IsA(linitial(((ColumnRef *) corrtle)->fields), String)) + { + /* Get column name from correspondingClause. */ + char *name = strVal(linitial(((ColumnRef *) corrtle)->fields)); + bool hasMatch = false; + + foreach(mctl, matchingColumns) + { + TargetEntry *mctle = (TargetEntry *) lfirst(mctl); + + Assert(mctle->resname != NULL); + Assert(name != NULL); + + /* Compare correspondingClause column name with matchingColumns column names. */ + if(strcmp(mctle->resname, name) == 0) + { + // we have a match. + hasMatch = true; + break; + } + } + + if(!hasMatch) + { + /* CORRESPONDING BY clause contains a column name that is not in both tables. */ + ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("CORRESPONDING BY clause must only contain column names from both tables."))); + } + + } + else + { + /* Only column names are supported, constants are syntax error in CORRESPONDING BY clause. */ + ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), + errmsg( + "%s queries with CORRESPONDING BY clause must have only column names and not constants or ordinals in the column name list.", + context))); + } + } + + /* To preserve column ordering from correspondingClause and to remove + * columns from matchingColumns if they are not in correspondingClause, + * create a new list and finalize our column list for the + * CORRESPONDING BY clause. + */ + + matchingColumnsFiltered = NIL; + + /* For each column in CORRESPONDING BY column list, check + * column existence in matchingColumns. + */ + foreach(corrtl, stmt->correspondingClause) + { + Node* corrtle = lfirst(corrtl); + + if (IsA(corrtle, ColumnRef) && + list_length(((ColumnRef *) corrtle)->fields) == 1 && + IsA(linitial(((ColumnRef *) corrtle)->fields), String)) + { + char *name = strVal(linitial(((ColumnRef *) corrtle)->fields)); + + foreach(mctl, matchingColumns) + { + TargetEntry *mctle = (TargetEntry *) lfirst(mctl); + + Assert(mctle->resname != NULL); + Assert(name != NULL); + + if(strcmp(mctle->resname, name) == 0) + { + // we have a match. + matchingColumnsFiltered = lappend(matchingColumnsFiltered, mctle); + break; + } + } + } + } + + /* If matchingColumnsFiltered is empty, there is a semantic error. At least one column in the select lists must have the same name. */ + if(list_length(matchingColumnsFiltered) == 0) + { + ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("%s queries with CORRESPONDING BY clause must have at least one column name in BY clause and in both of the queries.", + context))); + } + + op->correspondingColumns = matchingColumnsFiltered; + op->hasCorrespondingBy = true; + + + // Create subquery for larg, selecting only columns from matchingColumnsFiltered. + stmt->larg = createSubqueryForCorresponding(matchingColumnsFiltered, stmt->larg); + + // Assign newly generated query to original left query. + op->larg = transformSetOperationTree(pstate, stmt->larg, + false, + <argetlist); + + // Create subquery for rarg, selecting only columns from matchingColumnsFiltered. + stmt->rarg = createSubqueryForCorresponding(matchingColumnsFiltered, stmt->rarg); + + // Assign newly generated query to original right query. + op->rarg = transformSetOperationTree(pstate, stmt->rarg, + false, + &rtargetlist); + } + + /* * Verify that the two children have the same number of non-junk * columns, and determine the types of the merged output columns. */ *************** *** 1838,1843 **** --- 2043,2131 ---- } /* + * Returns a subquery selecting outputColumns from main_arg. + * main_arg is modified and returned. + */ + static SelectStmt * + createSubqueryForCorresponding(List* outputColumns, SelectStmt* main_arg) + { + ColumnRef *cr; + ResTarget *rt; + SelectStmt *n; + + RangeSubselect * rss; + ListCell* mctl; + + n = makeNode(SelectStmt); + n->targetList = NIL; + foreach(mctl, outputColumns) + { + TargetEntry *mctle = (TargetEntry *) lfirst(mctl); + + cr = makeNode(ColumnRef); + cr->fields = list_make1(makeString(mctle->resname)); + cr->location = -1; + + rt = makeNode(ResTarget); + rt->name = NULL; + rt->indirection = NIL; + rt->val = (Node *)cr; + rt->location = -1; + + n->targetList = lappend(n->targetList, rt); + } + + rss = makeNode(RangeSubselect); + + // XXX makeAlias alias name should be empty?? + rss->alias = makeAlias("", NULL); + rss->subquery = (Node *)main_arg; + + n->fromClause = list_make1(rss); + + main_arg = n; + + return main_arg; + } + + + /* + * Processes targetlists of two queries for column equivalence to use + * with UNION/INTERSECT/EXCEPT CORRESPONDING. + */ + static List * + determineMatchingColumns(List *ltargetlist, List *rtargetlist) + { + List *matchingColumns = NIL; + ListCell *ltl; + ListCell *rtl; + + foreach(ltl, ltargetlist) + { + foreach(rtl, rtargetlist) + { + TargetEntry *ltle = (TargetEntry *) lfirst(ltl); + TargetEntry *rtle = (TargetEntry *) lfirst(rtl); + + elog(DEBUG4, "%s", ltle->resname); + + /* Names of the columns must be resolved before calling this method. */ + Assert(ltle->resname != NULL); + Assert(rtle->resname != NULL); + + /* If column names are the same, append it to the result. */ + if(strcmp(ltle->resname, rtle->resname) == 0) + { + matchingColumns = lappend(matchingColumns, ltle); + continue; + } + } + } + + return matchingColumns; + } + + /* * Process the outputs of the non-recursive term of a recursive union * to set up the parent CTE's columns */ *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 130,136 **** Node *limitOffset, Node *limitCount, WithClause *withClause, core_yyscan_t yyscanner); ! static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg); static Node *doNegate(Node *n, int location); static void doNegateFloat(Value *v); static Node *makeAArrayExpr(List *elements, int location); --- 130,136 ---- Node *limitOffset, Node *limitCount, WithClause *withClause, core_yyscan_t yyscanner); ! static Node *makeSetOp(SetOperation op, bool all, List *correspondingClause, Node *larg, Node *rarg); static Node *doNegate(Node *n, int location); static void doNegateFloat(Value *v); static Node *makeAArrayExpr(List *elements, int location); *************** *** 321,326 **** --- 321,327 ---- opt_enum_val_list enum_val_list table_func_column_list create_generic_options alter_generic_options relation_expr_list dostmt_opt_list + opt_corresponding_clause %type <list> opt_fdw_options fdw_options %type <defelt> fdw_option *************** *** 498,504 **** CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS ! CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE CROSS CSV CURRENT_P CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE --- 499,505 ---- CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS ! CONTENT_P CONTINUE_P CONVERSION_P COPY CORRESPONDING COST CREATE CROSS CSV CURRENT_P CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE *************** *** 8489,8507 **** n->fromClause = list_make1($2); $$ = (Node *)n; } ! | select_clause UNION opt_all select_clause { ! $$ = makeSetOp(SETOP_UNION, $3, $1, $4); } ! | select_clause INTERSECT opt_all select_clause { ! $$ = makeSetOp(SETOP_INTERSECT, $3, $1, $4); } ! | select_clause EXCEPT opt_all select_clause { ! $$ = makeSetOp(SETOP_EXCEPT, $3, $1, $4); } ; /* * SQL standard WITH clause looks like: --- 8490,8514 ---- n->fromClause = list_make1($2); $$ = (Node *)n; } ! | select_clause UNION opt_all opt_corresponding_clause select_clause { ! $$ = makeSetOp(SETOP_UNION, $3, $4, $1, $5); } ! | select_clause INTERSECT opt_all opt_corresponding_clause select_clause { ! $$ = makeSetOp(SETOP_INTERSECT, $3, $4, $1, $5); } ! | select_clause EXCEPT opt_all opt_corresponding_clause select_clause { ! $$ = makeSetOp(SETOP_EXCEPT, $3, $4, $1, $5); } ; + + opt_corresponding_clause: + CORRESPONDING BY '(' expr_list ')' { $$ = $4; } + | CORRESPONDING { $$ = list_make1(NIL); } + | /*EMPTY*/ { $$ = NIL; } + ; /* * SQL standard WITH clause looks like: *************** *** 12642,12648 **** } static Node * ! makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg) { SelectStmt *n = makeNode(SelectStmt); --- 12649,12655 ---- } static Node * ! makeSetOp(SetOperation op, bool all, List *correspondingClause, Node *larg, Node *rarg) { SelectStmt *n = makeNode(SelectStmt); *************** *** 12650,12655 **** --- 12657,12663 ---- n->all = all; n->larg = (SelectStmt *) larg; n->rarg = (SelectStmt *) rarg; + n->correspondingClause = correspondingClause; return (Node *) n; } *** a/src/backend/parser/parse_cte.c --- b/src/backend/parser/parse_cte.c *************** *** 927,932 **** --- 927,934 ---- case SETOP_INTERSECT: if (stmt->all) cstate->context = RECURSION_INTERSECT; + checkWellFormedRecursionWalker((Node *) stmt->correspondingClause, + cstate); checkWellFormedRecursionWalker((Node *) stmt->larg, cstate); checkWellFormedRecursionWalker((Node *) stmt->rarg, *************** *** 945,950 **** --- 947,954 ---- case SETOP_EXCEPT: if (stmt->all) cstate->context = RECURSION_EXCEPT; + checkWellFormedRecursionWalker((Node *) stmt->correspondingClause, + cstate); checkWellFormedRecursionWalker((Node *) stmt->larg, cstate); cstate->context = RECURSION_EXCEPT; *** a/src/backend/parser/parse_type.c --- b/src/backend/parser/parse_type.c *************** *** 711,717 **** stmt->limitOffset != NULL || stmt->limitCount != NULL || stmt->lockingClause != NIL || ! stmt->op != SETOP_NONE) goto fail; if (list_length(stmt->targetList) != 1) goto fail; --- 711,718 ---- stmt->limitOffset != NULL || stmt->limitCount != NULL || stmt->lockingClause != NIL || ! stmt->op != SETOP_NONE || ! stmt->correspondingClause != NIL) goto fail; if (list_length(stmt->targetList) != 1) goto fail; *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 1006,1011 **** --- 1006,1013 ---- /* * These fields are used only in "leaf" SelectStmts. */ + List *correspondingClause; /* NULL, list of CORRESPONDING BY exprs, or */ + /* lcons(NIL, NIL) for CORRESPONDING */ List *distinctClause; /* NULL, list of DISTINCT ON exprs, or * lcons(NIL,NIL) for all (SELECT DISTINCT) */ IntoClause *intoClause; /* target for SELECT INTO / CREATE TABLE AS */ *************** *** 1043,1052 **** bool all; /* ALL specified? */ struct SelectStmt *larg; /* left child */ struct SelectStmt *rarg; /* right child */ - /* Eventually add fields for CORRESPONDING spec here */ } SelectStmt; - /* ---------------------- * Set Operation node for post-analysis query trees * --- 1045,1052 ---- *************** *** 1073,1079 **** bool all; /* ALL specified? */ Node *larg; /* left child */ Node *rarg; /* right child */ ! /* Eventually add fields for CORRESPONDING spec here */ /* Fields derived during parse analysis: */ List *colTypes; /* OID list of output column type OIDs */ --- 1073,1082 ---- bool all; /* ALL specified? */ Node *larg; /* left child */ Node *rarg; /* right child */ ! ! /* CORRESPONDING clause fields */ ! List *correspondingColumns; /* NIL: No corresponding, else: CORRESPONDING or CORRESPONDING BY matching columns. Not the original clause. */ ! bool hasCorrespondingBy; /* If correspondingColumns is not NULL then hasCorrespondingBy if effective, otherwise it is junk. */ /* Fields derived during parse analysis: */ List *colTypes; /* OID list of output column type OIDs */ *** a/src/include/parser/kwlist.h --- b/src/include/parser/kwlist.h *************** *** 94,99 **** --- 94,100 ---- PG_KEYWORD("continue", CONTINUE_P, UNRESERVED_KEYWORD) PG_KEYWORD("conversion", CONVERSION_P, UNRESERVED_KEYWORD) PG_KEYWORD("copy", COPY, UNRESERVED_KEYWORD) + PG_KEYWORD("corresponding", CORRESPONDING, UNRESERVED_KEYWORD) PG_KEYWORD("cost", COST, UNRESERVED_KEYWORD) PG_KEYWORD("create", CREATE, RESERVED_KEYWORD) PG_KEYWORD("cross", CROSS, TYPE_FUNC_NAME_KEYWORD) *** a/src/test/regress/expected/corresponding_union.out --- b/src/test/regress/expected/corresponding_union.out *************** *** 0 **** --- 1,431 ---- + -- + -- UNION (also INTERSECT, EXCEPT) with CORRESPONDING. + -- + -- Simple UNION CORRESPONDING constructs + SELECT 1 AS two UNION CORRESPONDING SELECT 2 two; + two + ----- + 1 + 2 + (2 rows) + + SELECT 1 AS one UNION CORRESPONDING SELECT 1 one; + one + ----- + 1 + (1 row) + + SELECT 1 AS two UNION ALL CORRESPONDING SELECT 2 two; + two + ----- + 1 + 2 + (2 rows) + + SELECT 1 AS two UNION ALL CORRESPONDING SELECT 1 two; + two + ----- + 1 + 1 + (2 rows) + + SELECT 1 AS two UNION CORRESPONDING SELECT 2 two UNION CORRESPONDING SELECT 2 two; + two + ----- + 1 + 2 + (2 rows) + + SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION ALL CORRESPONDING SELECT 2 three; + three + ------- + 1 + 2 + 2 + (3 rows) + + SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION CORRESPONDING SELECT 3 three; + three + ------- + 1 + 2 + 3 + (3 rows) + + SELECT 1.1 AS two UNION SELECT 2.2 two; + two + ----- + 1.1 + 2.2 + (2 rows) + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c; + a | b | c + ---+---+--- + 1 | 2 | 3 + 4 | 5 | 6 + (2 rows) + + SELECT 3 c, 2 b, 1 a UNION CORRESPONDING SELECT 6 c, 4 a, 5 b; + c | b | a + ---+---+--- + 3 | 2 | 1 + 6 | 5 | 4 + (2 rows) + + -- Simple UNION CORRESPONDING BY constructs + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a) SELECT 4 a, 5 b, 6 c; + a + --- + 1 + 4 + (2 rows) + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) SELECT 4 a, 5 b, 6 c; + b + --- + 2 + 5 + (2 rows) + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c) SELECT 4 a, 5 b, 6 c; + c + --- + 3 + 6 + (2 rows) + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b) SELECT 4 a, 5 b, 6 c; + a | b + ---+--- + 1 | 2 + 4 | 5 + (2 rows) + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b, c) SELECT 4 a, 5 b, 6 c; + b | c + ---+--- + 2 | 3 + 5 | 6 + (2 rows) + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, c) SELECT 4 a, 5 b, 6 c; + a | c + ---+--- + 1 | 3 + 4 | 6 + (2 rows) + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 4 a, 5 b, 6 c; + a | b | c + ---+---+--- + 1 | 2 | 3 + 4 | 5 | 6 + (2 rows) + + -- CORRESPONDING column ordering, left clause's column ordering must be preserved. + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c; + a | b | c + ---+---+--- + 1 | 2 | 3 + 4 | 5 | 6 + (2 rows) + + SELECT 2 b, 1 a, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c; + b | a | c + ---+---+--- + 2 | 1 | 3 + 5 | 4 | 6 + (2 rows) + + SELECT 1 a, 3 c, 2 b UNION CORRESPONDING SELECT 4 a, 5 b, 6 c; + a | c | b + ---+---+--- + 1 | 3 | 2 + 4 | 6 | 5 + (2 rows) + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 5 b, 6 c, 4 a; + a | b | c + ---+---+--- + 1 | 2 | 3 + 4 | 5 | 6 + (2 rows) + + SELECT 2 b, 1 a, 3 c UNION CORRESPONDING SELECT 5 b, 6 c, 4 a; + b | a | c + ---+---+--- + 2 | 1 | 3 + 5 | 4 | 6 + (2 rows) + + -- CORRESPONDING BY column ordering, BY clause column ordering must be preserved. + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 4 a, 5 b, 6 c; + a | b | c + ---+---+--- + 1 | 2 | 3 + 4 | 5 | 6 + (2 rows) + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b, c, a) SELECT 4 a, 5 b, 6 c; + b | c | a + ---+---+--- + 2 | 3 | 1 + 5 | 6 | 4 + (2 rows) + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c, a, b) SELECT 4 a, 5 b, 6 c; + c | a | b + ---+---+--- + 3 | 1 | 2 + 6 | 4 | 5 + (2 rows) + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 5 b, 6 c, 4 a; + a | b | c + ---+---+--- + 1 | 2 | 3 + 4 | 5 | 6 + (2 rows) + + SELECT 2 b, 3 c, 1 a UNION CORRESPONDING BY(b, c, a) SELECT 4 a, 5 b, 6 c; + b | c | a + ---+---+--- + 2 | 3 | 1 + 5 | 6 | 4 + (2 rows) + + SELECT 3 c, 2 b, 1 a UNION CORRESPONDING BY(c, a, b) SELECT 4 a, 5 b, 6 c; + c | a | b + ---+---+--- + 3 | 1 | 2 + 6 | 4 | 5 + (2 rows) + + -- + -- Try testing from tables... + -- + SELECT f1 AS five FROM FLOAT8_TBL + UNION CORRESPONDING + SELECT f1 AS five FROM FLOAT8_TBL + ORDER BY 1; + five + ----------------------- + -1.2345678901234e+200 + -1004.3 + -34.84 + -1.2345678901234e-200 + 0 + (5 rows) + + SELECT f1 AS five FROM FLOAT8_TBL + UNION CORRESPONDING BY(five) + SELECT f1 AS five FROM FLOAT8_TBL + ORDER BY 1; + five + ----------------------- + -1.2345678901234e+200 + -1004.3 + -34.84 + -1.2345678901234e-200 + 0 + (5 rows) + + SELECT f1 AS ten FROM FLOAT8_TBL + UNION ALL CORRESPONDING + SELECT f1 AS ten FROM FLOAT8_TBL; + ten + ----------------------- + 0 + -34.84 + -1004.3 + -1.2345678901234e+200 + -1.2345678901234e-200 + 0 + -34.84 + -1004.3 + -1.2345678901234e+200 + -1.2345678901234e-200 + (10 rows) + + SELECT f1 AS nine FROM FLOAT8_TBL + UNION CORRESPONDING + SELECT f1 AS nine FROM INT4_TBL + ORDER BY 1; + nine + ----------------------- + -1.2345678901234e+200 + -2147483647 + -123456 + -1004.3 + -34.84 + -1.2345678901234e-200 + 0 + 123456 + 2147483647 + (9 rows) + + SELECT f1 AS ten FROM FLOAT8_TBL + UNION ALL CORRESPONDING + SELECT f1 AS ten FROM INT4_TBL; + ten + ----------------------- + 0 + -34.84 + -1004.3 + -1.2345678901234e+200 + -1.2345678901234e-200 + 0 + 123456 + -123456 + 2147483647 + -2147483647 + (10 rows) + + SELECT f1 AS five FROM FLOAT8_TBL + WHERE f1 BETWEEN -1e6 AND 1e6 + UNION CORRESPONDING + SELECT f1 AS five FROM INT4_TBL + WHERE f1 BETWEEN 0 AND 1000000; + five + ----------------------- + -1004.3 + -34.84 + -1.2345678901234e-200 + 0 + 123456 + (5 rows) + + -- + -- INTERSECT and EXCEPT + -- + SELECT q2 FROM int8_tbl INTERSECT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl; + q2 + ------------------ + 4567890123456789 + 123 + (2 rows) + + SELECT q2 FROM int8_tbl INTERSECT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl; + q2 + ------------------ + 4567890123456789 + 4567890123456789 + 123 + (3 rows) + + SELECT q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1; + q2 + ------------------- + -4567890123456789 + 456 + (2 rows) + + SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1; + q2 + ------------------- + -4567890123456789 + 456 + (2 rows) + + SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q1 AS q2 FROM int8_tbl ORDER BY 1; + q2 + ------------------- + -4567890123456789 + 456 + 4567890123456789 + (3 rows) + + SELECT q1 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2 AS q1 FROM int8_tbl; + q1 + ---- + (0 rows) + + SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q2 AS q1 FROM int8_tbl; + q1 + ------------------ + 4567890123456789 + 123 + (2 rows) + + SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q2 AS q1 FROM int8_tbl; + q1 + ------------------ + 4567890123456789 + 4567890123456789 + 123 + (3 rows) + + -- + -- Mixed types + -- + SELECT f1 FROM float8_tbl INTERSECT CORRESPONDING SELECT f1 FROM int4_tbl; + f1 + ---- + 0 + (1 row) + + SELECT f1 FROM float8_tbl EXCEPT CORRESPONDING SELECT f1 FROM int4_tbl ORDER BY 1; + f1 + ----------------------- + -1.2345678901234e+200 + -1004.3 + -34.84 + -1.2345678901234e-200 + (4 rows) + + -- + -- Subqueries with ORDER BY & LIMIT clauses + -- + -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT + SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2,q1 FROM int8_tbl + ORDER BY q2,q1; + q1 | q2 + ----+---- + (0 rows) + + SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q1) SELECT q2,q1 FROM int8_tbl + ORDER BY q1; + q1 + ---- + (0 rows) + + SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q2) SELECT q2,q1 FROM int8_tbl + ORDER BY q2; + q2 + ---- + (0 rows) + + -- + -- New syntaxes (7.1) permit new tests + -- + (((((select * from int8_tbl))))); + q1 | q2 + ------------------+------------------- + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 + (5 rows) + + -- + -- Check handling of a case with unknown constants. We don't guarantee + -- an undecorated constant will work in all cases, but historically this + -- usage has worked, so test we don't break it. + -- + SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a + UNION CORRESPONDING + SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b + ORDER BY 1; + f1 + ------ + a + ab + abcd + test + (4 rows) + + -- This should fail, but it should produce an error cursor + SELECT '3.4'::numeric AS a UNION CORRESPONDING SELECT 'foo' AS a; + ERROR: failed to find conversion function from unknown to numeric *** a/src/test/regress/parallel_schedule --- b/src/test/regress/parallel_schedule *************** *** 74,80 **** # ---------- # Another group of parallel tests # ---------- ! test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete # ---------- # Another group of parallel tests --- 74,80 ---- # ---------- # Another group of parallel tests # ---------- ! test: select_into select_distinct select_distinct_on select_implicit select_having subselect union corresponding_union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete # ---------- # Another group of parallel tests *** a/src/test/regress/serial_schedule --- b/src/test/regress/serial_schedule *************** *** 73,78 **** --- 73,79 ---- test: select_having test: subselect test: union + test: corresponding_union test: case test: join test: aggregates *** a/src/test/regress/sql/corresponding_union.sql --- b/src/test/regress/sql/corresponding_union.sql *************** *** 0 **** --- 1,162 ---- + -- + -- UNION (also INTERSECT, EXCEPT) with CORRESPONDING. + -- + + -- Simple UNION CORRESPONDING constructs + + SELECT 1 AS two UNION CORRESPONDING SELECT 2 two; + + SELECT 1 AS one UNION CORRESPONDING SELECT 1 one; + + SELECT 1 AS two UNION ALL CORRESPONDING SELECT 2 two; + + SELECT 1 AS two UNION ALL CORRESPONDING SELECT 1 two; + + SELECT 1 AS two UNION CORRESPONDING SELECT 2 two UNION CORRESPONDING SELECT 2 two; + + SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION ALL CORRESPONDING SELECT 2 three; + + SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION CORRESPONDING SELECT 3 three; + + SELECT 1.1 AS two UNION SELECT 2.2 two; + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c; + + SELECT 3 c, 2 b, 1 a UNION CORRESPONDING SELECT 6 c, 4 a, 5 b; + + -- Simple UNION CORRESPONDING BY constructs + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a) SELECT 4 a, 5 b, 6 c; + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) SELECT 4 a, 5 b, 6 c; + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c) SELECT 4 a, 5 b, 6 c; + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b) SELECT 4 a, 5 b, 6 c; + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b, c) SELECT 4 a, 5 b, 6 c; + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, c) SELECT 4 a, 5 b, 6 c; + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 4 a, 5 b, 6 c; + + -- CORRESPONDING column ordering, left clause's column ordering must be preserved. + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c; + + SELECT 2 b, 1 a, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c; + + SELECT 1 a, 3 c, 2 b UNION CORRESPONDING SELECT 4 a, 5 b, 6 c; + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 5 b, 6 c, 4 a; + + SELECT 2 b, 1 a, 3 c UNION CORRESPONDING SELECT 5 b, 6 c, 4 a; + + -- CORRESPONDING BY column ordering, BY clause column ordering must be preserved. + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 4 a, 5 b, 6 c; + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b, c, a) SELECT 4 a, 5 b, 6 c; + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c, a, b) SELECT 4 a, 5 b, 6 c; + + SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 5 b, 6 c, 4 a; + + SELECT 2 b, 3 c, 1 a UNION CORRESPONDING BY(b, c, a) SELECT 4 a, 5 b, 6 c; + + SELECT 3 c, 2 b, 1 a UNION CORRESPONDING BY(c, a, b) SELECT 4 a, 5 b, 6 c; + + -- + -- Try testing from tables... + -- + + SELECT f1 AS five FROM FLOAT8_TBL + UNION CORRESPONDING + SELECT f1 AS five FROM FLOAT8_TBL + ORDER BY 1; + + SELECT f1 AS five FROM FLOAT8_TBL + UNION CORRESPONDING BY(five) + SELECT f1 AS five FROM FLOAT8_TBL + ORDER BY 1; + + SELECT f1 AS ten FROM FLOAT8_TBL + UNION ALL CORRESPONDING + SELECT f1 AS ten FROM FLOAT8_TBL; + + SELECT f1 AS nine FROM FLOAT8_TBL + UNION CORRESPONDING + SELECT f1 AS nine FROM INT4_TBL + ORDER BY 1; + + SELECT f1 AS ten FROM FLOAT8_TBL + UNION ALL CORRESPONDING + SELECT f1 AS ten FROM INT4_TBL; + + SELECT f1 AS five FROM FLOAT8_TBL + WHERE f1 BETWEEN -1e6 AND 1e6 + UNION CORRESPONDING + SELECT f1 AS five FROM INT4_TBL + WHERE f1 BETWEEN 0 AND 1000000; + + -- + -- INTERSECT and EXCEPT + -- + + SELECT q2 FROM int8_tbl INTERSECT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl; + + SELECT q2 FROM int8_tbl INTERSECT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl; + + SELECT q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1; + + SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1; + + SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q1 AS q2 FROM int8_tbl ORDER BY 1; + + SELECT q1 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2 AS q1 FROM int8_tbl; + + SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q2 AS q1 FROM int8_tbl; + + SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q2 AS q1 FROM int8_tbl; + + -- + -- Mixed types + -- + + SELECT f1 FROM float8_tbl INTERSECT CORRESPONDING SELECT f1 FROM int4_tbl; + + SELECT f1 FROM float8_tbl EXCEPT CORRESPONDING SELECT f1 FROM int4_tbl ORDER BY 1; + + -- + -- Subqueries with ORDER BY & LIMIT clauses + -- + + -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT + SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2,q1 FROM int8_tbl + ORDER BY q2,q1; + + SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q1) SELECT q2,q1 FROM int8_tbl + ORDER BY q1; + + SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q2) SELECT q2,q1 FROM int8_tbl + ORDER BY q2; + + -- + -- New syntaxes (7.1) permit new tests + -- + + (((((select * from int8_tbl))))); + + -- + -- Check handling of a case with unknown constants. We don't guarantee + -- an undecorated constant will work in all cases, but historically this + -- usage has worked, so test we don't break it. + -- + + SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a + UNION CORRESPONDING + SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b + ORDER BY 1; + + -- This should fail, but it should produce an error cursor + SELECT '3.4'::numeric AS a UNION CORRESPONDING SELECT 'foo' AS a;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers