Hi
2017-03-30 21:55 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>:
>
>
> 2017-03-30 21:43 GMT+02:00 Tom Lane <t...@sss.pgh.pa.us>:
>
>> Pavel Stehule <pavel.steh...@gmail.com> writes:
>> > Is following use case defined in standard?
>>
>> > postgres=# 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 x6
>> > UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS a, -100 AS aa;
>> > ┌───┐
>> > │ a │
>> > ╞═══╡
>> > │ 1 │
>> > │ 3 │
>> > │ 6 │
>> > └───┘
>> > (3 rows)
>>
>> > It depends on order of implementation
>>
>> > if we do (T1 U T2) U T3 ---> then result is correct,
>> > but if we do T1 U (T2 U T3) ---> than it should to fail
>>
>> UNION ALL should associate left-to-right, just like most other binary
>> operators, so this looks fine to me. Did you check that you get an
>> error if you put in parens to force the other order?
>>
>
> yes - it fails
>
> postgres=# 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 x6 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS a, -100 AS aa);
> ERROR: column name "b" can not be used in CORRESPONDING BY list
> LINE 1: ...b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING BY(a,b) (SELECT...
> ^
> HINT: UNION queries with a CORRESPONDING BY clause must contain column
> names from both tables.
> Time: 1,135 ms
>
>
I fixed wrong my comment
I have no any other objections, I'll mark this patch as ready for commiter
Regards
Pavel
> Regards
>
> Pavel
>
>>
>> regards, tom lane
>>
>
>
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 30792f45f1..2d60718ff1 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,14 +1662,31 @@ 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>
- </sect1>
+ <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. The names in column list
+ must be unique.
+ </para>
+
+ <para>
+ The names of columns in result when <literal>CORRESPONDING</> or
+ <literal>CORRESPONDING BY</> clause is used must be unique in
+ <replaceable>query1</replaceable> and <replaceable>query2</replaceable>.
+ </para>
+ </sect1>
<sect1 id="queries-order">
<title>Sorting Rows</title>
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 1c88d601bd..11e0590eec 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);
@@ -4588,6 +4591,8 @@ _copyValue(const Value *from)
(int) from->type);
break;
}
+ COPY_LOCATION_FIELD(location);
+
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 5941b7a2bf..dd6598d85b 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);
@@ -2935,6 +2938,8 @@ _equalValue(const Value *a, const Value *b)
break;
}
+ COMPARE_LOCATION_FIELD(location);
+
return true;
}
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 bbb63a4bfa..09c097857d 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2664,6 +2664,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);
}
@@ -2971,6 +2972,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);
@@ -3148,6 +3151,7 @@ _outAExpr(StringInfo str, const A_Expr *node)
static void
_outValue(StringInfo str, const Value *value)
{
+ /* NB: this isn't a complete set of fields */
switch (value->type)
{
case T_Integer:
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/nodes/value.c b/src/backend/nodes/value.c
index 5d2f96c103..72afc172f9 100644
--- a/src/backend/nodes/value.c
+++ b/src/backend/nodes/value.c
@@ -26,6 +26,7 @@ makeInteger(long i)
v->type = T_Integer;
v->val.ival = i;
+ v->location = -1;
return v;
}
@@ -41,6 +42,7 @@ makeFloat(char *numericStr)
v->type = T_Float;
v->val.str = numericStr;
+ v->location = -1;
return v;
}
@@ -56,6 +58,7 @@ makeString(char *str)
v->type = T_String;
v->val.str = str;
+ v->location = -1;
return v;
}
@@ -71,5 +74,6 @@ makeBitString(char *str)
v->type = T_BitString;
v->val.str = str;
+ v->location = -1;
return v;
}
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index e327e66f6b..f02066dd5b 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 f6025225be..65dc7d153c 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
@@ -1653,7 +1661,37 @@ 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);
+ /*
+ * In the case of corresponding without by clause property across
+ * the statement may differ
+ */
+ if (!sostmt->hasCorrespondingBy)
+ {
+ Node *correspodning_node;
+ correspodning_node = sostmt->larg;
+ while (correspodning_node && IsA(correspodning_node, SetOperationStmt))
+ {
+ SetOperationStmt *op = (SetOperationStmt *) correspodning_node;
+ op->correspondingColumns = sostmt->correspondingColumns;
+ op->colTypes = sostmt->colTypes;
+ op->colTypmods = sostmt->colTypmods;
+ op->colCollations = sostmt->colCollations;
+ op->groupClauses = sostmt->groupClauses;
+
+ correspodning_node = op->larg;
+ }
+ }
+ }
+ else
+ left_tlist = list_head(leftmostQuery->targetList);
forthree(lct, sostmt->colTypes,
lcm, sostmt->colTypmods,
@@ -1913,8 +1951,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" :
@@ -1925,6 +1961,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,
@@ -1949,177 +2063,417 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
false,
&rtargetlist);
- /*
- * 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))));
-
- if (targetlist)
- *targetlist = NIL;
- op->colTypes = NIL;
- op->colTypmods = NIL;
- op->colCollations = NIL;
- op->groupClauses = NIL;
- forboth(ltl, ltargetlist, rtl, rtargetlist)
+ if (op->correspondingColumns == NIL )
{
- 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;
-
+ makeUnionDatatype(ltargetlist, rtargetlist, op, targetlist, pstate,
+ context);
/*
- * 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.
+ * Verify that the two children have the same number of non-junk
+ * columns, and determine the types of the merged output columns.
*/
- 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;
- }
+ 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);
- if (rcoltype != UNKNOWNOID)
- rcolnode = coerce_to_common_type(pstate, rcolnode,
- rescoltype, context);
- else if (IsA(rcolnode, Const) ||
- IsA(rcolnode, Param))
+ foreach(rtlc, rtargetlist)
+ {
+ ListCell *lc;
+ TargetEntry *rte = (TargetEntry *) lfirst(rtlc);
+
+ Assert(rte->resname != NULL);
+
+ if (strcmp(lte->resname, rte->resname) == 0)
{
- rcolnode = coerce_to_common_type(pstate, rcolnode,
- rescoltype, context);
- rtle->expr = (Expr *) rcolnode;
+ 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));
}
+ }
+ }
- /*
- * 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 common_columns;
+}
- /* emit results */
- op->colTypes = lappend_oid(op->colTypes, rescoltype);
- op->colTypmods = lappend_int(op->colTypmods, rescoltypmod);
- op->colCollations = lappend_oid(op->colCollations, rescolcoll);
+/*
+ * 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;
- /*
- * 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)
+ Assert(common_columns != NIL);
+ Assert(filter != NIL);
+
+ foreach(flc, filter)
+ {
+ Value *strval = (Value *) lfirst(flc);
+ char *name = strVal(strval);
+ ListCell *tlc;
+ bool found = false;
+
+ foreach(tlc, common_columns)
+ {
+ TargetEntry *tec = (TargetEntry *) lfirst(tlc);
+
+ if (strcmp(tec->resname, name) == 0)
{
- 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);
+ 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, strval->location)));
+ }
+
+ /* create te with correct resno */
+ filtered_columns = lappend(filtered_columns,
+ makeTargetEntry(tec->expr,
+ (AttrNumber) resno++,
+ tec->resname,
+ false));
}
+ }
- /*
- * 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)
+ /*
+ * CORRESPONDING BY clause contains a column name that is not
+ * in common columns.
+ */
+ if (!found)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ 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, strval->location)));
+ }
+
+ return filtered_columns;
+}
+
+/*
+ * Prepare target list for right query of CORRESPONDING clause.
+ * When check_uniq is true, we should to check uniq names from
+ * filter in target list. When it is false, then uniquenes was
+ * checked in CommonColumns function and should not be checked
+ * here again.
+ */
+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 *te = (TargetEntry *) lfirst(tle);
+
+ if (strcmp(fte->resname, te->resname) == 0)
{
- 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);
+ /* create te with correct resno */
+ result = lappend(result,
+ makeTargetEntry(te->expr,
+ (AttrNumber) resno++,
+ te->resname,
+ false));
+
+ if (!check_uniq)
+ break;
+
+ /*
+ * 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;
}
}
+ }
- return (Node *) op;
+ 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;
+
+ if (targetlist)
+ *targetlist = NIL;
+
+ op->colTypes = NIL;
+ op->colTypmods = NIL;
+ op->colCollations = NIL;
+ op->groupClauses = NIL;
+
+ 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;
+ }
+
+ 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 9d53a29ad2..6e5257d30d 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);
@@ -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
@@ -3579,7 +3579,10 @@ columnList:
columnElem: ColId
{
- $$ = (Node *) makeString($1);
+ Value *v = makeString($1);
+
+ v->location = @1;
+ $$ = (Node *) v;
}
;
@@ -10878,20 +10881,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 '(' columnList ')' { $$ = $4; }
+ | CORRESPONDING { $$ = list_make1(NIL); }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
/*
* SQL standard WITH clause looks like:
*
@@ -14232,7 +14241,6 @@ name_list: name
{ $$ = lappend($1, makeString($3)); }
;
-
name: ColId { $$ = $1; };
database_name:
@@ -14549,6 +14557,7 @@ unreserved_keyword:
| CONTINUE_P
| CONVERSION_P
| COPY
+ | CORRESPONDING
| COST
| CSV
| CUBE
@@ -15383,7 +15392,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);
@@ -15391,6 +15400,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 c2681ced2a..adf797d600 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5442,6 +5442,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/nodes/value.h b/src/include/nodes/value.h
index ede97b7bcd..bf3b6e9b68 100644
--- a/src/include/nodes/value.h
+++ b/src/include/nodes/value.h
@@ -47,6 +47,7 @@ typedef struct Value
long ival; /* machine integer */
char *str; /* string */
} val;
+ int location; /* token location, or -1 if unknown */
} Value;
#define intVal(v) (((Value *)(v))->val.ival)
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index cd21a789d5..7f1c2554e3 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 c719262720..3bcb89ceca 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -1626,3 +1626,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 d706f42b2d..3e36dc58f9 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2345,6 +2345,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..59d6c001ef 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,147 @@ 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 SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x6 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS b, 5 AS a;
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
+ 5 | 6
+(3 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)
+
+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 x6 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS b, 5 AS a;
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
+ 5 | 6
+(3 rows)
+
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING BY(b) SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x6 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS b, -100 AS a;
+ b
+---
+ 2
+ 4
+ 6
+(3 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 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS a, -100 AS x9;
+ a
+---
+ 1
+ 3
+ 6
+(3 rows)
+
+SELECT 0 AS a, 1 AS b, 0 AS c UNION ALL CORRESPONDING SELECT 2 AS a, 3 AS b, 10 AS c, 20 AS d UNION ALL CORRESPONDING SELECT 11 AS c, 21 AS d;
+ c
+----
+ 0
+ 10
+ 11
+(3 rows)
+
+SELECT 0 AS a, 1 AS b UNION ALL CORRESPONDING SELECT 2 AS a, 3 AS b, 10 AS c, 20 AS d UNION ALL CORRESPONDING SELECT 11 AS c, 21 AS d, 4 AS a;
+ a
+---
+ 0
+ 2
+ 4
+(3 rows)
+
+SELECT 0 AS a, 1 AS b UNION ALL CORRESPONDING SELECT 2 AS a, 3 AS b, 10 AS c, 20 AS d UNION ALL CORRESPONDING SELECT 11 AS c, 21 AS d, 5 AS b;
+ b
+---
+ 1
+ 3
+ 5
+(3 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: column name "b" is not unique in CORRESPONDING BY clause
+LINE 1: ... 0 AS x3, -1 AS x6 UNION ALL CORRESPONDING BY(a,b,b) SELECT ...
+ ^
+HINT: CORRESPONDING BY clause must contain unique column names only.
+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 x6 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS bb, -100 AS aa;
+ERROR: there is not any corresponding name
+LINE 1: SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 U...
+ ^
+HINT: UNION queries with a CORRESPONDING clause must have at least one column with the same name
+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 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS xxx, -100 AS x9;
+ERROR: there is not any corresponding name
+LINE 1: SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 U...
+ ^
+HINT: UNION queries with a CORRESPONDING clause must have at least one column with the same name
+SELECT 0 AS a, 1 AS b UNION ALL CORRESPONDING SELECT 2 AS a, 3 AS b, 10 AS c, 20 AS d UNION ALL CORRESPONDING SELECT 11 AS c, 21 AS d;
+ERROR: there is not any corresponding name
+LINE 1: SELECT 0 AS a, 1 AS b UNION ALL CORRESPONDING SELECT 2 AS a,...
+ ^
+HINT: UNION queries with a CORRESPONDING clause must have at least one column with the same name
--
-- Try testing from tables...
--
@@ -258,6 +480,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 +610,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 +685,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 +786,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 +839,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 d6f50d6105..f61a01323b 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -551,3 +551,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..731e886b9a 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,37 @@ 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 SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x6 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS b, 5 AS a;
+
+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;
+
+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 x6 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS b, 5 AS a;
+SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING BY(b) SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6, -1 AS x6 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS b, -100 AS a;
+
+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 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS a, -100 AS x9;
+SELECT 0 AS a, 1 AS b, 0 AS c UNION ALL CORRESPONDING SELECT 2 AS a, 3 AS b, 10 AS c, 20 AS d UNION ALL CORRESPONDING SELECT 11 AS c, 21 AS d;
+SELECT 0 AS a, 1 AS b UNION ALL CORRESPONDING SELECT 2 AS a, 3 AS b, 10 AS c, 20 AS d UNION ALL CORRESPONDING SELECT 11 AS c, 21 AS d, 4 AS a;
+SELECT 0 AS a, 1 AS b UNION ALL CORRESPONDING SELECT 2 AS a, 3 AS b, 10 AS c, 20 AS d UNION ALL CORRESPONDING SELECT 11 AS c, 21 AS d, 5 AS b;
+
+-- 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;
+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 x6 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS bb, -100 AS aa;
+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 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS xxx, -100 AS x9;
+SELECT 0 AS a, 1 AS b UNION ALL CORRESPONDING SELECT 2 AS a, 3 AS b, 10 AS c, 20 AS d UNION ALL CORRESPONDING SELECT 11 AS c, 21 AS d;
+
--
-- Try testing from tables...
--
@@ -90,6 +150,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 +195,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 +219,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 +253,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 +282,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
