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

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:

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