From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | jesper(at)udby(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #13658: DELETE with syntax error in subselect deletes ALL |
Date: | 2015-10-01 15:52:59 |
Message-ID: | 17895.1443714779@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
jesper(at)udby(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2015-10-01 15:58:46 | BUG #13658: DELETE with syntax error in subselect deletes ALL |
Previous Message | Tom Lane | 2015-10-01 15:50:21 | Re: BUG #13659: Constraint names truncated without error |