CORRESPONDING clause take 2
After realizing that modifying prepunion.c to include a custom subquery
is not easy(incomprehensible to me) as it sounds and turning into a
hassle after making several uninformed changes, I decided to go with
modifying analyze.c.
The incomprehensible part is constructing a custom subquery as a
SubqueryScan.
Anyway I managed to implement the clause as a Subquery in analyze.c.
In the method transformSetOperationTree, if the node is a setoperation and
contains a corresponding clause, i.e. CORRESPONDING, or CORRESPONDING
BY(columns...),
we determine the common column names. Column ordering in select statements
are not important to the CORRESPONDING. With the common column names
in hand, we create a RangeSubselect node accordingly and replace the original
statement op->larg with the new RangeSubselect. RangeSubselect in turn has the
original op->larg as a from clause. We do the same to op->rarg too.
There were no changes done in prepunion.c
There are documentation changes and one regression test in the patch.
Best Regards,
Kerem KAT
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***************
*** 1225,1230 ****
--- 1225,1233 ----
<primary>EXCEPT</primary>
</indexterm>
<indexterm zone="queries-union">
+ <primary>CORRESPONDING</primary>
+ </indexterm>
+ <indexterm zone="queries-union">
<primary>set union</primary>
</indexterm>
<indexterm zone="queries-union">
***************
*** 1241,1249 ****
The results of two queries can be combined using the set operations
union, intersection, and difference. The syntax is
<synopsis>
! <replaceable>query1</replaceable> UNION <optional>ALL</optional> <replaceable>query2</replaceable>
! <replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <replaceable>query2</replaceable>
! <replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <replaceable>query2</replaceable>
</synopsis>
<replaceable>query1</replaceable> and
<replaceable>query2</replaceable> are queries that can use any of
--- 1244,1252 ----
The results of two queries can be combined using the set operations
union, intersection, and difference. The syntax is
<synopsis>
! <replaceable>query1</replaceable> UNION <optional>ALL</optional> <optional>CORRESPONDING <optional>BY (<replaceable>select_list</replaceable>)</optional></optional> <replaceable>query2</replaceable>
! <replaceable>query1</replaceable> INTERSECT <optional>ALL</optional> <optional>CORRESPONDING <optional>BY (<replaceable>select_list</replaceable>)</optional></optional> <replaceable>query2</replaceable>
! <replaceable>query1</replaceable> EXCEPT <optional>ALL</optional> <optional>CORRESPONDING <optional>BY (<replaceable>select_list</replaceable>)</optional></optional> <replaceable>query2</replaceable>
</synopsis>
<replaceable>query1</replaceable> and
<replaceable>query2</replaceable> are queries that can use any of
***************
*** 1283,1288 ****
--- 1286,1299 ----
</para>
<para>
+ <literal>CORRESPONDING</> returns all columns that are in both <replaceable>query1</> and <replaceable>query2</> with the same name.
+ </para>
+
+ <para>
+ <literal>CORRESPONDING BY</> returns all columns in the column list that are also in both <replaceable>query1</> and <replaceable>query2</> with the same name.
+ </para>
+
+ <para>
In order to calculate the union, intersection, or difference of two
queries, the two queries must be <quote>union compatible</quote>,
which means that they return the same number of columns and
*** a/doc/src/sgml/sql.sgml
--- b/doc/src/sgml/sql.sgml
***************
*** 859,865 ****
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
[ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="PARAMETER">select</replaceable> ]
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
--- 859,865 ----
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
[ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] [ CORRESPONDING [ BY ( <replaceable class="PARAMETER">expression</replaceable> ) ] ] <replaceable class="PARAMETER">select</replaceable> ]
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 2507,2512 ****
--- 2507,2513 ----
COPY_NODE_FIELD(lockingClause);
COPY_SCALAR_FIELD(op);
COPY_SCALAR_FIELD(all);
+ COPY_NODE_FIELD(correspondingClause);
COPY_NODE_FIELD(larg);
COPY_NODE_FIELD(rarg);
***************
*** 2522,2527 ****
--- 2523,2530 ----
COPY_SCALAR_FIELD(all);
COPY_NODE_FIELD(larg);
COPY_NODE_FIELD(rarg);
+ COPY_NODE_FIELD(correspondingColumns);
+ COPY_SCALAR_FIELD(hasCorrespondingBy);
COPY_NODE_FIELD(colTypes);
COPY_NODE_FIELD(colTypmods);
COPY_NODE_FIELD(colCollations);
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 982,987 ****
--- 982,988 ----
COMPARE_NODE_FIELD(lockingClause);
COMPARE_SCALAR_FIELD(op);
COMPARE_SCALAR_FIELD(all);
+ COMPARE_NODE_FIELD(correspondingClause);
COMPARE_NODE_FIELD(larg);
COMPARE_NODE_FIELD(rarg);
***************
*** 995,1000 ****
--- 996,1003 ----
COMPARE_SCALAR_FIELD(all);
COMPARE_NODE_FIELD(larg);
COMPARE_NODE_FIELD(rarg);
+ COMPARE_NODE_FIELD(correspondingColumns);
+ COMPARE_SCALAR_FIELD(hasCorrespondingBy);
COMPARE_NODE_FIELD(colTypes);
COMPARE_NODE_FIELD(colTypmods);
COMPARE_NODE_FIELD(colCollations);
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
***************
*** 2894,2899 ****
--- 2894,2901 ----
return true;
if (walker(stmt->lockingClause, context))
return true;
+ if (walker(stmt->correspondingClause, context))
+ return true;
if (walker(stmt->larg, context))
return true;
if (walker(stmt->rarg, context))
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 2031,2036 ****
--- 2031,2037 ----
WRITE_NODE_FIELD(lockingClause);
WRITE_ENUM_FIELD(op, SetOperation);
WRITE_BOOL_FIELD(all);
+ WRITE_NODE_FIELD(correspondingClause);
WRITE_NODE_FIELD(larg);
WRITE_NODE_FIELD(rarg);
}
***************
*** 2295,2300 ****
--- 2296,2303 ----
WRITE_BOOL_FIELD(all);
WRITE_NODE_FIELD(larg);
WRITE_NODE_FIELD(rarg);
+ WRITE_NODE_FIELD(correspondingColumns);
+ WRITE_BOOL_FIELD(hasCorrespondingBy);
WRITE_NODE_FIELD(colTypes);
WRITE_NODE_FIELD(colTypmods);
WRITE_NODE_FIELD(colCollations);
*** a/src/backend/nodes/readfuncs.c
--- b/src/backend/nodes/readfuncs.c
***************
*** 342,347 ****
--- 342,349 ----
READ_BOOL_FIELD(all);
READ_NODE_FIELD(larg);
READ_NODE_FIELD(rarg);
+ READ_NODE_FIELD(correspondingColumns);
+ READ_BOOL_FIELD(hasCorrespondingBy);
READ_NODE_FIELD(colTypes);
READ_NODE_FIELD(colTypmods);
READ_NODE_FIELD(colCollations);
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 54,59 ****
--- 54,62 ----
static Query *transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt);
static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
bool isTopLevel, List **targetlist);
+ static Node *createSubqueryForCorresponding(List* outputColumns,
+ Node* 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,1898 ----
&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;
+ elog(DEBUG4, "no corresponding.");
+ }
+ else if(linitial(stmt->correspondingClause) == NULL)
+ {
+ // CORRESPONDING, 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;
+
+ elog(DEBUG4, "corresponding.");
+
+ /* Analyze left query to resolve column names. */
+ largQuery = parse_sub_analyze((Node *) stmt->larg, pstate, NULL, false);
+
+ /* Analyze 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;
+
+ foreach(mctl, matchingColumns)
+ {
+ TargetEntry* mctle = (TargetEntry*) lfirst(mctl);
+ elog(DEBUG4, "matchingColumns: %s", mctle->resname);
+ }
+
+ /* 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 CORRESPONDING clause must have at least one column with the same name",
+ context)));
+ }
+
+
+ // Create subquery for larg, selecting only columns from matchingColumns.
+ stmt->larg = createSubqueryForCorresponding(matchingColumns, stmt->larg);
+
+ op->larg = transformSetOperationTree(pstate, stmt->larg,
+ false,
+ <argetlist);
+
+ // Create subquery for rarg, selecting only columns from matchingColumns.
+ stmt->rarg = createSubqueryForCorresponding(matchingColumns, stmt->rarg);
+
+ op->rarg = transformSetOperationTree(pstate, stmt->rarg,
+ false,
+ &rtargetlist);
+ }
+ else
+ {
+ // CORRESPONDING BY.
+
+ Query *largQuery;
+ Query *rargQuery;
+ List *matchingColumns;
+ ListCell *byresname;
+ ListCell *mctl;
+
+ elog(DEBUG4, "corresponding by.");
+
+ /* Analyze left query to resolve column names. */
+ largQuery = parse_sub_analyze((Node *) stmt->larg, pstate, NULL, false);
+
+ /* Analyze 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;
+
+ elog(DEBUG4, "CORRESPONDING BY column name: %s", name);
+
+ 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 contains column names existing in 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 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);
+
+ elog(DEBUG4, "Processing matchingColumns: %s", mctle->resname);
+
+ 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);
+
+ elog(DEBUG4, "Processing correspondingClause: %s", name);
+
+ if(strcmp(mctle->resname, name) == 0)
+ {
+ // we have a match.
+ hasMatch = true;
+ break;
+ }
+ }
+ }
+
+ elog(DEBUG4, "hasmatch: %s", hasMatch ? "T" : "F");
+
+ 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);
+
+ op->larg = transformSetOperationTree(pstate, stmt->larg,
+ false,
+ <argetlist);
+
+ // Create subquery for rarg, selecting only columns from matchingColumns.
+ stmt->rarg = createSubqueryForCorresponding(matchingColumns, stmt->rarg);
+
+ 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.
*/
***************
*** 1837,1842 ****
--- 2065,2151 ----
}
}
+ static Node *
+ createSubqueryForCorresponding(List* outputColumns, Node* 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->fields = list_make1(makeNode(A_Star));
+ 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);
+ }
+
+ //elog(NOTICE, "Before: main_arg: %s", nodeToString(main_arg));
+
+ rss = makeNode(RangeSubselect);
+
+ rss->alias = makeAlias("test111", NULL);
+ rss->subquery = main_arg;
+
+ n->fromClause = list_make1(rss);
+
+ main_arg = n;
+ //elog(NOTICE, "After: main_arg: %s", nodeToString(main_arg));
+
+ return main_arg;
+ }
+
+
+ /*
+ * Processes targetlists of two queries for column equivalence to use
+ * with UNION/INTERSECT/EXCEPT CORRESPONDING.
+ */
+ static List *
+ determineMatchingColumns(List *ltargetlist, List *rtargetlist)
+ {
+ List *matchingColumns = NIL;
+ ListCell *ltl;
+ ListCell *rtl;
+
+ foreach(ltl, ltargetlist)
+ {
+ foreach(rtl, rtargetlist)
+ {
+ TargetEntry *ltle = (TargetEntry *) lfirst(ltl);
+ TargetEntry *rtle = (TargetEntry *) lfirst(rtl);
+
+ elog(DEBUG4, "%s", ltle->resname);
+
+ /* Names of the columns must be resolved before calling this method. */
+ Assert(ltle->resname != NULL);
+ Assert(rtle->resname != NULL);
+
+ /* If column names are the same, append it to the result. */
+ if(strcmp(ltle->resname, rtle->resname) == 0)
+ {
+ matchingColumns = lappend(matchingColumns, ltle);
+ }
+ }
+ }
+
+ return matchingColumns;
+ }
+
/*
* Process the outputs of the non-recursive term of a recursive union
* to set up the parent CTE's columns
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 130,136 ****
Node *limitOffset, Node *limitCount,
WithClause *withClause,
core_yyscan_t yyscanner);
! static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg);
static Node *doNegate(Node *n, int location);
static void doNegateFloat(Value *v);
static Node *makeAArrayExpr(List *elements, int location);
--- 130,136 ----
Node *limitOffset, Node *limitCount,
WithClause *withClause,
core_yyscan_t yyscanner);
! static Node *makeSetOp(SetOperation op, bool all, List *correspondingClause, Node *larg, Node *rarg);
static Node *doNegate(Node *n, int location);
static void doNegateFloat(Value *v);
static Node *makeAArrayExpr(List *elements, int location);
***************
*** 321,326 ****
--- 321,327 ----
opt_enum_val_list enum_val_list table_func_column_list
create_generic_options alter_generic_options
relation_expr_list dostmt_opt_list
+ opt_corresponding_clause
%type <list> opt_fdw_options fdw_options
%type <defelt> fdw_option
***************
*** 498,504 ****
CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT
COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS
! CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE
CROSS CSV CURRENT_P
CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
--- 499,505 ----
CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
CLUSTER COALESCE COLLATE COLLATION COLUMN COMMENT COMMENTS COMMIT
COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS
! CONTENT_P CONTINUE_P CONVERSION_P COPY CORRESPONDING COST CREATE
CROSS CSV CURRENT_P
CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE CURRENT_SCHEMA
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
***************
*** 8480,8498 ****
n->fromClause = list_make1($2);
$$ = (Node *)n;
}
! | select_clause UNION opt_all select_clause
{
! $$ = makeSetOp(SETOP_UNION, $3, $1, $4);
}
! | select_clause INTERSECT opt_all select_clause
{
! $$ = makeSetOp(SETOP_INTERSECT, $3, $1, $4);
}
! | select_clause EXCEPT opt_all select_clause
{
! $$ = makeSetOp(SETOP_EXCEPT, $3, $1, $4);
}
;
/*
* SQL standard WITH clause looks like:
--- 8481,8505 ----
n->fromClause = list_make1($2);
$$ = (Node *)n;
}
! | select_clause UNION opt_all opt_corresponding_clause select_clause
{
! $$ = makeSetOp(SETOP_UNION, $3, $4, $1, $5);
}
! | select_clause INTERSECT opt_all opt_corresponding_clause select_clause
{
! $$ = makeSetOp(SETOP_INTERSECT, $3, $4, $1, $5);
}
! | select_clause EXCEPT opt_all opt_corresponding_clause select_clause
{
! $$ = makeSetOp(SETOP_EXCEPT, $3, $4, $1, $5);
}
;
+
+ opt_corresponding_clause:
+ CORRESPONDING BY '(' expr_list ')' { $$ = $4; }
+ | CORRESPONDING { $$ = list_make1(NIL); }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
/*
* SQL standard WITH clause looks like:
***************
*** 12633,12639 ****
}
static Node *
! makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg)
{
SelectStmt *n = makeNode(SelectStmt);
--- 12640,12646 ----
}
static Node *
! makeSetOp(SetOperation op, bool all, List *correspondingClause, Node *larg, Node *rarg)
{
SelectStmt *n = makeNode(SelectStmt);
***************
*** 12641,12646 ****
--- 12648,12654 ----
n->all = all;
n->larg = (SelectStmt *) larg;
n->rarg = (SelectStmt *) rarg;
+ n->correspondingClause = correspondingClause;
return (Node *) n;
}
*** a/src/backend/parser/parse_cte.c
--- b/src/backend/parser/parse_cte.c
***************
*** 927,932 ****
--- 927,934 ----
case SETOP_INTERSECT:
if (stmt->all)
cstate->context = RECURSION_INTERSECT;
+ checkWellFormedRecursionWalker((Node *) stmt->correspondingClause,
+ cstate);
checkWellFormedRecursionWalker((Node *) stmt->larg,
cstate);
checkWellFormedRecursionWalker((Node *) stmt->rarg,
***************
*** 945,950 ****
--- 947,954 ----
case SETOP_EXCEPT:
if (stmt->all)
cstate->context = RECURSION_EXCEPT;
+ checkWellFormedRecursionWalker((Node *) stmt->correspondingClause,
+ cstate);
checkWellFormedRecursionWalker((Node *) stmt->larg,
cstate);
cstate->context = RECURSION_EXCEPT;
*** a/src/backend/parser/parse_type.c
--- b/src/backend/parser/parse_type.c
***************
*** 711,717 ****
stmt->limitOffset != NULL ||
stmt->limitCount != NULL ||
stmt->lockingClause != NIL ||
! stmt->op != SETOP_NONE)
goto fail;
if (list_length(stmt->targetList) != 1)
goto fail;
--- 711,718 ----
stmt->limitOffset != NULL ||
stmt->limitCount != NULL ||
stmt->lockingClause != NIL ||
! stmt->op != SETOP_NONE ||
! stmt->correspondingClause != NIL)
goto fail;
if (list_length(stmt->targetList) != 1)
goto fail;
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 1006,1011 ****
--- 1006,1013 ----
/*
* These fields are used only in "leaf" SelectStmts.
*/
+ List *correspondingClause; /* NULL, list of CORRESPONDING BY exprs, or */
+ /* lcons(NIL, NIL) for CORRESPONDING */
List *distinctClause; /* NULL, list of DISTINCT ON exprs, or
* lcons(NIL,NIL) for all (SELECT DISTINCT) */
IntoClause *intoClause; /* target for SELECT INTO / CREATE TABLE AS */
***************
*** 1043,1052 ****
bool all; /* ALL specified? */
struct SelectStmt *larg; /* left child */
struct SelectStmt *rarg; /* right child */
- /* Eventually add fields for CORRESPONDING spec here */
} SelectStmt;
-
/* ----------------------
* Set Operation node for post-analysis query trees
*
--- 1045,1052 ----
***************
*** 1073,1079 ****
bool all; /* ALL specified? */
Node *larg; /* left child */
Node *rarg; /* right child */
! /* Eventually add fields for CORRESPONDING spec here */
/* Fields derived during parse analysis: */
List *colTypes; /* OID list of output column type OIDs */
--- 1073,1082 ----
bool all; /* ALL specified? */
Node *larg; /* left child */
Node *rarg; /* right child */
!
! /* CORRESPONDING clause fields */
! List *correspondingColumns; /* NIL: No corresponding, else: CORRESPONDING or CORRESPONDING BY matching columns. Not the original clause. */
! bool hasCorrespondingBy; /* If correspondingColumns is not NULL then hasCorrespondingBy if effective, otherwise it is junk. */
/* Fields derived during parse analysis: */
List *colTypes; /* OID list of output column type OIDs */
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
***************
*** 94,99 ****
--- 94,100 ----
PG_KEYWORD("continue", CONTINUE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("conversion", CONVERSION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("copy", COPY, UNRESERVED_KEYWORD)
+ PG_KEYWORD("corresponding", CORRESPONDING, UNRESERVED_KEYWORD)
PG_KEYWORD("cost", COST, UNRESERVED_KEYWORD)
PG_KEYWORD("create", CREATE, RESERVED_KEYWORD)
PG_KEYWORD("cross", CROSS, TYPE_FUNC_NAME_KEYWORD)
*** a/src/test/regress/expected/corresponding_union.out
--- b/src/test/regress/expected/corresponding_union.out
***************
*** 0 ****
--- 1,352 ----
+ --
+ -- UNION (also INTERSECT, EXCEPT) with CORRESPONDING.
+ --
+ -- Simple UNION CORRESPONDING constructs
+ SELECT 1 AS two UNION CORRESPONDING SELECT 2 two;
+ two
+ -----
+ 1
+ 2
+ (2 rows)
+
+ SELECT 1 AS one UNION CORRESPONDING SELECT 1 one;
+ one
+ -----
+ 1
+ (1 row)
+
+ SELECT 1 AS two UNION ALL CORRESPONDING SELECT 2 two;
+ two
+ -----
+ 1
+ 2
+ (2 rows)
+
+ SELECT 1 AS two UNION ALL CORRESPONDING SELECT 1 two;
+ two
+ -----
+ 1
+ 1
+ (2 rows)
+
+ SELECT 1 AS two UNION CORRESPONDING SELECT 2 two UNION CORRESPONDING SELECT 2 two;
+ two
+ -----
+ 1
+ 2
+ (2 rows)
+
+ SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION ALL CORRESPONDING SELECT 2 three;
+ three
+ -------
+ 1
+ 2
+ 2
+ (3 rows)
+
+ SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION CORRESPONDING SELECT 3 three;
+ three
+ -------
+ 1
+ 2
+ 3
+ (3 rows)
+
+ SELECT 1.1 AS two UNION SELECT 2.2 two;
+ two
+ -----
+ 1.1
+ 2.2
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c;
+ a | b | c
+ ---+---+---
+ 1 | 2 | 3
+ 4 | 5 | 6
+ (2 rows)
+
+ SELECT 3 c, 2 b, 1 a UNION CORRESPONDING SELECT 6 c, 4 a, 5 b;
+ c | b | a
+ ---+---+---
+ 3 | 2 | 1
+ 6 | 5 | 4
+ (2 rows)
+
+ -- Simple UNION CORRESPONDING BY constructs
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a) SELECT 4 a, 5 b, 6 c;
+ a
+ ---
+ 1
+ 4
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) SELECT 4 a, 5 b, 6 c;
+ b
+ ---
+ 2
+ 5
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c) SELECT 4 a, 5 b, 6 c;
+ c
+ ---
+ 3
+ 6
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b) SELECT 4 a, 5 b, 6 c;
+ a | b
+ ---+---
+ 1 | 2
+ 4 | 5
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b, c) SELECT 4 a, 5 b, 6 c;
+ b | c
+ ---+---
+ 2 | 3
+ 5 | 6
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, c) SELECT 4 a, 5 b, 6 c;
+ a | c
+ ---+---
+ 1 | 3
+ 4 | 6
+ (2 rows)
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 4 a, 5 b, 6 c;
+ a | b | c
+ ---+---+---
+ 1 | 2 | 3
+ 4 | 5 | 6
+ (2 rows)
+
+ --
+ -- Try testing from tables...
+ --
+ SELECT f1 AS five FROM FLOAT8_TBL
+ UNION CORRESPONDING
+ SELECT f1 AS five FROM FLOAT8_TBL
+ ORDER BY 1;
+ five
+ -----------------------
+ -1.2345678901234e+200
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ 0
+ (5 rows)
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ UNION CORRESPONDING BY(five)
+ SELECT f1 AS five FROM FLOAT8_TBL
+ ORDER BY 1;
+ five
+ -----------------------
+ -1.2345678901234e+200
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ 0
+ (5 rows)
+
+ SELECT f1 AS ten FROM FLOAT8_TBL
+ UNION ALL CORRESPONDING
+ SELECT f1 AS ten FROM FLOAT8_TBL;
+ ten
+ -----------------------
+ 0
+ -34.84
+ -1004.3
+ -1.2345678901234e+200
+ -1.2345678901234e-200
+ 0
+ -34.84
+ -1004.3
+ -1.2345678901234e+200
+ -1.2345678901234e-200
+ (10 rows)
+
+ SELECT f1 AS nine FROM FLOAT8_TBL
+ UNION CORRESPONDING
+ SELECT f1 AS nine FROM INT4_TBL
+ ORDER BY 1;
+ nine
+ -----------------------
+ -1.2345678901234e+200
+ -2147483647
+ -123456
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ 0
+ 123456
+ 2147483647
+ (9 rows)
+
+ SELECT f1 AS ten FROM FLOAT8_TBL
+ UNION ALL CORRESPONDING
+ SELECT f1 AS ten FROM INT4_TBL;
+ ten
+ -----------------------
+ 0
+ -34.84
+ -1004.3
+ -1.2345678901234e+200
+ -1.2345678901234e-200
+ 0
+ 123456
+ -123456
+ 2147483647
+ -2147483647
+ (10 rows)
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ WHERE f1 BETWEEN -1e6 AND 1e6
+ UNION CORRESPONDING
+ SELECT f1 AS five FROM INT4_TBL
+ WHERE f1 BETWEEN 0 AND 1000000;
+ five
+ -----------------------
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ 0
+ 123456
+ (5 rows)
+
+ --
+ -- INTERSECT and EXCEPT
+ --
+ SELECT q2 FROM int8_tbl INTERSECT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+ q2
+ ------------------
+ 4567890123456789
+ 123
+ (2 rows)
+
+ SELECT q2 FROM int8_tbl INTERSECT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+ q2
+ ------------------
+ 4567890123456789
+ 4567890123456789
+ 123
+ (3 rows)
+
+ SELECT q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1;
+ q2
+ -------------------
+ -4567890123456789
+ 456
+ (2 rows)
+
+ SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1;
+ q2
+ -------------------
+ -4567890123456789
+ 456
+ (2 rows)
+
+ SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q1 AS q2 FROM int8_tbl ORDER BY 1;
+ q2
+ -------------------
+ -4567890123456789
+ 456
+ 4567890123456789
+ (3 rows)
+
+ SELECT q1 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2 AS q1 FROM int8_tbl;
+ q1
+ ----
+ (0 rows)
+
+ SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q2 AS q1 FROM int8_tbl;
+ q1
+ ------------------
+ 4567890123456789
+ 123
+ (2 rows)
+
+ SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q2 AS q1 FROM int8_tbl;
+ q1
+ ------------------
+ 4567890123456789
+ 4567890123456789
+ 123
+ (3 rows)
+
+ --
+ -- Mixed types
+ --
+ SELECT f1 FROM float8_tbl INTERSECT CORRESPONDING SELECT f1 FROM int4_tbl;
+ f1
+ ----
+ 0
+ (1 row)
+
+ SELECT f1 FROM float8_tbl EXCEPT CORRESPONDING SELECT f1 FROM int4_tbl ORDER BY 1;
+ f1
+ -----------------------
+ -1.2345678901234e+200
+ -1004.3
+ -34.84
+ -1.2345678901234e-200
+ (4 rows)
+
+ --
+ -- Subqueries with ORDER BY & LIMIT clauses
+ --
+ -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2,q1 FROM int8_tbl
+ ORDER BY q2,q1;
+ q1 | q2
+ ----+----
+ (0 rows)
+
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q1) SELECT q2,q1 FROM int8_tbl
+ ORDER BY q1;
+ q1
+ ----
+ (0 rows)
+
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q2) SELECT q2,q1 FROM int8_tbl
+ ORDER BY q2;
+ q2
+ ----
+ (0 rows)
+
+ --
+ -- New syntaxes (7.1) permit new tests
+ --
+ (((((select * from int8_tbl)))));
+ q1 | q2
+ ------------------+-------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+ (5 rows)
+
+ --
+ -- Check handling of a case with unknown constants. We don't guarantee
+ -- an undecorated constant will work in all cases, but historically this
+ -- usage has worked, so test we don't break it.
+ --
+ SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a
+ UNION CORRESPONDING
+ SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b
+ ORDER BY 1;
+ f1
+ ------
+ a
+ ab
+ abcd
+ test
+ (4 rows)
+
+ -- This should fail, but it should produce an error cursor
+ SELECT '3.4'::numeric AS a UNION CORRESPONDING SELECT 'foo' AS a;
+ ERROR: failed to find conversion function from unknown to numeric
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
***************
*** 74,80 ****
# ----------
# Another group of parallel tests
# ----------
! test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete
# ----------
# Another group of parallel tests
--- 74,80 ----
# ----------
# Another group of parallel tests
# ----------
! test: select_into select_distinct select_distinct_on select_implicit select_having subselect union corresponding_union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete
# ----------
# Another group of parallel tests
*** a/src/test/regress/serial_schedule
--- b/src/test/regress/serial_schedule
***************
*** 73,78 ****
--- 73,79 ----
test: select_having
test: subselect
test: union
+ test: corresponding_union
test: case
test: join
test: aggregates
*** a/src/test/regress/sql/corresponding_union.sql
--- b/src/test/regress/sql/corresponding_union.sql
***************
*** 0 ****
--- 1,136 ----
+ --
+ -- UNION (also INTERSECT, EXCEPT) with CORRESPONDING.
+ --
+
+ -- Simple UNION CORRESPONDING constructs
+
+ SELECT 1 AS two UNION CORRESPONDING SELECT 2 two;
+
+ SELECT 1 AS one UNION CORRESPONDING SELECT 1 one;
+
+ SELECT 1 AS two UNION ALL CORRESPONDING SELECT 2 two;
+
+ SELECT 1 AS two UNION ALL CORRESPONDING SELECT 1 two;
+
+ SELECT 1 AS two UNION CORRESPONDING SELECT 2 two UNION CORRESPONDING SELECT 2 two;
+
+ SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION ALL CORRESPONDING SELECT 2 three;
+
+ SELECT 1 AS three UNION CORRESPONDING SELECT 2 three UNION CORRESPONDING SELECT 3 three;
+
+ SELECT 1.1 AS two UNION SELECT 2.2 two;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING SELECT 4 a, 5 b, 6 c;
+
+ SELECT 3 c, 2 b, 1 a UNION CORRESPONDING SELECT 6 c, 4 a, 5 b;
+
+ -- Simple UNION CORRESPONDING BY constructs
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b, c) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, c) SELECT 4 a, 5 b, 6 c;
+
+ SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, b, c) SELECT 4 a, 5 b, 6 c;
+
+ --
+ -- Try testing from tables...
+ --
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ UNION CORRESPONDING
+ SELECT f1 AS five FROM FLOAT8_TBL
+ ORDER BY 1;
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ UNION CORRESPONDING BY(five)
+ SELECT f1 AS five FROM FLOAT8_TBL
+ ORDER BY 1;
+
+ SELECT f1 AS ten FROM FLOAT8_TBL
+ UNION ALL CORRESPONDING
+ SELECT f1 AS ten FROM FLOAT8_TBL;
+
+ SELECT f1 AS nine FROM FLOAT8_TBL
+ UNION CORRESPONDING
+ SELECT f1 AS nine FROM INT4_TBL
+ ORDER BY 1;
+
+ SELECT f1 AS ten FROM FLOAT8_TBL
+ UNION ALL CORRESPONDING
+ SELECT f1 AS ten FROM INT4_TBL;
+
+ SELECT f1 AS five FROM FLOAT8_TBL
+ WHERE f1 BETWEEN -1e6 AND 1e6
+ UNION CORRESPONDING
+ SELECT f1 AS five FROM INT4_TBL
+ WHERE f1 BETWEEN 0 AND 1000000;
+
+ --
+ -- INTERSECT and EXCEPT
+ --
+
+ SELECT q2 FROM int8_tbl INTERSECT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+
+ SELECT q2 FROM int8_tbl INTERSECT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl;
+
+ SELECT q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1;
+
+ SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q1 AS q2 FROM int8_tbl ORDER BY 1;
+
+ SELECT q2 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q1 AS q2 FROM int8_tbl ORDER BY 1;
+
+ SELECT q1 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2 AS q1 FROM int8_tbl;
+
+ SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT q2 AS q1 FROM int8_tbl;
+
+ SELECT q1 FROM int8_tbl EXCEPT ALL CORRESPONDING SELECT DISTINCT q2 AS q1 FROM int8_tbl;
+
+ --
+ -- Mixed types
+ --
+
+ SELECT f1 FROM float8_tbl INTERSECT CORRESPONDING SELECT f1 FROM int4_tbl;
+
+ SELECT f1 FROM float8_tbl EXCEPT CORRESPONDING SELECT f1 FROM int4_tbl ORDER BY 1;
+
+ --
+ -- Subqueries with ORDER BY & LIMIT clauses
+ --
+
+ -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING SELECT q2,q1 FROM int8_tbl
+ ORDER BY q2,q1;
+
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q1) SELECT q2,q1 FROM int8_tbl
+ ORDER BY q1;
+
+ SELECT q1,q2 FROM int8_tbl EXCEPT CORRESPONDING BY(q2) SELECT q2,q1 FROM int8_tbl
+ ORDER BY q2;
+
+ --
+ -- New syntaxes (7.1) permit new tests
+ --
+
+ (((((select * from int8_tbl)))));
+
+ --
+ -- Check handling of a case with unknown constants. We don't guarantee
+ -- an undecorated constant will work in all cases, but historically this
+ -- usage has worked, so test we don't break it.
+ --
+
+ SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a
+ UNION CORRESPONDING
+ SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b
+ ORDER BY 1;
+
+ -- This should fail, but it should produce an error cursor
+ SELECT '3.4'::numeric AS a UNION CORRESPONDING SELECT 'foo' AS a;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
