Re: Inconsistent RestrictInfo serial numbers - Mailing list pgsql-hackers

On Tue, Oct 8, 2024 at 4:50 PM Richard Guo <guofenglinux@gmail.com> wrote:
>
> I ran into an "ERROR:  variable not found in subplan target lists"
> error, which can be reproduced with the following query.
>
> create table t (a int primary key, b int);
> insert into t select i, i from generate_series(1, 10)i;
> analyze t;
>
> explain (costs off)
> select 1 from t t1
>     left join
>         (t t2 left join t t3 on t2.a = t3.a) on true
>     left join t t4 on t1.a is null and t1.b = 1
>     right join t t5 on t1.b = t5.b;
> ERROR:  variable not found in subplan target lists
>
> The first bad commit is a3179ab69.
>
> commit a3179ab692be4314d5ee5cd56598976c487d5ef2
> Author: Tom Lane <tgl@sss.pgh.pa.us>
> Date:   Fri Sep 27 16:04:04 2024 -0400
>
>     Recalculate where-needed data accurately after a join removal.
>
> However, after digging into it further, it seems to me that the issue
> originated in b262ad440, and the changes in a3179ab69 made it easier
> to surface.
>
> commit b262ad440edecda0b1aba81d967ab560a83acb8a
> Author: David Rowley <drowley@postgresql.org>
> Date:   Tue Jan 23 18:09:18 2024 +1300
>
>     Add better handling of redundant IS [NOT] NULL quals
>
> When we generate multiple clones of the same qual condition to cope
> with outer join identity 3, we need to ensure that all the clones get
> the same serial number.  To achieve this, we reset the
> root->last_rinfo_serial counter each time we produce RestrictInfo(s)
> from the qual (see deconstruct_distribute_oj_quals).  This approach
> works only if we ensure that we are not changing the qual list in
> any way that'd affect the number of RestrictInfos built from it.
>
> However, with b262ad440, an IS NULL qual on a NOT NULL column might
> result in an additional constant-FALSE RestrictInfo.  This can
> unexpectedly increase root->last_rinfo_serial, causing inconsistent
> RestrictInfo serial numbers across multiple clones of the same qual,
> which can confuse users of 'rinfo_serial', such as
> rebuild_joinclause_attr_needed, and lead to planner errors.
>
> In the query above, the has_clone version of qual 't1.a is null' would
> be reduced to constant-FALSE, while the is_clone version would not.
> This results in differing serial numbers for subsequent quals (such as
> qual 't1.b = 1') across different versions.
>
> To fix, I think we can reset the root->last_rinfo_serial counter after
> generating the additional constant-FALSE RestrictInfo.  Please see
> attached.

A naive question: Why don't we just reuse the same RestrictInfo
instead of creating a new one. I understand that the original
RestrictInfo was passed from somewhere else to add it to a given
relation. But I don't see any relation specific information being
considered while deciding whether the clause is constant false. So may
be we should do this processing elsewhere and replace the original
clause itself?

--
Best Wishes,
Ashutosh Bapat