Re: Wrong results with references to JOIN USING aliases

Richard Guo <guofenglinux(at)gmail(dot)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