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 <t...@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 <drow...@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. Thanks Richard
v1-0001-Fix-inconsistent-RestrictInfo-serial-numbers.patch
Description: Binary data