diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/join.out | 132 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 72 |
2 files changed, 204 insertions, 0 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index cfe7ded08d9..78b0d89685d 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -1869,6 +1869,138 @@ SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name); (4 rows) -- +-- Test interactions of join syntax and subqueries +-- +-- Basic cases (we expect planner to pull up the subquery here) +SELECT * FROM +(SELECT * FROM t2) as s2 +INNER JOIN +(SELECT * FROM t3) s3 +USING (name); + name | n | n +------+----+---- + aa | 12 | 13 + bb | 22 | 23 +(2 rows) + +SELECT * FROM +(SELECT * FROM t2) as s2 +LEFT JOIN +(SELECT * FROM t3) s3 +USING (name); + name | n | n +------+----+---- + aa | 12 | 13 + bb | 22 | 23 + dd | 42 | +(3 rows) + +SELECT * FROM +(SELECT * FROM t2) as s2 +FULL JOIN +(SELECT * FROM t3) s3 +USING (name); + name | n | n +------+----+---- + aa | 12 | 13 + bb | 22 | 23 + cc | | 33 + dd | 42 | +(4 rows) + +-- Cases with non-nullable expressions in subquery results; +-- make sure these go to null as expected +SELECT * FROM +(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 +NATURAL INNER JOIN +(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; + name | s2_n | s2_2 | s3_n | s3_2 +------+------+------+------+------ + aa | 12 | 2 | 13 | 3 + bb | 22 | 2 | 23 | 3 +(2 rows) + +SELECT * FROM +(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 +NATURAL LEFT JOIN +(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; + name | s2_n | s2_2 | s3_n | s3_2 +------+------+------+------+------ + aa | 12 | 2 | 13 | 3 + bb | 22 | 2 | 23 | 3 + dd | 42 | 2 | | +(3 rows) + +SELECT * FROM +(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 +NATURAL FULL JOIN +(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; + name | s2_n | s2_2 | s3_n | s3_2 +------+------+------+------+------ + aa | 12 | 2 | 13 | 3 + bb | 22 | 2 | 23 | 3 + cc | | | 33 | 3 + dd | 42 | 2 | | +(4 rows) + +SELECT * FROM +(SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 +NATURAL INNER JOIN +(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 +NATURAL INNER JOIN +(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; + name | s1_n | s1_1 | s2_n | s2_2 | s3_n | s3_2 +------+------+------+------+------+------+------ + aa | 11 | 1 | 12 | 2 | 13 | 3 +(1 row) + +SELECT * FROM +(SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 +NATURAL FULL JOIN +(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 +NATURAL FULL JOIN +(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; + name | s1_n | s1_1 | s2_n | s2_2 | s3_n | s3_2 +------+------+------+------+------+------+------ + aa | 11 | 1 | 12 | 2 | 13 | 3 + bb | | | 22 | 2 | 23 | 3 + cc | | | | | 33 | 3 + dd | | | 42 | 2 | | +(4 rows) + +SELECT * FROM +(SELECT name, n as s1_n FROM t1) as s1 +NATURAL FULL JOIN + (SELECT * FROM + (SELECT name, n as s2_n FROM t2) as s2 + NATURAL FULL JOIN + (SELECT name, n as s3_n FROM t3) as s3 + ) ss2; + name | s1_n | s2_n | s3_n +------+------+------+------ + aa | 11 | 12 | 13 + bb | | 22 | 23 + cc | | | 33 + dd | | 42 | +(4 rows) + +SELECT * FROM +(SELECT name, n as s1_n FROM t1) as s1 +NATURAL FULL JOIN + (SELECT * FROM + (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 + NATURAL FULL JOIN + (SELECT name, n as s3_n FROM t3) as s3 + ) ss2; + name | s1_n | s2_n | s2_2 | s3_n +------+------+------+------+------ + aa | 11 | 12 | 2 | 13 + bb | | 22 | 2 | 23 + cc | | | | 33 + dd | | 42 | 2 | +(4 rows) + +-- -- Clean up -- DROP TABLE t1; diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 91e64adfc94..5baf7fd3cf6 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -217,6 +217,78 @@ INSERT INTO t3 VALUES ( 'cc', 33 ); SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name); -- +-- Test interactions of join syntax and subqueries +-- + +-- Basic cases (we expect planner to pull up the subquery here) +SELECT * FROM +(SELECT * FROM t2) as s2 +INNER JOIN +(SELECT * FROM t3) s3 +USING (name); + +SELECT * FROM +(SELECT * FROM t2) as s2 +LEFT JOIN +(SELECT * FROM t3) s3 +USING (name); + +SELECT * FROM +(SELECT * FROM t2) as s2 +FULL JOIN +(SELECT * FROM t3) s3 +USING (name); + +-- Cases with non-nullable expressions in subquery results; +-- make sure these go to null as expected +SELECT * FROM +(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 +NATURAL INNER JOIN +(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; + +SELECT * FROM +(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 +NATURAL LEFT JOIN +(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; + +SELECT * FROM +(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 +NATURAL FULL JOIN +(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; + +SELECT * FROM +(SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 +NATURAL INNER JOIN +(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 +NATURAL INNER JOIN +(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; + +SELECT * FROM +(SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 +NATURAL FULL JOIN +(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 +NATURAL FULL JOIN +(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; + +SELECT * FROM +(SELECT name, n as s1_n FROM t1) as s1 +NATURAL FULL JOIN + (SELECT * FROM + (SELECT name, n as s2_n FROM t2) as s2 + NATURAL FULL JOIN + (SELECT name, n as s3_n FROM t3) as s3 + ) ss2; + +SELECT * FROM +(SELECT name, n as s1_n FROM t1) as s1 +NATURAL FULL JOIN + (SELECT * FROM + (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 + NATURAL FULL JOIN + (SELECT name, n as s3_n FROM t3) as s3 + ) ss2; + +-- -- Clean up -- |