Postgres Pro
Downloads
Home   >   mailing lists

Thread: UPDATE with subquery; possible bug in query parser?

UPDATE with subquery; possible bug in query parser?

From
Scott Parkerson
Date:
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@parkerson.net>
--
---------------------------------------------------------------------------
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

Re: UPDATE with subquery; possible bug in query parser?

From
Alban Hertroys
Date:
On Apr 20, 2013, at 18:27, Scott Parkerson <scott@parkerson.net> wrote:

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

Nope, the query parser is fine.
You just referenced a column from the outer query from within the inner query, which is perfectly legal and useful and whatnot. In your case you end up with an inner query that's not correlated to the outer one at all, but how is the query parser to know whether that was what you intended or not?

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

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.