From: Bruce Momjian <bruce@momjian.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: lxndrkrlv@gmail.com <lxndrkrlv@gmail.com>
Cc: 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
Date: Thu, 18 Aug 2022 17:39:06 -0400
Message-ID: <Yv6xeki829pitIXd@momjian.us> (raw)
In-Reply-To: <1409812.1634481258@sss.pgh.pa.us>
References: <17233-afb9d806aaa64b17@postgresql.org>
<CAKFQuwbnETbhnBYtMA8HNJwf0ipwAETd58=K0JzPs22Of2ZhcA@mail.gmail.com>
<1409812.1634481258@sss.pgh.pa.us>
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
Attachments:reference.diff (text/x-diff, 789B) downloaddiff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.cindex 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)));
}
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: bruce@momjian.us, tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, lxndrkrlv@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
In-Reply-To: <Yv6xeki829pitIXd@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox