Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause - Mailing list pgsql-bugs

On Sun, Oct 17, 2021 at 10:34:18AM -0400, Tom Lane wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > The virtual join table doesn’t have a ctid, only physical tables do, and
> > the ctid of physical tables apparently aren’t propogated when they are
> > joined.
> 
> We leave the system columns out of the join because otherwise they'd
> surely conflict between the two sides of the join.  However, you could
> still reference either one with "tmp1.ctid" or "tmp2.ctid".
> 
> There might be an opportunity here to improve the error message's hint:
> 
> regression=# SELECT CTID
> regression-# FROM tmp1
> regression-# INNER JOIN tmp2 ON tmp1.id = tmp2.id;
> ERROR:  column "ctid" does not exist
> LINE 1: SELECT CTID
>                ^
> HINT:  There is a column named "ctid" in table "tmp1", but it cannot be referenced from this part of the query.
> 
> "cannot be referenced" is probably a shade misleading, given the
> availability of the qualified-name alternative.

I looked into this by modifying the error message with the attached
patch and running the regression tests.  I saw the following regression
failures due to the message change.  While the email posted query was
fixed by table-qualifying the column, the first few queries of the
regression tests were fixed by adding LATERAL, but I couldn't get the
UPDATE/DELETE queries to work.

I am feeling there isn't much we can add to this message except to say
maybe:

    There is a column named "f1" in table "a", but it cannot be referenced
    from this part of the query as structured.
                                -------------

It suggests you might be able to get it working by restructuring the
query, e.g., table-qualified or LATERAL.

Feedback?

---------------------------------------------------------------------------

 -- test some error cases where LATERAL should have been used but wasn't
 select f1,g from int4_tbl a, (select f1 as g) ss;
 ERROR:  column "f1" does not exist
 LINE 1: select f1,g from int4_tbl a, (select f1 as g) ss;
                                              ^
-HINT:  There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+HINT:  There is a column named "f1" in table "a" and another table so a table-qualified column reference is required.

 select f1,g from int4_tbl a, (select a.f1 as g) ss;
 ERROR:  invalid reference to FROM-clause entry for table "a"
 LINE 1: select f1,g from int4_tbl a, (select a.f1 as g) ss;
                                              ^
 HINT:  There is an entry for table "a", but it cannot be referenced from this part of the query.

 select f1,g from int4_tbl a cross join (select f1 as g) ss;
 ERROR:  column "f1" does not exist
 LINE 1: select f1,g from int4_tbl a cross join (select f1 as g) ss;
                                                        ^
-HINT:  There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
+HINT:  There is a column named "f1" in table "a" and another table so a table-qualified column reference is required.

 select f1,g from int4_tbl a cross join (select a.f1 as g) ss;
 ERROR:  invalid reference to FROM-clause entry for table "a"
 LINE 1: select f1,g from int4_tbl a cross join (select a.f1 as g) ss...
                                                        ^
 HINT:  There is an entry for table "a", but it cannot be referenced from this part of the query.

 -- check behavior of LATERAL in UPDATE/DELETE
 create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl;
 -- error, can't do this:

 update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
 ERROR:  column "x1" does not exist
 LINE 1: ... set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
                                                                 ^
-HINT:  There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
+HINT:  There is a column named "x1" in table "xx1" and another table so a table-qualified column reference is
required.

 update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss;
 ERROR:  invalid reference to FROM-clause entry for table "xx1"
 LINE 1: ...t x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss...
                                                              ^
 HINT:  There is an entry for table "xx1", but it cannot be referenced from this part of the query.
 -- can't do it even with LATERAL:

 update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss;
 ERROR:  invalid reference to FROM-clause entry for table "xx1"
 LINE 1: ...= f1 from lateral (select * from int4_tbl where f1 = x1) ss;
                                                                 ^
 HINT:  There is an entry for table "xx1", but it cannot be referenced from this part of the query.
 -- we might in future allow something like this, but for now it's an error:

 update xx1 set x2 = f1 from xx1, lateral (select * from int4_tbl where f1 = x1) ss;
 ERROR:  table name "xx1" specified more than once
 -- also errors:

 delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
 ERROR:  column "x1" does not exist
 LINE 1: ...te from xx1 using (select * from int4_tbl where f1 = x1) ss;
                                                                 ^
-HINT:  There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
+HINT:  There is a column named "x1" in table "xx1" and another table so a table-qualified column reference is
required.

 delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss;
 ERROR:  invalid reference to FROM-clause entry for table "xx1"
 LINE 1: ...from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss...
                                                              ^
 HINT:  There is an entry for table "xx1", but it cannot be referenced from this part of the query.

 delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
 ERROR:  invalid reference to FROM-clause entry for table "xx1"
 LINE 1: ...xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
                                                                 ^
 -- this should fail because f1 is not exposed for unqualified reference:

 create rule rules_foorule as on insert to rules_foo where f1 < 100
 do instead insert into rules_foo2 values (f1);
 ERROR:  column "f1" does not exist
 LINE 2: do instead insert into rules_foo2 values (f1);
                                                   ^
-HINT:  There is a column named "f1" in table "old", but it cannot be referenced from this part of the query.
+HINT:  There is a column named "f1" in table "old" and another table so a table-qualified column reference is
required.

 -- This should fail, because q2 isn't a name of an EXCEPT output column

 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
 ERROR:  column "q2" does not exist
 LINE 1: ... int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1...
                                                              ^
-HINT:  There is a column named "q2" in table "*SELECT* 2", but it cannot be referenced from this part of the query.
+HINT:  There is a column named "q2" in table "*SELECT* 2" and another table so a table-qualified column reference is
required.



-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson