Re: wrong query result due to wang plan

On Thu, Feb 16, 2023 at 5:50 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:

> It seems we still need to check whether a variable-free qual comes from
> somewhere that is below the nullable side of an outer join before we
> decide that it can be evaluated at join domain level, just like we did
> before. So I wonder if we can add a 'below_outer_join' flag in
> JoinTreeItem, fill its value during deconstruct_recurse, and check it in
> distribute_qual_to_rels()
>

It occurs to me that we can leverage JoinDomain to tell if we are below
the nullable side of a higher-level outer join if the clause is not an
outer-join clause. If we are belew outer join, the current JoinDomain
is supposed to be a proper subset of top JoinDomain. Otherwise the
current JoinDomain must be equal to top JoinDomain. And that leads to a
fix as code changes below.

--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2269,8 +2269,16 @@ distribute_qual_to_rels(PlannerInfo *root, Node
*clause,
}
else
{
- /* eval at join domain level */
- relids = bms_copy(jtitem->jdomain->jd_relids);
+ JoinDomain *top_jdomain =
+ linitial_node(JoinDomain, root->join_domains);
+
+ /*
+ * eval at original syntactic level if we are below an outer join,
+ * otherwise eval at join domain level, which is actually the top
+ * of tree
+ */
+ relids = jtitem->jdomain == top_jdomain ?
+ bms_copy(jtitem->jdomain->jd_relids) :
bms_copy(qualscope);

Thanks
Richard