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

On Thursday, October 1, 2015, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Thursday, October 1, 2015, <jesper(at)udby(dot)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.