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:
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:
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?
reach if you are a skilled C hacker. It's certainly more complicated
than my first patch:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a0b76dc662efde6e02921c2d16e06418483b7534
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
