Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause

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

In response to

Responses

Browse pgsql-bugs by date

  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