diff options
Diffstat (limited to 'src/test/regress/expected/join.out')
| -rw-r--r-- | src/test/regress/expected/join.out | 35 |
1 files changed, 32 insertions, 3 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index b8d43e4c14..3ddea3b683 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2630,19 +2630,47 @@ reset enable_memoize; -- create temp table tt3(f1 int, f2 text); insert into tt3 select x, repeat('xyzzy', 100) from generate_series(1,10000) x; -create index tt3i on tt3(f1); analyze tt3; create temp table tt4(f1 int); insert into tt4 values (0),(1),(9999); analyze tt4; +set enable_nestloop to off; +EXPLAIN (COSTS OFF) +SELECT a.f1 +FROM tt4 a +LEFT JOIN ( + SELECT b.f1 + FROM tt3 b LEFT JOIN tt3 c ON (b.f1 = c.f1) + WHERE COALESCE(c.f1, 0) = 0 +) AS d ON (a.f1 = d.f1) +WHERE COALESCE(d.f1, 0) = 0 +ORDER BY 1; + QUERY PLAN +----------------------------------------------- + Sort + Sort Key: a.f1 + -> Hash Right Join + Hash Cond: (b.f1 = a.f1) + Filter: (COALESCE(b.f1, 0) = 0) + -> Hash Left Join + Hash Cond: (b.f1 = c.f1) + Filter: (COALESCE(c.f1, 0) = 0) + -> Seq Scan on tt3 b + -> Hash + -> Seq Scan on tt3 c + -> Hash + -> Seq Scan on tt4 a +(13 rows) + SELECT a.f1 FROM tt4 a LEFT JOIN ( SELECT b.f1 FROM tt3 b LEFT JOIN tt3 c ON (b.f1 = c.f1) - WHERE c.f1 IS NULL + WHERE COALESCE(c.f1, 0) = 0 ) AS d ON (a.f1 = d.f1) -WHERE d.f1 IS NULL; +WHERE COALESCE(d.f1, 0) = 0 +ORDER BY 1; f1 ------ 0 @@ -2650,6 +2678,7 @@ WHERE d.f1 IS NULL; 9999 (3 rows) +reset enable_nestloop; -- -- basic semijoin and antijoin recognition tests -- |