[BUG] Remove self joins causes 'variable not found in subplan target lists' error

From: Sergey Soloviev <sergey(dot)soloviev(at)tantorlabs(dot)ru>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: [BUG] Remove self joins causes 'variable not found in subplan target lists' error
Date: 2025-08-22 15:27:16
Message-ID: 1fd1a421-4609-4d46-a1af-ab74d5de504a@tantorlabs.ru
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi, hackers!

We have encountered a bug in the planner which raises an error 'variable
not found in target lists'.

To reproduce apply patch '0001-Disable-JOIN_SEMI-and-JOIN_RIGHT_SEMI-paths.patch'
and run this query:

```
create table t1(i int, j int);
create table t2(i int, j int);
create table t3(i int, j int);
create table t4(i int, j int);
create table t5(i int, j int);
create unique index on t2(i, j);

select t1.j from t1
    left join t2 on t1.i = t2.i and t2.j = 1
    left join (
        select i from t3
        where exists (
            select true from t4
                left join t5 as t5 on t5.j = t4.j
                left join t5 as t6 on t6.j = t4.j
            where t4.i = t3.i
                and t4.i =2
            )
        ) t on t1.j = t.i;
```

This bug was caused by 'rebuild_eclass_attr_needed' function which
did not process EC with constants. The logic behind it is clear - we
can substitute these attributes with constants and do not use them
later, but it is not true when `EXISTS` converts to JOIN. If planner
decided to use Unique + Sort, then the following happens:

1. `t4.i`, `t3.i` and `2` are contained in same equivalence class, so
    'ec_has_const' is 'true'
2. During 'rebuild_eclass_attr_needed' their EC is skipped because it
    contains constant (src/backend/optimizer/path/equivclass.c:2590)

```
if (list_length(ec->ec_members) > 1 && !ec->ec_has_const)
```

3. 'attr_needed' of 't4.i' is still NULL, so it is not added to 'pathtarget'
     of 'RelOptInfo' (src/backend/optimizer/util/relnode.c:1199)

```
if (!bms_nonempty_difference(baserel->attr_needed[ndx], relids))
    continue;        /* nope, skip it */
```

4. 'UniquePath' is created, but `t4.i` (which must be unique expression)
     is not in 'pathtarget' of RelOptInfo, so added to path's targetlist
     manually (src/backend/optimizer/plan/planner.c:8395)

```
tle = tlist_member(uniqexpr, newtlist);
if (!tle)
{
    tle = makeTargetEntry((Expr *) uniqexpr,
                                                nextresno,
                                                NULL,
                                                false);
    newtlist = lappend(newtlist, tle);
}
```

5. When creating a Plan targetlist is just copied from Path
6. At the very end 'set_plan_references' can not find expression at
    level below (because it was "not needed" and was not added to
    targetlist), so it throws 'variable not found in subplan target lists'

This patch fixes this error by taking considering multi-member EC
even with constants, but skipping constant members during members
iteration.

There are 4 attachments:

- 'schema.sql' - schema for reproducing the bug
- 'query.sql' - actual query to raise error
- '0001-Disable-JOIN_SEMI-and-JOIN_RIGHT_SEMI-paths.patch' - disable
   JOIN_SEMI to be able to reproduce the bug
- '0001-fix-variable-not-found-in-subplan-target-lists.patch' - patch
   with actual fix of this bug

I would like write a test in 'join.sql', but for now it requires patches
to easily reproduce the bug. I appreciate it if someone could find
an easier way to reproduce the bug and write a simple test.

---
Regards,
Sergey Soloviev
Tantor Labs LLC

Attachment Content-Type Size
schema.sql application/sql 188 bytes
query.sql application/sql 401 bytes
0001-Disable-JOIN_SEMI-and-JOIN_RIGHT_SEMI-paths.patch text/x-patch 2.1 KB
0001-fix-variable-not-found-in-subplan-target-lists.patch text/x-patch 2.1 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Álvaro Herrera 2025-08-22 15:34:06 Re: misleading error message in ProcessUtilitySlow T_CreateStatsStmt
Previous Message Euler Taveira 2025-08-22 15:26:29 Re: Add support for specifying tables in pg_createsubscriber.