diff options
| -rw-r--r-- | src/test/regress/expected/join.out | 63 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 25 |
2 files changed, 88 insertions, 0 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 9b69a8c122..b901d7299f 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2651,6 +2651,69 @@ WHERE d.f1 IS NULL; (3 rows) -- +-- basic semijoin and antijoin recognition tests +-- +explain (costs off) +select a.* from tenk1 a +where unique1 in (select unique2 from tenk1 b); + QUERY PLAN +------------------------------------------------------------ + Hash Semi Join + Hash Cond: (a.unique1 = b.unique2) + -> Seq Scan on tenk1 a + -> Hash + -> Index Only Scan using tenk1_unique2 on tenk1 b +(5 rows) + +-- sadly, this is not an antijoin +explain (costs off) +select a.* from tenk1 a +where unique1 not in (select unique2 from tenk1 b); + QUERY PLAN +-------------------------------------------------------- + Seq Scan on tenk1 a + Filter: (NOT (hashed SubPlan 1)) + SubPlan 1 + -> Index Only Scan using tenk1_unique2 on tenk1 b +(4 rows) + +explain (costs off) +select a.* from tenk1 a +where exists (select 1 from tenk1 b where a.unique1 = b.unique2); + QUERY PLAN +------------------------------------------------------------ + Hash Semi Join + Hash Cond: (a.unique1 = b.unique2) + -> Seq Scan on tenk1 a + -> Hash + -> Index Only Scan using tenk1_unique2 on tenk1 b +(5 rows) + +explain (costs off) +select a.* from tenk1 a +where not exists (select 1 from tenk1 b where a.unique1 = b.unique2); + QUERY PLAN +------------------------------------------------------------ + Hash Anti Join + Hash Cond: (a.unique1 = b.unique2) + -> Seq Scan on tenk1 a + -> Hash + -> Index Only Scan using tenk1_unique2 on tenk1 b +(5 rows) + +explain (costs off) +select a.* from tenk1 a left join tenk1 b on a.unique1 = b.unique2 +where b.unique2 is null; + QUERY PLAN +------------------------------------------------------------ + Hash Anti Join + Hash Cond: (a.unique1 = b.unique2) + -> Seq Scan on tenk1 a + -> Hash + -> Index Only Scan using tenk1_unique2 on tenk1 b +(5 rows) + +-- -- regression test for proper handling of outer joins within antijoins -- create temp table tt4x(c1 int, c2 int, c3 int); diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 27e7e741a1..ccbbe5454c 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -625,6 +625,31 @@ LEFT JOIN ( WHERE d.f1 IS NULL; -- +-- basic semijoin and antijoin recognition tests +-- + +explain (costs off) +select a.* from tenk1 a +where unique1 in (select unique2 from tenk1 b); + +-- sadly, this is not an antijoin +explain (costs off) +select a.* from tenk1 a +where unique1 not in (select unique2 from tenk1 b); + +explain (costs off) +select a.* from tenk1 a +where exists (select 1 from tenk1 b where a.unique1 = b.unique2); + +explain (costs off) +select a.* from tenk1 a +where not exists (select 1 from tenk1 b where a.unique1 = b.unique2); + +explain (costs off) +select a.* from tenk1 a left join tenk1 b on a.unique1 = b.unique2 +where b.unique2 is null; + +-- -- regression test for proper handling of outer joins within antijoins -- |