Date: Thu, 18 Aug 2022 17:39:06 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
	"lxndrkrlv@gmail.com" <lxndrkrlv@gmail.com>,
	"pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17233: Incorrect behavior of DELETE command with bad
 subquery in WHERE clause
Message-ID: <Yv6xeki829pitIXd@momjian.us>
References: <17233-afb9d806aaa64b17@postgresql.org>
 <CAKFQuwbnETbhnBYtMA8HNJwf0ipwAETd58=K0JzPs22Of2ZhcA@mail.gmail.com>
 <1409812.1634481258@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="gFCTRxxnFXj1lFLg"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1409812.1634481258@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/Yv6xeki829pitIXd%40momjian.us>
Precedence: bulk


--gFCTRxxnFXj1lFLg
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

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


--gFCTRxxnFXj1lFLg
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="reference.diff"

diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index f6b740df0a..a1b44cc590 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -3672,7 +3672,7 @@ errorMissingColumn(ParseState *pstate,
 				 state->rfirst ? closestfirst ?
 				 errhint("Perhaps you meant to reference the column \"%s.%s\".",
 						 state->rfirst->eref->aliasname, closestfirst) :
-				 errhint("There is a column named \"%s\" in table \"%s\", but it cannot be referenced from this part of the query.",
+				 errhint("There is a column named \"%s\" in table \"%s\" and another table so a table-qualified column reference is required.",
 						 colname, state->rfirst->eref->aliasname) : 0,
 				 parser_errposition(pstate, location)));
 	}

--gFCTRxxnFXj1lFLg--