Wrong results with references to JOIN USING aliases
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
Richard Guo <guofenglinux@gmail.com> writes:
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 */
Ugh :-(. I think that's my comment; should have taken the issue more
seriously.
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.
I've not thought hard about this fix, but assuming it's OK, it seems
to me that that objection's not a dealbreaker. It would only matter
to people who actually got bit by this bug, which must be a pretty
small number or we'd have heard about it before now. And they'd
have had to hit it with a wholerow ref that's inside the view,
not in a dynamic query, which makes it even less likely that there
are many people who'd need to rebuild their views.
BTW, I have a different issue with this query: it seems like "x"
is just ROW(a), not ROW(a, t2.b, t3.b). It's late and I'm frazzled,
but isn't that wrong?
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> 于2026年11月7日周六 10:51写道:
BTW, I have a different issue with this query: it seems like "x"
is just ROW(a), not ROW(a, t2.b, t3.b). It's late and I'm frazzled,
but isn't that wrong?
I think ROW(a) is intended here: in “JOIN ... USING (a) AS x”, x
aliases only the USING columns, rather than the whole join result.
To make x refer to ROW(a, t2.b, t3.b), the alias would go outside the
parentheses:
“(t t2 JOIN t t3 USING (a)) AS x”
--
Thanks,
Tender Wang
Hi,
Richard Guo <guofenglinux@gmail.com> 于2026年11月7日周六 10:21写道:
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.
Using expandNSItemVars() looks right to me: it adds the required
nullingrels and expands only the USING alias's columns, so the
truncation is no longer needed.
The patch looks good to me.
--
Thanks,
Tender Wang
On Sat, Nov 7, 2026 at 11:51 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
BTW, I have a different issue with this query: it seems like "x"
is just ROW(a), not ROW(a, t2.b, t3.b). It's late and I'm frazzled,
but isn't that wrong?
As Tender said, I think this is intended. The SELECT doc says:
If a join_using_alias name is specified, it provides a table alias for
the join columns. Only the join columns listed in the USING clause are
addressable by this name.
So x.* gives just (a).
- Richard