From 4aa0e645e28ba5b8ba6aa02ae3d37bb3ea0a82ab Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Tue, 23 Feb 1999 07:27:13 +0000 Subject: [PATCH] First tests using JOIN syntax. --- src/test/regress/expected/join.out | 94 ++++++++++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 87 +++++++++++++++++++++++++++++++++++ src/test/regress/sql/tests | 1 + 3 files changed, 182 insertions(+) create mode 100644 src/test/regress/expected/join.out create mode 100644 src/test/regress/sql/join.sql diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out new file mode 100644 index 0000000000..17bf7c6a51 --- /dev/null +++ b/src/test/regress/expected/join.out @@ -0,0 +1,94 @@ +QUERY: CREATE TABLE JOIN_TBL ( + i integer, + j integer, + x text +); +QUERY: CREATE TABLE JOIN2_TBL ( + i integer, + k integer +); +QUERY: INSERT INTO JOIN_TBL VALUES (1, 3, 'one'); +QUERY: INSERT INTO JOIN_TBL VALUES (2, 2, 'two'); +QUERY: INSERT INTO JOIN_TBL VALUES (3, 1, 'three'); +QUERY: INSERT INTO JOIN_TBL VALUES (4, 0, 'four'); +QUERY: INSERT INTO JOIN2_TBL VALUES (1, -1); +QUERY: INSERT INTO JOIN2_TBL VALUES (2, 2); +QUERY: INSERT INTO JOIN2_TBL VALUES (3, -3); +QUERY: INSERT INTO JOIN2_TBL VALUES (2, 4); +QUERY: SELECT '' AS "xxx", * + FROM JOIN_TBL CROSS JOIN JOIN2_TBL; +xxx|i|j|x |i| k +---+-+-+-----+-+-- + |1|3|one |1|-1 + |2|2|two |1|-1 + |3|1|three|1|-1 + |4|0|four |1|-1 + |1|3|one |2| 2 + |2|2|two |2| 2 + |3|1|three|2| 2 + |4|0|four |2| 2 + |1|3|one |3|-3 + |2|2|two |3|-3 + |3|1|three|3|-3 + |4|0|four |3|-3 + |1|3|one |2| 4 + |2|2|two |2| 4 + |3|1|three|2| 4 + |4|0|four |2| 4 +(16 rows) + +QUERY: SELECT '' AS "xxx", * + FROM JOIN_TBL NATURAL JOIN JOIN2_TBL; +ERROR: JOIN expressions are not yet implemented +QUERY: SELECT '' AS "xxx", * + FROM JOIN_TBL INNER JOIN JOIN2_TBL USING (i); +ERROR: JOIN expressions are not yet implemented +QUERY: SELECT '' AS "xxx", * + FROM JOIN_TBL JOIN JOIN2_TBL ON (JOIN_TBL.i = JOIN2_TBL.i); +ERROR: JOIN expressions are not yet implemented +QUERY: SELECT '' AS "xxx", * + FROM JOIN_TBL JOIN JOIN2_TBL ON (JOIN_TBL.i = JOIN2_TBL.k); +ERROR: JOIN expressions are not yet implemented +QUERY: SELECT '' AS "xxx", * + FROM JOIN_TBL CROSS JOIN JOIN2_TBL; +xxx|i|j|x |i| k +---+-+-+-----+-+-- + |1|3|one |1|-1 + |2|2|two |1|-1 + |3|1|three|1|-1 + |4|0|four |1|-1 + |1|3|one |2| 2 + |2|2|two |2| 2 + |3|1|three|2| 2 + |4|0|four |2| 2 + |1|3|one |3|-3 + |2|2|two |3|-3 + |3|1|three|3|-3 + |4|0|four |3|-3 + |1|3|one |2| 4 + |2|2|two |2| 4 + |3|1|three|2| 4 + |4|0|four |2| 4 +(16 rows) + +QUERY: SELECT '' AS "xxx", * + FROM JOIN_TBL JOIN JOIN2_TBL ON (JOIN_TBL.i <= JOIN2_TBL.k); +ERROR: JOIN expressions are not yet implemented +QUERY: SELECT '' AS "xxx", * + FROM JOIN_TBL OUTER JOIN JOIN2_TBL USING (i); +NOTICE: OUTER JOIN not yet implemented +ERROR: JOIN expressions are not yet implemented +QUERY: SELECT '' AS "xxx", * + FROM JOIN_TBL LEFT OUTER JOIN JOIN2_TBL USING (i); +NOTICE: LEFT OUTER JOIN not yet implemented +ERROR: JOIN expressions are not yet implemented +QUERY: SELECT '' AS "xxx", * + FROM JOIN_TBL RIGHT OUTER JOIN JOIN2_TBL USING (i); +NOTICE: RIGHT OUTER JOIN not yet implemented +ERROR: JOIN expressions are not yet implemented +QUERY: SELECT '' AS "xxx", * + FROM JOIN_TBL FULL OUTER JOIN JOIN2_TBL USING (i); +NOTICE: FULL OUTER JOIN not yet implemented +ERROR: JOIN expressions are not yet implemented +QUERY: DROP TABLE JOIN_TBL; +QUERY: DROP TABLE JOIN2_TBL; diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql new file mode 100644 index 0000000000..dedff3a170 --- /dev/null +++ b/src/test/regress/sql/join.sql @@ -0,0 +1,87 @@ +-- +-- join.sql +-- +-- Test join clauses +-- + +CREATE TABLE JOIN_TBL ( + i integer, + j integer, + x text +); + +CREATE TABLE JOIN2_TBL ( + i integer, + k integer +); + +INSERT INTO JOIN_TBL VALUES (1, 3, 'one'); +INSERT INTO JOIN_TBL VALUES (2, 2, 'two'); +INSERT INTO JOIN_TBL VALUES (3, 1, 'three'); +INSERT INTO JOIN_TBL VALUES (4, 0, 'four'); + +INSERT INTO JOIN2_TBL VALUES (1, -1); +INSERT INTO JOIN2_TBL VALUES (2, 2); +INSERT INTO JOIN2_TBL VALUES (3, -3); +INSERT INTO JOIN2_TBL VALUES (2, 4); + + +-- +-- Inner joins (equi-joins) +-- + +SELECT '' AS "xxx", * + FROM JOIN_TBL CROSS JOIN JOIN2_TBL; + +SELECT '' AS "xxx", * + FROM JOIN_TBL NATURAL JOIN JOIN2_TBL; + +SELECT '' AS "xxx", * + FROM JOIN_TBL INNER JOIN JOIN2_TBL USING (i); + +SELECT '' AS "xxx", * + FROM JOIN_TBL JOIN JOIN2_TBL ON (JOIN_TBL.i = JOIN2_TBL.i); + +SELECT '' AS "xxx", * + FROM JOIN_TBL JOIN JOIN2_TBL ON (JOIN_TBL.i = JOIN2_TBL.k); + +SELECT '' AS "xxx", * + FROM JOIN_TBL CROSS JOIN JOIN2_TBL; + + +-- +-- Non-equi-joins +-- + +SELECT '' AS "xxx", * + FROM JOIN_TBL JOIN JOIN2_TBL ON (JOIN_TBL.i <= JOIN2_TBL.k); + + +-- +-- Outer joins +-- + +SELECT '' AS "xxx", * + FROM JOIN_TBL OUTER JOIN JOIN2_TBL USING (i); + +SELECT '' AS "xxx", * + FROM JOIN_TBL LEFT OUTER JOIN JOIN2_TBL USING (i); + +SELECT '' AS "xxx", * + FROM JOIN_TBL RIGHT OUTER JOIN JOIN2_TBL USING (i); + +SELECT '' AS "xxx", * + FROM JOIN_TBL FULL OUTER JOIN JOIN2_TBL USING (i); + + +-- +-- More complicated constructs +-- + +-- +-- Clean up +-- + +DROP TABLE JOIN_TBL; +DROP TABLE JOIN2_TBL; + diff --git a/src/test/regress/sql/tests b/src/test/regress/sql/tests index 44529af2b2..ab6aba8612 100644 --- a/src/test/regress/sql/tests +++ b/src/test/regress/sql/tests @@ -50,6 +50,7 @@ select_having subselect union case +join aggregates transactions random -- 2.11.0