From: | jesper(at)udby(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #13658: DELETE with syntax error in subselect deletes ALL |
Date: | 2015-10-01 12:17:48 |
Message-ID: | 20151001121748.348.81279@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 13658
Logged by: Jesper Udby
Email address: jesper(at)udby(dot)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...
From | Date | Subject | |
---|---|---|---|
Next Message | Maxim Boguk | 2015-10-01 12:52:32 | Re: BUG #13657: Some kind of undetected deadlock between query and "startup process" on replica. |
Previous Message | maxim.boguk | 2015-10-01 12:17:45 | BUG #13657: Some kind of undetected deadlock between query and "startup process" on replica. |