From: | PG Doc comments form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-docs(at)lists(dot)postgresql(dot)org |
Cc: | cpainterwakefield(at)gmail(dot)com |
Subject: | clarification on chaining of set operations |
Date: | 2021-10-04 23:08:37 |
Message-ID: | 163338891727.12510.3939775743980651160@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/12/queries-union.html
Description:
Apologies if this has been raised previously - I searched the archives and
did not find anything.
Regarding section 7.4 of the documentation. I was curious about the
behavior when combining different set operations in one query, e.g,
query1 op1 query2 op2 query3;
where op1 and op2 are one of UNION, INTERSECT, EXCEPT.
The documentation suggests that this is equivalent to
(query1 op1 query2) op2 query3;
but only states it for the case when op1 = op2 = UNION. (Which really
doesn't matter anyway given that set union is associative.)
In fact, mixing INTERSECT and UNION does not exhibit the expected behavior
(at least in version 12 - I have not tested in later versions). Here's my
counterexample:
CREATE TABLE test1 (x INTEGER);
CREATE TABLE test2 (x INTEGER);
CREATE TABLE test3 (x INTEGER);
INSERT INTO test1 VALUES (1), (2);
INSERT INTO test2 VALUES (2), (3);
INSERT INTO test3 VALUES (3), (4);
SELECT * FROM test1
UNION
SELECT * FROM test2
INTERSECT
SELECT * FROM test3;
(SELECT * FROM test1
UNION
SELECT * FROM test2)
INTERSECT
SELECT * FROM test3;
The observed behavior suggests that INTERSECT is applied before UNION. I
haven't tested to figure out other interactions (e.g., with EXCEPT or the
ALL variations).
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-10-04 23:40:38 | Re: clarification on chaining of set operations |
Previous Message | Bruce Momjian | 2021-10-04 21:11:29 | Re: [PATCH] fix ICU explorer link in locale documentation |