From: | Александр Королев <lxndrkrlv(at)gmail(dot)com> |
---|---|
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>, "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: | 2021-10-18 04:19:30 |
Message-ID: | CANWr0s029ET54ZwgxHr88utD8+9EaaQQPvdbvPprcVx4NGKL4A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
>
> This is not a bug:
>
>
> https://wiki.postgresql.org/wiki/FAQ#Why_doesn.27t_PostgreSQL_report_a_column_not_found_error_when_using_the_wrong_name_in_a_subquery.3F
>
> 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.
>
Possibly this is not a bug, but this behavior is strange.
Also, this subquery has different behavior in SELECT and DELETE:
--------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS pg_temp.tmp1;
DROP TABLE IF EXISTS pg_temp.tmp2;
CREATE TEMPORARY TABLE tmp1 (id int NOT NULL, name text);
CREATE TEMPORARY TABLE tmp2 (id int NOT NULL, name text);
INSERT INTO tmp1 (id, name) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
INSERT INTO tmp2 (id, name) VALUES (1, 'aaa');
-- select outputs all rows from tmp1
SELECT * FROM tmp1
WHERE CTID in (
SELECT CTID
FROM tmp1
INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR UPDATE
);
--delete affects only first row from tmp1
DELETE FROM tmp1
WHERE CTID in (
SELECT CTID
FROM tmp1
INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR UPDATE
) RETURNING *;
--------------------------------------------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-10-18 04:51:47 | Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause |
Previous Message | Noah Misch | 2021-10-17 15:12:05 | Re: CREATE INDEX CONCURRENTLY does not index prepared xact's data |