Re: BUG #13658: DELETE with syntax error in subselect deletes ALL - Mailing list pgsql-bugs

jesper@udby.com writes:
> -- 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...

Unfortunately, this is not a bug, it's just pilot error: parent_uid is a
legal outer reference to uid_child's column of that name.  This behavior
is required by SQL standard.

A lot of people make a practice of always table-qualifying names in
sub-selects to help protect against this type of thinko.

            regards, tom lane