Fix bit-rotted planner test case.

tglsfdc · tglsfdc · commit 7122f9d54377 · 2022-12-17T18:51:24.000-05:00
While fooling with my pet outer-join-variables patch, I discovered that the test case I added in commit 11086f2 no longer demonstrates what it's supposed to. The idea is to tempt the planner to reverse the order of the two outer joins, which would leave noplace to correctly evaluate the WHERE clause that's inserted between them. Before the addition of the delay_upper_joins mechanism, it would have taken the bait. However, subsequent improvements broke the test in two different ways. First, we now recognize the IS NULL coding pattern as an antijoin, and we won't re-order antijoins; even if we did, the IS NULL test clauses get removed so there would be no opportunity for them to misbehave. Second, the planner now discovers that nested parameterized indexscans are a lot cheaper than the double hash join it used back in the day, and that approach doesn't want to re-order the joins anyway. Thus, in HEAD the test passes even if one dikes out delay_upper_joins. To fix, change the IS NULL tests to COALESCE clauses, which produce the same results but the planner isn't smart enough to convert them to antijoins. It'll still go for parameterized indexscans though, so drop the index enabling that (don't know why I added that in the first place), and disable nestloop joining just to be sure. This time around, add an EXPLAIN to make the choice of plan visible.
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
@@ -2630,26 +2630,55 @@ 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
     1
  9999
 (3 rows)
 
+reset enable_nestloop;
 --
 -- basic semijoin and antijoin recognition tests
 --
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
@@ -608,21 +608,36 @@ 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;
+
 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;
+
+reset enable_nestloop;
 
 --
 -- basic semijoin and antijoin recognition tests
