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

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

--------------------------------------------------------------------------------------------------