(PATCH) Adding CORRESPONDING to Set Operations
Adding CORRESPONDING to Set Operations
Initial patch, filename: corresponding_clause_v2.patch
This patch adds CORRESPONDING clause to set operations according to
SQL20nn standard draft as Feature F301, "CORRESPONDING in query
expressions"
Corresponding clause either contains a BY(...) clause or not. If it
doesn't have a BY(...) clause the usage is as follows.
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING 4 b, 5 d, 6 c, 7 f;
with output:
b c
-----
2 3
4 6
i.e. matching column names are filtered and are only output from the
whole set operation clause.
If we introduce a BY(...) clause, then column names are further
intersected with that BY clause:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) 4 b, 5 d, 6 c, 7 f;
with output:
b
--
2
4
This patch compiles and tests successfully with master branch.
It has been tested only on Pardus Linux i686 ( Kernel 2.6.37.6 #1 SMP
i686 i686 i386 GNU/Linux)
This patch includes documentation and add one regression file.
This patch addresses the following TODO item:
SQL Commands: Add CORRESPONDING BY to UNION/INTERSECT/EXCEPT
Best Regards,
Kerem KAT
Attachments:
corresponding_clause_v2.patchtext/x-patch; charset=US-ASCII; name=corresponding_clause_v2.patchDownload
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***************
*** 1225,1230 ****
--- 1225,1233 ----
<primary>EXCEPT</primary>
</indexterm>
<indexterm zone="queries-union">
+ <primary>CORRESPONDING</primary>
+ </indexterm>
+ <indexterm zone="queries-union">
<primary>set union</primary>
</indexterm>
<indexterm zone="queries-union">
***************
*** 1241,1249 ****
The results of two queries can be combined using the set operations
union, intersection, and difference. The syntax is
<synopsis>
! <replaceable>query1</replaceable> UNION <optional>ALL</optional> <replaceable>query2</replaceable>
! <replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <replaceable>query2</replaceable>
! <replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <replaceable>query2</replaceable>
</synopsis>
<replaceable>query1</replaceable> and
<replaceable>query2</replaceable> are queries that can use any of
--- 1244,1252 ----
The results of two queries can be combined using the set operations
union, intersection, and difference. The syntax is
<synopsis>
! <replaceable>query1</replaceable> UNION <optional>ALL</optional> <optional>CORRESPONDING <optional>BY (<replaceable>select_list</replaceable>)</optional></optional> <replaceable>query2</replaceable>
! <replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <optional>CORRESPONDING <optional>BY (<replaceable>select_list</replaceable>)</optional></optional> <replaceable>query2</replaceable>
! <replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <optional>CORRESPONDING <optional>BY (<replaceable>select_list</replaceable>)</optional></optional> <replaceable>query2</replaceable>
</synopsis>
<replaceable>query1</replaceable> and
<replaceable>query2</replaceable> are queries that can use any of
***************
*** 1283,1288 ****
--- 1286,1299 ----
</para>
<para>
+ <literal>CORRESPONDING</> returns all columns that are in both <replaceable>query1</> and <replaceable>query2</> with the same name.
+ </para>
+
+ <para>
+ <literal>CORRESPONDING BY</> returns all columns in the column list that are also in both <replaceable>query1</> and <replaceable>query2</> with the same name.
+ </para>
+
+ <para>
In order to calculate the union, intersection, or difference of two
queries, the two queries must be <quote>union compatible</quote>,
which means that they return the same number of columns and
*** a/doc/src/sgml/sql.sgml
--- b/doc/src/sgml/sql.sgml
***************
*** 859,865 ****
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
[ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="PARAMETER">select</replaceable> ]
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
--- 859,865 ----
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
[ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] [ CORRESPONDING [ BY ( <replaceable class="PARAMETER">expression</replaceable> ) ] ] <replaceable class="PARAMETER">select</replaceable> ]
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 2507,2512 ****
--- 2507,2513 ----
COPY_NODE_FIELD(lockingClause);
COPY_SCALAR_FIELD(op);
COPY_SCALAR_FIELD(all);
+ COPY_NODE_FIELD(correspondingClause);
COPY_NODE_FIELD(larg);
COPY_NODE_FIELD(rarg);
***************
*** 2522,2527 ****
--- 2523,2530 ----
COPY_SCALAR_FIELD(all);
COPY_NODE_FIELD(larg);
COPY_NODE_FIELD(rarg);
+ COPY_NODE_FIELD(correspondingColumns);
+ COPY_SCALAR_FIELD(hasCorrespondingBy);
COPY_NODE_FIELD(colTypes);
COPY_NODE_FIELD(colTypmods);
COPY_NODE_FIELD(colCollations);
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 982,987 ****
--- 982,988 ----
COMPARE_NODE_FIELD(lockingClause);
COMPARE_SCALAR_FIELD(op);
COMPARE_SCALAR_FIELD(all);
+ COMPARE_NODE_FIELD(correspondingClause);
COMPARE_NODE_FIELD(larg);
COMPARE_NODE_FIELD(rarg);
***************
*** 995,1000 ****
--- 996,1003 ----
COMPARE_SCALAR_FIELD(all);
COMPARE_NODE_FIELD(larg);
COMPARE_NODE_FIELD(rarg);
+ COMPARE_NODE_FIELD(correspondingColumns);
+ COMPARE_SCALAR_FIELD(hasCorrespondingBy);
COMPARE_NODE_FIELD(colTypes);
COMPARE_NODE_FIELD(colTypmods);
COMPARE_NODE_FIELD(colCollations);
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
***************
*** 2894,2899 ****
--- 2894,2901 ----
return true;
if (walker(stmt->lockingClause, context))
return true;
+ if (walker(stmt->correspondingClause, context))
+ return true;
if (walker(stmt->larg, context))
return true;
if (walker(stmt->rarg, context))
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 2031,2036 ****
--- 2031,2037 ----
WRITE_NODE_FIELD(lockingClause);
WRITE_ENUM_FIELD(op, SetOperation);
WRITE_BOOL_FIELD(all);
+ WRITE_NODE_FIELD(correspondingClause);
WRITE_NODE_FIELD(larg);
WRITE_NODE_FIELD(rarg);
}
***************
*** 2295,2300 ****
--- 2296,2303 ----
WRITE_BOOL_FIELD(all);
WRITE_NODE_FIELD(larg);
WRITE_NODE_FIELD(rarg);
+ WRITE_NODE_FIELD(correspondingColumns);
+ WRITE_BOOL_FIELD(hasCorrespondingBy);
WRITE_NODE_FIELD(colTypes);
WRITE_NODE_FIELD(colTypmods);
WRITE_NODE_FIELD(colCollations);
*** a/src/backend/nodes/readfuncs.c
--- b/src/backend/nodes/readfuncs.c
***************
*** 342,347 ****
--- 342,349 ----
READ_BOOL_FIELD(all);
READ_NODE_FIELD(larg);
READ_NODE_FIELD(rarg);
+ READ_NODE_FIELD(correspondingColumns);
+ READ_BOOL_FIELD(hasCorrespondingBy);
READ_NODE_FIELD(colTypes);
READ_NODE_FIELD(colTypmods);
READ_NODE_FIELD(colCollations);
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 54,59 ****
--- 54,62 ----
static Query *transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt);
static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
bool isTopLevel, List **targetlist);
+ static Node *createSubqueryForCorresponding(List* outputColumns,
+ SelectStmt* main_arg);
+ static List *determineMatchingColumns(List *ltargetlist, List *rtargetlist);
static void determineRecursiveColTypes(ParseState *pstate,
Node *larg, List *nrtargetlist);
static void applyColumnNames(List *dst, List *src);
***************
*** 1665,1670 ****
--- 1668,1886 ----
&rtargetlist);
/*
+ * If CORRESPONDING is specified, syntax and column name validities checked,
+ * column filtering is done by a subquery later on.
+ */
+ if(stmt->correspondingClause == NIL)
+ {
+ // No CORRESPONDING clause, no operation needed for column filtering.
+ op->correspondingColumns = stmt->correspondingClause;
+ op->hasCorrespondingBy = false;
+ }
+ else if(linitial(stmt->correspondingClause) == NULL)
+ {
+ // CORRESPONDING clause, find matching column names from both tables. If there are none then it is a syntax error.
+
+ Query *largQuery;
+ Query *rargQuery;
+ List *matchingColumns;
+ ListCell* mctl;
+
+ /* Analyze left query to resolve column names. */
+ largQuery = parse_sub_analyze((Node *) stmt->larg, pstate, NULL, false);
+
+ /* Analyze right query to resolve column names. */
+ rargQuery = parse_sub_analyze((Node *) stmt->rarg, pstate, NULL, false);
+
+ /* Find matching columns from both queries. */
+ matchingColumns = determineMatchingColumns(largQuery->targetList,
+ rargQuery->targetList);
+
+ op->correspondingColumns = matchingColumns;
+ op->hasCorrespondingBy = false;
+
+ /* If matchingColumns is empty, there is an error. At least one column in the select lists must have the same name. */
+ if(list_length(matchingColumns) == 0)
+ {
+ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("%s queries with a CORRESPONDING clause must have at least one column with the same name",
+ context)));
+ }
+
+
+ // Create subquery for larg, selecting column names from matchingColumns.
+ stmt->larg = createSubqueryForCorresponding(matchingColumns, stmt->larg);
+
+ // Assign newly generated query to original left query.
+ op->larg = transformSetOperationTree(pstate, stmt->larg,
+ false,
+ <argetlist);
+
+ // Create subquery for rarg, selecting column names from matchingColumns.
+ stmt->rarg = createSubqueryForCorresponding(matchingColumns, stmt->rarg);
+
+ // Assign newly generated query to original right query.
+ op->rarg = transformSetOperationTree(pstate, stmt->rarg,
+ false,
+ &rtargetlist);
+ }
+ else
+ {
+ // CORRESPONDING BY clause, find matching column names from both tables
+ // and intersect them with BY(...) column list. If there are none
+ // then it is a syntax error.
+
+ Query *largQuery;
+ Query *rargQuery;
+ List *matchingColumns;
+ ListCell *byresname;
+ ListCell *mctl;
+
+ /* Analyze left query to resolve column names. */
+ largQuery = parse_sub_analyze((Node *) stmt->larg, pstate, NULL, false);
+
+ /* Analyze right query to resolve column names. */
+ rargQuery = parse_sub_analyze((Node *) stmt->rarg, pstate, NULL, false);
+
+ /*
+ * Find matching columns from both queries.
+ * In CORRESPONDING BY, column names will be removed from
+ * matchingColumns if they are not in the BY clause.
+ * All columns in the BY clause must be in matchingColumns,
+ * otherwise raise syntax error in BY clause.
+ */
+
+ matchingColumns = determineMatchingColumns(largQuery->targetList,
+ rargQuery->targetList);
+
+ /*
+ * Every column name in correspondingClause must be in matchingColumns,
+ * otherwise it is a syntax error.
+ */
+ foreach(byresname, stmt->correspondingClause)
+ {
+ Node* node = lfirst(byresname);
+ if (IsA(node, ColumnRef) &&
+ list_length(((ColumnRef *) node)->fields) == 1 &&
+ IsA(linitial(((ColumnRef *) node)->fields), String))
+ {
+ /* Get column name from correspondingClause. */
+ char *name = strVal(linitial(((ColumnRef *) node)->fields));
+ bool hasMatch = false;
+
+ foreach(mctl, matchingColumns)
+ {
+ TargetEntry *mctle = (TargetEntry *) lfirst(mctl);
+
+ Assert(mctle->resname != NULL);
+ Assert(name != NULL);
+
+ /* Compare correspondingClause column name with matchingColumns column names. */
+ if(strcmp(mctle->resname, name) == 0)
+ {
+ // we have a match.
+ hasMatch = true;
+ break;
+ }
+ }
+
+ if(!hasMatch)
+ {
+ /* CORRESPONDING BY clause contains a column name that is not in both tables. */
+ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("CORRESPONDING BY clause must only contain column names from both tables.")));
+ }
+
+ }
+ else
+ {
+ /* Only column names are supported, constants are syntax error in CORRESPONDING BY clause. */
+ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg(
+ "%s queries with CORRESPONDING BY clause must have only column names and not constants or ordinals in the column name list.",
+ context)));
+ }
+ }
+
+ /* Remove columns from matchingColumns if they are not in correspondingClause,
+ * thus finalizing our column list for the CORRESPONDING BY clause.
+ */
+
+ /* cannot use foreach here because of possible list_delete_ptr */
+ mctl = list_head(matchingColumns);
+ while (mctl)
+ {
+ TargetEntry *mctle = (TargetEntry *) lfirst(mctl);
+ bool hasMatch = false;
+
+ /* must advance mctl before list_delete_ptr possibly deletes it */
+ mctl = lnext(mctl);
+
+ foreach(byresname, stmt->correspondingClause)
+ {
+ Node* node = lfirst(byresname);
+
+ hasMatch = false;
+
+ if (IsA(node, ColumnRef) &&
+ list_length(((ColumnRef *) node)->fields) == 1 &&
+ IsA(linitial(((ColumnRef *) node)->fields), String))
+ {
+ char *name = strVal(linitial(((ColumnRef *) node)->fields));
+
+ Assert(mctle->resname != NULL);
+ Assert(name != NULL);
+
+ if(strcmp(mctle->resname, name) == 0)
+ {
+ // we have a match.
+ hasMatch = true;
+ break;
+ }
+ }
+ }
+
+ if(!hasMatch)
+ {
+ // remove current from matchingColumns
+ matchingColumns = list_delete_ptr(matchingColumns, mctle);
+
+ /* List is emptied. */
+ if(matchingColumns == NIL)
+ break;
+ }
+ }
+
+ op->correspondingColumns = matchingColumns;
+ op->hasCorrespondingBy = true;
+
+ /* If matchingColumns is empty, there is a semantic error. At least one column in the select lists must have the same name. */
+ if(list_length(matchingColumns) == 0)
+ {
+ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("%s queries with CORRESPONDING BY clause must have at least one column name in BY clause and in both of the queries.",
+ context)));
+ }
+
+
+ // Create subquery for larg, selecting only columns from matchingColumns.
+ stmt->larg = createSubqueryForCorresponding(matchingColumns, stmt->larg);
+
+ // Assign newly generated query to original left query.
+ op->larg = transformSetOperationTree(pstate, stmt->larg,
+ false,
+ <argetlist);
+
+ // Create subquery for rarg, selecting only columns from matchingColumns.
+ stmt->rarg = createSubqueryForCorresponding(matchingColumns, stmt->rarg);
+
+ // Assign newly generated query to original right query.
+ op->rarg = transformSetOperationTree(pstate, stmt->rarg,
+ false,
+ &rtargetlist);
+ }
+
+ /*
* Verify that the two children have the same number of non-junk
* columns, and determine the types of the merged output columns.
*/
***************
*** 1838,1843 ****
--- 2054,2141 ----
}
/*
+ * Returns a subquery selecting outputColumns from main_arg.
+ * main_arg is modified and returned.
+ */
+ static Node *
+ createSubqueryForCorresponding(List* outputColumns, SelectStmt* main_arg)
+ {
+ ColumnRef *cr;
+ ResTarget *rt;
+ SelectStmt *n;
+
+ RangeSubselect * rss;
+ ListCell* mctl;
+
+ n = makeNode(SelectStmt);
+ n->targetList = NIL;
+ foreach(mctl, outputColumns)
+ {
+ TargetEntry *mctle = (TargetEntry *) lfirst(mctl);
+
+ cr = makeNode(ColumnRef);
+ cr->fields = list_make1(makeString(mctle->resname));
+ cr->location = -1;
+
+ rt = makeNode(ResTarget);
+ rt->name = NULL;
+ rt->indirection = NIL;
+ rt->val = (Node *)cr;
+ rt->location = -1;
+
+ n->targetList = lappend(n->targetList, rt);
+ }
+
+ rss = makeNode(RangeSubselect);
+
+ // XXX makeAlias alias name should be empty??
+ rss->alias = makeAlias("", NULL);
+ rss->subquery = main_arg;
+
+ n->fromClause = list_make1(rss);
+
+ main_arg = n;
+
+ return main_arg;
+ }
+
+
+ /*
+ * Processes targetlists of two queries for column equivalence to use
+ * with UNION/INTERSECT/EXCEPT CORRESPONDING.
+ */
+ static List *
+ determineMatchingColumns(List *ltargetlist, List *rtargetlist)
+ {
+ List *matchingColumns = NIL;
+ ListCell *ltl;
+ ListCell *rtl;
+
+ foreach(ltl, ltargetlist)
+ {
+ foreach(rtl, rtargetlist)
+ {
+ TargetEntry *ltle = (TargetEntry *) lfirst(ltl);
+ TargetEntry *rtle = (TargetEntry *) lfirst(rtl);
+
+ elog(DEBUG4, "%s", ltle->resname);
+
+ /* Names of the columns must be resolved before calling this method. */
+ Assert(ltle->resname != NULL);
+ Assert(rtle->resname != NULL);
+
+ /* If column names are the same, append it to the result. */
+ if(strcmp(ltle->resname, rtle->resname) == 0)
+ {
+ matchingColumns = lappend(matchingColumns, ltle);
+ }
+ }
+ }
+
+ return matchingColumns;
+ }
+
+ /*
* Process the outputs of the non-recursive term of a recursive union
* to set up the parent CTE's columns
*/
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 130,136 ****
Node *limitOffset, Node *limitCount,
WithClause *withClause,
core_yyscan_t yyscanner);
! static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg);
static Node *doNegate(Node *n, int location);
static void doNegateFloat(Value *v);
static Node *makeAArrayExpr(List *elements, int location);
--- 130,136 ----
Node *limitOffset, Node *limitCount,
WithClause *withClause,
core_yyscan_t yyscanner);
! static Node *makeSetOp(SetOperation op, bool all, List *correspondingClause, Node *larg, Node *rarg);
static Node *doNegate(Node *n, int location);
static void doNegateFloat(Value *v);
static Node *makeAArrayExpr(List *elements, int location);
***************
*** 321,326 ****
--- 321,327 ----
opt_enum_val_list enum_val_list table_func_column_list
create_generic_options alter_generic_options
relation_expr_list dostmt_opt_list
+ opt_corresponding_clause
%type <list> opt_fdw_options fdw_options
%type <defelt> fdw_option
***************
*** 498,504 ****
CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT
COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS
! CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE
CROSS CSV CURRENT_P
CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
--- 499,505 ----
CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT
COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS
! CONTENT_P CONTINUE_P CONVERSION_P COPY CORRESPONDING COST CREATE
CROSS CSV CURRENT_P
CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
***************
*** 8480,8498 ****
n->fromClause = list_make1($2);
$$ = (Node *)n;
}
! | select_clause UNION opt_all select_clause
{
! $$ = makeSetOp(SETOP_UNION, $3, $1, $4);
}
! | select_clause INTERSECT opt_all select_clause
{
! $$ = makeSetOp(SETOP_INTERSECT, $3, $1, $4);
}
! | select_clause EXCEPT opt_all select_clause
{
! $$ = makeSetOp(SETOP_EXCEPT, $3, $1, $4);
}
;
/*
* SQL standard WITH clause looks like:
--- 8481,8505 ----
n->fromClause = list_make1($2);
$$ = (Node *)n;
}
! | select_clause UNION opt_all opt_corresponding_clause select_clause
{
! $$ = makeSetOp(SETOP_UNION, $3, $4, $1, $5);
}
! | select_clause INTERSECT opt_all opt_corresponding_clause select_clause
{
! $$ = makeSetOp(SETOP_INTERSECT, $3, $4, $1, $5);
}
! | select_clause EXCEPT opt_all opt_corresponding_clause select_clause
{
! $$ = makeSetOp(SETOP_EXCEPT, $3, $4, $1, $5);
}
;
+
+ opt_corresponding_clause:
+ CORRESPONDING BY '(' expr_list ')' { $$ = $4; }
+ | CORRESPONDING { $$ = list_make1(NIL); }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
/*
* SQL standard WITH clause looks like:
***************
*** 12633,12639 ****
}
static Node *
! makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg)
{
SelectStmt *n = makeNode(SelectStmt);
--- 12640,12646 ----
}
static Node *
! makeSetOp(SetOperation op, bool all, List *correspondingClause, Node *larg, Node *rarg)
{
SelectStmt *n = makeNode(SelectStmt);
***************
*** 12641,12646 ****
--- 12648,12654 ----
n->all = all;
n->larg = (SelectStmt *) larg;
n->rarg = (SelectStmt *) rarg;
+ n->correspondingClause = correspondingClause;
return (Node *) n;
}
*** a/src/backend/parser/parse_cte.c
--- b/src/backend/parser/parse_cte.c
***************
*** 927,932 ****
--- 927,934 ----
case SETOP_INTERSECT:
if (stmt->all)
cstate->context = RECURSION_INTERSECT;
+ checkWellFormedRecursionWalker((Node *) stmt->correspondingClause,
+ cstate);
checkWellFormedRecursionWalker((Node *) stmt->larg,
cstate);
checkWellFormedRecursionWalker((Node *) stmt->rarg,
***************
*** 945,950 ****
--- 947,954 ----
case SETOP_EXCEPT:
if (stmt->all)
cstate->context = RECURSION_EXCEPT;
+ checkWellFormedRecursionWalker((Node *) stmt->correspondingClause,
+ cstate);
checkWellFormedRecursionWalker((Node *) stmt->larg,
cstate);
cstate->context = RECURSION_EXCEPT;
*** a/src/backend/parser/parse_type.c
--- b/src/backend/parser/parse_type.c
***************
*** 711,717 ****
stmt->limitOffset != NULL ||
stmt->limitCount != NULL ||
stmt->lockingClause != NIL ||
! stmt->op != SETOP_NONE)
goto fail;
if (list_length(stmt->targetList) != 1)
goto fail;
--- 711,718 ----
stmt->limitOffset != NULL ||
stmt->limitCount != NULL ||
stmt->lockingClause != NIL ||
! stmt->op != SETOP_NONE ||
! stmt->correspondingClause != NIL)
goto fail;
if (list_length(stmt->targetList) != 1)
goto fail;
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 1006,1011 ****
--- 1006,1013 ----
/*
* These fields are used only in "leaf" SelectStmts.
*/
+ List *correspondingClause; /* NULL, list of CORRESPONDING BY exprs, or */
+ /* lcons(NIL, NIL) for CORRESPONDING */
List *distinctClause; /* NULL, list of DISTINCT ON exprs, or
* lcons(NIL,NIL) for all (SELECT DISTINCT) */
IntoClause *intoClause; /* target for SELECT INTO / CREATE TABLE AS */
***************
*** 1043,1052 ****
bool all; /* ALL specified? */
struct SelectStmt *larg; /* left child */
struct SelectStmt *rarg; /* right child */
- /* Eventually add fields for CORRESPONDING spec here */
} SelectStmt;
-
/* ----------------------
* Set Operation node for post-analysis query trees
*
--- 1045,1052 ----
***************
*** 1073,1079 ****
bool all; /* ALL specified? */
Node *larg; /* left child */
Node *rarg; /* right child */
! /* Eventually add fields for CORRESPONDING spec here */
/* Fields derived during parse analysis: */
List *colTypes; /* OID list of output column type OIDs */
--- 1073,1082 ----
bool all; /* ALL specified? */
Node *larg; /* left child */
Node *rarg; /* right child */
!
! /* CORRESPONDING clause fields */
! List *correspondingColumns; /* NIL: No corresponding, else: CORRESPONDING or CORRESPONDING BY matching columns. Not the original clause. */
! bool hasCorrespondingBy; /* If correspondingColumns is not NULL then hasCorrespondingBy if effective, otherwise it is junk. */
/* Fields derived during parse analysis: */
List *colTypes; /* OID list of output column type OIDs */
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
***************
*** 94,99 ****
--- 94,100 ----
PG_KEYWORD("continue", CONTINUE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("conversion", CONVERSION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("copy", COPY, UNRESERVED_KEYWORD)
+ PG_KEYWORD("corresponding", CORRESPONDING, UNRESERVED_KEYWORD)
PG_KEYWORD("cost", COST, UNRESERVED_KEYWORD)
PG_KEYWORD("create", CREATE, RESERVED_KEYWORD)
PG_KEYWORD("cross", CROSS, TYPE_FUNC_NAME_KEYWORD)
*** a/src/test/regress/expected/corresponding_union.out
--- b/src/test/regress/expected/corresponding_union.out
***************
*** 0 ****
--- 1,352 ----
+ --
+ -- UNION (also INTERSECT, EXCEPT) with CORRESPONDING.
+ --
+ -- Simple UNION CORRESPONDING constructs
+ SELECT 1 AS two UNION CORRESPONDING SELECT 2 two;
+ two
+ -----
+ 1
+ 2
+ (2 rows)
+
+ SELECT 1 AS one UNION CORRESPONDING SELECT 1 one;
+ one
+ -----
+ 1
+ (1 row)
+
+ SELECT 1 AS two UNION ALL CORRESPONDING SELECT 2 two;
+ two
+ -----
+ 1
+ 2
+ (2 rows)
+
+ SELECT 1 AS two UNION ALL CORRESPONDING SELECT 1 two;
+ two
+ -----
+ 1
+ 1
+ (2 rows)
+
+ SELECT 1 AS two UNION CORRESPONDING SELECT 2 two UNION CORRESPONDING SELECT 2 two;
+ two
+ -----
+ 1
+ 2
+ (2 rows)
+
+ SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION ALL CORRESPONDING SELECT 2 three;
+ three
+ -------
+ 1
+ 2
+ 2
+ (3 rows)
+
+ SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION CORRESPONDING SELECT 3 three;
+ three
+ -------
+ 1
+ 2
+ 3
+ (3 rows)
+
+ SELECT 1.1 AS two UNION SELECT 2.2 two;
+ two
+ -----
+ 1.1
+ 2.2
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c;
+ a | b | c
+ ---+---+---
+ 1 | 2 | 3
+ 4 | 5 | 6
+ (2 rows)
+
+ SELECT 3 c, 2 b, 1 a UNION CORRESPONDING SELECT 6 c, 4 a, 5 b;
+ c | b | a
+ ---+---+---
+ 3 | 2 | 1
+ 6 | 5 | 4
+ (2 rows)
+
+ -- Simple UNION CORRESPONDING BY constructs
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a) SELECT 4 a, 5 b, 6 c;
+ a
+ ---
+ 1
+ 4
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) SELECT 4 a, 5 b, 6 c;
+ b
+ ---
+ 2
+ 5
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c) SELECT 4 a, 5 b, 6 c;
+ c
+ ---
+ 3
+ 6
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b) SELECT 4 a, 5 b, 6 c;
+ a | b
+ ---+---
+ 1 | 2
+ 4 | 5
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b, c) SELECT 4 a, 5 b, 6 c;
+ b | c
+ ---+---
+ 2 | 3
+ 5 | 6
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, c) SELECT 4 a, 5 b, 6 c;
+ a | c
+ ---+---
+ 1 | 3
+ 4 | 6
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 4 a, 5 b, 6 c;
+ a | b | c
+ ---+---+---
+ 1 | 2 | 3
+ 4 | 5 | 6
+ (2 rows)
+
+ --
+ -- Try testing from tables...
+ --
+ SELECT f1 AS five FROM FLOAT8_TBL
+ UNION CORRESPONDING
+ SELECT f1 AS five FROM FLOAT8_TBL
+ ORDER BY 1;
+ five
+ -----------------------
+ -1.2345678901234e+200
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ 0
+ (5 rows)
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ UNION CORRESPONDING BY(five)
+ SELECT f1 AS five FROM FLOAT8_TBL
+ ORDER BY 1;
+ five
+ -----------------------
+ -1.2345678901234e+200
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ 0
+ (5 rows)
+
+ SELECT f1 AS ten FROM FLOAT8_TBL
+ UNION ALL CORRESPONDING
+ SELECT f1 AS ten FROM FLOAT8_TBL;
+ ten
+ -----------------------
+ 0
+ -34.84
+ -1004.3
+ -1.2345678901234e+200
+ -1.2345678901234e-200
+ 0
+ -34.84
+ -1004.3
+ -1.2345678901234e+200
+ -1.2345678901234e-200
+ (10 rows)
+
+ SELECT f1 AS nine FROM FLOAT8_TBL
+ UNION CORRESPONDING
+ SELECT f1 AS nine FROM INT4_TBL
+ ORDER BY 1;
+ nine
+ -----------------------
+ -1.2345678901234e+200
+ -2147483647
+ -123456
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ 0
+ 123456
+ 2147483647
+ (9 rows)
+
+ SELECT f1 AS ten FROM FLOAT8_TBL
+ UNION ALL CORRESPONDING
+ SELECT f1 AS ten FROM INT4_TBL;
+ ten
+ -----------------------
+ 0
+ -34.84
+ -1004.3
+ -1.2345678901234e+200
+ -1.2345678901234e-200
+ 0
+ 123456
+ -123456
+ 2147483647
+ -2147483647
+ (10 rows)
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ WHERE f1 BETWEEN -1e6 AND 1e6
+ UNION CORRESPONDING
+ SELECT f1 AS five FROM INT4_TBL
+ WHERE f1 BETWEEN 0 AND 1000000;
+ five
+ -----------------------
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ 0
+ 123456
+ (5 rows)
+
+ --
+ -- INTERSECT and EXCEPT
+ --
+ SELECT q2 FROM int8_tbl INTERSECT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+ q2
+ ------------------
+ 4567890123456789
+ 123
+ (2 rows)
+
+ SELECT q2 FROM int8_tbl INTERSECT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+ q2
+ ------------------
+ 4567890123456789
+ 4567890123456789
+ 123
+ (3 rows)
+
+ SELECT q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1;
+ q2
+ -------------------
+ -4567890123456789
+ 456
+ (2 rows)
+
+ SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1;
+ q2
+ -------------------
+ -4567890123456789
+ 456
+ (2 rows)
+
+ SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q1 AS q2 FROM int8_tbl ORDER BY 1;
+ q2
+ -------------------
+ -4567890123456789
+ 456
+ 4567890123456789
+ (3 rows)
+
+ SELECT q1 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2 AS q1 FROM int8_tbl;
+ q1
+ ----
+ (0 rows)
+
+ SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q2 AS q1 FROM int8_tbl;
+ q1
+ ------------------
+ 4567890123456789
+ 123
+ (2 rows)
+
+ SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q2 AS q1 FROM int8_tbl;
+ q1
+ ------------------
+ 4567890123456789
+ 4567890123456789
+ 123
+ (3 rows)
+
+ --
+ -- Mixed types
+ --
+ SELECT f1 FROM float8_tbl INTERSECT CORRESPONDING SELECT f1 FROM int4_tbl;
+ f1
+ ----
+ 0
+ (1 row)
+
+ SELECT f1 FROM float8_tbl EXCEPT CORRESPONDING SELECT f1 FROM int4_tbl ORDER BY 1;
+ f1
+ -----------------------
+ -1.2345678901234e+200
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ (4 rows)
+
+ --
+ -- Subqueries with ORDER BY & LIMIT clauses
+ --
+ -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2,q1 FROM int8_tbl
+ ORDER BY q2,q1;
+ q1 | q2
+ ----+----
+ (0 rows)
+
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q1) SELECT q2,q1 FROM int8_tbl
+ ORDER BY q1;
+ q1
+ ----
+ (0 rows)
+
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q2) SELECT q2,q1 FROM int8_tbl
+ ORDER BY q2;
+ q2
+ ----
+ (0 rows)
+
+ --
+ -- New syntaxes (7.1) permit new tests
+ --
+ (((((select * from int8_tbl)))));
+ q1 | q2
+ ------------------+-------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+ (5 rows)
+
+ --
+ -- Check handling of a case with unknown constants. We don't guarantee
+ -- an undecorated constant will work in all cases, but historically this
+ -- usage has worked, so test we don't break it.
+ --
+ SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a
+ UNION CORRESPONDING
+ SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b
+ ORDER BY 1;
+ f1
+ ------
+ a
+ ab
+ abcd
+ test
+ (4 rows)
+
+ -- This should fail, but it should produce an error cursor
+ SELECT '3.4'::numeric AS a UNION CORRESPONDING SELECT 'foo' AS a;
+ ERROR: failed to find conversion function from unknown to numeric
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
***************
*** 74,80 ****
# ----------
# Another group of parallel tests
# ----------
! test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete
# ----------
# Another group of parallel tests
--- 74,80 ----
# ----------
# Another group of parallel tests
# ----------
! test: select_into select_distinct select_distinct_on select_implicit select_having subselect union corresponding_union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete
# ----------
# Another group of parallel tests
*** a/src/test/regress/serial_schedule
--- b/src/test/regress/serial_schedule
***************
*** 73,78 ****
--- 73,79 ----
test: select_having
test: subselect
test: union
+ test: corresponding_union
test: case
test: join
test: aggregates
*** a/src/test/regress/sql/corresponding_union.sql
--- b/src/test/regress/sql/corresponding_union.sql
***************
*** 0 ****
--- 1,136 ----
+ --
+ -- UNION (also INTERSECT, EXCEPT) with CORRESPONDING.
+ --
+
+ -- Simple UNION CORRESPONDING constructs
+
+ SELECT 1 AS two UNION CORRESPONDING SELECT 2 two;
+
+ SELECT 1 AS one UNION CORRESPONDING SELECT 1 one;
+
+ SELECT 1 AS two UNION ALL CORRESPONDING SELECT 2 two;
+
+ SELECT 1 AS two UNION ALL CORRESPONDING SELECT 1 two;
+
+ SELECT 1 AS two UNION CORRESPONDING SELECT 2 two UNION CORRESPONDING SELECT 2 two;
+
+ SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION ALL CORRESPONDING SELECT 2 three;
+
+ SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION CORRESPONDING SELECT 3 three;
+
+ SELECT 1.1 AS two UNION SELECT 2.2 two;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c;
+
+ SELECT 3 c, 2 b, 1 a UNION CORRESPONDING SELECT 6 c, 4 a, 5 b;
+
+ -- Simple UNION CORRESPONDING BY constructs
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b, c) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, c) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 4 a, 5 b, 6 c;
+
+ --
+ -- Try testing from tables...
+ --
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ UNION CORRESPONDING
+ SELECT f1 AS five FROM FLOAT8_TBL
+ ORDER BY 1;
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ UNION CORRESPONDING BY(five)
+ SELECT f1 AS five FROM FLOAT8_TBL
+ ORDER BY 1;
+
+ SELECT f1 AS ten FROM FLOAT8_TBL
+ UNION ALL CORRESPONDING
+ SELECT f1 AS ten FROM FLOAT8_TBL;
+
+ SELECT f1 AS nine FROM FLOAT8_TBL
+ UNION CORRESPONDING
+ SELECT f1 AS nine FROM INT4_TBL
+ ORDER BY 1;
+
+ SELECT f1 AS ten FROM FLOAT8_TBL
+ UNION ALL CORRESPONDING
+ SELECT f1 AS ten FROM INT4_TBL;
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ WHERE f1 BETWEEN -1e6 AND 1e6
+ UNION CORRESPONDING
+ SELECT f1 AS five FROM INT4_TBL
+ WHERE f1 BETWEEN 0 AND 1000000;
+
+ --
+ -- INTERSECT and EXCEPT
+ --
+
+ SELECT q2 FROM int8_tbl INTERSECT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+
+ SELECT q2 FROM int8_tbl INTERSECT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+
+ SELECT q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1;
+
+ SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1;
+
+ SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q1 AS q2 FROM int8_tbl ORDER BY 1;
+
+ SELECT q1 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2 AS q1 FROM int8_tbl;
+
+ SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q2 AS q1 FROM int8_tbl;
+
+ SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q2 AS q1 FROM int8_tbl;
+
+ --
+ -- Mixed types
+ --
+
+ SELECT f1 FROM float8_tbl INTERSECT CORRESPONDING SELECT f1 FROM int4_tbl;
+
+ SELECT f1 FROM float8_tbl EXCEPT CORRESPONDING SELECT f1 FROM int4_tbl ORDER BY 1;
+
+ --
+ -- Subqueries with ORDER BY & LIMIT clauses
+ --
+
+ -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2,q1 FROM int8_tbl
+ ORDER BY q2,q1;
+
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q1) SELECT q2,q1 FROM int8_tbl
+ ORDER BY q1;
+
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q2) SELECT q2,q1 FROM int8_tbl
+ ORDER BY q2;
+
+ --
+ -- New syntaxes (7.1) permit new tests
+ --
+
+ (((((select * from int8_tbl)))));
+
+ --
+ -- Check handling of a case with unknown constants. We don't guarantee
+ -- an undecorated constant will work in all cases, but historically this
+ -- usage has worked, so test we don't break it.
+ --
+
+ SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a
+ UNION CORRESPONDING
+ SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b
+ ORDER BY 1;
+
+ -- This should fail, but it should produce an error cursor
+ SELECT '3.4'::numeric AS a UNION CORRESPONDING SELECT 'foo' AS a;
On Wed, October 19, 2011 15:01, Kerem Kat wrote:
Adding CORRESPONDING to Set Operations
Initial patch, filename: corresponding_clause_v2.patch
I had a quick look at the behaviour of this patch.
Btw, the examples in your email were typoed (one select is missing):
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING 4 b, 5 d, 6 c, 7 f;
should be:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING select 4 b, 5 d, 6 c, 7 f;
and
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) 4 b, 5 d, 6 c, 7 f;
should be:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) select 4 b, 5 d, 6 c, 7 f;
But there is also a small bug, I think: the order in the CORRESPONDING BY list should be followed,
according to the standard (foundation, p. 408):
"2) If <corresponding column list> is specified, then let SL be a <select list> of those <column
name>s explicitly appearing in the <corresponding column list> in the order that these
<column name>s appear in the <corresponding column list>. Every <column name> in the
<corresponding column list> shall be a <column name> of both T1 and T2."
That would make this wrong, I think:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c,b) select 5 d, 6 c, 7 f, 4 b ;
b | c
---+---
2 | 3
4 | 6
(2 rows)
i.e., I think it should show columns in the order c, b (and not b, c); the order of the
CORRESPONDING BY phrase.
(but maybe I'm misreading the text of the standard; I find it often difficult to follow)
Thanks,
Erik Rijkers
On Mon, Oct 24, 2011 at 20:52, Erik Rijkers <er@xs4all.nl> wrote:
On Wed, October 19, 2011 15:01, Kerem Kat wrote:
Adding CORRESPONDING to Set Operations
Initial patch, filename: corresponding_clause_v2.patchI had a quick look at the behaviour of this patch.
Btw, the examples in your email were typoed (one select is missing):
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING 4 b, 5 d, 6 c, 7 f;
should be:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING select 4 b, 5 d, 6 c, 7 f;and
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) 4 b, 5 d, 6 c, 7 f;
should be:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) select 4 b, 5 d, 6 c, 7 f;
Yes you are correct, mea culpa.
But there is also a small bug, I think: the order in the CORRESPONDING BY list should be followed,
according to the standard (foundation, p. 408):"2) If <corresponding column list> is specified, then let SL be a <select list> of those <column
name>s explicitly appearing in the <corresponding column list> in the order that these
<column name>s appear in the <corresponding column list>. Every <column name> in the
<corresponding column list> shall be a <column name> of both T1 and T2."That would make this wrong, I think:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c,b) select 5 d, 6 c, 7 f, 4 b ;
b | c
---+---
2 | 3
4 | 6
(2 rows)i.e., I think it should show columns in the order c, b (and not b, c); the order of the
CORRESPONDING BY phrase.(but maybe I'm misreading the text of the standard; I find it often difficult to follow)
It wasn't a misread, I checked the draft, in my version same
explanation is at p.410.
I have corrected the ordering of the targetlists of subqueries. And
added 12 regression
tests for column list ordering. Can you confirm that the order has
changed for you?
Thanks,
Erik Rijkers
Regards,
Kerem KAT
Attachments:
corresponding_clause_v3.patchtext/x-patch; charset=US-ASCII; name=corresponding_clause_v3.patchDownload
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***************
*** 1225,1230 ****
--- 1225,1233 ----
<primary>EXCEPT</primary>
</indexterm>
<indexterm zone="queries-union">
+ <primary>CORRESPONDING</primary>
+ </indexterm>
+ <indexterm zone="queries-union">
<primary>set union</primary>
</indexterm>
<indexterm zone="queries-union">
***************
*** 1241,1249 ****
The results of two queries can be combined using the set operations
union, intersection, and difference. The syntax is
<synopsis>
! <replaceable>query1</replaceable> UNION <optional>ALL</optional> <replaceable>query2</replaceable>
! <replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <replaceable>query2</replaceable>
! <replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <replaceable>query2</replaceable>
</synopsis>
<replaceable>query1</replaceable> and
<replaceable>query2</replaceable> are queries that can use any of
--- 1244,1252 ----
The results of two queries can be combined using the set operations
union, intersection, and difference. The syntax is
<synopsis>
! <replaceable>query1</replaceable> UNION <optional>ALL</optional> <optional>CORRESPONDING <optional>BY (<replaceable>select_list</replaceable>)</optional></optional> <replaceable>query2</replaceable>
! <replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <optional>CORRESPONDING <optional>BY (<replaceable>select_list</replaceable>)</optional></optional> <replaceable>query2</replaceable>
! <replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <optional>CORRESPONDING <optional>BY (<replaceable>select_list</replaceable>)</optional></optional> <replaceable>query2</replaceable>
</synopsis>
<replaceable>query1</replaceable> and
<replaceable>query2</replaceable> are queries that can use any of
***************
*** 1283,1288 ****
--- 1286,1299 ----
</para>
<para>
+ <literal>CORRESPONDING</> returns all columns that are in both <replaceable>query1</> and <replaceable>query2</> with the same name.
+ </para>
+
+ <para>
+ <literal>CORRESPONDING BY</> returns all columns in the column list that are also in both <replaceable>query1</> and <replaceable>query2</> with the same name.
+ </para>
+
+ <para>
In order to calculate the union, intersection, or difference of two
queries, the two queries must be <quote>union compatible</quote>,
which means that they return the same number of columns and
*** a/doc/src/sgml/sql.sgml
--- b/doc/src/sgml/sql.sgml
***************
*** 859,865 ****
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
[ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="PARAMETER">select</replaceable> ]
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
--- 859,865 ----
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
[ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] [ CORRESPONDING [ BY ( <replaceable class="PARAMETER">expression</replaceable> ) ] ] <replaceable class="PARAMETER">select</replaceable> ]
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 2507,2512 ****
--- 2507,2513 ----
COPY_NODE_FIELD(lockingClause);
COPY_SCALAR_FIELD(op);
COPY_SCALAR_FIELD(all);
+ COPY_NODE_FIELD(correspondingClause);
COPY_NODE_FIELD(larg);
COPY_NODE_FIELD(rarg);
***************
*** 2522,2527 ****
--- 2523,2530 ----
COPY_SCALAR_FIELD(all);
COPY_NODE_FIELD(larg);
COPY_NODE_FIELD(rarg);
+ COPY_NODE_FIELD(correspondingColumns);
+ COPY_SCALAR_FIELD(hasCorrespondingBy);
COPY_NODE_FIELD(colTypes);
COPY_NODE_FIELD(colTypmods);
COPY_NODE_FIELD(colCollations);
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 982,987 ****
--- 982,988 ----
COMPARE_NODE_FIELD(lockingClause);
COMPARE_SCALAR_FIELD(op);
COMPARE_SCALAR_FIELD(all);
+ COMPARE_NODE_FIELD(correspondingClause);
COMPARE_NODE_FIELD(larg);
COMPARE_NODE_FIELD(rarg);
***************
*** 995,1000 ****
--- 996,1003 ----
COMPARE_SCALAR_FIELD(all);
COMPARE_NODE_FIELD(larg);
COMPARE_NODE_FIELD(rarg);
+ COMPARE_NODE_FIELD(correspondingColumns);
+ COMPARE_SCALAR_FIELD(hasCorrespondingBy);
COMPARE_NODE_FIELD(colTypes);
COMPARE_NODE_FIELD(colTypmods);
COMPARE_NODE_FIELD(colCollations);
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
***************
*** 2894,2899 ****
--- 2894,2901 ----
return true;
if (walker(stmt->lockingClause, context))
return true;
+ if (walker(stmt->correspondingClause, context))
+ return true;
if (walker(stmt->larg, context))
return true;
if (walker(stmt->rarg, context))
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 2032,2037 ****
--- 2032,2038 ----
WRITE_NODE_FIELD(lockingClause);
WRITE_ENUM_FIELD(op, SetOperation);
WRITE_BOOL_FIELD(all);
+ WRITE_NODE_FIELD(correspondingClause);
WRITE_NODE_FIELD(larg);
WRITE_NODE_FIELD(rarg);
}
***************
*** 2296,2301 ****
--- 2297,2304 ----
WRITE_BOOL_FIELD(all);
WRITE_NODE_FIELD(larg);
WRITE_NODE_FIELD(rarg);
+ WRITE_NODE_FIELD(correspondingColumns);
+ WRITE_BOOL_FIELD(hasCorrespondingBy);
WRITE_NODE_FIELD(colTypes);
WRITE_NODE_FIELD(colTypmods);
WRITE_NODE_FIELD(colCollations);
*** a/src/backend/nodes/readfuncs.c
--- b/src/backend/nodes/readfuncs.c
***************
*** 342,347 ****
--- 342,349 ----
READ_BOOL_FIELD(all);
READ_NODE_FIELD(larg);
READ_NODE_FIELD(rarg);
+ READ_NODE_FIELD(correspondingColumns);
+ READ_BOOL_FIELD(hasCorrespondingBy);
READ_NODE_FIELD(colTypes);
READ_NODE_FIELD(colTypmods);
READ_NODE_FIELD(colCollations);
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 54,59 ****
--- 54,62 ----
static Query *transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt);
static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
bool isTopLevel, List **targetlist);
+ static SelectStmt *createSubqueryForCorresponding(List* outputColumns,
+ SelectStmt* main_arg);
+ static List *determineMatchingColumns(List *ltargetlist, List *rtargetlist);
static void determineRecursiveColTypes(ParseState *pstate,
Node *larg, List *nrtargetlist);
static void applyColumnNames(List *dst, List *src);
***************
*** 1665,1670 ****
--- 1668,1875 ----
&rtargetlist);
/*
+ * If CORRESPONDING is specified, syntax and column name validities checked,
+ * column filtering is done by a subquery later on.
+ */
+ if(stmt->correspondingClause == NIL)
+ {
+ // No CORRESPONDING clause, no operation needed for column filtering.
+ op->correspondingColumns = stmt->correspondingClause;
+ op->hasCorrespondingBy = false;
+ }
+ else if(linitial(stmt->correspondingClause) == NULL)
+ {
+ // CORRESPONDING clause, find matching column names from both tables. If there are none then it is a syntax error.
+
+ Query *largQuery;
+ Query *rargQuery;
+ List *matchingColumns;
+
+ /* Analyze left query to resolve column names. */
+ largQuery = parse_sub_analyze((Node *) stmt->larg, pstate, NULL, false);
+
+ /* Analyze right query to resolve column names. */
+ rargQuery = parse_sub_analyze((Node *) stmt->rarg, pstate, NULL, false);
+
+ /* Find matching columns from both queries. */
+ matchingColumns = determineMatchingColumns(largQuery->targetList,
+ rargQuery->targetList);
+
+ op->correspondingColumns = matchingColumns;
+ op->hasCorrespondingBy = false;
+
+ /* If matchingColumns is empty, there is an error. At least one column in the select lists must have the same name. */
+ if(list_length(matchingColumns) == 0)
+ {
+ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("%s queries with a CORRESPONDING clause must have at least one column with the same name",
+ context)));
+ }
+
+
+ // Create subquery for larg, selecting column names from matchingColumns.
+ stmt->larg = createSubqueryForCorresponding(matchingColumns, stmt->larg);
+
+ // Assign newly generated query to original left query.
+ op->larg = transformSetOperationTree(pstate, stmt->larg,
+ false,
+ <argetlist);
+
+ // Create subquery for rarg, selecting column names from matchingColumns.
+ stmt->rarg = createSubqueryForCorresponding(matchingColumns, stmt->rarg);
+
+ // Assign newly generated query to original right query.
+ op->rarg = transformSetOperationTree(pstate, stmt->rarg,
+ false,
+ &rtargetlist);
+ }
+ else
+ {
+ // CORRESPONDING BY clause, find matching column names from both tables
+ // and intersect them with BY(...) column list. If there are none
+ // then it is a syntax error.
+
+ Query *largQuery;
+ Query *rargQuery;
+ List *matchingColumns;
+ List *matchingColumnsFiltered;
+ ListCell *corrtl;
+ ListCell *mctl;
+
+ /* Analyze left query to resolve column names. */
+ largQuery = parse_sub_analyze((Node *) stmt->larg, pstate, NULL, false);
+
+ /* Analyze right query to resolve column names. */
+ rargQuery = parse_sub_analyze((Node *) stmt->rarg, pstate, NULL, false);
+
+ /*
+ * Find matching columns from both queries.
+ * In CORRESPONDING BY, column names will be removed from
+ * matchingColumns if they are not in the BY clause.
+ * All columns in the BY clause must be in matchingColumns,
+ * otherwise raise syntax error in BY clause.
+ */
+
+ matchingColumns = determineMatchingColumns(largQuery->targetList,
+ rargQuery->targetList);
+
+ /*
+ * Every column name in correspondingClause must be in matchingColumns,
+ * otherwise it is a syntax error.
+ */
+ foreach(corrtl, stmt->correspondingClause)
+ {
+ Node* corrtle = lfirst(corrtl);
+ if (IsA(corrtle, ColumnRef) &&
+ list_length(((ColumnRef *) corrtle)->fields) == 1 &&
+ IsA(linitial(((ColumnRef *) corrtle)->fields), String))
+ {
+ /* Get column name from correspondingClause. */
+ char *name = strVal(linitial(((ColumnRef *) corrtle)->fields));
+ bool hasMatch = false;
+
+ foreach(mctl, matchingColumns)
+ {
+ TargetEntry *mctle = (TargetEntry *) lfirst(mctl);
+
+ Assert(mctle->resname != NULL);
+ Assert(name != NULL);
+
+ /* Compare correspondingClause column name with matchingColumns column names. */
+ if(strcmp(mctle->resname, name) == 0)
+ {
+ // we have a match.
+ hasMatch = true;
+ break;
+ }
+ }
+
+ if(!hasMatch)
+ {
+ /* CORRESPONDING BY clause contains a column name that is not in both tables. */
+ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("CORRESPONDING BY clause must only contain column names from both tables.")));
+ }
+
+ }
+ else
+ {
+ /* Only column names are supported, constants are syntax error in CORRESPONDING BY clause. */
+ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg(
+ "%s queries with CORRESPONDING BY clause must have only column names and not constants or ordinals in the column name list.",
+ context)));
+ }
+ }
+
+ /* To preserve column ordering from correspondingClause and to remove
+ * columns from matchingColumns if they are not in correspondingClause,
+ * create a new list and finalize our column list for the
+ * CORRESPONDING BY clause.
+ */
+
+ matchingColumnsFiltered = NIL;
+
+ /* For each column in CORRESPONDING BY column list, check
+ * column existence in matchingColumns.
+ */
+ foreach(corrtl, stmt->correspondingClause)
+ {
+ Node* corrtle = lfirst(corrtl);
+
+ if (IsA(corrtle, ColumnRef) &&
+ list_length(((ColumnRef *) corrtle)->fields) == 1 &&
+ IsA(linitial(((ColumnRef *) corrtle)->fields), String))
+ {
+ char *name = strVal(linitial(((ColumnRef *) corrtle)->fields));
+
+ foreach(mctl, matchingColumns)
+ {
+ TargetEntry *mctle = (TargetEntry *) lfirst(mctl);
+
+ Assert(mctle->resname != NULL);
+ Assert(name != NULL);
+
+ if(strcmp(mctle->resname, name) == 0)
+ {
+ // we have a match.
+ matchingColumnsFiltered = lappend(matchingColumnsFiltered, mctle);
+ break;
+ }
+ }
+ }
+ }
+
+ /* If matchingColumnsFiltered is empty, there is a semantic error. At least one column in the select lists must have the same name. */
+ if(list_length(matchingColumnsFiltered) == 0)
+ {
+ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("%s queries with CORRESPONDING BY clause must have at least one column name in BY clause and in both of the queries.",
+ context)));
+ }
+
+ op->correspondingColumns = matchingColumnsFiltered;
+ op->hasCorrespondingBy = true;
+
+
+ // Create subquery for larg, selecting only columns from matchingColumnsFiltered.
+ stmt->larg = createSubqueryForCorresponding(matchingColumnsFiltered, stmt->larg);
+
+ // Assign newly generated query to original left query.
+ op->larg = transformSetOperationTree(pstate, stmt->larg,
+ false,
+ <argetlist);
+
+ // Create subquery for rarg, selecting only columns from matchingColumnsFiltered.
+ stmt->rarg = createSubqueryForCorresponding(matchingColumnsFiltered, stmt->rarg);
+
+ // Assign newly generated query to original right query.
+ op->rarg = transformSetOperationTree(pstate, stmt->rarg,
+ false,
+ &rtargetlist);
+ }
+
+ /*
* Verify that the two children have the same number of non-junk
* columns, and determine the types of the merged output columns.
*/
***************
*** 1838,1843 ****
--- 2043,2131 ----
}
/*
+ * Returns a subquery selecting outputColumns from main_arg.
+ * main_arg is modified and returned.
+ */
+ static SelectStmt *
+ createSubqueryForCorresponding(List* outputColumns, SelectStmt* main_arg)
+ {
+ ColumnRef *cr;
+ ResTarget *rt;
+ SelectStmt *n;
+
+ RangeSubselect * rss;
+ ListCell* mctl;
+
+ n = makeNode(SelectStmt);
+ n->targetList = NIL;
+ foreach(mctl, outputColumns)
+ {
+ TargetEntry *mctle = (TargetEntry *) lfirst(mctl);
+
+ cr = makeNode(ColumnRef);
+ cr->fields = list_make1(makeString(mctle->resname));
+ cr->location = -1;
+
+ rt = makeNode(ResTarget);
+ rt->name = NULL;
+ rt->indirection = NIL;
+ rt->val = (Node *)cr;
+ rt->location = -1;
+
+ n->targetList = lappend(n->targetList, rt);
+ }
+
+ rss = makeNode(RangeSubselect);
+
+ // XXX makeAlias alias name should be empty??
+ rss->alias = makeAlias("", NULL);
+ rss->subquery = (Node *)main_arg;
+
+ n->fromClause = list_make1(rss);
+
+ main_arg = n;
+
+ return main_arg;
+ }
+
+
+ /*
+ * Processes targetlists of two queries for column equivalence to use
+ * with UNION/INTERSECT/EXCEPT CORRESPONDING.
+ */
+ static List *
+ determineMatchingColumns(List *ltargetlist, List *rtargetlist)
+ {
+ List *matchingColumns = NIL;
+ ListCell *ltl;
+ ListCell *rtl;
+
+ foreach(ltl, ltargetlist)
+ {
+ foreach(rtl, rtargetlist)
+ {
+ TargetEntry *ltle = (TargetEntry *) lfirst(ltl);
+ TargetEntry *rtle = (TargetEntry *) lfirst(rtl);
+
+ elog(DEBUG4, "%s", ltle->resname);
+
+ /* Names of the columns must be resolved before calling this method. */
+ Assert(ltle->resname != NULL);
+ Assert(rtle->resname != NULL);
+
+ /* If column names are the same, append it to the result. */
+ if(strcmp(ltle->resname, rtle->resname) == 0)
+ {
+ matchingColumns = lappend(matchingColumns, ltle);
+ continue;
+ }
+ }
+ }
+
+ return matchingColumns;
+ }
+
+ /*
* Process the outputs of the non-recursive term of a recursive union
* to set up the parent CTE's columns
*/
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 130,136 ****
Node *limitOffset, Node *limitCount,
WithClause *withClause,
core_yyscan_t yyscanner);
! static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg);
static Node *doNegate(Node *n, int location);
static void doNegateFloat(Value *v);
static Node *makeAArrayExpr(List *elements, int location);
--- 130,136 ----
Node *limitOffset, Node *limitCount,
WithClause *withClause,
core_yyscan_t yyscanner);
! static Node *makeSetOp(SetOperation op, bool all, List *correspondingClause, Node *larg, Node *rarg);
static Node *doNegate(Node *n, int location);
static void doNegateFloat(Value *v);
static Node *makeAArrayExpr(List *elements, int location);
***************
*** 321,326 ****
--- 321,327 ----
opt_enum_val_list enum_val_list table_func_column_list
create_generic_options alter_generic_options
relation_expr_list dostmt_opt_list
+ opt_corresponding_clause
%type <list> opt_fdw_options fdw_options
%type <defelt> fdw_option
***************
*** 498,504 ****
CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT
COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS
! CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE
CROSS CSV CURRENT_P
CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
--- 499,505 ----
CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT
COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS
! CONTENT_P CONTINUE_P CONVERSION_P COPY CORRESPONDING COST CREATE
CROSS CSV CURRENT_P
CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
***************
*** 8489,8507 ****
n->fromClause = list_make1($2);
$$ = (Node *)n;
}
! | select_clause UNION opt_all select_clause
{
! $$ = makeSetOp(SETOP_UNION, $3, $1, $4);
}
! | select_clause INTERSECT opt_all select_clause
{
! $$ = makeSetOp(SETOP_INTERSECT, $3, $1, $4);
}
! | select_clause EXCEPT opt_all select_clause
{
! $$ = makeSetOp(SETOP_EXCEPT, $3, $1, $4);
}
;
/*
* SQL standard WITH clause looks like:
--- 8490,8514 ----
n->fromClause = list_make1($2);
$$ = (Node *)n;
}
! | select_clause UNION opt_all opt_corresponding_clause select_clause
{
! $$ = makeSetOp(SETOP_UNION, $3, $4, $1, $5);
}
! | select_clause INTERSECT opt_all opt_corresponding_clause select_clause
{
! $$ = makeSetOp(SETOP_INTERSECT, $3, $4, $1, $5);
}
! | select_clause EXCEPT opt_all opt_corresponding_clause select_clause
{
! $$ = makeSetOp(SETOP_EXCEPT, $3, $4, $1, $5);
}
;
+
+ opt_corresponding_clause:
+ CORRESPONDING BY '(' expr_list ')' { $$ = $4; }
+ | CORRESPONDING { $$ = list_make1(NIL); }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
/*
* SQL standard WITH clause looks like:
***************
*** 12642,12648 ****
}
static Node *
! makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg)
{
SelectStmt *n = makeNode(SelectStmt);
--- 12649,12655 ----
}
static Node *
! makeSetOp(SetOperation op, bool all, List *correspondingClause, Node *larg, Node *rarg)
{
SelectStmt *n = makeNode(SelectStmt);
***************
*** 12650,12655 ****
--- 12657,12663 ----
n->all = all;
n->larg = (SelectStmt *) larg;
n->rarg = (SelectStmt *) rarg;
+ n->correspondingClause = correspondingClause;
return (Node *) n;
}
*** a/src/backend/parser/parse_cte.c
--- b/src/backend/parser/parse_cte.c
***************
*** 927,932 ****
--- 927,934 ----
case SETOP_INTERSECT:
if (stmt->all)
cstate->context = RECURSION_INTERSECT;
+ checkWellFormedRecursionWalker((Node *) stmt->correspondingClause,
+ cstate);
checkWellFormedRecursionWalker((Node *) stmt->larg,
cstate);
checkWellFormedRecursionWalker((Node *) stmt->rarg,
***************
*** 945,950 ****
--- 947,954 ----
case SETOP_EXCEPT:
if (stmt->all)
cstate->context = RECURSION_EXCEPT;
+ checkWellFormedRecursionWalker((Node *) stmt->correspondingClause,
+ cstate);
checkWellFormedRecursionWalker((Node *) stmt->larg,
cstate);
cstate->context = RECURSION_EXCEPT;
*** a/src/backend/parser/parse_type.c
--- b/src/backend/parser/parse_type.c
***************
*** 711,717 ****
stmt->limitOffset != NULL ||
stmt->limitCount != NULL ||
stmt->lockingClause != NIL ||
! stmt->op != SETOP_NONE)
goto fail;
if (list_length(stmt->targetList) != 1)
goto fail;
--- 711,718 ----
stmt->limitOffset != NULL ||
stmt->limitCount != NULL ||
stmt->lockingClause != NIL ||
! stmt->op != SETOP_NONE ||
! stmt->correspondingClause != NIL)
goto fail;
if (list_length(stmt->targetList) != 1)
goto fail;
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 1006,1011 ****
--- 1006,1013 ----
/*
* These fields are used only in "leaf" SelectStmts.
*/
+ List *correspondingClause; /* NULL, list of CORRESPONDING BY exprs, or */
+ /* lcons(NIL, NIL) for CORRESPONDING */
List *distinctClause; /* NULL, list of DISTINCT ON exprs, or
* lcons(NIL,NIL) for all (SELECT DISTINCT) */
IntoClause *intoClause; /* target for SELECT INTO / CREATE TABLE AS */
***************
*** 1043,1052 ****
bool all; /* ALL specified? */
struct SelectStmt *larg; /* left child */
struct SelectStmt *rarg; /* right child */
- /* Eventually add fields for CORRESPONDING spec here */
} SelectStmt;
-
/* ----------------------
* Set Operation node for post-analysis query trees
*
--- 1045,1052 ----
***************
*** 1073,1079 ****
bool all; /* ALL specified? */
Node *larg; /* left child */
Node *rarg; /* right child */
! /* Eventually add fields for CORRESPONDING spec here */
/* Fields derived during parse analysis: */
List *colTypes; /* OID list of output column type OIDs */
--- 1073,1082 ----
bool all; /* ALL specified? */
Node *larg; /* left child */
Node *rarg; /* right child */
!
! /* CORRESPONDING clause fields */
! List *correspondingColumns; /* NIL: No corresponding, else: CORRESPONDING or CORRESPONDING BY matching columns. Not the original clause. */
! bool hasCorrespondingBy; /* If correspondingColumns is not NULL then hasCorrespondingBy if effective, otherwise it is junk. */
/* Fields derived during parse analysis: */
List *colTypes; /* OID list of output column type OIDs */
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
***************
*** 94,99 ****
--- 94,100 ----
PG_KEYWORD("continue", CONTINUE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("conversion", CONVERSION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("copy", COPY, UNRESERVED_KEYWORD)
+ PG_KEYWORD("corresponding", CORRESPONDING, UNRESERVED_KEYWORD)
PG_KEYWORD("cost", COST, UNRESERVED_KEYWORD)
PG_KEYWORD("create", CREATE, RESERVED_KEYWORD)
PG_KEYWORD("cross", CROSS, TYPE_FUNC_NAME_KEYWORD)
*** a/src/test/regress/expected/corresponding_union.out
--- b/src/test/regress/expected/corresponding_union.out
***************
*** 0 ****
--- 1,431 ----
+ --
+ -- UNION (also INTERSECT, EXCEPT) with CORRESPONDING.
+ --
+ -- Simple UNION CORRESPONDING constructs
+ SELECT 1 AS two UNION CORRESPONDING SELECT 2 two;
+ two
+ -----
+ 1
+ 2
+ (2 rows)
+
+ SELECT 1 AS one UNION CORRESPONDING SELECT 1 one;
+ one
+ -----
+ 1
+ (1 row)
+
+ SELECT 1 AS two UNION ALL CORRESPONDING SELECT 2 two;
+ two
+ -----
+ 1
+ 2
+ (2 rows)
+
+ SELECT 1 AS two UNION ALL CORRESPONDING SELECT 1 two;
+ two
+ -----
+ 1
+ 1
+ (2 rows)
+
+ SELECT 1 AS two UNION CORRESPONDING SELECT 2 two UNION CORRESPONDING SELECT 2 two;
+ two
+ -----
+ 1
+ 2
+ (2 rows)
+
+ SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION ALL CORRESPONDING SELECT 2 three;
+ three
+ -------
+ 1
+ 2
+ 2
+ (3 rows)
+
+ SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION CORRESPONDING SELECT 3 three;
+ three
+ -------
+ 1
+ 2
+ 3
+ (3 rows)
+
+ SELECT 1.1 AS two UNION SELECT 2.2 two;
+ two
+ -----
+ 1.1
+ 2.2
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c;
+ a | b | c
+ ---+---+---
+ 1 | 2 | 3
+ 4 | 5 | 6
+ (2 rows)
+
+ SELECT 3 c, 2 b, 1 a UNION CORRESPONDING SELECT 6 c, 4 a, 5 b;
+ c | b | a
+ ---+---+---
+ 3 | 2 | 1
+ 6 | 5 | 4
+ (2 rows)
+
+ -- Simple UNION CORRESPONDING BY constructs
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a) SELECT 4 a, 5 b, 6 c;
+ a
+ ---
+ 1
+ 4
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) SELECT 4 a, 5 b, 6 c;
+ b
+ ---
+ 2
+ 5
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c) SELECT 4 a, 5 b, 6 c;
+ c
+ ---
+ 3
+ 6
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b) SELECT 4 a, 5 b, 6 c;
+ a | b
+ ---+---
+ 1 | 2
+ 4 | 5
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b, c) SELECT 4 a, 5 b, 6 c;
+ b | c
+ ---+---
+ 2 | 3
+ 5 | 6
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, c) SELECT 4 a, 5 b, 6 c;
+ a | c
+ ---+---
+ 1 | 3
+ 4 | 6
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 4 a, 5 b, 6 c;
+ a | b | c
+ ---+---+---
+ 1 | 2 | 3
+ 4 | 5 | 6
+ (2 rows)
+
+ -- CORRESPONDING column ordering, left clause's column ordering must be preserved.
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c;
+ a | b | c
+ ---+---+---
+ 1 | 2 | 3
+ 4 | 5 | 6
+ (2 rows)
+
+ SELECT 2 b, 1 a, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c;
+ b | a | c
+ ---+---+---
+ 2 | 1 | 3
+ 5 | 4 | 6
+ (2 rows)
+
+ SELECT 1 a, 3 c, 2 b UNION CORRESPONDING SELECT 4 a, 5 b, 6 c;
+ a | c | b
+ ---+---+---
+ 1 | 3 | 2
+ 4 | 6 | 5
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 5 b, 6 c, 4 a;
+ a | b | c
+ ---+---+---
+ 1 | 2 | 3
+ 4 | 5 | 6
+ (2 rows)
+
+ SELECT 2 b, 1 a, 3 c UNION CORRESPONDING SELECT 5 b, 6 c, 4 a;
+ b | a | c
+ ---+---+---
+ 2 | 1 | 3
+ 5 | 4 | 6
+ (2 rows)
+
+ -- CORRESPONDING BY column ordering, BY clause column ordering must be preserved.
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 4 a, 5 b, 6 c;
+ a | b | c
+ ---+---+---
+ 1 | 2 | 3
+ 4 | 5 | 6
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b, c, a) SELECT 4 a, 5 b, 6 c;
+ b | c | a
+ ---+---+---
+ 2 | 3 | 1
+ 5 | 6 | 4
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c, a, b) SELECT 4 a, 5 b, 6 c;
+ c | a | b
+ ---+---+---
+ 3 | 1 | 2
+ 6 | 4 | 5
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 5 b, 6 c, 4 a;
+ a | b | c
+ ---+---+---
+ 1 | 2 | 3
+ 4 | 5 | 6
+ (2 rows)
+
+ SELECT 2 b, 3 c, 1 a UNION CORRESPONDING BY(b, c, a) SELECT 4 a, 5 b, 6 c;
+ b | c | a
+ ---+---+---
+ 2 | 3 | 1
+ 5 | 6 | 4
+ (2 rows)
+
+ SELECT 3 c, 2 b, 1 a UNION CORRESPONDING BY(c, a, b) SELECT 4 a, 5 b, 6 c;
+ c | a | b
+ ---+---+---
+ 3 | 1 | 2
+ 6 | 4 | 5
+ (2 rows)
+
+ --
+ -- Try testing from tables...
+ --
+ SELECT f1 AS five FROM FLOAT8_TBL
+ UNION CORRESPONDING
+ SELECT f1 AS five FROM FLOAT8_TBL
+ ORDER BY 1;
+ five
+ -----------------------
+ -1.2345678901234e+200
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ 0
+ (5 rows)
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ UNION CORRESPONDING BY(five)
+ SELECT f1 AS five FROM FLOAT8_TBL
+ ORDER BY 1;
+ five
+ -----------------------
+ -1.2345678901234e+200
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ 0
+ (5 rows)
+
+ SELECT f1 AS ten FROM FLOAT8_TBL
+ UNION ALL CORRESPONDING
+ SELECT f1 AS ten FROM FLOAT8_TBL;
+ ten
+ -----------------------
+ 0
+ -34.84
+ -1004.3
+ -1.2345678901234e+200
+ -1.2345678901234e-200
+ 0
+ -34.84
+ -1004.3
+ -1.2345678901234e+200
+ -1.2345678901234e-200
+ (10 rows)
+
+ SELECT f1 AS nine FROM FLOAT8_TBL
+ UNION CORRESPONDING
+ SELECT f1 AS nine FROM INT4_TBL
+ ORDER BY 1;
+ nine
+ -----------------------
+ -1.2345678901234e+200
+ -2147483647
+ -123456
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ 0
+ 123456
+ 2147483647
+ (9 rows)
+
+ SELECT f1 AS ten FROM FLOAT8_TBL
+ UNION ALL CORRESPONDING
+ SELECT f1 AS ten FROM INT4_TBL;
+ ten
+ -----------------------
+ 0
+ -34.84
+ -1004.3
+ -1.2345678901234e+200
+ -1.2345678901234e-200
+ 0
+ 123456
+ -123456
+ 2147483647
+ -2147483647
+ (10 rows)
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ WHERE f1 BETWEEN -1e6 AND 1e6
+ UNION CORRESPONDING
+ SELECT f1 AS five FROM INT4_TBL
+ WHERE f1 BETWEEN 0 AND 1000000;
+ five
+ -----------------------
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ 0
+ 123456
+ (5 rows)
+
+ --
+ -- INTERSECT and EXCEPT
+ --
+ SELECT q2 FROM int8_tbl INTERSECT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+ q2
+ ------------------
+ 4567890123456789
+ 123
+ (2 rows)
+
+ SELECT q2 FROM int8_tbl INTERSECT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+ q2
+ ------------------
+ 4567890123456789
+ 4567890123456789
+ 123
+ (3 rows)
+
+ SELECT q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1;
+ q2
+ -------------------
+ -4567890123456789
+ 456
+ (2 rows)
+
+ SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1;
+ q2
+ -------------------
+ -4567890123456789
+ 456
+ (2 rows)
+
+ SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q1 AS q2 FROM int8_tbl ORDER BY 1;
+ q2
+ -------------------
+ -4567890123456789
+ 456
+ 4567890123456789
+ (3 rows)
+
+ SELECT q1 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2 AS q1 FROM int8_tbl;
+ q1
+ ----
+ (0 rows)
+
+ SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q2 AS q1 FROM int8_tbl;
+ q1
+ ------------------
+ 4567890123456789
+ 123
+ (2 rows)
+
+ SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q2 AS q1 FROM int8_tbl;
+ q1
+ ------------------
+ 4567890123456789
+ 4567890123456789
+ 123
+ (3 rows)
+
+ --
+ -- Mixed types
+ --
+ SELECT f1 FROM float8_tbl INTERSECT CORRESPONDING SELECT f1 FROM int4_tbl;
+ f1
+ ----
+ 0
+ (1 row)
+
+ SELECT f1 FROM float8_tbl EXCEPT CORRESPONDING SELECT f1 FROM int4_tbl ORDER BY 1;
+ f1
+ -----------------------
+ -1.2345678901234e+200
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ (4 rows)
+
+ --
+ -- Subqueries with ORDER BY & LIMIT clauses
+ --
+ -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2,q1 FROM int8_tbl
+ ORDER BY q2,q1;
+ q1 | q2
+ ----+----
+ (0 rows)
+
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q1) SELECT q2,q1 FROM int8_tbl
+ ORDER BY q1;
+ q1
+ ----
+ (0 rows)
+
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q2) SELECT q2,q1 FROM int8_tbl
+ ORDER BY q2;
+ q2
+ ----
+ (0 rows)
+
+ --
+ -- New syntaxes (7.1) permit new tests
+ --
+ (((((select * from int8_tbl)))));
+ q1 | q2
+ ------------------+-------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+ (5 rows)
+
+ --
+ -- Check handling of a case with unknown constants. We don't guarantee
+ -- an undecorated constant will work in all cases, but historically this
+ -- usage has worked, so test we don't break it.
+ --
+ SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a
+ UNION CORRESPONDING
+ SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b
+ ORDER BY 1;
+ f1
+ ------
+ a
+ ab
+ abcd
+ test
+ (4 rows)
+
+ -- This should fail, but it should produce an error cursor
+ SELECT '3.4'::numeric AS a UNION CORRESPONDING SELECT 'foo' AS a;
+ ERROR: failed to find conversion function from unknown to numeric
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
***************
*** 74,80 ****
# ----------
# Another group of parallel tests
# ----------
! test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete
# ----------
# Another group of parallel tests
--- 74,80 ----
# ----------
# Another group of parallel tests
# ----------
! test: select_into select_distinct select_distinct_on select_implicit select_having subselect union corresponding_union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete
# ----------
# Another group of parallel tests
*** a/src/test/regress/serial_schedule
--- b/src/test/regress/serial_schedule
***************
*** 73,78 ****
--- 73,79 ----
test: select_having
test: subselect
test: union
+ test: corresponding_union
test: case
test: join
test: aggregates
*** a/src/test/regress/sql/corresponding_union.sql
--- b/src/test/regress/sql/corresponding_union.sql
***************
*** 0 ****
--- 1,162 ----
+ --
+ -- UNION (also INTERSECT, EXCEPT) with CORRESPONDING.
+ --
+
+ -- Simple UNION CORRESPONDING constructs
+
+ SELECT 1 AS two UNION CORRESPONDING SELECT 2 two;
+
+ SELECT 1 AS one UNION CORRESPONDING SELECT 1 one;
+
+ SELECT 1 AS two UNION ALL CORRESPONDING SELECT 2 two;
+
+ SELECT 1 AS two UNION ALL CORRESPONDING SELECT 1 two;
+
+ SELECT 1 AS two UNION CORRESPONDING SELECT 2 two UNION CORRESPONDING SELECT 2 two;
+
+ SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION ALL CORRESPONDING SELECT 2 three;
+
+ SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION CORRESPONDING SELECT 3 three;
+
+ SELECT 1.1 AS two UNION SELECT 2.2 two;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c;
+
+ SELECT 3 c, 2 b, 1 a UNION CORRESPONDING SELECT 6 c, 4 a, 5 b;
+
+ -- Simple UNION CORRESPONDING BY constructs
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b, c) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, c) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 4 a, 5 b, 6 c;
+
+ -- CORRESPONDING column ordering, left clause's column ordering must be preserved.
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c;
+
+ SELECT 2 b, 1 a, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 3 c, 2 b UNION CORRESPONDING SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 5 b, 6 c, 4 a;
+
+ SELECT 2 b, 1 a, 3 c UNION CORRESPONDING SELECT 5 b, 6 c, 4 a;
+
+ -- CORRESPONDING BY column ordering, BY clause column ordering must be preserved.
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b, c, a) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c, a, b) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 5 b, 6 c, 4 a;
+
+ SELECT 2 b, 3 c, 1 a UNION CORRESPONDING BY(b, c, a) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 3 c, 2 b, 1 a UNION CORRESPONDING BY(c, a, b) SELECT 4 a, 5 b, 6 c;
+
+ --
+ -- Try testing from tables...
+ --
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ UNION CORRESPONDING
+ SELECT f1 AS five FROM FLOAT8_TBL
+ ORDER BY 1;
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ UNION CORRESPONDING BY(five)
+ SELECT f1 AS five FROM FLOAT8_TBL
+ ORDER BY 1;
+
+ SELECT f1 AS ten FROM FLOAT8_TBL
+ UNION ALL CORRESPONDING
+ SELECT f1 AS ten FROM FLOAT8_TBL;
+
+ SELECT f1 AS nine FROM FLOAT8_TBL
+ UNION CORRESPONDING
+ SELECT f1 AS nine FROM INT4_TBL
+ ORDER BY 1;
+
+ SELECT f1 AS ten FROM FLOAT8_TBL
+ UNION ALL CORRESPONDING
+ SELECT f1 AS ten FROM INT4_TBL;
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ WHERE f1 BETWEEN -1e6 AND 1e6
+ UNION CORRESPONDING
+ SELECT f1 AS five FROM INT4_TBL
+ WHERE f1 BETWEEN 0 AND 1000000;
+
+ --
+ -- INTERSECT and EXCEPT
+ --
+
+ SELECT q2 FROM int8_tbl INTERSECT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+
+ SELECT q2 FROM int8_tbl INTERSECT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+
+ SELECT q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1;
+
+ SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1;
+
+ SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q1 AS q2 FROM int8_tbl ORDER BY 1;
+
+ SELECT q1 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2 AS q1 FROM int8_tbl;
+
+ SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q2 AS q1 FROM int8_tbl;
+
+ SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q2 AS q1 FROM int8_tbl;
+
+ --
+ -- Mixed types
+ --
+
+ SELECT f1 FROM float8_tbl INTERSECT CORRESPONDING SELECT f1 FROM int4_tbl;
+
+ SELECT f1 FROM float8_tbl EXCEPT CORRESPONDING SELECT f1 FROM int4_tbl ORDER BY 1;
+
+ --
+ -- Subqueries with ORDER BY & LIMIT clauses
+ --
+
+ -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2,q1 FROM int8_tbl
+ ORDER BY q2,q1;
+
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q1) SELECT q2,q1 FROM int8_tbl
+ ORDER BY q1;
+
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q2) SELECT q2,q1 FROM int8_tbl
+ ORDER BY q2;
+
+ --
+ -- New syntaxes (7.1) permit new tests
+ --
+
+ (((((select * from int8_tbl)))));
+
+ --
+ -- Check handling of a case with unknown constants. We don't guarantee
+ -- an undecorated constant will work in all cases, but historically this
+ -- usage has worked, so test we don't break it.
+ --
+
+ SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a
+ UNION CORRESPONDING
+ SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b
+ ORDER BY 1;
+
+ -- This should fail, but it should produce an error cursor
+ SELECT '3.4'::numeric AS a UNION CORRESPONDING SELECT 'foo' AS a;
On Tue, October 25, 2011 19:49, Kerem Kat wrote:
On Mon, Oct 24, 2011 at 20:52, Erik Rijkers <er@xs4all.nl> wrote:
On Wed, October 19, 2011 15:01, Kerem Kat wrote:
Adding CORRESPONDING to Set Operations
I have corrected the ordering of the targetlists of subqueries. And
added 12 regression
tests for column list ordering. Can you confirm that the order has
changed for you?
Yes, this one is OK.
thanks,
Erik Rijkers
(pgsql 9.2devel (25 oct) with your latest CORRESPONDING patch;
linux x86_64 GNU/Linux 2.6.18-274.3.1.el5)
Hi,
here is another peculiarity, which I think is a bug:
-- first without CORRESPONDING:
$ psql -Xaf null.sql
select 1 a , 2 b
union all
select null a, 4 b ;
a | b
---+---
1 | 2
| 4
(2 rows)
-- then with CORRESPONDING:
select 1 a , 2 b
union all
corresponding
select null a, 4 b ;
psql:null.sql:9: ERROR: failed to find conversion function from unknown to integer
If the null value is in a table column the error does not occur:
drop table if exists t1; create table t1 (a int, b int); insert into t1 values (1,2);
drop table if exists t2; create table t2 (a int, b int); insert into t2 values (null,2);
select a,b from t1
union all
corresponding
select a,b from t2 ;
a | b
---+---
1 | 2
| 2
(2 rows)
I'm not sure it is actually a bug; but it seems an unneccessary error.
thanks,
Erik Rijkers
Hi,
Union with NULL error persists without the corresponding patch. Here
is the output from postgres without the patch:
SELECT a FROM (SELECT 1 a) foo
UNION
SELECT a FROM (SELECT NULL a) foo2;
ERROR: failed to find conversion function from unknown to integer
It is thrown from parse_coerce.c:coerce_type method. I will try to dig
deep on it.
Regards,
Kerem KAT
Show quoted text
On Thu, Oct 27, 2011 at 15:45, Erik Rijkers <er@xs4all.nl> wrote:
(pgsql 9.2devel (25 oct) with your latest CORRESPONDING patch;
linux x86_64 GNU/Linux 2.6.18-274.3.1.el5)Hi,
here is another peculiarity, which I think is a bug:
-- first without CORRESPONDING:
$ psql -Xaf null.sql
select 1 a , 2 b
union all
select null a, 4 b ;
a | b
---+---
1 | 2
| 4
(2 rows)-- then with CORRESPONDING:
select 1 a , 2 b
union all
corresponding
select null a, 4 b ;
psql:null.sql:9: ERROR: failed to find conversion function from unknown to integerIf the null value is in a table column the error does not occur:
drop table if exists t1; create table t1 (a int, b int); insert into t1 values (1,2);
drop table if exists t2; create table t2 (a int, b int); insert into t2 values (null,2);
select a,b from t1
union all
corresponding
select a,b from t2 ;
a | b
---+---
1 | 2
| 2
(2 rows)I'm not sure it is actually a bug; but it seems an unneccessary error.
thanks,
Erik Rijkers
Kerem Kat <keremkat@gmail.com> writes:
Union with NULL error persists without the corresponding patch. Here
is the output from postgres without the patch:
SELECT a FROM (SELECT 1 a) foo
UNION
SELECT a FROM (SELECT NULL a) foo2;
ERROR: failed to find conversion function from unknown to integer
Yeah, this is a longstanding issue that is not simple to fix without
introducing other unpleasantnesses. It is not something you should
try to deal with at the same time as implementing CORRESPONDING.
regards, tom lane
I wrote:
Kerem Kat <keremkat@gmail.com> writes:
Union with NULL error persists without the corresponding patch. Here
is the output from postgres without the patch:
SELECT a FROM (SELECT 1 a) foo
UNION
SELECT a FROM (SELECT NULL a) foo2;
ERROR: failed to find conversion function from unknown to integer
Yeah, this is a longstanding issue that is not simple to fix without
introducing other unpleasantnesses. It is not something you should
try to deal with at the same time as implementing CORRESPONDING.
BTW, just to clarify: although that case fails, the case Erik was
complaining of does work in unmodified Postgres:
regression=# select 1 a , 2 b
union all
select null a, 4 b ;
a | b
---+---
1 | 2
| 4
(2 rows)
and I agree with him that it should still work with CORRESPONDING.
Even though the behavior of unlabeled NULLs is less than perfect,
we definitely don't want to break cases that work now. I suspect
the failure means that you tried to postpone too much work to plan
time. You do have to match up the columns honestly at parse time
and do the necessary type coercions on them then.
regards, tom lane
On Thu, Oct 27, 2011 at 23:20, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I wrote:
Kerem Kat <keremkat@gmail.com> writes:
Union with NULL error persists without the corresponding patch. Here
is the output from postgres without the patch:SELECT a FROM (SELECT 1 a) foo
UNION
SELECT a FROM (SELECT NULL a) foo2;ERROR: failed to find conversion function from unknown to integer
Yeah, this is a longstanding issue that is not simple to fix without
introducing other unpleasantnesses. It is not something you should
try to deal with at the same time as implementing CORRESPONDING.BTW, just to clarify: although that case fails, the case Erik was
complaining of does work in unmodified Postgres:regression=# select 1 a , 2 b
union all
select null a, 4 b ;
a | b
---+---
1 | 2
| 4
(2 rows)and I agree with him that it should still work with CORRESPONDING.
Even though the behavior of unlabeled NULLs is less than perfect,
we definitely don't want to break cases that work now. I suspect
the failure means that you tried to postpone too much work to plan
time. You do have to match up the columns honestly at parse time
and do the necessary type coercions on them then.regards, tom lane
That is by design, because CORRESPONDING is implemented as subqueries:
select 1 a , 2 b
union all
corresponding
select null a, 4 b ;
is equivalent to
SELECT a, b FROM ( SELECT 1 a, 2 b ) foo
UNION ALL
SELECT a, b FROM ( SELECT null a, 4 b ) foo2;
which gives the same error in unpatched postgres.
Regards,
Kerem KAT
Kerem Kat <keremkat@gmail.com> writes:
On Thu, Oct 27, 2011 at 23:20, Tom Lane <tgl@sss.pgh.pa.us> wrote:
BTW, just to clarify: although that case fails, the case Erik was
complaining of does work in unmodified Postgres:
...
and I agree with him that it should still work with CORRESPONDING.
That is by design, because CORRESPONDING is implemented as subqueries:
Well, this appears to me to be a counterexample sufficient to refute
that implementation decision. You can inject subqueries at plan time,
if that helps you make things match up, but you can't rearrange things
that way at parse time, as I gather you're doing or else you would not
be seeing this problem. In any case, I already pointed out to you that
rearranging the parse tree that way is problematic for reverse-listing
the parse tree. We don't want to see subqueries injected in the results
of printing parse trees with ruleutils.c.
regards, tom lane
On 25 October 2011 18:49, Kerem Kat <keremkat@gmail.com> wrote:
On Mon, Oct 24, 2011 at 20:52, Erik Rijkers <er@xs4all.nl> wrote:
On Wed, October 19, 2011 15:01, Kerem Kat wrote:
Adding CORRESPONDING to Set Operations
Initial patch, filename: corresponding_clause_v2.patchI had a quick look at the behaviour of this patch.
Btw, the examples in your email were typoed (one select is missing):
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING 4 b, 5 d, 6 c, 7 f;
should be:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING select 4 b, 5 d, 6 c, 7 f;and
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) 4 b, 5 d, 6 c, 7 f;
should be:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) select 4 b, 5 d, 6 c, 7 f;Yes you are correct, mea culpa.
But there is also a small bug, I think: the order in the CORRESPONDING BY list should be followed,
according to the standard (foundation, p. 408):"2) If <corresponding column list> is specified, then let SL be a <select list> of those <column
name>s explicitly appearing in the <corresponding column list> in the order that these
<column name>s appear in the <corresponding column list>. Every <column name> in the
<corresponding column list> shall be a <column name> of both T1 and T2."That would make this wrong, I think:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c,b) select 5 d, 6 c, 7 f, 4 b ;
b | c
---+---
2 | 3
4 | 6
(2 rows)i.e., I think it should show columns in the order c, b (and not b, c); the order of the
CORRESPONDING BY phrase.(but maybe I'm misreading the text of the standard; I find it often difficult to follow)
It wasn't a misread, I checked the draft, in my version same
explanation is at p.410.
I have corrected the ordering of the targetlists of subqueries. And
added 12 regression
tests for column list ordering. Can you confirm that the order has
changed for you?Thanks,
Erik Rijkers
Regards,
Kerem KAT
This explain plan doesn't look right to me:
test=# explain select a,b,c from one intersect corresponding by (a,c)
select a,b,c from two;
QUERY PLAN
---------------------------------------------------------------------------------
HashSetOp Intersect (cost=0.00..117.00 rows=200 width=8)
-> Append (cost=0.00..97.60 rows=3880 width=8)
-> Subquery Scan on "*SELECT* 3" (cost=0.00..48.80 rows=1940 width=8)
-> Seq Scan on one (cost=0.00..29.40 rows=1940 width=8)
-> Subquery Scan on "*SELECT* 4" (cost=0.00..48.80 rows=1940 width=8)
-> Seq Scan on two (cost=0.00..29.40 rows=1940 width=8)
(6 rows)
If I do the same thing without the "corresponding...":
test=# explain select a,b,c from one intersect select a,b,c from two;
QUERY PLAN
----------------------------------------------------------------------------------
HashSetOp Intersect (cost=0.00..126.70 rows=200 width=12)
-> Append (cost=0.00..97.60 rows=3880 width=12)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..48.80
rows=1940 width=12)
-> Seq Scan on one (cost=0.00..29.40 rows=1940 width=12)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..48.80
rows=1940 width=12)
-> Seq Scan on two (cost=0.00..29.40 rows=1940 width=12)
(6 rows)
So it looks like it's now seeing the two tables as the 3rd and 4th
tables, even though there are only 2 tables in total.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
This explain plan doesn't look right to me:
test=# explain select a,b,c from one intersect corresponding by (a,c)
select a,b,c from two;
QUERY PLAN
---------------------------------------------------------------------------------
HashSetOp Intersect (cost=0.00..117.00 rows=200 width=8)
-> Append (cost=0.00..97.60 rows=3880 width=8)
-> Subquery Scan on "*SELECT* 3" (cost=0.00..48.80 rows=1940 width=8)
-> Seq Scan on one (cost=0.00..29.40 rows=1940 width=8)
-> Subquery Scan on "*SELECT* 4" (cost=0.00..48.80 rows=1940 width=8)
-> Seq Scan on two (cost=0.00..29.40 rows=1940 width=8)
(6 rows)
In the current implementation,
select a,b,c from one intersect corresponding by (a,c) select a,b,c from two;
is translated to equivalent
select a, c from (select a,b,c from one)
intersect
select a, c from (select a,b,c from two);
Methinks that's the reason for this explain output.
Corresponding is currently implemented in the parse/analyze phase. If
it were to be implemented in the planning phase, explain output would
likely be as you expect it to be.
If I do the same thing without the "corresponding...":
test=# explain select a,b,c from one intersect select a,b,c from two;
QUERY PLAN
----------------------------------------------------------------------------------
HashSetOp Intersect (cost=0.00..126.70 rows=200 width=12)
-> Append (cost=0.00..97.60 rows=3880 width=12)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..48.80
rows=1940 width=12)
-> Seq Scan on one (cost=0.00..29.40 rows=1940 width=12)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..48.80
rows=1940 width=12)
-> Seq Scan on two (cost=0.00..29.40 rows=1940 width=12)
(6 rows)So it looks like it's now seeing the two tables as the 3rd and 4th
tables, even though there are only 2 tables in total.--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Regards,
Kerem KAT
On 14 November 2011 11:29, Kerem Kat <keremkat@gmail.com> wrote:
This explain plan doesn't look right to me:
test=# explain select a,b,c from one intersect corresponding by (a,c)
select a,b,c from two;
QUERY PLAN---------------------------------------------------------------------------------
HashSetOp Intersect (cost=0.00..117.00 rows=200 width=8)
-> Append (cost=0.00..97.60 rows=3880 width=8)
-> Subquery Scan on "*SELECT* 3" (cost=0.00..48.80 rows=1940width=8)
-> Seq Scan on one (cost=0.00..29.40 rows=1940 width=8)
-> Subquery Scan on "*SELECT* 4" (cost=0.00..48.80 rows=1940width=8)
-> Seq Scan on two (cost=0.00..29.40 rows=1940 width=8)
(6 rows)In the current implementation,
select a,b,c from one intersect corresponding by (a,c) select a,b,c from
two;is translated to equivalent
select a, c from (select a,b,c from one)
intersect
select a, c from (select a,b,c from two);Methinks that's the reason for this explain output.
Corresponding is currently implemented in the parse/analyze phase. If
it were to be implemented in the planning phase, explain output would
likely be as you expect it to be.
I'm certainly no expert on what the right way to represent the plan is, but
I'm still uncomfortable with its current representation. And having just
tested the translated equivalent, I still don't get the same explain plan:
test=# explain select a, c from (select a,b,c from one) a
intersect
select a, c from (select a,b,c from two) b;
QUERY PLAN
---------------------------------------------------------------------------------
HashSetOp Intersect (cost=0.00..117.00 rows=200 width=8)
-> Append (cost=0.00..97.60 rows=3880 width=8)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..48.80 rows=1940
width=8)
-> Seq Scan on one (cost=0.00..29.40 rows=1940 width=8)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..48.80 rows=1940
width=8)
-> Seq Scan on two (cost=0.00..29.40 rows=1940 width=8)
(6 rows)
Also you probably want to update src/backend/catalog/sql_features.txt so
that F301 is marked as "YES" for supporting the standard. :)
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Kerem Kat <keremkat@gmail.com> writes:
Corresponding is currently implemented in the parse/analyze phase. If
it were to be implemented in the planning phase, explain output would
likely be as you expect it to be.
It's already been pointed out to you that doing this at parse time is
unacceptable, because of the implications for reverse-listing of rules
(views).
regards, tom lane
On Mon, Nov 14, 2011 at 15:32, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Kerem Kat <keremkat@gmail.com> writes:
Corresponding is currently implemented in the parse/analyze phase. If
it were to be implemented in the planning phase, explain output would
likely be as you expect it to be.It's already been pointed out to you that doing this at parse time is
unacceptable, because of the implications for reverse-listing of rules
(views).regards, tom lane
I am well aware of that thank you.
Regards,
Kerem KAT
On Mon, Nov 14, 2011 at 6:09 AM, Kerem Kat <keremkat@gmail.com> wrote:
On Mon, Nov 14, 2011 at 15:32, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Kerem Kat <keremkat@gmail.com> writes:
Corresponding is currently implemented in the parse/analyze phase. If
it were to be implemented in the planning phase, explain output would
likely be as you expect it to be.It's already been pointed out to you that doing this at parse time is
unacceptable, because of the implications for reverse-listing of rules
(views).regards, tom lane
I am well aware of that thank you.
I took a quick look at the patch and found some miscellaneous points including:
- don't use // style comment
- keep consistent in terms of space around parenthesis like if and foreach
- ereport should have error position as long as possible, especially
in syntax error
- I'm not convinced that new correspoinding_union.sql test is added. I
prefer to include new tests in union.sql
- CORRESPONDING BY should have column name list, not expression list.
It's not legal to say CORRESPONDING BY (1 + 1)
- column list rule should be presented in document, too
- I don't see why you call checkWellFormedRecursionWalker on
corresponding clause
And more than above, Tom's point is the biggest blocker. I'd suggest
to rework it so that target list of Query of RangeTblEntry on the top
of tree have less columns that match the filter. By that way, I guess
you can keep original information as well as filtered top-most target
list. Eventually you need to work on the planner, too. Though I've not
read all of the patch, the design rework should be done first. I'll
mark this as Waiting on Author.
Regards,
--
Hitoshi Harada