Re: New CORRESPONDING clause design

Hi

2017-03-30 21:55 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2017-03-30 21:43 GMT+02:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> > Is following use case defined in standard?
>>
>> > postgres=# SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3
>> > UNION ALL CORRESPONDING BY(a,b) SELECT 4 AS b, 0 AS x4, 3 AS
>> a,
>> > 0 AS x6, -1 AS x6
>> > UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS a, -100 AS aa;
>> > ┌───┐
>> > │ a │
>> > ╞═══╡
>> > │ 1 │
>> > │ 3 │
>> > │ 6 │
>> > └───┘
>> > (3 rows)
>>
>> > It depends on order of implementation
>>
>> > if we do (T1 U T2) U T3 ---> then result is correct,
>> > but if we do T1 U (T2 U T3) ---> than it should to fail
>>
>> UNION ALL should associate left-to-right, just like most other binary
>> operators, so this looks fine to me. Did you check that you get an
>> error if you put in parens to force the other order?
>>
>
> yes - it fails
>
> postgres=# SELECT 0 AS x1, 1 AS a, 0 AS x2, 2 AS b, 0 AS x3, -1 AS x3
> UNION ALL CORRESPONDING BY(a,b) (SELECT 4 AS b, 0 AS x4, 3 AS a, 0 AS x6,
> -1 AS x6 UNION ALL CORRESPONDING SELECT 0 AS x8, 6 AS a, -100 AS aa);
> ERROR: column name "b" can not be used in CORRESPONDING BY list
> LINE 1: ...b, 0 AS x3, -1 AS x3 UNION ALL CORRESPONDING BY(a,b) (SELECT...
> ^
> HINT: UNION queries with a CORRESPONDING BY clause must contain column
> names from both tables.
> Time: 1,135 ms
>
>
I fixed wrong my comment

I have no any other objections, I'll mark this patch as ready for commiter

Regards

Pavel

> Regards
>
> Pavel
>
>>
>> regards, tom lane
>>
>
>