2017-03-30 21:43 GMT+02:00 Tom Lane <t...@sss.pgh.pa.us>: > Pavel Stehule <pavel.steh...@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. Time: 1,135 ms Regards Pavel > > regards, tom lane >