I ran into this issue where an update command with a bad subquery in the WHERE clause still ran and updated everything as if there was no WHERE clause at all. Needless to say, it was a bit surprising. o_O
I've created a test (see below) that demonstrates what I think is to be the issue; it's self-contained, so you can use it on a database without having to worry about mucking anything up.
tl;dr; it seems to be related to when the "bad" subquery references a field that matches the token in the WHERE clause.
This was tested on PostgreSQL 9.1.6 and 9.1.9; the former was on Mac OS X (EnterpriseDB) and the latter was on CentOS 6.x using the PGDG build.
I wasn't sure if this should be posted to pgsql-bugs using the form yet; I'd like to see if there's something I'm doing wrong here before I do that.
Thank you,
Scott Parkerson
---------------------------------------------------------------------------
--
-- test_update_subquery.sql
-- Demo a possible bug in the way the query parser works
--
-- Scott Parkerson <scott@xxxxxxxxxxxxx>
--
---------------------------------------------------------------------------
BEGIN;
-- Create tables for testing
CREATE TABLE foo (
id int NOT NULL,
name character varying(32) NOT NULL,
kind character varying(20) NOT NULL,
otherid int NOT NULL
);
CREATE TABLE other (
id int NOT NULL,
name character varying(20) NOT NULL
);
-- Test data
INSERT INTO other VALUES(1, 'Nothing');
INSERT INTO other VALUES(2, 'Nada');
INSERT INTO other VALUES(3, 'Zilch');
INSERT INTO foo VALUES(1, 'Electronecromegastompers, Inc.', 'CORP', 1);
INSERT INTO foo VALUES(2, 'Lizard Lick Towing', 'LLC', 1);
INSERT INTO foo VALUES(3, 'Bob Loblaw Law Firm', 'LLC', 2);
INSERT INTO foo VALUES(4, 'Cyberdyne Systems', 'CORP', 3);
INSERT INTO foo VALUES(5, 'Chock Full Of Gears and Stuff', 'PRIVATE', 3);
-- Test selects
SELECT COUNT(*) FROM foo WHERE kind = 'LLC'; -- expect 2
SELECT COUNT(*) FROM foo WHERE kind = 'LLC' AND otherid = 3; -- expect 0
-- Save here
SAVEPOINT before_update;
-- Update using a subselect; should update 3 items in foo
UPDATE foo
SET kind = 'LLC'
WHERE otherid IN
(SELECT id
FROM other
WHERE name != 'Nothing');
-- Check results
SELECT COUNT(*) FROM foo WHERE kind = 'LLC'; -- expect 4
SELECT COUNT(*) FROM foo WHERE kind = 'LLC' AND otherid = 3; -- expect 2
-- Rollback to before the update
ROLLBACK TO before_update;
-- This update has a bad select, but is allowed to go update the whole table
-- as if the where clause doesn't exist! Think it's because the "bad" column in
-- the subquery matches a "good" column in the outer query's where clause?
UPDATE foo
SET kind = 'LLC'
WHERE otherid IN
(SELECT otherid
FROM other
WHERE name != 'Nothing');
-- Check results
SELECT COUNT(*) FROM foo WHERE kind = 'LLC'; -- expect 4, but returns 5
ROLLBACK TO before_update;
-- This update has a bad select with a difference; the bad token is different
-- than the outer where clause. It fails as expected:
--
-- psql:test_update_subquery.sql:73: ERROR: column "herpderpid" does not exist
-- LINE 4: (SELECT herpderpid
-- ^
UPDATE foo
SET kind = 'LLC'
WHERE otherid IN
(SELECT herpderpid
FROM other
WHERE name != 'Nothing');
ROLLBACK;
-- END OF TEST
