BUG #16958: "Invalid reference to FROM-clause entry for table" when qualifying columns in "on conflict .. where"

On Fri, Apr 9, 2021 at 11:00 PM PG Bug reporting form <
noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 16958
Logged by: Lukas Eder
Email address: lukas.eder@gmail.com
PostgreSQL version: 13.2
Operating system: Docker on Windows
Description:

I'm using Docker on Windows:

-------------------------------------------------------------------
select version();
-------------------------------------------------------------------
PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Consider the following SQL script:
-------------------------------------------------------------------
create table t (
a int,
b int,
c int
);

create unique index i on t(a)
where t.b is null;

insert into t (a, b)
values (
1,
1
)
on conflict (a)
where t.b is null -- Error here
do update
set
c = 1
returning t.a, t.b, t.c;

drop table t;
-------------------------------------------------------------------

There's an error reported:

SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for table
"t"
Hint: There is an entry for table "t", but it cannot be referenced from
this part of the query.
Position: 71

Notice the qualification of the t.b column in the "on conflict .. where"
clause. I don't understand why b cannot be qualified at this location. It
can be qualified in the index definition, looks like a bug to me.

You don't need and shouldn't prefix the column with the table name in that
part. This should work:

...
on conflict (a)
where b is null
do update
...

On 4/10/21 9:57 AM, Pantelis Theodosiou wrote:

On Fri, Apr 9, 2021 at 11:00 PM PG Bug reporting form <
noreply@postgresql.org> wrote:

insert into t (a, b)
values (
1,
1
)
on conflict (a)
where t.b is null -- Error here
do update
set
c = 1
returning t.a, t.b, t.c;

There's an error reported:

SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for table
"t"
Hint: There is an entry for table "t", but it cannot be referenced from
this part of the query.
Position: 71

Notice the qualification of the t.b column in the "on conflict .. where"
clause. I don't understand why b cannot be qualified at this location. It
can be qualified in the index definition, looks like a bug to me.

You don't need and shouldn't prefix the column with the table name in that
part. This should work:

...
on conflict (a)
where b is null
do update
...

Not needing to and not being able to are two completely different
things. I believe tablename qualification should be allowed here even
if it isn't necessary.
--
Vik Fearing

Vik Fearing <vik@postgresfriends.org> writes:

On 4/10/21 9:57 AM, Pantelis Theodosiou wrote:

On Fri, Apr 9, 2021 at 11:00 PM PG Bug reporting form <
noreply@postgresql.org> wrote:

SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for table "t"
Hint: There is an entry for table "t", but it cannot be referenced from
this part of the query.

Notice the qualification of the t.b column in the "on conflict .. where"
clause. I don't understand why b cannot be qualified at this location. It
can be qualified in the index definition, looks like a bug to me.

You don't need and shouldn't prefix the column with the table name in that
part. This should work:

Not needing to and not being able to are two completely different
things. I believe tablename qualification should be allowed here even
if it isn't necessary.

It does seem like a pointless prohibition, but the comment about it in
the source code implies it was intentional. Peter, do you remember
why?

Anyway, if the prohibition isn't necessary, the attached one-liner
gets most of the way to removing it. I'm not quite satisfied with
this though:

regression=# create table t(a int primary key, b int, c int);
CREATE TABLE
regression=# insert into t (a,b) values(1,1) on conflict(a)
where excluded.b is null do update set c=excluded.c+1;
ERROR: missing FROM-clause entry for table "excluded"
LINE 2: where excluded.b is null do update set c=excluded.c+1;
^

Seems like we want the same cannot-be-referenced HINT here as in
the complaint, so just nuking the whole namespace as the code
currently does ought to be revisited.

regards, tom lane

On Sat, Apr 10, 2021 at 9:28 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

It does seem like a pointless prohibition, but the comment about it in
the source code implies it was intentional. Peter, do you remember
why?

No. The intention was to make it like CREATE INDEX. Apparently CREATE
INDEX allows the columns to be qualified, though, so that explanation
doesn't justify it.

There might have been a concern about users being confused about the
difference between what the INSERT docs call 'index_predicate' and
what they call 'condition' in the synopsis.

--
Peter Geoghegan

Peter Geoghegan <pg@bowt.ie> writes:

On Sat, Apr 10, 2021 at 9:28 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

It does seem like a pointless prohibition, but the comment about it in
the source code implies it was intentional. Peter, do you remember
why?

No. The intention was to make it like CREATE INDEX. Apparently CREATE
INDEX allows the columns to be qualified, though, so that explanation
doesn't justify it.

OK. Here's a more fleshed-out patch that makes an effort to get rid of
duplicate namespace-munging.

I found that creating the EXCLUDED RTE soon enough to draw the complaint
I wanted about bogus references also caused this interesting change in
the regression test results:

 ERROR:  column "keyy" does not exist
 LINE 1: ...nsertconflicttest values (1, 'Apple') on conflict (keyy) do ...
                                                              ^
-HINT:  Perhaps you meant to reference the column "insertconflicttest.key".
+HINT:  Perhaps you meant to reference the column "insertconflicttest.key" or the column "excluded.key".
 -- Have useful HINT for EXCLUDED.* RTE within UPDATE:

This seems to me like an independent bug: why is the misspelled-column-
name hint machinery including inaccessible columns in its results?
That seems much more likely to be confusing than helpful. But if there
is actually a defensible reason for doing that, then this seems like
an okay change.

regards, tom lane