public inbox for [email protected]  

help / color / mirror / Atom feed

From: Richard Guo <[email protected]>
To: PostgreSQL-development <[email protected]>
Subject: Inconsistent nullingrels due to oversight in deconstruct_distribute_oj_quals
Date: Thu, 9 Feb 2023 17:16:15 +0800
Message-ID: <CAMbWs497CmBruMx1SOjepWEz+T5NWa4scqbdE9v7ZzSXqH_gQw@mail.gmail.com> (raw)

When we try to generate qual variants with different nullingrels in
deconstruct_distribute_oj_quals, we traverse all the JoinTreeItems and
adjust qual nulling bits as we crawl up the join tree.  For a
SpecialJoinInfo which commutes with current sjinfo from below left, in
the next level up it would null all the relids in its righthand.  So we
adjust qual nulling bits as below.

     /*
      * Adjust qual nulling bits for next level up, if needed.  We
      * don't want to put sjinfo's own bit in at all, and if we're
      * above sjinfo then we did it already.
      */
     if (below_sjinfo)
         quals = (List *)
             add_nulling_relids((Node *) quals,
                                othersj->min_righthand,
                                bms_make_singleton(othersj->ojrelid));

It seems to me there is oversight here.  Actually in next level up this
othersj would null all the relids in its syn_righthand, not only the
relids in its min_righthand.   If the quals happen to contain references
to relids which are in othersj->syn_righthand but not in
othersj->min_righthand, these relids would not get updated with
othersj->ojrelid added.  And this would cause qual nulling bits not
consistent.

I've managed to devise a query that can show this problem.

create table t1(a int, b int);
create table t2(a int, b int);
create table t3(a int, b int);
create table t4(a int, b int);

insert into t1 select i, i from generate_series(1,10)i;
insert into t2 select i, i from generate_series(1,10)i;
insert into t3 select i, i from generate_series(1,1000)i;
insert into t4 select i, i from generate_series(1,1000)i;
analyze;

select * from t1 left join (t2 left join t3 on t2.a > t3.a) on t1.b = t2.b
left join t4 on t2.b = t3.b;

This query would trigger the Assert() in search_indexed_tlist_for_var.
So I wonder that we should use othersj->syn_righthand here.

--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2046,7 +2046,7 @@ deconstruct_distribute_oj_quals(PlannerInfo *root,
        if (below_sjinfo)
            quals = (List *)
                add_nulling_relids((Node *) quals,
-                                  othersj->min_righthand,
+                                  othersj->syn_righthand,
                                   bms_make_singleton(othersj->ojrelid));

Thanks
Richard

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Inconsistent nullingrels due to oversight in deconstruct_distribute_oj_quals
  In-Reply-To: <CAMbWs497CmBruMx1SOjepWEz+T5NWa4scqbdE9v7ZzSXqH_gQw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox