The output of a query with UNION ALL seems to produce results that are similar to UNION, which is incorrect. To be honest I am surprised that the query even ran in the first place.
terrier=# CREATE TABLE xxx (id INT PRIMARY KEY, val INT);
CREATE TABLE
terrier=# INSERT INTO xxx VALUES (1,1),(2,2),(3,3),(4,4);
INSERT 0 4
terrier=# CREATE TABLE yyy (id INT PRIMARY KEY, val INT);
CREATE TABLE
terrier=# INSERT INTO yyy VALUES (1,1),(2,2),(3,3),(4,4);
INSERT 0 4
Our system:
terrier=# (SELECT * FROM xxx) UNION ALL (SELECT * FROM yyy);
id | val
----+-----
1 | 1
2 | 2
3 | 3
4 | 4
(4 rows)
terrier=# (SELECT * FROM xxx) UNION (SELECT * FROM yyy);
id | val
----+-----
1 | 1
2 | 2
3 | 3
4 | 4
(4 rows)
Postgres:
pavlo=# (SELECT * FROM xxx) UNION ALL (SELECT * FROM yyy);
id | val
----+-----
1 | 1
2 | 2
3 | 3
4 | 4
1 | 1
2 | 2
3 | 3
4 | 4
(8 rows)
pavlo=# (SELECT * FROM xxx) UNION (SELECT * FROM yyy);
id | val
----+-----
2 | 2
3 | 3
4 | 4
1 | 1
(4 rows)
lmwnshn
commented
3 years ago
The output of a query with
UNION ALLseems to produce results that are similar toUNION, which is incorrect. To be honest I am surprised that the query even ran in the first place.Our system:
Postgres: