--
-- JOIN
--- Test join clauses
+-- Test JOIN clauses
--
-CREATE TABLE JOIN1_TBL (
+CREATE TABLE J1_TBL (
i integer,
j integer,
t text
);
-CREATE TABLE JOIN2_TBL (
+CREATE TABLE J2_TBL (
i integer,
k integer
);
-CREATE TABLE JOIN3_TBL (
- i integer,
- j integer,
- y integer
-);
-CREATE TABLE JOIN4_TBL (
- k integer,
- z integer
-);
+INSERT INTO J1_TBL VALUES (1, 4, 'one');
+INSERT INTO J1_TBL VALUES (2, 3, 'two');
+INSERT INTO J1_TBL VALUES (3, 2, 'three');
+INSERT INTO J1_TBL VALUES (4, 1, 'four');
+INSERT INTO J1_TBL VALUES (5, 0, 'five');
+INSERT INTO J1_TBL VALUES (6, 6, 'six');
+INSERT INTO J1_TBL VALUES (7, 7, 'seven');
+INSERT INTO J1_TBL VALUES (8, 8, 'eight');
+INSERT INTO J1_TBL VALUES (0, NULL, 'zero');
+INSERT INTO J1_TBL VALUES (NULL, NULL, 'null');
+INSERT INTO J1_TBL VALUES (NULL, 0, 'zero');
+
+INSERT INTO J2_TBL VALUES (1, -1);
+INSERT INTO J2_TBL VALUES (2, 2);
+INSERT INTO J2_TBL VALUES (3, -3);
+INSERT INTO J2_TBL VALUES (2, 4);
+INSERT INTO J2_TBL VALUES (5, -5);
+INSERT INTO J2_TBL VALUES (5, -5);
+INSERT INTO J2_TBL VALUES (0, NULL);
+INSERT INTO J2_TBL VALUES (NULL, NULL);
+INSERT INTO J2_TBL VALUES (NULL, 0);
+
+--
+-- CORRELATION NAMES
+-- Make sure that table/column aliases are supported
+-- before diving into more complex join syntax.
+--
+
+SELECT '' AS "xxx", *
+ FROM J1_TBL AS tx;
-INSERT INTO JOIN1_TBL VALUES (1, 3, 'one');
-INSERT INTO JOIN1_TBL VALUES (2, 2, 'two');
-INSERT INTO JOIN1_TBL VALUES (3, 1, 'three');
-INSERT INTO JOIN1_TBL VALUES (4, 0, 'four');
+SELECT '' AS "xxx", *
+ FROM J1_TBL tx;
-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);
+SELECT '' AS "xxx", *
+ FROM J1_TBL AS t1 (a, b, c);
+
+SELECT '' AS "xxx", *
+ FROM J1_TBL t1 (a, b, c);
+
+SELECT '' AS "xxx", *
+ FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e);
+
+SELECT '' AS "xxx", t1.a, t2.e
+ FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e)
+ WHERE t1.a = t2.d;
--
--
SELECT '' AS "xxx", *
- FROM JOIN1_TBL CROSS JOIN JOIN2_TBL;
+ FROM J1_TBL CROSS JOIN J2_TBL;
+-- ambiguous column
SELECT '' AS "xxx", i, k, t
- FROM JOIN1_TBL CROSS JOIN JOIN2_TBL;
+ FROM J1_TBL CROSS JOIN J2_TBL;
+
+-- resolve previous ambiguity by specifying the table name
+SELECT '' AS "xxx", t1.i, k, t
+ FROM J1_TBL t1 CROSS JOIN J2_TBL t2;
SELECT '' AS "xxx", ii, tt, kk
- FROM JOIN1_TBL CROSS JOIN JOIN2_TBL AS JT (ii, jj, tt, ii2, kk);
+ FROM (J1_TBL CROSS JOIN J2_TBL)
+ AS tx (ii, jj, tt, ii2, kk);
+
+SELECT '' AS "xxx", tx.ii, tx.jj, tx.kk
+ FROM (J1_TBL t1 (a, b, c) CROSS JOIN J2_TBL t2 (d, e))
+ AS tx (ii, jj, tt, ii2, kk);
-SELECT '' AS "xxx", jt.ii, jt.jj, jt.kk
- FROM JOIN1_TBL CROSS JOIN JOIN2_TBL AS JT (ii, jj, tt, ii2, kk);
+SELECT '' AS "xxx", *
+ FROM J1_TBL CROSS JOIN J2_TBL a CROSS JOIN J2_TBL b;
--
-- by including a column in the USING clause only once in the result.
--
--- Inner equi-join on all columns with the same name
-SELECT '' AS "xxx", *
- FROM JOIN1_TBL NATURAL JOIN JOIN2_TBL;
-
-- Inner equi-join on specified column
SELECT '' AS "xxx", *
- FROM JOIN1_TBL INNER JOIN JOIN2_TBL USING (i);
+ FROM J1_TBL INNER JOIN J2_TBL USING (i);
-- Same as above, slightly different syntax
SELECT '' AS "xxx", *
- FROM JOIN1_TBL JOIN JOIN2_TBL USING (i);
+ FROM J1_TBL JOIN J2_TBL USING (i);
+
+SELECT '' AS "xxx", *
+ FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a)
+ ORDER BY a, d;
+
+SELECT '' AS "xxx", *
+ FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b)
+ ORDER BY b, t1.a;
--
--- Inner joins (equi-joins)
+-- NATURAL JOIN
+-- Inner equi-join on all columns with the same name
--
SELECT '' AS "xxx", *
- FROM JOIN1_TBL JOIN JOIN2_TBL ON (JOIN1_TBL.i = JOIN2_TBL.i);
+ FROM J1_TBL NATURAL JOIN J2_TBL;
+
+SELECT '' AS "xxx", *
+ FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d);
+
+SELECT '' AS "xxx", *
+ FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
+
+-- mismatch number of columns
+-- currently, Postgres will fill in with underlying names
+SELECT '' AS "xxx", *
+ FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a);
+
+
+--
+-- Inner joins (equi-joins)
+--
SELECT '' AS "xxx", *
- FROM JOIN1_TBL JOIN JOIN2_TBL ON (JOIN1_TBL.i = JOIN2_TBL.k);
+ FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i);
SELECT '' AS "xxx", *
- FROM JOIN1_TBL CROSS JOIN JOIN2_TBL;
+ FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k);
--
--
SELECT '' AS "xxx", *
- FROM JOIN1_TBL JOIN JOIN2_TBL ON (JOIN1_TBL.i <= JOIN2_TBL.k);
+ FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i <= J2_TBL.k);
--
-- Outer joins
+-- Note that OUTER is a noise word
--
SELECT '' AS "xxx", *
- FROM JOIN1_TBL OUTER JOIN JOIN2_TBL USING (i);
+ FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i)
+ ORDER BY i, k, t;
+
+SELECT '' AS "xxx", *
+ FROM J1_TBL LEFT JOIN J2_TBL USING (i)
+ ORDER BY i, k, t;
+
+SELECT '' AS "xxx", *
+ FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);
+
+SELECT '' AS "xxx", *
+ FROM J1_TBL RIGHT JOIN J2_TBL USING (i);
SELECT '' AS "xxx", *
- FROM JOIN1_TBL LEFT OUTER JOIN JOIN2_TBL USING (i);
+ FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i)
+ ORDER BY i, k, t;
SELECT '' AS "xxx", *
- FROM JOIN1_TBL RIGHT OUTER JOIN JOIN2_TBL USING (i);
+ FROM J1_TBL FULL JOIN J2_TBL USING (i)
+ ORDER BY i, k, t;
SELECT '' AS "xxx", *
- FROM JOIN1_TBL FULL OUTER JOIN JOIN2_TBL USING (i);
+ FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1);
+
+SELECT '' AS "xxx", *
+ FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1);
--
-- More complicated constructs
--
+--
+-- Multiway full join
+--
+
+CREATE TABLE t1 (name TEXT, n INTEGER);
+CREATE TABLE t2 (name TEXT, n INTEGER);
+CREATE TABLE t3 (name TEXT, n INTEGER);
+
+INSERT INTO t1 VALUES ( 'aa', 11 );
+INSERT INTO t2 VALUES ( 'aa', 12 );
+INSERT INTO t2 VALUES ( 'bb', 22 );
+INSERT INTO t2 VALUES ( 'dd', 42 );
+INSERT INTO t3 VALUES ( 'aa', 13 );
+INSERT INTO t3 VALUES ( 'bb', 23 );
+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;
+
+
+-- Test for propagation of nullability constraints into sub-joins
+
+create temp table x (x1 int, x2 int);
+insert into x values (1,11);
+insert into x values (2,22);
+insert into x values (3,null);
+insert into x values (4,44);
+insert into x values (5,null);
+
+create temp table y (y1 int, y2 int);
+insert into y values (1,111);
+insert into y values (2,222);
+insert into y values (3,333);
+insert into y values (4,null);
+
+select * from x;
+select * from y;
+
+select * from x left join y on (x1 = y1 and x2 is not null);
+select * from x left join y on (x1 = y1 and y2 is not null);
+
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1);
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1 and x2 is not null);
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1 and y2 is not null);
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1 and xx2 is not null);
+-- these should NOT give the same answers as above
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1) where (x2 is not null);
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1) where (y2 is not null);
+select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
+on (x1 = xx1) where (xx2 is not null);
+
+--
+-- regression test: check for bug with propagation of implied equality
+-- to outside an IN
+--
+select count(*) from tenk1 a where unique1 in
+ (select unique1 from tenk1 b join tenk1 c using (unique1)
+ where b.unique2 = 42);
+
+--
+-- regression test: check for failure to generate a plan with multiple
+-- degenerate IN clauses
+--
+select count(*) from tenk1 x where
+ x.unique1 in (select a.f1 from int4_tbl a,float8_tbl b where a.f1=b.f1) and
+ x.unique1 = 0 and
+ x.unique1 in (select aa.f1 from int4_tbl aa,float8_tbl bb where aa.f1=bb.f1);
+
+
--
-- Clean up
--
-DROP TABLE JOIN1_TBL;
-DROP TABLE JOIN2_TBL;
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+
+DROP TABLE J1_TBL;
+DROP TABLE J2_TBL;
+
+-- Both DELETE and UPDATE allow the specification of additional tables
+-- to "join" against to determine which rows should be modified.
+
+CREATE TEMP TABLE t1 (a int, b int);
+CREATE TEMP TABLE t2 (a int, b int);
+CREATE TEMP TABLE t3 (x int, y int);
+
+INSERT INTO t1 VALUES (5, 10);
+INSERT INTO t1 VALUES (15, 20);
+INSERT INTO t1 VALUES (100, 100);
+INSERT INTO t1 VALUES (200, 1000);
+INSERT INTO t2 VALUES (200, 2000);
+INSERT INTO t3 VALUES (5, 20);
+INSERT INTO t3 VALUES (6, 7);
+INSERT INTO t3 VALUES (7, 8);
+INSERT INTO t3 VALUES (500, 100);
+
+DELETE FROM t3 USING t1 table1 WHERE t3.x = table1.a;
+SELECT * FROM t3;
+DELETE FROM t3 USING t1 JOIN t2 USING (a) WHERE t3.x > t1.a;
+SELECT * FROM t3;
+DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y;
+SELECT * FROM t3;
+
+--
+-- regression test for 8.1 merge right join bug
+--
+
+CREATE TEMP TABLE tt1 ( tt1_id int4, joincol int4 );
+INSERT INTO tt1 VALUES (1, 11);
+INSERT INTO tt1 VALUES (2, NULL);
+
+CREATE TEMP TABLE tt2 ( tt2_id int4, joincol int4 );
+INSERT INTO tt2 VALUES (21, 11);
+INSERT INTO tt2 VALUES (22, 11);
+
+set enable_hashjoin to off;
+set enable_nestloop to off;
+
+-- these should give the same results
+
+select tt1.*, tt2.* from tt1 left join tt2 on tt1.joincol = tt2.joincol;
+select tt1.*, tt2.* from tt2 right join tt1 on tt1.joincol = tt2.joincol;
projects / postgresql / blobdiff