On Mon, Oct 24, 2011 at 20:52, Erik Rijkers <[email protected]> wrote:
> 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;
>>
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
*** 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;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
