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

On Apr 20, 2013, at 18:27, Scott Parkerson <scott(at)parkerson(dot)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.