RESET SESSION, plus related new DDL commands. Patch from Marko Kreen,

[postgresql] / src / test / regress / sql / join.sql

diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index e4ccfa45828340d09fa8f73acd356291d40791c7..71208d2d2d83822360030d32e33e1808b973de6d 100644 (file)
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -167,11 +167,11 @@ SELECT '' AS "xxx", *
 
 SELECT '' AS "xxx", *
   FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i)
-  ORDER BY i;
+  ORDER BY i, k, t;
 
 SELECT '' AS "xxx", *
   FROM J1_TBL LEFT JOIN J2_TBL USING (i)
-  ORDER BY i;
+  ORDER BY i, k, t;
 
 SELECT '' AS "xxx", *
   FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);
@@ -181,11 +181,11 @@ SELECT '' AS "xxx", *
 
 SELECT '' AS "xxx", *
   FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i)
-  ORDER BY i, t;
+  ORDER BY i, k, t;
 
 SELECT '' AS "xxx", *
   FROM J1_TBL FULL JOIN J2_TBL USING (i)
-  ORDER BY i, t;
+  ORDER BY i, k, t;
 
 SELECT '' AS "xxx", *
   FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1);
@@ -198,10 +198,6 @@ SELECT '' AS "xxx", *
 -- More complicated constructs
 --
 
--- UNION JOIN isn't implemented yet
-SELECT '' AS "xxx", *
-  FROM J1_TBL UNION JOIN J2_TBL;
-
 --
 -- Multiway full join
 --
@@ -330,6 +326,23 @@ 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
@@ -341,3 +354,48 @@ 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;