Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
jesper@udby.com
Date:
The following bug has been logged on the website:

Bug reference:      13658
Logged by:          Jesper Udby
Email address:      jesper@udby.com
PostgreSQL version: 9.3.9
Operating system:   Ubuntu 14.04 LTS
Description:

The following SQL illustrates an error in the SQL parser. There is a syntax
error in the subselect for the DELETE statements below, but the DELETE
succeeds, and either deletes nothing or EVERYTHING in the referenced table:

--
-- SETUP
--
CREATE TABLE uid_parent
(
  id integer NOT NULL,
  uid character varying(32) NOT NULL,
  name text NOT NULL,
  CONSTRAINT uid_parent_pkey PRIMARY KEY (id),
  CONSTRAINT uid_parent_uid_key UNIQUE (uid)
);

CREATE TABLE uid_child
(
  id serial NOT NULL,
  uid character varying(32) NOT NULL,
  parent_uid character varying(32) NOT NULL,
  name text NOT NULL,
  CONSTRAINT uid_child_pkey PRIMARY KEY (id),
  CONSTRAINT uid_child_uid_key UNIQUE (uid)
);

-- TEST-DATA
INSERT INTO uid_parent(id, uid, name) VALUES (1,
'bb9515ebbe724f0d9e0b4fc2d72a59b6', 'parent 1');
INSERT INTO uid_parent(id, uid, name) VALUES (2,
'c4d48e735c02450bbea9731e8cf9b749', 'parent 2');
INSERT INTO uid_parent(id, uid, name) VALUES (3,
'4b0cf89e9f66463bb52df13f6bf477eb', 'parent 3');
INSERT INTO uid_parent(id, uid, name) VALUES (4,
'266043aced6546aba96126d9afad2f24', 'parent 4');

INSERT INTO uid_child(uid, parent_uid, name) VALUES
('6337a55732184bb9b58e687e1ebccbd5', 'bb9515ebbe724f0d9e0b4fc2d72a59b6',
'child 1.1');
INSERT INTO uid_child(uid, parent_uid, name) VALUES
('19463cf1f0e049bc84b6991ec940cd9f', 'bb9515ebbe724f0d9e0b4fc2d72a59b6',
'child 1.2');
INSERT INTO uid_child(uid, parent_uid, name) VALUES
('e54c9a867f374b1889031c2fd06d8ea9', 'bb9515ebbe724f0d9e0b4fc2d72a59b6',
'child 1.3');
INSERT INTO uid_child(uid, parent_uid, name) VALUES
('0a4df934c57446b995a391ac62664633', 'c4d48e735c02450bbea9731e8cf9b749',
'child 2.1');
INSERT INTO uid_child(uid, parent_uid, name) VALUES
('2e441dfe655f48b1b9ea5faabc9707d4', 'c4d48e735c02450bbea9731e8cf9b749',
'child 2.2');
INSERT INTO uid_child(uid, parent_uid, name) VALUES
('5e7bba4b1d134e0faf40b5e45eb093c4', '4b0cf89e9f66463bb52df13f6bf477eb',
'child 3.1');

-- 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...

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

From
Tom Lane
Date:
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

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

From
"David G. Johnston"
Date:
On Thursday, October 1, 2015, <jesper@udby.com
<javascript:_e(%7B%7D,'cvml','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.

David J.

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

From
"David G. Johnston"
Date:
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.