Re: Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware)

 

A quick hack that comes to my mind is that for a pushed down clause we
check all outer join relids it mentions and add the outer joins'
commute_below to the clause's required_relids, so that after we've
commuted the outer joins, the clause would still be placed in the right
place.

--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2349,12 +2349,27 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
    }
    else
    {
+       ListCell   *l;
+
        /*
         * Normal qual clause or degenerate outer-join clause.  Either way, we
         * can mark it as pushed-down.
         */
        is_pushed_down = true;

+       /*
+        * Add in commute_below of outer joins mentioned within the clause, so
+        * that after we've commuted the outer joins, the clause would still be
+        * placed correctly.
+        */
+       foreach(l, root->join_info_list)
+       {
+           SpecialJoinInfo *sji = (SpecialJoinInfo *) lfirst(l);
+
+           if (bms_is_member(sji->ojrelid, relids))
+               relids = bms_add_members(relids, sji->commute_below);
+       }
+

For a formal fix, I wonder if we need to generate multiple versions of
such a clause and apply the appropriate one depending on which join
order is chosen, just like what we do for left join quals in
deconstruct_distribute_oj_quals.

Thanks
Richard