Inconsistent RestrictInfo serial numbers

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Inconsistent RestrictInfo serial numbers
Date: 2024-10-08 11:20:30
Message-ID: CAMbWs4-B6kafn+LmPuh-TYFwFyEm-vVj3Qqv7Yo-69CEv14rRg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)sss(dot)pgh(dot)pa(dot)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(at)postgresql(dot)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.

Thanks
Richard

Attachment Content-Type Size
v1-0001-Fix-inconsistent-RestrictInfo-serial-numbers.patch application/octet-stream 6.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 2024-10-08 11:31:07 Re: Doc: typo in config.sgml
Previous Message Yugo NAGATA 2024-10-08 10:51:23 Re: First draft of PG 17 release notes