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

On Sunday, October 17, 2021, Александр Королев <lxndrkrlv(at)gmail(dot)com> wrote:

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

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.