Re: Adding CORRESPONDING to Set Operations

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(at)gmail(dot)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(at)gmail(dot)com> wrote:
>
>> On Sun, Sep 18, 2011 at 5:39 AM, Kerem Kat <keremkat(at)gmail(dot)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:
>>
>>
>> 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
>>
>
>