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 *;