Adding CORRESPONDING to Set Operations
Hello,
I am new to postgresql code, I would like to start implementing easyish TODO
items. I have read most of the development guidelines, faqs, articles by
Greg Smith (Hacking Postgres with UDFs, Adding WHEN to triggers).
The item I would like to implement is adding CORRESPONDING [BY
(col1[,col2,...]])] to INTERSECT and EXCEPT operators.
Can anyone comment on how much effort this item needs?
regards, kerem kat.
Is it feasible to implement the CORRESPONDING [BY (expr_list)] statement in
set operations by the following changes:
i) In analyze.c:transformSetOperationStmt after parsing left and right
queries as subnodes to a set operation tree,
a) CORRESPONDING: Find matching column targets from both statements,
eliminate unmatching targets and proceed.
b) CORRESPONDING BY (expr_list): Verify expr_list columns exist in both
select statements. Eliminate unmatched column names to expr_list and
proceed.
ii) Instead of elimination set TargetEntry->resjunk = true for unwanted
output columns.
Thank you for your attention,
Any comments are welcome.
Kerem KAT
On Sun, Sep 18, 2011 at 12:39, Kerem Kat <keremkat@gmail.com> wrote:
Show quoted text
Hello,
I am new to postgresql code, I would like to start implementing easyish
TODO items. I have read most of the development guidelines, faqs, articles
by Greg Smith (Hacking Postgres with UDFs, Adding WHEN to triggers).The item I would like to implement is adding CORRESPONDING [BY
(col1[,col2,...]])] to INTERSECT and EXCEPT operators.Can anyone comment on how much effort this item needs?
regards, kerem kat.
On Sun, Sep 18, 2011 at 5:39 AM, Kerem Kat <keremkat@gmail.com> wrote:
I am new to postgresql code, I would like to start implementing easyish TODO
items. I have read most of the development guidelines, faqs, articles by
Greg Smith (Hacking Postgres with UDFs, Adding WHEN to triggers).
The item I would like to implement is adding CORRESPONDING [BY
(col1[,col2,...]])] to INTERSECT and EXCEPT operators.
Can anyone comment on how much effort this item needs?
This seems reasonably tricky for a first project, but maybe not out of
reach if you are a skilled C hacker. It's certainly more complicated
than my first patch:
I guess the first question that needs to be answered here is ... what
exactly is this syntax supposed to do? A little looking around
suggests that EXCEPT CORRESPONDING is supposed to make the
correspondence run by column names rather than by column positions,
and if you further add BY col1, ... then it restricts the comparison
to those columns.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
I delved into the code without waiting for comments from the list just to
learn something about postgresql internals. And I have finished the
CORRESPONDING, now CORRESPONDING BY is being tested. I will also write
documentation and regression tests.
Yes Robert, you are correct. Having used SQL 20nn standard draft as a guide,
a brief explanation can be provided as such:
Shorter version: column name lists are intersected.
Short version: In the set operation queries, which are queries containing
INTERSECT, EXCEPT or UNION, a CORRESPONDING clause can be used to project
the resulting columns to only columns contained in both sides of the query.
There is also and addition of BY(col1, col2, ...) to the clause which
projects the columns to its own list. An example query would clarifiy.
SELECT 1 a, 2 b UNION CORRESPONDING SELECT 3 a;
a
--
1
3
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, c) SELECT 4 a, 5 c
a c
------
1 3
4 5
On Thu, Sep 22, 2011 at 16:20, Robert Haas <robertmhaas@gmail.com> wrote:
Show quoted text
On Sun, Sep 18, 2011 at 5:39 AM, Kerem Kat <keremkat@gmail.com> wrote:
I am new to postgresql code, I would like to start implementing easyish
TODO
items. I have read most of the development guidelines, faqs, articles by
Greg Smith (Hacking Postgres with UDFs, Adding WHEN to triggers).
The item I would like to implement is adding CORRESPONDING [BY
(col1[,col2,...]])] to INTERSECT and EXCEPT operators.
Can anyone comment on how much effort this item needs?This seems reasonably tricky for a first project, but maybe not out of
reach if you are a skilled C hacker. It's certainly more complicated
than my first patch:I guess the first question that needs to be answered here is ... what
exactly is this syntax supposed to do? A little looking around
suggests that EXCEPT CORRESPONDING is supposed to make the
correspondence run by column names rather than by column positions,
and if you further add BY col1, ... then it restricts the comparison
to those columns.--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
While testing I noticed that ordering is incorrect in my implementation. At
first I thought that removing mismatched entries from ltargetlist and
rtargetlist would be enough, it didn't seem enough so I added rtargetlist
sorting.
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING 4 b, 5 a, 6 c;
returns incorrectly:
a b c
1 2 3
4 5 6
Correct:
a b c
1 2 3
5 4 6
In the analyze.c:transfromSetOperationStmt, I tried to sort rtargetlist
before the forboth(ltl, ltargetlist, rtl,rtargetlist) to no avail.
Sorted column names are in correct order in rtargetlist, but query is
executed as if rtargetlist is never sorted.
Where the targetlist gets the column ordering? Apparently not while
targetlist is being lappend'ed (?).
regards,
Kerem KAT
On Thu, Sep 22, 2011 at 17:03, Kerem Kat <keremkat@gmail.com> wrote:
Show quoted text
I delved into the code without waiting for comments from the list just to
learn something about postgresql internals. And I have finished the
CORRESPONDING, now CORRESPONDING BY is being tested. I will also write
documentation and regression tests.Yes Robert, you are correct. Having used SQL 20nn standard draft as a
guide, a brief explanation can be provided as such:Shorter version: column name lists are intersected.
Short version: In the set operation queries, which are queries containing
INTERSECT, EXCEPT or UNION, a CORRESPONDING clause can be used to project
the resulting columns to only columns contained in both sides of the query.
There is also and addition of BY(col1, col2, ...) to the clause which
projects the columns to its own list. An example query would clarifiy.SELECT 1 a, 2 b UNION CORRESPONDING SELECT 3 a;
a
--
1
3SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, c) SELECT 4 a, 5 c
a c
------
1 3
4 5On Thu, Sep 22, 2011 at 16:20, Robert Haas <robertmhaas@gmail.com> wrote:
On Sun, Sep 18, 2011 at 5:39 AM, Kerem Kat <keremkat@gmail.com> wrote:
I am new to postgresql code, I would like to start implementing easyish
TODO
items. I have read most of the development guidelines, faqs, articles by
Greg Smith (Hacking Postgres with UDFs, Adding WHEN to triggers).
The item I would like to implement is adding CORRESPONDING [BY
(col1[,col2,...]])] to INTERSECT and EXCEPT operators.
Can anyone comment on how much effort this item needs?This seems reasonably tricky for a first project, but maybe not out of
reach if you are a skilled C hacker. It's certainly more complicated
than my first patch:I guess the first question that needs to be answered here is ... what
exactly is this syntax supposed to do? A little looking around
suggests that EXCEPT CORRESPONDING is supposed to make the
correspondence run by column names rather than by column positions,
and if you further add BY col1, ... then it restricts the comparison
to those columns.--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Kerem Kat <keremkat@gmail.com> writes:
While testing I noticed that ordering is incorrect in my implementation. At
first I thought that removing mismatched entries from ltargetlist and
rtargetlist would be enough, it didn't seem enough so I added rtargetlist
sorting.
I don't think you can get away with changing the targetlists of the
UNION subqueries; you could break their semantics. Consider for
instance
select distinct a, b, c from t1
union corresponding
select b, c from t2;
If you discard the A column from t1's output list then it will deliver a
different set of rows than it should, because the DISTINCT is
considering the wrong set of values.
One possible way to fix that is to introduce a level of sub-select,
as if the query had been written
select b, c from (select distinct a, b, c from t1) ss1
union
select b, c from (select b, c from t2) ss2;
However, the real problem with either type of hackery is that these
machinations will be visible in the parsed query, which means for
example that a view defined as
create view v1 as
select distinct a, b, c from t1
union corresponding
select b, c from t2;
would come out looking like the transformed version rather than the
original when it's dumped, or even just examined with tools such as
psql's \d+. I think this is bad style. It's certainly ugly to expose
your implementation shortcuts to the user like that, and it also can
cause problems down the road: if in the future we think of some better
way to implement CORRESPONDING, we've lost the chance to do so for any
stored views that got transformed this way. (There are several places
in Postgres now that take such shortcuts, and all of them were mistakes
that we need to clean up someday, IMO.)
So I think that as far as the parser is concerned, you just want to
store the CORRESPONDING clause more or less as-is, and not do too much
more than verify that it's valid. The place to actually implement it is
in the planner (see prepunion.c). Possibly the add-a-level-of-subselect
approach will work, but you want to do that querytree transformation at
plan time not parse time.
regards, tom lane
I am looking into perpunion.c and analyze.c
There is a catch inserting subqueries for corresponding in the planner.
Parser expects to see equal number of columns in both sides of the
UNION query. If there is corresponding however we cannot guarantee that.
Target columns, collations and types for the SetOperationStmt are
determined in the parser. If we pass the column number equality checks,
it is not clear that how one would proceed with the targetlist generation
loop
which is a forboth for two table's columns.
One way would be filtering the columns in the parser anyway and inserting
subqueries in the planner but it leads to the previous problem of column
ordering and view definition mess-up, and it would be too much bloat
methinks.
I can guess what needs to be done in prepunion.c, but I need a waypointer
for the parser.
tom lane: Thanks for your description
regards
Kerem KAT
On Fri, Sep 23, 2011 at 07:40, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Kerem Kat <keremkat@gmail.com> writes:
While testing I noticed that ordering is incorrect in my implementation.
At
first I thought that removing mismatched entries from ltargetlist and
rtargetlist would be enough, it didn't seem enough so I added rtargetlist
sorting.I don't think you can get away with changing the targetlists of the
UNION subqueries; you could break their semantics. Consider for
instanceselect distinct a, b, c from t1
union corresponding
select b, c from t2;If you discard the A column from t1's output list then it will deliver a
different set of rows than it should, because the DISTINCT is
considering the wrong set of values.One possible way to fix that is to introduce a level of sub-select,
as if the query had been writtenselect b, c from (select distinct a, b, c from t1) ss1
union
select b, c from (select b, c from t2) ss2;However, the real problem with either type of hackery is that these
machinations will be visible in the parsed query, which means for
example that a view defined ascreate view v1 as
select distinct a, b, c from t1
union corresponding
select b, c from t2;would come out looking like the transformed version rather than the
original when it's dumped, or even just examined with tools such as
psql's \d+. I think this is bad style. It's certainly ugly to expose
your implementation shortcuts to the user like that, and it also can
cause problems down the road: if in the future we think of some better
way to implement CORRESPONDING, we've lost the chance to do so for any
stored views that got transformed this way. (There are several places
in Postgres now that take such shortcuts, and all of them were mistakes
that we need to clean up someday, IMO.)So I think that as far as the parser is concerned, you just want to
store the CORRESPONDING clause more or less as-is, and not do too much
more than verify that it's valid. The place to actually implement it is
in the planner (see prepunion.c). Possibly the add-a-level-of-subselect
approach will work, but you want to do that querytree transformation at
plan time not parse time.regards, tom lane
Kerem Kat <keremkat@gmail.com> writes:
There is a catch inserting subqueries for corresponding in the planner.
Parser expects to see equal number of columns in both sides of the
UNION query. If there is corresponding however we cannot guarantee that.
Well, you certainly need the parse analysis code to be aware of
CORRESPONDING's effects. But I think you can confine the changes to
adjusting the computation of a SetOperationStmt's list of output column
types. It might be a good idea to also add a list of output column
names to SetOperationStmt, and get rid of the logic that digs down into
the child queries when we need to know the output column names.
Target columns, collations and types for the SetOperationStmt are
determined in the parser. If we pass the column number equality checks,
it is not clear that how one would proceed with the targetlist generation
loop which is a forboth for two table's columns.
Obviously, that logic doesn't work at all for CORRESPONDING, so you'll
need to have a separate code path to deduce the output column list in
that case.
regards, tom lane
On Sat, Sep 24, 2011 at 18:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Kerem Kat <keremkat@gmail.com> writes:
There is a catch inserting subqueries for corresponding in the planner.
Parser expects to see equal number of columns in both sides of the
UNION query. If there is corresponding however we cannot guarantee that.Well, you certainly need the parse analysis code to be aware of
CORRESPONDING's effects. But I think you can confine the changes to
adjusting the computation of a SetOperationStmt's list of output column
types. It might be a good idea to also add a list of output column
names to SetOperationStmt, and get rid of the logic that digs down into
the child queries when we need to know the output column names.
In the parser while analyzing SetOperationStmt, larg and rarg needs to be
transformed as subqueries. SetOperationStmt can have two fields representing
larg and rarg with projected columns according to corresponding:
larg_corresponding,
rarg_corresponding.
Planner uses _corresponding ones if query is a corresponding query,
view-definition-generator
uses larg and rarg which represent the query user entered.
Comments?
Target columns, collations and types for the SetOperationStmt are
determined in the parser. If we pass the column number equality checks,
it is not clear that how one would proceed with the targetlist generation
loop which is a forboth for two table's columns.Obviously, that logic doesn't work at all for CORRESPONDING, so you'll
need to have a separate code path to deduce the output column list in
that case.
If the output column list to be determined at that stage it needs to
be filtered and ordered.
In that case aren't we breaking the non-modification of user query argument?
note: I am new to this list, am I asking too much detail?
regards,
Kerem KAT
Kerem Kat <keremkat@gmail.com> writes:
In the parser while analyzing SetOperationStmt, larg and rarg needs to be
transformed as subqueries. SetOperationStmt can have two fields representing
larg and rarg with projected columns according to corresponding:
larg_corresponding,
rarg_corresponding.
Why? CORRESPONDING at a given set-operation level doesn't affect either
sub-query, so I don't see why you'd need a different representation for
the sub-queries.
Obviously, that logic doesn't work at all for CORRESPONDING, so you'll
need to have a separate code path to deduce the output column list in
that case.
If the output column list to be determined at that stage it needs to
be filtered and ordered.
In that case aren't we breaking the non-modification of user query argument?
No. All that you're doing is correctly computing the lists of the
set-operation's output column types (and probably names too). These are
internal details that needn't be examined when printing the query, so
they won't affect ruleutils.c.
note: I am new to this list, am I asking too much detail?
Well, I am beginning to wonder if you should choose a smaller project
for your first venture into patching Postgres.
regards, tom lane
On Sat, Sep 24, 2011 at 19:51, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Kerem Kat <keremkat@gmail.com> writes:
In the parser while analyzing SetOperationStmt, larg and rarg needs to be
transformed as subqueries. SetOperationStmt can have two fields representing
larg and rarg with projected columns according to corresponding:
larg_corresponding,
rarg_corresponding.Why? CORRESPONDING at a given set-operation level doesn't affect either
sub-query, so I don't see why you'd need a different representation for
the sub-queries.
In the planner to construct a subquery out of SetOperationStmt or
RangeTblRef, a new RangeTblRef is needed.
To create a RangeTableRef, parser state is needed and planner assumes
root->parse->rtable be not modified
after generating simple_rte_array.
SELECT a,b,c FROM t is larg
SELECT a,b FROM (SELECT a,b,c FROM t) is larg_corresponding
SELECT d,a,b FROM t is rarg
SELECT a,b FROM (SELECT d,a,b FROM t); is rarg_corresponding
In the planner choose _corresponding ones if the query has corresponding.
SELECT a,b FROM (SELECT a,b,c FROM t)
UNION
SELECT a,b FROM (SELECT d,a,b FROM t);
Obviously, that logic doesn't work at all for CORRESPONDING, so you'll
need to have a separate code path to deduce the output column list in
that case.If the output column list to be determined at that stage it needs to
be filtered and ordered.
In that case aren't we breaking the non-modification of user query argument?No. All that you're doing is correctly computing the lists of the
set-operation's output column types (and probably names too). These are
internal details that needn't be examined when printing the query, so
they won't affect ruleutils.c.note: I am new to this list, am I asking too much detail?
Well, I am beginning to wonder if you should choose a smaller project
for your first venture into patching Postgres.
regards,
Kerem KAT
Kerem Kat <keremkat@gmail.com> writes:
On Sat, Sep 24, 2011 at 19:51, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Why? CORRESPONDING at a given set-operation level doesn't affect either
sub-query, so I don't see why you'd need a different representation for
the sub-queries.
In the planner to construct a subquery out of SetOperationStmt or
RangeTblRef, a new RangeTblRef is needed.
To create a RangeTableRef, parser state is needed and planner assumes
root->parse->rtable be not modified
after generating simple_rte_array.
Actually, after looking at the code again, I don't think you need any of
that, since there's already a SubqueryScan node being inserted into the
plan. You just need to improve generate_setop_tlist so that it can deal
with cases where the mapping from subplan targetlist to the setop output
columns isn't one-to-one.
regards, tom lane
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
Attachments:
corresponding_clause.patchtext/x-patch; charset=US-ASCII; name=corresponding_clause.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,
+ 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;
On Sun, Oct 16, 2011 at 7:46 PM, Kerem Kat <keremkat@gmail.com> wrote:
CORRESPONDING clause take 2
You should probably read this:
http://wiki.postgresql.org/wiki/Submitting_a_Patch
And add your patch here:
https://commitfest.postgresql.org/action/commitfest_view/open
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company