Re: Bug in planner - Mailing list pgsql-hackers

David Rowley <dgrowleyml@gmail.com> writes:
> On 24 April 2015 at 21:43, Teodor Sigaev <teodor@sigaev.ru> wrote:
>> I faced with planner error:
>> ERROR:  could not find RelOptInfo for given relids

> I've done a little debugging on this too and I get the idea that in
> eqjoinsel() that min_righthand incorrectly does not have a bit set for "t3"

Yeah.  The short of it seems to be that initsplan.c is too optimistic
about whether antijoins can be reordered against outer joins in their RHS.
The discussion in optimizer/README says pretty clearly that they can't
(and eqjoinsel is relying on that, per the comment therein), so I think
this is basically brain fade in translating that logic to code.

            regards, tom lane

diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index a7655e4..00b2625 100644
*** a/src/backend/optimizer/plan/initsplan.c
--- b/src/backend/optimizer/plan/initsplan.c
*************** make_outerjoininfo(PlannerInfo *root,
*** 1165,1173 ****
           * For a lower OJ in our RHS, if our join condition does not use the
           * lower join's RHS and the lower OJ's join condition is strict, we
           * can interchange the ordering of the two OJs; otherwise we must add
!          * lower OJ's full syntactic relset to min_righthand.  Here, we must
!          * preserve ordering anyway if either the current join is a semijoin,
!          * or the lower OJ is either a semijoin or an antijoin.
           *
           * Here, we have to consider that "our join condition" includes any
           * clauses that syntactically appeared above the lower OJ and below
--- 1165,1173 ----
           * For a lower OJ in our RHS, if our join condition does not use the
           * lower join's RHS and the lower OJ's join condition is strict, we
           * can interchange the ordering of the two OJs; otherwise we must add
!          * the lower OJ's full syntactic relset to min_righthand.  Also, we
!          * must preserve ordering anyway if either the current join or the
!          * lower OJ is either a semijoin or an antijoin.
           *
           * Here, we have to consider that "our join condition" includes any
           * clauses that syntactically appeared above the lower OJ and below
*************** make_outerjoininfo(PlannerInfo *root,
*** 1184,1189 ****
--- 1184,1190 ----
          {
              if (bms_overlap(clause_relids, otherinfo->syn_righthand) ||
                  jointype == JOIN_SEMI ||
+                 jointype == JOIN_ANTI ||
                  otherinfo->jointype == JOIN_SEMI ||
                  otherinfo->jointype == JOIN_ANTI ||
                  !otherinfo->lhs_strict || otherinfo->delay_upper_joins)
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index e4f3f22..ed9ad0e 100644
*** a/src/test/regress/expected/join.out
--- b/src/test/regress/expected/join.out
*************** WHERE d.f1 IS NULL;
*** 2284,2289 ****
--- 2284,2331 ----
  (3 rows)

  --
+ -- regression test for proper handling of outer joins within antijoins
+ --
+ create temp table tt4x(c1 int, c2 int, c3 int);
+ explain (costs off)
+ select * from tt4x t1
+ where not exists (
+   select 1 from tt4x t2
+     left join tt4x t3 on t2.c3 = t3.c1
+     left join ( select t5.c1 as c1
+                 from tt4x t4 left join tt4x t5 on t4.c2 = t5.c1
+               ) a1 on t3.c2 = a1.c1
+   where t1.c1 = t2.c2
+ );
+                        QUERY PLAN
+ ---------------------------------------------------------
+  Hash Anti Join
+    Hash Cond: (t1.c1 = t2.c2)
+    ->  Seq Scan on tt4x t1
+    ->  Hash
+          ->  Merge Right Join
+                Merge Cond: (t5.c1 = t3.c2)
+                ->  Merge Join
+                      Merge Cond: (t4.c2 = t5.c1)
+                      ->  Sort
+                            Sort Key: t4.c2
+                            ->  Seq Scan on tt4x t4
+                      ->  Sort
+                            Sort Key: t5.c1
+                            ->  Seq Scan on tt4x t5
+                ->  Sort
+                      Sort Key: t3.c2
+                      ->  Merge Left Join
+                            Merge Cond: (t2.c3 = t3.c1)
+                            ->  Sort
+                                  Sort Key: t2.c3
+                                  ->  Seq Scan on tt4x t2
+                            ->  Sort
+                                  Sort Key: t3.c1
+                                  ->  Seq Scan on tt4x t3
+ (24 rows)
+
+ --
  -- regression test for problems of the sort depicted in bug #3494
  --
  create temp table tt5(f1 int, f2 int);
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index d0cf0a0..5b65ea8 100644
*** a/src/test/regress/sql/join.sql
--- b/src/test/regress/sql/join.sql
*************** LEFT JOIN (
*** 448,453 ****
--- 448,470 ----
  WHERE d.f1 IS NULL;

  --
+ -- regression test for proper handling of outer joins within antijoins
+ --
+
+ create temp table tt4x(c1 int, c2 int, c3 int);
+
+ explain (costs off)
+ select * from tt4x t1
+ where not exists (
+   select 1 from tt4x t2
+     left join tt4x t3 on t2.c3 = t3.c1
+     left join ( select t5.c1 as c1
+                 from tt4x t4 left join tt4x t5 on t4.c2 = t5.c1
+               ) a1 on t3.c2 = a1.c1
+   where t1.c1 = t2.c2
+ );
+
+ --
  -- regression test for problems of the sort depicted in bug #3494
  --