Adding CORRESPONDING to Set Operations
Initial patch, filename: corresponding_clause_v2.patch
This patch adds CORRESPONDING clause to set operations according to
SQL20nn standard draft as Feature F301, "CORRESPONDING in query
expressions"
Corresponding clause either contains a BY(...) clause or not. If it
doesn't have a BY(...) clause the usage is as follows.
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING 4 b, 5 d, 6 c, 7 f;
with output:
b c
-----
2 3
4 6
i.e. matching column names are filtered and are only output from the
whole set operation clause.
If we introduce a BY(...) clause, then column names are further
intersected with that BY clause:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) 4 b, 5 d, 6 c, 7 f;
with output:
b
--
2
4
This patch compiles and tests successfully with master branch.
It has been tested only on Pardus Linux i686 ( Kernel 2.6.37.6 #1 SMP
i686 i686 i386 GNU/Linux)
This patch includes documentation and add one regression file.
This patch addresses the following TODO item:
SQL Commands: Add CORRESPONDING BY to UNION/INTERSECT/EXCEPT
Best 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
***************
*** 2031,2036 ****
--- 2031,2037 ----
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);
}
***************
*** 2295,2300 ****
--- 2296,2303 ----
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 Node *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,1886 ----
&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;
+ 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. */
+ 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;
+ ListCell *byresname;
+ 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(byresname, stmt->correspondingClause)
+ {
+ Node* node = lfirst(byresname);
+ if (IsA(node, ColumnRef) &&
+ list_length(((ColumnRef *) node)->fields) == 1 &&
+ IsA(linitial(((ColumnRef *) node)->fields), String))
+ {
+ /* Get column name from correspondingClause. */
+ char *name = strVal(linitial(((ColumnRef *) node)->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)));
+ }
+ }
+
+ /* Remove columns from matchingColumns if they are not in correspondingClause,
+ * thus finalizing our column list for the CORRESPONDING BY clause.
+ */
+
+ /* cannot use foreach here because of possible list_delete_ptr */
+ mctl = list_head(matchingColumns);
+ while (mctl)
+ {
+ TargetEntry *mctle = (TargetEntry *) lfirst(mctl);
+ bool hasMatch = false;
+
+ /* must advance mctl before list_delete_ptr possibly deletes it */
+ mctl = lnext(mctl);
+
+ foreach(byresname, stmt->correspondingClause)
+ {
+ Node* node = lfirst(byresname);
+
+ hasMatch = false;
+
+ if (IsA(node, ColumnRef) &&
+ list_length(((ColumnRef *) node)->fields) == 1 &&
+ IsA(linitial(((ColumnRef *) node)->fields), String))
+ {
+ char *name = strVal(linitial(((ColumnRef *) node)->fields));
+
+ Assert(mctle->resname != NULL);
+ Assert(name != NULL);
+
+ if(strcmp(mctle->resname, name) == 0)
+ {
+ // we have a match.
+ hasMatch = true;
+ break;
+ }
+ }
+ }
+
+ if(!hasMatch)
+ {
+ // remove current from matchingColumns
+ matchingColumns = list_delete_ptr(matchingColumns, mctle);
+
+ /* List is emptied. */
+ if(matchingColumns == NIL)
+ break;
+ }
+ }
+
+ op->correspondingColumns = matchingColumns;
+ op->hasCorrespondingBy = true;
+
+ /* If matchingColumns is empty, there is a semantic 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 CORRESPONDING BY clause must have at least one column name in BY clause and in both of the queries.",
+ context)));
+ }
+
+
+ // Create subquery for larg, selecting only columns 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 only columns from matchingColumns.
+ stmt->rarg = createSubqueryForCorresponding(matchingColumns, 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 ****
--- 2054,2141 ----
}
/*
+ * Returns a subquery selecting outputColumns from main_arg.
+ * main_arg is modified and returned.
+ */
+ static Node *
+ 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 = 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);
+ }
+ }
+ }
+
+ 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
***************
*** 8480,8498 ****
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:
--- 8481,8505 ----
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:
***************
*** 12633,12639 ****
}
static Node *
! makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg)
{
SelectStmt *n = makeNode(SelectStmt);
--- 12640,12646 ----
}
static Node *
! makeSetOp(SetOperation op, bool all, List *correspondingClause, Node *larg, Node *rarg)
{
SelectStmt *n = makeNode(SelectStmt);
***************
*** 12641,12646 ****
--- 12648,12654 ----
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,352 ----
+ --
+ -- 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)
+
+ --
+ -- 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,136 ----
+ --
+ -- 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;
+
+ --
+ -- 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
