Re: New CORRESPONDING clause design

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

Regards

Pavel

>
> regards, tom lane
>