Wrong results with references to JOIN USING aliases

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Wrong results with references to JOIN USING aliases
Date: 2026-11-07 02:21:15
Message-ID: CAMbWs48Rsz-vtROV5=Hzsm7=Y1VoPsrRRzBtdhF+_V_UX161eA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Fuzzing with LLM found $subject in v16 to master branches.

create table t (a int, b int);
insert into t values (1, 1), (2, 2);

select * from t t1
left join (t t2 join t t3 using (a) as x) on t1.a = x.a + 1
where x is null;
a | b | a | b | b
---+---+---+---+---
1 | 1 | | |
2 | 2 | | |
(2 rows)

The second row should not be there. A similar query fails instead:

select * from t t1
left join (t t2 join t t3 using (a) as x) on true
left join t t4 on x is null;
ERROR: wrong varnullingrels (b) (expected (b 5)) for Var 2/1

The problem is in transformWholeRowRef(), which expands "x" into a
RowExpr over the join's merged columns. It builds the fields with
expandRTE(), so the Vars are not marked as nullable by the outer join
above, and the planner pushes "x is null" down below that join. There
is even an XXX comment there:

/* XXX we ought to mark the row as possibly nullable */

To fix, I think we can build the fields with expandNSItemVars()
instead. It yields the same Vars, but also marks them with
markNullableIfNeeded(), as scanNSItemForColumn() does for a plain
reference such as x.a.

The annoying thing is that stored views containing such a reference
would need to be recreated.

- Richard

Attachment Content-Type Size
v1-0001-Fix-nullingrels-of-whole-row-references-to-JOIN-U.patch application/octet-stream 4.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2026-11-07 02:29:07 Re: First draft of PG 19 release notes
Previous Message Bruce Momjian 2026-11-07 02:17:02 Re: First draft of PG 19 release notes