Pavel Stehule <pavel.stehule@gmail.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.
