On Sunday, October 17, 2021, Александр Королев <
lxndrkrlv@gmail.com> wrote:
This is not a bug:
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:
-- 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
);
As long as the subquery returns at least one row every row in the table will be returned.
--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 *;
Here, as soon as you delete the single row that the subquery returns no additional rows will be deleted. It seems indeterminate as to how many, and which, rows actually get removed. At least one, but possibly all. The is more procedural an execution plan than I would expect from SQL but it’s all that seems to fit the described behavior.
In short, your subquery is basically bogus and so, yes, you will see strange behavior if you use it.
The server cannot always inform you that you’ve written something bogus (i.e., error) because the same general query form can be used to write something useful. Correlated subqueries are one of those cases.
David J.