Hi
2017-03-25 13:41 GMT+01:00 Surafel Temesgen <surafel3...@gmail.com>:
>
>
>>
>> I took a quick look through this and noted that it fails to touch
>> ruleutils.c, which means that dumping of views containing CORRESPONDING
>> certainly doesn't work.
>>
> fixed
>
>> Also, the changes in parser/analyze.c seem rather massive and
>> correspondingly hard to review. Is it possible to rearrange the
>> patch to reduce the size of that diff? If you can avoid moving
>> or reindenting existing code, that'd help.
>>
> Part of transformSetOperationTree that make union data type of
> set operation target list became makeUnionDatatype inorder to
> easy using it multiple time and avoid very long transformSetOperationTree
> function
>
>
>> The code in that area seems rather confused, too. For instance,
>> I'm not sure exactly what orderCorrespondingList() is good for,
>> but it certainly doesn't look to be doing anything that either its
>> name or its header comment (or the comments at the call sites) would
>> suggest. Its input and output tlists are always in the same order.
>>
>> It give corresponding target list a sequential resnos
> Inorder to avoid touching generate_append_tlist I change
> the comment and function name as such
>
> I also think there should be some comments about exactly what matching
>> semantics we're enforcing. The SQL standard says
>>
>> a) If CORRESPONDING is specified, then:
>> i) Within the columns of T1, equivalent <column name>s shall
>> not be specified more than once and within the columns of
>> T2, equivalent <column name>s shall not be specified more
>> than once.
>>
>> That seems unreasonably stringent to me; it ought to be sufficient to
>> forbid duplicates of the names listed in CORRESPONDING, or the common
>> column names if there's no BY list. But whichever restriction you prefer,
>> this code seems to be failing to check it --- I certainly don't see any
>> new error message about "column name "foo" appears more than once".
>>
> fixed
>
> I'm not impressed by using A_Const for the members of the CORRESPONDING
>> name list. That's not a clever solution, that's a confusing kluge,
>> because it's a complete violation of the meaning of A_Const. Elsewhere
>> we just use lists of String for name lists, and that seems sufficient
>> here. Personally I'd just use the existing columnList production rather
>> than rolling your own.
>>
> fixed
>
>>
>>
I am sending updated version:
1. the corresponding columns must be unique, other not - code refactoring
(the code is still O(N*M*Z) - but some slow operations (string cmp)
reduced) + regress tests
2. regress tests for views
3. some cleaning (white chars)
Regards
Pavel
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 30792f45f1..c3cdee54ad 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -1601,6 +1601,9 @@ SELECT DISTINCT ON (<replaceable>expression</replaceable> <optional>, <replaceab
<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">
@@ -1617,9 +1620,9 @@ SELECT DISTINCT ON (<replaceable>expression</replaceable> <optional>, <replaceab
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>
+<replaceable>query1</replaceable> UNION <optional>ALL</optional> <optional>CORRESPONDING</optional> <optional>BY</optional> <replaceable>query2</replaceable>
+<replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <optional>CORRESPONDING</optional> <optional>BY</optional> <replaceable>query2</replaceable>
+<replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <optional>CORRESPONDING</optional> <optional>BY</optional> <replaceable>query2</replaceable>
</synopsis>
<replaceable>query1</replaceable> and
<replaceable>query2</replaceable> are queries that can use any of
@@ -1659,11 +1662,22 @@ SELECT DISTINCT ON (<replaceable>expression</replaceable> <optional>, <replaceab
</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
- the corresponding columns have compatible data types, as
- described in <xref linkend="typeconv-union-case">.
+ <literal>EXCEPT</> returns all rows that are in the result of
+ <replaceable>query1</replaceable> but not in the result of
+ <replaceable>query2</replaceable>. (This is sometimes called the
+ <firstterm>difference</> between two queries.) Again, duplicates
+ are eliminated unless <literal>EXCEPT ALL</> is used.
+ </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>
</sect1>
diff --git a/doc/src/sgml/sql.sgml b/doc/src/sgml/sql.sgml
index 57396d7c24..f98c22e696 100644
--- a/doc/src/sgml/sql.sgml
+++ b/doc/src/sgml/sql.sgml
@@ -859,7 +859,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replac
[ 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> ]
+ [ { 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> ]
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index c23d5c5285..74db9529a1 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2995,6 +2995,7 @@ _copySelectStmt(const SelectStmt *from)
COPY_NODE_FIELD(withClause);
COPY_SCALAR_FIELD(op);
COPY_SCALAR_FIELD(all);
+ COPY_NODE_FIELD(correspondingClause);
COPY_NODE_FIELD(larg);
COPY_NODE_FIELD(rarg);
@@ -3010,6 +3011,8 @@ _copySetOperationStmt(const SetOperationStmt *from)
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);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 5941b7a2bf..26ded380a9 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1050,6 +1050,7 @@ _equalSelectStmt(const SelectStmt *a, const SelectStmt *b)
COMPARE_NODE_FIELD(withClause);
COMPARE_SCALAR_FIELD(op);
COMPARE_SCALAR_FIELD(all);
+ COMPARE_NODE_FIELD(correspondingClause);
COMPARE_NODE_FIELD(larg);
COMPARE_NODE_FIELD(rarg);
@@ -1063,6 +1064,8 @@ _equalSetOperationStmt(const SetOperationStmt *a, const SetOperationStmt *b)
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);
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 6e52eb7231..7102ea96c2 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -3444,6 +3444,8 @@ raw_expression_tree_walker(Node *node,
return true;
if (walker(stmt->lockingClause, context))
return true;
+ if (walker(stmt->correspondingClause, context))
+ return true;
if (walker(stmt->withClause, context))
return true;
if (walker(stmt->larg, context))
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 541af02935..5334c01698 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2642,6 +2642,7 @@ _outSelectStmt(StringInfo str, const SelectStmt *node)
WRITE_NODE_FIELD(withClause);
WRITE_ENUM_FIELD(op, SetOperation);
WRITE_BOOL_FIELD(all);
+ WRITE_NODE_FIELD(correspondingClause);
WRITE_NODE_FIELD(larg);
WRITE_NODE_FIELD(rarg);
}
@@ -2949,6 +2950,8 @@ _outSetOperationStmt(StringInfo str, const SetOperationStmt *node)
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);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 474f221a75..6e284f9ef8 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -416,6 +416,8 @@ _readSetOperationStmt(void)
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);
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index d88738ec7c..29f86ebbad 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -91,7 +91,8 @@ static List *generate_setop_tlist(List *colTypes, List *colCollations,
Index varno,
bool hack_constants,
List *input_tlist,
- List *refnames_tlist);
+ List *refnames_tlist,
+ bool no_corresponding);
static List *generate_append_tlist(List *colTypes, List *colCollations,
bool flag,
List *input_tlists,
@@ -110,6 +111,7 @@ static Node *adjust_appendrel_attrs_mutator(Node *node,
static Relids adjust_relid_set(Relids relids, Index oldrelid, Index newrelid);
static List *adjust_inherited_tlist(List *tlist,
AppendRelInfo *context);
+static List *make_corresponding_target(List *corresponding_list, List *subroot_list);
/*
@@ -187,6 +189,24 @@ plan_set_operations(PlannerInfo *root)
leftmostQuery->targetList,
&top_tlist);
}
+ /*
+ * If corresponding column specified, we take column names from it.
+ */
+ else if (topop->correspondingColumns != NIL )
+ {
+ /*
+ * Recurse on setOperations tree to generate paths for set ops. The
+ * final output path should have just the column types shown as the
+ * output from the top-level node, plus possibly resjunk working
+ * columns (we can rely on upper-level nodes to deal with that).
+ */
+ path = recurse_set_operations((Node *) topop, root,
+ topop->colTypes, topop->colCollations,
+ true, -1,
+ topop->correspondingColumns,
+ &top_tlist,
+ NULL);
+ }
else
{
/*
@@ -252,6 +272,8 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
List **pTargetList,
double *pNumGroups)
{
+ SetOperationStmt *topop = (SetOperationStmt *) root->parse->setOperations;
+
if (IsA(setOp, RangeTblRef))
{
RangeTblRef *rtr = (RangeTblRef *) setOp;
@@ -316,23 +338,53 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
path = (Path *) create_subqueryscan_path(root, rel, subpath,
NIL, NULL);
- /*
- * Figure out the appropriate target list, and update the
- * SubqueryScanPath with the PathTarget form of that.
- */
- tlist = generate_setop_tlist(colTypes, colCollations,
+ if (topop->correspondingColumns != NIL )
+ {
+ List *correspondingTarget;
+
+ /*
+ * make target list that only contains corresponding column
+ * from sub-queries list ito use it for projection
+ */
+ correspondingTarget = make_corresponding_target(
+ topop->correspondingColumns,
+ subroot->processed_tlist);
+
+ /*
+ * Figure out the appropriate target list, and update the
+ * SubqueryScanPath with the PathTarget form of that.
+ */
+ tlist = generate_setop_tlist(colTypes, colCollations, flag,
+ rtr->rtindex, true,
+ correspondingTarget,
+ refnames_tlist, false);
+
+ path = apply_projection_to_path(root, rel, path,
+ create_pathtarget(root, tlist));
+
+ /* Return the fully-fledged tlist to caller, too */
+ *pTargetList = tlist;
+
+ }
+ else
+ {
+ /*
+ * Figure out the appropriate target list, and update the
+ * SubqueryScanPath with the PathTarget form of that.
+ */
+ tlist = generate_setop_tlist(colTypes, colCollations,
flag,
rtr->rtindex,
true,
subroot->processed_tlist,
- refnames_tlist);
+ refnames_tlist, true);
- path = apply_projection_to_path(root, rel, path,
+ path = apply_projection_to_path(root, rel, path,
create_pathtarget(root, tlist));
- /* Return the fully-fledged tlist to caller, too */
- *pTargetList = tlist;
-
+ /* Return the fully-fledged tlist to caller, too */
+ *pTargetList = tlist;
+ }
/*
* Estimate number of groups if caller wants it. If the subquery used
* grouping or aggregation, its output is probably mostly unique
@@ -392,7 +444,7 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
0,
false,
*pTargetList,
- refnames_tlist);
+ refnames_tlist, true);
path = apply_projection_to_path(root,
path->parent,
path,
@@ -1004,7 +1056,8 @@ generate_setop_tlist(List *colTypes, List *colCollations,
Index varno,
bool hack_constants,
List *input_tlist,
- List *refnames_tlist)
+ List *refnames_tlist,
+ bool no_corresponding)
{
List *tlist = NIL;
int resno = 1;
@@ -1026,8 +1079,8 @@ generate_setop_tlist(List *colTypes, List *colCollations,
rtlc = lnext(rtlc);
- Assert(inputtle->resno == resno);
- Assert(reftle->resno == resno);
+ Assert(!no_corresponding || inputtle->resno == resno);
+ Assert(!no_corresponding || reftle->resno == resno);
Assert(!inputtle->resjunk);
Assert(!reftle->resjunk);
@@ -2150,3 +2203,70 @@ adjust_appendrel_attrs_multilevel(PlannerInfo *root, Node *node,
/* Now translate for this child */
return adjust_appendrel_attrs(root, node, appinfo);
}
+
+/*
+ * generate target list from left target list with the order
+ * of right target list
+ */
+static List *
+make_corresponding_target(List *corresponding_list, List *subroot_list)
+{
+ Index internal = 0;
+ ListCell *ltl;
+ ListCell *rtl;
+ int size;
+ int i;
+ List *matchingColumns = NIL;
+ TargetEntry *simple_te_array;
+
+ size = list_length(corresponding_list) + 1;
+
+ /* Use array to find the order of corresponding columen */
+ simple_te_array = (TargetEntry *) palloc0(size * sizeof(TargetEntry));
+ foreach(ltl, corresponding_list)
+ {
+ foreach(rtl, subroot_list)
+ {
+ TargetEntry *ltle = (TargetEntry *) lfirst(ltl);
+ TargetEntry *rtle = (TargetEntry *) lfirst(rtl);
+
+ /* 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, add it to array. */
+ if (strcmp(ltle->resname, rtle->resname) == 0)
+ {
+ simple_te_array[internal].xpr = rtle->xpr;
+ simple_te_array[internal].expr = rtle->expr;
+ simple_te_array[internal].resno = rtle->resno;
+ simple_te_array[internal].resname = rtle->resname;
+ simple_te_array[internal].ressortgroupref =
+ rtle->ressortgroupref;
+ simple_te_array[internal].resorigtbl = rtle->resorigtbl;
+ simple_te_array[internal].resorigcol = rtle->resorigcol;
+ simple_te_array[internal].resjunk = rtle->resjunk;
+ internal++;
+ continue;
+ }
+ }
+ }
+ /* traverse the array and make targetlist */
+ for (i = 0; i < internal; i++)
+ {
+ TargetEntry *tle = makeNode(TargetEntry);
+
+ tle->xpr = simple_te_array[i].xpr;
+ tle->expr = simple_te_array[i].expr;
+ tle->resno = simple_te_array[i].resno;
+ tle->resname = simple_te_array[i].resname;
+ tle->ressortgroupref = simple_te_array[i].ressortgroupref;
+ tle->resorigtbl = simple_te_array[i].resorigtbl;
+ tle->resorigcol = simple_te_array[i].resorigcol;
+ tle->resjunk = simple_te_array[i].resjunk;
+
+ matchingColumns = lappend(matchingColumns, tle);
+
+ }
+ return matchingColumns;
+}
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 3571e50aea..cc57d69ad9 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -76,10 +76,18 @@ static Query *transformCreateTableAsStmt(ParseState *pstate,
CreateTableAsStmt *stmt);
static void transformLockingClause(ParseState *pstate, Query *qry,
LockingClause *lc, bool pushedDown);
+static void makeUnionDatatype(List *ltargetlist, List *rtargetlist,
+ SetOperationStmt *op, List **targetlist, ParseState *parentParseState,
+ const char *context);
#ifdef RAW_EXPRESSION_COVERAGE_TEST
static bool test_raw_expression_coverage(Node *node, void *context);
#endif
-
+static List *CommonColumns(List *ltargetlist, List *rtargetlist, bool filtered,
+ ParseState *pstate, const char *context);
+static List *FilterColumnsByNames(List *common_columns, List *filter,
+ ParseState *pstate, const char *context);
+static List *FilterColumnsByTL(List *targetlist, List *filter, bool check_uniq,
+ ParseState *pstate, const char *context);
/*
* parse_analyze
@@ -1661,7 +1669,15 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
qry->targetList = NIL;
targetvars = NIL;
targetnames = NIL;
- left_tlist = list_head(leftmostQuery->targetList);
+
+ /*
+ * for corresponding clause limits top-level query targetlist to those
+ * corresponding column list only
+ */
+ if (sostmt->correspondingColumns != NIL )
+ left_tlist = list_head(sostmt->correspondingColumns);
+ else
+ left_tlist = list_head(leftmostQuery->targetList);
forthree(lct, sostmt->colTypes,
lcm, sostmt->colTypmods,
@@ -1921,8 +1937,6 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
SetOperationStmt *op = makeNode(SetOperationStmt);
List *ltargetlist;
List *rtargetlist;
- ListCell *ltl;
- ListCell *rtl;
const char *context;
context = (stmt->op == SETOP_UNION ? "UNION" :
@@ -1933,6 +1947,84 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
op->all = stmt->all;
/*
+ * 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
+ {
+ /*
+ * 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;
+ List *rightCorrespondingColumns;
+
+ op->hasCorrespondingBy = linitial(stmt->correspondingClause) != NULL;
+
+ /* Analyze left query to resolve column names. */
+ largQuery = parse_sub_analyze((Node *) stmt->larg,
+ pstate, NULL, false, false );
+
+ /* Analyze right query to resolve column names. */
+ rargQuery = parse_sub_analyze((Node *) stmt->rarg,
+ pstate, NULL, false, false );
+
+ /* Find matching columns from both queries. */
+ matchingColumns = CommonColumns(largQuery->targetList,
+ rargQuery->targetList,
+ op->hasCorrespondingBy,
+ pstate,
+ context);
+
+ /*
+ * If matchingColumns is empty, there is an error.
+ * At least one column in the select lists must have the same name.
+ */
+ if (matchingColumns == NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("there is not any corresponding name"),
+ errhint("%s queries with a CORRESPONDING clause must have at least one column with the same name",
+ context),
+ parser_errposition(pstate,
+ exprLocation((Node *)
+ linitial(largQuery->targetList)))));
+
+ /* Use column filter when it is known */
+ if (op->hasCorrespondingBy)
+ matchingColumns = FilterColumnsByNames(matchingColumns,
+ stmt->correspondingClause,
+ pstate,
+ context);
+
+ op->correspondingColumns = matchingColumns;
+
+ /*
+ * When we know matching columns, we can quickly create
+ * corresponding target list for right target list. It is faster,
+ * than using symmetry. Ensure unique columns when hasCorrespondingBy
+ * is true - in this case, the uniq is not checked already.
+ */
+ rightCorrespondingColumns = FilterColumnsByTL(rargQuery->targetList,
+ matchingColumns,
+ op->hasCorrespondingBy,
+ pstate,
+ context);
+
+ /* make union'd datatype of output column */
+ makeUnionDatatype(matchingColumns, rightCorrespondingColumns,
+ op, targetlist, pstate, context);
+ }
+
+ /*
* Recursively transform the left child node.
*/
op->larg = transformSetOperationTree(pstate, stmt->larg,
@@ -1957,177 +2049,416 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
false,
&rtargetlist);
+ if (op->correspondingColumns == NIL )
+ {
+ makeUnionDatatype(ltargetlist, rtargetlist, op, targetlist, pstate,
+ context);
+ /*
+ * Verify that the two children have the same number of non-junk
+ * columns, and determine the types of the merged output columns.
+ */
+ if (list_length(ltargetlist) != list_length(rtargetlist))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("each %s query must have the same number of columns", context),
+ parser_errposition(pstate,
+ exprLocation((Node *) rtargetlist))));
+ }
+
+ return (Node *) op;
+ }
+}
+
+/*
+ * Processes targetlists of two queries for columns with same names to use
+ * with UNION/INTERSECT/EXCEPT CORRESPONDING. filtered is true, when
+ * CORRESPONDING BY is used. When it is false, we can check uniq names
+ * in rtargetlist here.
+ */
+static List *
+CommonColumns(List *ltargetlist, List *rtargetlist, bool filtered,
+ ParseState *pstate, const char *context)
+{
+ List *common_columns = NIL;
+ ListCell *ltlc;
+ ListCell *rtlc;
+ int resno = 1;
+
+ foreach(ltlc, ltargetlist)
+ {
+ TargetEntry *lte = (TargetEntry *) lfirst(ltlc);
+ bool found = false;
+
+ Assert(lte->resname != NULL);
+
+ foreach(rtlc, rtargetlist)
+ {
+ ListCell *lc;
+ TargetEntry *rte = (TargetEntry *) lfirst(rtlc);
+
+ Assert(rte->resname != NULL);
+
+ if (strcmp(lte->resname, rte->resname) == 0)
+ {
+ if (filtered)
+ {
+ /*
+ * We found common column, but we don't know if it
+ * is in CORRESPONDING BY list - so don't try do more
+ * work here. The column list will be modified later,
+ * so use shall copy here.
+ */
+ common_columns = lappend(common_columns, lte);
+ break;
+ }
+
+ /* If same column name mentioned more than once it is syntax error . */
+ if (found)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("corresponding column \"%s\" is used more times", rte->resname),
+ errhint("In %s queries with CORRESPONDING clause the corresponding column names must be unique.",
+ context),
+ parser_errposition(pstate,
+ exprLocation((Node *) rte))));
+
+ found = true;
+
+ /* In this case, common_columns must be unique */
+ foreach(lc, common_columns)
+ {
+ TargetEntry *te = (TargetEntry *) lfirst(lc);
+
+ if (strcmp(te->resname, lte->resname) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("corresponding column \"%s\" is used more times", lte->resname),
+ errhint("In %s queries with CORRESPONDING clause the corresponding column names must be unique.",
+ context),
+ parser_errposition(pstate,
+ exprLocation((Node *) lte))));
+ }
+
+ /* When is not any other filter create final te */
+ common_columns = lappend(common_columns,
+ makeTargetEntry(lte->expr,
+ (AttrNumber) resno++,
+ lte->resname,
+ false));
+ }
+ }
+ }
+
+ return common_columns;
+}
+
+/*
+ * Returns filtered common columns list - filter is based on CORRESPONDING BY
+ * list Ensure CORRESPONDING BY list is unique. Result is in CORRESPONDING BY
+ * list order. Common columns list can hold duplicate columns.
+ */
+static List *
+FilterColumnsByNames(List *common_columns, List *filter,
+ ParseState *pstate, const char *context)
+{
+ List *filtered_columns = NIL;
+ ListCell *flc;
+ int resno = 1;
+
+ Assert(common_columns != NIL);
+ Assert(filter != NIL);
+
+ foreach(flc, filter)
+ {
+ char *name = strVal((Value *) lfirst(flc));
+ ListCell *tlc;
+ bool found = false;
+
+ foreach(tlc, common_columns)
+ {
+ TargetEntry *tec = (TargetEntry *) lfirst(tlc);
+
+ if (strcmp(tec->resname, name) == 0)
+ {
+ ListCell *lc;
+
+ /*
+ * When "found" is true, then common_columns contains
+ * duplicate columns. Raise exception then.
+ */
+ if (found)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("corresponding column \"%s\" is used more times", name),
+ errhint("In %s queries with CORRESPONDING BY clause the corresponding column names must be unique.",
+ context),
+ parser_errposition(pstate,
+ exprLocation((Node *) tec))));
+
+ found = true;
+
+ /* result list should not to contains this name */
+ foreach(lc, filtered_columns)
+ {
+ TargetEntry *te = (TargetEntry *) lfirst(lc);
+
+ /*
+ * CORRESPONDING BY clause contains a column name that is
+ * not in unique in this clause
+ */
+ if (strcmp(te->resname, name) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column name \"%s\" is not unique in CORRESPONDING BY clause", name),
+ errhint("CORRESPONDING BY clause must contain unique column names only."),
+ parser_errposition(pstate,
+ exprLocation((Node *) tec))));
+ }
+
+ /* create te with correct resno */
+ filtered_columns = lappend(filtered_columns,
+ makeTargetEntry(tec->expr,
+ (AttrNumber) resno++,
+ tec->resname,
+ false));
+ }
+ }
+
/*
- * Verify that the two children have the same number of non-junk
- * columns, and determine the types of the merged output columns.
+ * CORRESPONDING BY clause contains a column name that is not
+ * in common columns.
*/
- if (list_length(ltargetlist) != list_length(rtargetlist))
+ if (!found)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("each %s query must have the same number of columns",
- context),
+ errmsg("column name \"%s\" can not be used in CORRESPONDING BY list", name),
+ errhint("%s queries with a CORRESPONDING BY clause must contain column names from both tables.",
+ context),
parser_errposition(pstate,
- exprLocation((Node *) rtargetlist))));
+ exprLocation((Node *) linitial(common_columns)))));
+ }
- if (targetlist)
- *targetlist = NIL;
- op->colTypes = NIL;
- op->colTypmods = NIL;
- op->colCollations = NIL;
- op->groupClauses = NIL;
- forboth(ltl, ltargetlist, rtl, rtargetlist)
+ return filtered_columns;
+}
+
+/*
+ * Prepare target list for right query of CORRESPONDING clause.
+ * When filtered is true, filtered columns in target list are
+ * unique.
+ */
+static List *
+FilterColumnsByTL(List *targetlist, List *filter, bool check_uniq,
+ ParseState *pstate, const char *context)
+{
+ List *result = NIL;
+ ListCell *lc;
+ int resno = 1;
+
+ foreach(lc, filter)
+ {
+ TargetEntry *fte = (TargetEntry *) lfirst(lc);
+ ListCell *tle;
+ bool found = false;
+
+ foreach(tle, targetlist)
{
- TargetEntry *ltle = (TargetEntry *) lfirst(ltl);
- TargetEntry *rtle = (TargetEntry *) lfirst(rtl);
- Node *lcolnode = (Node *) ltle->expr;
- Node *rcolnode = (Node *) rtle->expr;
- Oid lcoltype = exprType(lcolnode);
- Oid rcoltype = exprType(rcolnode);
- int32 lcoltypmod = exprTypmod(lcolnode);
- int32 rcoltypmod = exprTypmod(rcolnode);
- Node *bestexpr;
- int bestlocation;
- Oid rescoltype;
- int32 rescoltypmod;
- Oid rescolcoll;
-
- /* select common type, same as CASE et al */
- rescoltype = select_common_type(pstate,
- list_make2(lcolnode, rcolnode),
- context,
- &bestexpr);
- bestlocation = exprLocation(bestexpr);
- /* if same type and same typmod, use typmod; else default */
- if (lcoltype == rcoltype && lcoltypmod == rcoltypmod)
- rescoltypmod = lcoltypmod;
- else
- rescoltypmod = -1;
+ TargetEntry *te = (TargetEntry *) lfirst(tle);
- /*
- * Verify the coercions are actually possible. If not, we'd fail
- * later anyway, but we want to fail now while we have sufficient
- * context to produce an error cursor position.
- *
- * For all non-UNKNOWN-type cases, we verify coercibility but we
- * don't modify the child's expression, for fear of changing the
- * child query's semantics.
- *
- * If a child expression is an UNKNOWN-type Const or Param, we
- * want to replace it with the coerced expression. This can only
- * happen when the child is a leaf set-op node. It's safe to
- * replace the expression because if the child query's semantics
- * depended on the type of this output column, it'd have already
- * coerced the UNKNOWN to something else. We want to do this
- * because (a) we want to verify that a Const is valid for the
- * target type, or resolve the actual type of an UNKNOWN Param,
- * and (b) we want to avoid unnecessary discrepancies between the
- * output type of the child query and the resolved target type.
- * Such a discrepancy would disable optimization in the planner.
- *
- * If it's some other UNKNOWN-type node, eg a Var, we do nothing
- * (knowing that coerce_to_common_type would fail). The planner
- * is sometimes able to fold an UNKNOWN Var to a constant before
- * it has to coerce the type, so failing now would just break
- * cases that might work.
- */
- if (lcoltype != UNKNOWNOID)
- lcolnode = coerce_to_common_type(pstate, lcolnode,
- rescoltype, context);
- else if (IsA(lcolnode, Const) ||
- IsA(lcolnode, Param))
+ if (strcmp(fte->resname, te->resname) == 0)
{
- lcolnode = coerce_to_common_type(pstate, lcolnode,
- rescoltype, context);
- ltle->expr = (Expr *) lcolnode;
- }
+ /* create te with correct resno */
+ result = lappend(result,
+ makeTargetEntry(te->expr,
+ (AttrNumber) resno++,
+ te->resname,
+ false));
+
+ if (!check_uniq)
+ break;
- if (rcoltype != UNKNOWNOID)
- rcolnode = coerce_to_common_type(pstate, rcolnode,
- rescoltype, context);
- else if (IsA(rcolnode, Const) ||
- IsA(rcolnode, Param))
- {
- rcolnode = coerce_to_common_type(pstate, rcolnode,
- rescoltype, context);
- rtle->expr = (Expr *) rcolnode;
+ /*
+ * When "found" is true, then targetlist contains
+ * duplicate filtered columns. Raise exception then.
+ */
+ if (found)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("corresponding column \"%s\" is used more times", te->resname),
+ errhint("In %s queries with CORRESPONDING BY clause the corresponding column names must be unique.",
+ context),
+ parser_errposition(pstate,
+ exprLocation((Node *) te))));
+
+ found = true;
}
+ }
+ }
- /*
- * Select common collation. A common collation is required for
- * all set operators except UNION ALL; see SQL:2008 7.13 <query
- * expression> Syntax Rule 15c. (If we fail to identify a common
- * collation for a UNION ALL column, the curCollations element
- * will be set to InvalidOid, which may result in a runtime error
- * if something at a higher query level wants to use the column's
- * collation.)
- */
- rescolcoll = select_common_collation(pstate,
- list_make2(lcolnode, rcolnode),
- (op->op == SETOP_UNION && op->all));
+ return result;
+}
+
+/*
+ * process right and left target list to set up union'd datatype
+ */
+static void
+makeUnionDatatype(List *ltargetlist, List *rtargetlist, SetOperationStmt *op,
+ List **targetlist, ParseState *pstate, const char *context)
+{
+ ListCell *ltl;
+ ListCell *rtl;
- /* emit results */
- op->colTypes = lappend_oid(op->colTypes, rescoltype);
- op->colTypmods = lappend_int(op->colTypmods, rescoltypmod);
- op->colCollations = lappend_oid(op->colCollations, rescolcoll);
+ if (targetlist)
+ *targetlist = NIL;
- /*
- * For all cases except UNION ALL, identify the grouping operators
- * (and, if available, sorting operators) that will be used to
- * eliminate duplicates.
- */
- if (op->op != SETOP_UNION || !op->all)
- {
- SortGroupClause *grpcl = makeNode(SortGroupClause);
- Oid sortop;
- Oid eqop;
- bool hashable;
- ParseCallbackState pcbstate;
-
- setup_parser_errposition_callback(&pcbstate, pstate,
- bestlocation);
-
- /* determine the eqop and optional sortop */
- get_sort_group_operators(rescoltype,
- false, true, false,
- &sortop, &eqop, NULL,
- &hashable);
-
- cancel_parser_errposition_callback(&pcbstate);
-
- /* we don't have a tlist yet, so can't assign sortgrouprefs */
- grpcl->tleSortGroupRef = 0;
- grpcl->eqop = eqop;
- grpcl->sortop = sortop;
- grpcl->nulls_first = false; /* OK with or without sortop */
- grpcl->hashable = hashable;
-
- op->groupClauses = lappend(op->groupClauses, grpcl);
- }
+ op->colTypes = NIL;
+ op->colTypmods = NIL;
+ op->colCollations = NIL;
+ op->groupClauses = NIL;
- /*
- * Construct a dummy tlist entry to return. We use a SetToDefault
- * node for the expression, since it carries exactly the fields
- * needed, but any other expression node type would do as well.
- */
- if (targetlist)
- {
- SetToDefault *rescolnode = makeNode(SetToDefault);
- TargetEntry *restle;
-
- rescolnode->typeId = rescoltype;
- rescolnode->typeMod = rescoltypmod;
- rescolnode->collation = rescolcoll;
- rescolnode->location = bestlocation;
- restle = makeTargetEntry((Expr *) rescolnode,
- 0, /* no need to set resno */
- NULL,
- false);
- *targetlist = lappend(*targetlist, restle);
- }
+ forboth(ltl, ltargetlist, rtl, rtargetlist)
+ {
+ TargetEntry *ltle = (TargetEntry *) lfirst(ltl);
+ TargetEntry *rtle = (TargetEntry *) lfirst(rtl);
+ Node *lcolnode = (Node *) ltle->expr;
+ Node *rcolnode = (Node *) rtle->expr;
+ Oid lcoltype = exprType(lcolnode);
+ Oid rcoltype = exprType(rcolnode);
+ int32 lcoltypmod = exprTypmod(lcolnode);
+ int32 rcoltypmod = exprTypmod(rcolnode);
+ Node *bestexpr;
+ int bestlocation;
+ Oid rescoltype;
+ int32 rescoltypmod;
+ Oid rescolcoll;
+
+ /* select common type, same as CASE et al */
+ rescoltype = select_common_type(pstate, list_make2(lcolnode, rcolnode),
+ context, &bestexpr);
+ bestlocation = exprLocation(bestexpr);
+ /* if same type and same typmod, use typmod; else default */
+ if (lcoltype == rcoltype && lcoltypmod == rcoltypmod)
+ rescoltypmod = lcoltypmod;
+ else
+ rescoltypmod = -1;
+
+ /*
+ * Verify the coercions are actually possible. If not, we'd fail
+ * later anyway, but we want to fail now while we have sufficient
+ * context to produce an error cursor position.
+ *
+ * For all non-UNKNOWN-type cases, we verify coercibility but we
+ * don't modify the child's expression, for fear of changing the
+ * child query's semantics.
+ *
+ * If a child expression is an UNKNOWN-type Const or Param, we
+ * want to replace it with the coerced expression. This can only
+ * happen when the child is a leaf set-op node. It's safe to
+ * replace the expression because if the child query's semantics
+ * depended on the type of this output column, it'd have already
+ * coerced the UNKNOWN to something else. We want to do this
+ * because (a) we want to verify that a Const is valid for the
+ * target type, or resolve the actual type of an UNKNOWN Param,
+ * and (b) we want to avoid unnecessary discrepancies between the
+ * output type of the child query and the resolved target type.
+ * Such a discrepancy would disable optimization in the planner.
+ *
+ * If it's some other UNKNOWN-type node, eg a Var, we do nothing
+ * (knowing that coerce_to_common_type would fail). The planner
+ * is sometimes able to fold an UNKNOWN Var to a constant before
+ * it has to coerce the type, so failing now would just break
+ * cases that might work.
+ */
+ if (lcoltype != UNKNOWNOID)
+ lcolnode = coerce_to_common_type(pstate, lcolnode, rescoltype,
+ context);
+ else if (IsA(lcolnode, Const) || IsA(lcolnode, Param))
+ {
+ lcolnode = coerce_to_common_type(pstate, lcolnode, rescoltype,
+ context);
+ ltle->expr = (Expr *) lcolnode;
}
- return (Node *) op;
+ if (rcoltype != UNKNOWNOID)
+ rcolnode = coerce_to_common_type(pstate, rcolnode, rescoltype,
+ context);
+ else if (IsA(rcolnode, Const) || IsA(rcolnode, Param))
+ {
+ rcolnode = coerce_to_common_type(pstate, rcolnode, rescoltype,
+ context);
+ rtle->expr = (Expr *) rcolnode;
+ }
+
+ /*
+ * Select common collation. A common collation is required for
+ * all set operators except UNION ALL; see SQL:2008 7.13 <query
+ * expression> Syntax Rule 15c. (If we fail to identify a common
+ * collation for a UNION ALL column, the curCollations element
+ * will be set to InvalidOid, which may result in a runtime error
+ * if something at a higher query level wants to use the column's
+ * collation.)
+ */
+ rescolcoll = select_common_collation(pstate,
+ list_make2(lcolnode, rcolnode),
+ (op->op == SETOP_UNION && op->all));
+
+ /* emit results */
+ op->colTypes = lappend_oid(op->colTypes, rescoltype);
+ op->colTypmods = lappend_int(op->colTypmods, rescoltypmod);
+ op->colCollations = lappend_oid(op->colCollations, rescolcoll);
+
+ /*
+ * For all cases except UNION ALL, identify the grouping operators
+ * (and, if available, sorting operators) that will be used to
+ * eliminate duplicates.
+ */
+ if (op->op != SETOP_UNION || !op->all)
+ {
+ SortGroupClause *grpcl = makeNode(SortGroupClause);
+ Oid sortop;
+ Oid eqop;
+ bool hashable;
+ ParseCallbackState pcbstate;
+
+ setup_parser_errposition_callback(&pcbstate, pstate, bestlocation);
+
+ /* determine the eqop and optional sortop */
+ get_sort_group_operators(rescoltype, false, true, false, &sortop,
+ &eqop, NULL, &hashable);
+
+ cancel_parser_errposition_callback(&pcbstate);
+
+ /* we don't have a tlist yet, so can't assign sortgrouprefs */
+ grpcl->tleSortGroupRef = 0;
+ grpcl->eqop = eqop;
+ grpcl->sortop = sortop;
+ grpcl->nulls_first = false; /* OK with or without sortop */
+ grpcl->hashable = hashable;
+
+ op->groupClauses = lappend(op->groupClauses, grpcl);
+ }
+
+ /*
+ * Construct a dummy tlist entry to return. We use a SetToDefault
+ * node for the expression, since it carries exactly the fields
+ * needed, but any other expression node type would do as well.
+ */
+ if (targetlist)
+ {
+ SetToDefault *rescolnode = makeNode(SetToDefault);
+ TargetEntry *restle;
+
+ rescolnode->typeId = rescoltype;
+ rescolnode->typeMod = rescoltypmod;
+ rescolnode->collation = rescolcoll;
+ rescolnode->location = bestlocation;
+
+ /* no need to set resno */
+ restle = makeTargetEntry((Expr *) rescolnode, 0,
+ NULL, false );
+ *targetlist = lappend(*targetlist, restle);
+ }
}
+
}
/*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 19dd77d787..a2bef6083a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -166,7 +166,7 @@ static void insertSelectOptions(SelectStmt *stmt,
Node *limitOffset, Node *limitCount,
WithClause *withClause,
core_yyscan_t yyscanner);
-static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg);
+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 *makeAndExpr(Node *lexpr, Node *rexpr, int location);
@@ -375,7 +375,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
func_as createfunc_opt_list alterfunc_opt_list
old_aggr_definition old_aggr_list
oper_argtypes RuleActionList RuleActionMulti
- opt_column_list columnList opt_name_list
+ opt_column_list columnList uniqColumnList opt_name_list
sort_clause opt_sort_clause sortby_list index_params
name_list role_list from_clause from_list opt_array_bounds
qualified_name_list any_name any_name_list type_name_list
@@ -394,7 +394,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
relation_expr_list dostmt_opt_list
transform_element_list transform_type_list
TriggerTransitions TriggerReferencing
- publication_name_list
+ publication_name_list opt_corresponding_clause
%type <list> group_by_list
%type <node> group_by_item empty_grouping_set rollup_clause cube_clause
@@ -614,7 +614,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
CLUSTER COALESCE COLLATE COLLATION COLUMN COLUMNS COMMENT COMMENTS COMMIT
COMMITTED CONCURRENTLY CONFIGURATION CONFLICT CONNECTION CONSTRAINT
- CONSTRAINTS CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE
+ CONSTRAINTS CONTENT_P CONTINUE_P CONVERSION_P COPY CORRESPONDING COST CREATE
CROSS CSV CUBE CURRENT_P
CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
@@ -3577,6 +3577,31 @@ columnList:
| columnList ',' columnElem { $$ = lappend($1, $3); }
;
+uniqColumnList:
+ columnElem
+ {
+ $$ = list_make1($1);
+ }
+ | uniqColumnList ',' columnElem
+ {
+ ListCell *lc;
+ char *name = strVal($3);
+
+ foreach(lc, $1)
+ {
+ char *prevname = strVal((Value *) lfirst(lc));
+
+ if (strcmp(prevname, name) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("name is not unique"),
+ parser_errposition(@3)));
+ }
+
+ $$ = lappend($1, $3);
+ }
+ ;
+
columnElem: ColId
{
$$ = (Node *) makeString($1);
@@ -10877,20 +10902,26 @@ simple_select:
n->fromClause = list_make1($2);
$$ = (Node *)n;
}
- | select_clause UNION all_or_distinct select_clause
+ | select_clause UNION all_or_distinct opt_corresponding_clause select_clause
{
- $$ = makeSetOp(SETOP_UNION, $3, $1, $4);
+ $$ = makeSetOp(SETOP_UNION, $3, $4, $1, $5);
}
- | select_clause INTERSECT all_or_distinct select_clause
+ | select_clause INTERSECT all_or_distinct opt_corresponding_clause select_clause
{
- $$ = makeSetOp(SETOP_INTERSECT, $3, $1, $4);
+ $$ = makeSetOp(SETOP_INTERSECT, $3, $4, $1, $5);
}
- | select_clause EXCEPT all_or_distinct select_clause
+ | select_clause EXCEPT all_or_distinct opt_corresponding_clause select_clause
{
- $$ = makeSetOp(SETOP_EXCEPT, $3, $1, $4);
+ $$ = makeSetOp(SETOP_EXCEPT, $3, $4, $1, $5);
}
;
+opt_corresponding_clause:
+ CORRESPONDING BY '(' uniqColumnList ')' { $$ = $4; }
+ | CORRESPONDING { $$ = list_make1(NIL); }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
/*
* SQL standard WITH clause looks like:
*
@@ -14231,7 +14262,6 @@ name_list: name
{ $$ = lappend($1, makeString($3)); }
;
-
name: ColId { $$ = $1; };
database_name:
@@ -14548,6 +14578,7 @@ unreserved_keyword:
| CONTINUE_P
| CONVERSION_P
| COPY
+ | CORRESPONDING
| COST
| CSV
| CUBE
@@ -15381,7 +15412,7 @@ insertSelectOptions(SelectStmt *stmt,
}
static Node *
-makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg)
+makeSetOp(SetOperation op, bool all, List *correspondingClause, Node *larg, Node *rarg)
{
SelectStmt *n = makeNode(SelectStmt);
@@ -15389,6 +15420,7 @@ makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg)
n->all = all;
n->larg = (SelectStmt *) larg;
n->rarg = (SelectStmt *) rarg;
+ n->correspondingClause = correspondingClause;
return (Node *) n;
}
diff --git a/src/backend/parser/parse_type.c b/src/backend/parser/parse_type.c
index 0d7a2b1e1b..b553d847d8 100644
--- a/src/backend/parser/parse_type.c
+++ b/src/backend/parser/parse_type.c
@@ -735,7 +735,8 @@ typeStringToTypeName(const char *str)
stmt->limitCount != NULL ||
stmt->lockingClause != NIL ||
stmt->withClause != NULL ||
- stmt->op != SETOP_NONE)
+ stmt->op != SETOP_NONE ||
+ stmt->correspondingClause != NULL)
goto fail;
if (list_length(stmt->targetList) != 1)
goto fail;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index d57d5568b2..60715398a2 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5452,6 +5452,30 @@ get_setop_query(Node *setOp, Query *query, deparse_context *context,
}
if (op->all)
appendStringInfoString(buf, "ALL ");
+ if (op->correspondingColumns != NIL )
+ {
+ if (op->hasCorrespondingBy)
+ {
+ const char *sep;
+ ListCell *l;
+ appendStringInfoString(buf, "CORRESPONDING BY(");
+ sep = "";
+
+ foreach(l, op->correspondingColumns)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(l);
+
+ appendStringInfoString(buf, sep);
+ appendStringInfo(buf, "%s", tle->resname);
+ sep = ", ";
+ }
+ appendStringInfoChar(buf, ')');
+
+ }
+ else
+
+ appendStringInfoString(buf, "CORRESPONDING ");
+ }
/* Always parenthesize if RHS is another setop */
need_paren = IsA(op->rarg, SetOperationStmt);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 3a71dd5b37..ed8ce2f2d0 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1460,6 +1460,7 @@ typedef struct SelectStmt
*/
List *distinctClause; /* NULL, list of DISTINCT ON exprs, or
* lcons(NIL,NIL) for all (SELECT DISTINCT) */
+ List *correspondingClause; /* CORRESPONDING BY clauses*/
IntoClause *intoClause; /* target for SELECT INTO */
List *targetList; /* the target list (of ResTarget) */
List *fromClause; /* the FROM clause */
@@ -1495,7 +1496,6 @@ typedef struct SelectStmt
bool all; /* ALL specified? */
struct SelectStmt *larg; /* left child */
struct SelectStmt *rarg; /* right child */
- /* Eventually add fields for CORRESPONDING spec here */
} SelectStmt;
@@ -1525,8 +1525,8 @@ typedef struct SetOperationStmt
bool all; /* ALL specified? */
Node *larg; /* left child */
Node *rarg; /* right child */
- /* Eventually add fields for CORRESPONDING spec here */
-
+ List *correspondingColumns; /* list of corresponding column names */
+ bool hasCorrespondingBy; /* has corresponding by cluase? */
/* Fields derived during parse analysis: */
List *colTypes; /* OID list of output column type OIDs */
List *colTypmods; /* integer list of output column typmods */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 6cd36c7fe3..fad33d3950 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -97,6 +97,7 @@ PG_KEYWORD("content", CONTENT_P, UNRESERVED_KEYWORD)
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)
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index ce0c8cedf8..6b44903d7b 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -1597,3 +1597,28 @@ select pg_get_viewdef('tt19v', true);
set client_min_messages = warning;
DROP SCHEMA temp_view_test CASCADE;
DROP SCHEMA testviewschm2 CASCADE;
+-- views with corresponding clause
+create view view_corresponding_01 as select 1 as a, 2 as b union all corresponding select 3 as a, 4 as b;
+select * from view_corresponding_01;
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
+(2 rows)
+
+create view view_corresponding_02 as select 1 as a, 2 as b union all corresponding by (a,b) select 3 as a, 4 as b, 5 as c;
+select * from view_corresponding_02;
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
+(2 rows)
+
+create view view_corresponding_03 as select 1 as a, 2 as b union all corresponding by (b,a) select 3 as a, 4 as b, 5 as c;
+select * from view_corresponding_03;
+ b | a
+---+---
+ 2 | 1
+ 4 | 3
+(2 rows)
+
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index e8f8726c53..8c1ff70519 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2344,6 +2344,23 @@ toyemp| SELECT emp.name,
emp.location,
(12 * emp.salary) AS annualsal
FROM emp;
+view_corresponding_01| SELECT 1 AS a,
+ 2 AS b
+UNION ALL CORRESPONDING
+ SELECT 3 AS a,
+ 4 AS b;
+view_corresponding_02| SELECT 1 AS a,
+ 2 AS b
+UNION ALL CORRESPONDING BY(a, b)
+ SELECT 3 AS a,
+ 4 AS b,
+ 5 AS c;
+view_corresponding_03| SELECT 1 AS b,
+ 2 AS a
+UNION ALL CORRESPONDING BY(b, a)
+ SELECT 3 AS b,
+ 4 AS a,
+ 5 AS c;
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
pg_settings|pg_settings_n|CREATE RULE pg_settings_n AS
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 4d697bada7..dd64f37efd 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -59,6 +59,87 @@ SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1;
2.2
(2 rows)
+SELECT 1 AS two UNION CORRESPONDING SELECT 2 two UNION CORRESPONDING SELECT 2 two;
+ two
+-----
+ 2
+ 1
+(2 rows)
+
+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(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 (c,b) SELECT 4 a, 5 b, 6 c, 8 d;
+ c | b
+---+---
+ 3 | 2
+ 6 | 5
+(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)
+
+-- 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)
+
+-- should to fail
+SELECT 10 a, 20 b UNION ALL CORRESPONDING SELECT 10 c, 20 d;
+ERROR: there is not any corresponding name
+LINE 1: SELECT 10 a, 20 b UNION ALL CORRESPONDING SELECT 10 c, 20 d;
+ ^
+HINT: UNION queries with a CORRESPONDING clause must have at least one column with the same name
+SELECT 10 a, 20 b UNION ALL CORRESPONDING BY (a,b) SELECT 10 c, 20 d;
+ERROR: there is not any corresponding name
+LINE 1: SELECT 10 a, 20 b UNION ALL CORRESPONDING BY (a,b) SELECT 10...
+ ^
+HINT: UNION queries with a CORRESPONDING clause must have at least one column with the same name
+SELECT 10 a, 20 b UNION ALL CORRESPONDING BY (x) SELECT 10 c, 20 d;
+ERROR: there is not any corresponding name
+LINE 1: SELECT 10 a, 20 b UNION ALL CORRESPONDING BY (x) SELECT 10 c...
+ ^
+HINT: UNION queries with a CORRESPONDING clause must have at least one column with the same name
-- Mixed types
SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
two
@@ -124,6 +205,75 @@ SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
2
(2 rows)
+-- other corresponding clause tests,
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3 UNION ALL CORRESPONDING SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6;
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
+(2 rows)
+
+-- when column is not in result, then the name should not be unique
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6;
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
+(2 rows)
+
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3 UNION ALL CORRESPONDING SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x6;
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
+(2 rows)
+
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x6;
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
+(2 rows)
+
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING BY(a,b) SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x3;
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
+(2 rows)
+
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x6 UNION ALL CORRESPONDING BY(a,b) SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x6;
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
+(2 rows)
+
+-- should fail
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -5 AS x3;
+ERROR: corresponding column "x3" is used more times
+LINE 1: SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 U...
+ ^
+HINT: In UNION queries with CORRESPONDING clause the corresponding column names must be unique.
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3 UNION ALL CORRESPONDING SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -5 AS x3, -10 AS x3;
+ERROR: corresponding column "x3" is used more times
+LINE 1: ...ELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -5 AS x3, -10 AS x3;
+ ^
+HINT: In UNION queries with CORRESPONDING clause the corresponding column names must be unique.
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING BY(a,b,x3) SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -5 AS x3;
+ERROR: corresponding column "x3" is used more times
+LINE 1: SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 U...
+ ^
+HINT: In UNION queries with CORRESPONDING BY clause the corresponding column names must be unique.
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3 UNION ALL CORRESPONDING BY(a,b,x3) SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -5 AS x3, -10 AS x3;
+ERROR: corresponding column "x3" is used more times
+LINE 1: ...ELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -5 AS x3, -10 AS x3;
+ ^
+HINT: In UNION queries with CORRESPONDING BY clause the corresponding column names must be unique.
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x6 UNION ALL CORRESPONDING BY(a,b,b) SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x6;
+ERROR: name is not unique
+LINE 1: ... 0 AS x3, -1 AS x6 UNION ALL CORRESPONDING BY(a,b,b) SELECT ...
+ ^
--
-- Try testing from tables...
--
@@ -258,6 +408,74 @@ ORDER BY 1;
hi de ho neighbor
(5 rows)
+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 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)
+
+SELECT * FROM FLOAT8_TBL
+UNION corresponding SELECT * FROM FLOAT8_TBL;
+ f1
+-----------------------
+ 0
+ -1.2345678901234e-200
+ -34.84
+ -1.2345678901234e+200
+ -1004.3
+(5 rows)
+
--
-- INTERSECT and EXCEPT
--
@@ -320,6 +538,63 @@ SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1;
SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE;
ERROR: FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT
+SELECT q2 FROM int8_tbl INTERSECT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+ q2
+------------------
+ 123
+ 4567890123456789
+(2 rows)
+
+SELECT q2 FROM int8_tbl INTERSECT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+ q2
+------------------
+ 123
+ 4567890123456789
+ 4567890123456789
+(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
+------------------
+ 123
+ 4567890123456789
+(2 rows)
+
+SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q2 AS q1 FROM int8_tbl;
+ q1
+------------------
+ 123
+ 4567890123456789
+ 4567890123456789
+(3 rows)
+
--
-- Mixed types
--
@@ -338,6 +613,21 @@ SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1;
-1.2345678901234e-200
(4 rows)
+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)
+
--
-- Operator precedence and (((((extra))))) parentheses
--
@@ -424,6 +714,24 @@ SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)))
4567890123456789
(2 rows)
+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
--
@@ -459,6 +767,22 @@ SELECT '3.4'::numeric UNION SELECT 'foo';
ERROR: invalid input syntax for type numeric: "foo"
LINE 1: SELECT '3.4'::numeric UNION SELECT 'foo';
^
+SELECT '3.4'::numeric AS a UNION CORRESPONDING SELECT 'foo' AS a;
+ERROR: invalid input syntax for type numeric: "foo"
+LINE 1: ...CT '3.4'::numeric AS a UNION CORRESPONDING SELECT 'foo' AS a...
+ ^
+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)
+
--
-- Test that expression-index constraints can be pushed down through
-- UNION or UNION ALL
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index c27f1034e1..3ea31ba621 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -532,3 +532,13 @@ select pg_get_viewdef('tt19v', true);
set client_min_messages = warning;
DROP SCHEMA temp_view_test CASCADE;
DROP SCHEMA testviewschm2 CASCADE;
+
+-- views with corresponding clause
+create view view_corresponding_01 as select 1 as a, 2 as b union all corresponding select 3 as a, 4 as b;
+select * from view_corresponding_01;
+
+create view view_corresponding_02 as select 1 as a, 2 as b union all corresponding by (a,b) select 3 as a, 4 as b, 5 as c;
+select * from view_corresponding_02;
+
+create view view_corresponding_03 as select 1 as a, 2 as b union all corresponding by (b,a) select 3 as a, 4 as b, 5 as c;
+select * from view_corresponding_03;
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index 48e6850798..1f0967e4dd 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -20,6 +20,35 @@ SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1;
+SELECT 1 AS two UNION CORRESPONDING SELECT 2 two UNION CORRESPONDING SELECT 2 two;
+
+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(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 (c,b) SELECT 4 a, 5 b, 6 c, 8 d;
+
+-- 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;
+
+-- 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;
+
+-- should to fail
+SELECT 10 a, 20 b UNION ALL CORRESPONDING SELECT 10 c, 20 d;
+
+SELECT 10 a, 20 b UNION ALL CORRESPONDING BY (a,b) SELECT 10 c, 20 d;
+
+SELECT 10 a, 20 b UNION ALL CORRESPONDING BY (x) SELECT 10 c, 20 d;
+
-- Mixed types
SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
@@ -40,6 +69,24 @@ SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
+-- other corresponding clause tests,
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3 UNION ALL CORRESPONDING SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6;
+
+-- when column is not in result, then the name should not be unique
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6;
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3 UNION ALL CORRESPONDING SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x6;
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x6;
+
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING BY(a,b) SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x3;
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x6 UNION ALL CORRESPONDING BY(a,b) SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x6;
+
+-- should fail
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -5 AS x3;
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3 UNION ALL CORRESPONDING SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -5 AS x3, -10 AS x3;
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING BY(a,b,x3) SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -5 AS x3;
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3 UNION ALL CORRESPONDING BY(a,b,x3) SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -5 AS x3, -10 AS x3;
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x6 UNION ALL CORRESPONDING BY(a,b,b) SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x6;
+
--
-- Try testing from tables...
--
@@ -90,6 +137,29 @@ UNION
SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL
ORDER BY 1;
+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 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;
+
+SELECT * FROM FLOAT8_TBL
+UNION corresponding SELECT * FROM FLOAT8_TBL;
+
--
-- INTERSECT and EXCEPT
--
@@ -112,6 +182,22 @@ SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1;
SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE;
+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
--
@@ -120,6 +206,10 @@ SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl ORDER BY 1;
SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1;
+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;
+
--
-- Operator precedence and (((((extra))))) parentheses
--
@@ -150,6 +240,15 @@ SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
-- But this should work:
SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) ORDER BY 1;
+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
--
@@ -170,6 +269,13 @@ ORDER BY 1;
-- This should fail, but it should produce an error cursor
SELECT '3.4'::numeric UNION SELECT 'foo';
+SELECT '3.4'::numeric AS a UNION CORRESPONDING SELECT 'foo' AS a;
+
+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;
+
--
-- Test that expression-index constraints can be pushed down through
-- UNION or UNION ALL
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
