From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "lxndrkrlv(at)gmail(dot)com" <lxndrkrlv(at)gmail(dot)com>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause |
Date: | 2022-08-18 21:39:06 |
Message-ID: | Yv6xeki829pitIXd@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Sun, Oct 17, 2021 at 10:34:18AM -0400, Tom Lane wrote:
> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
Attachment | Content-Type | Size |
---|---|---|
reference.diff | text/x-diff | 790 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-08-18 21:52:08 | Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause |
Previous Message | Tom Lane | 2022-08-18 16:14:29 | Re: = TRUE vs IS TRUE confuses partition index creation |