Re: BUG #13658: DELETE with syntax error in subselect deletes ALL

On Thursday, October 1, 2015, David G. Johnston <david.g.johnston@gmail.com>
wrote:

> On Thursday, October 1, 2015, <jesper@udby.com> wrote:
>
>>
>> -- Failure #1
>> DELETE FROM uid_child WHERE parent_uid IN (SELECT parent_uid FROM
>> uid_parent
>> WHERE id=999);
>> -- Gives (pgAdmin III): Query returned successfully: 0 rows affected, 21
>> ms
>> execution time.
>> -- psql: DELETE 0
>> -- Should fail as there is no "parent_uid" in table uid_parent
>>
>> -- Failure #2
>> DELETE FROM uid_child WHERE parent_uid IN (SELECT parent_uid FROM
>> uid_parent
>> WHERE id=1);
>> -- Gives (pgAdmin III): Query returned successfully: 6 rows affected, 11
>> ms
>> execution time.
>> -- psql: DELETE 6
>> -- Should fail - and this is rather important, as it actually deletes
>> everything in uid_child as it is...
>>
>>
>>
> Not a bug.  You made the subquery into a correlated subquery by
> referencing a value in the containing part of the query.  While I've now
> come to the conclusion that this dynamic could be better treated in the
> documentation it cannot be changed given the usefulness of such a
> construct.  The second best advice I can give is to prefix columns in
> subqueries with ther source relation.  The best advice is to make sure to
> test your destructive queries before executing them.
>
>
Oh, and consider "DELETE FROM child USING parent WHERE child=parent AND
parent=?"

FKs with ON DELETE CASCADE works too in some circumstances.

David J.