First we create two tables:
CREATE TABLE foo (
id int
, name varchar(30)
);
CREATE TABLE
CREATE TABLE bar (
id int
, foo_id int
, description varchar(100)
);
CREATE TABLE
Then we insert some data:
INSERT INTO foo (id, name) VALUES
(1, 'Dev')
, (2, 'QA')
, (3, 'Preprod')
, (4, 'Prod');
INSERT 0 4
INSERT INTO bar (id, foo_id, description)
VALUES (1, 1, 'A')
, (2, 2, 'B')
, (3, 2, 'C')
, (4, 2, 'D')
, (5, 3, 'E');
INSERT 0 5
Here I'm using a SELECT rather than the original UPDATE just to test. This could (should) be done as a join, but I was sent something like this:
SELECT COUNT(*)
FROM bar
WHERE foo_id = (SELECT id FROM foo WHERE name='QA');
count
-------
3
(1 row)
Fair enough. It does the same thing as a join. However what I was sent was actually this (note the column name in the subquery):
SELECT COUNT(*)
FROM bar
WHERE foo_id = (SELECT foo_id FROM foo WHERE name='QA');
count
-------
5
(1 row)
I would expect an error since foo_id does not exist in table foo, like this:
SELECT foo_id FROM foo WHERE name='QA';
ERROR: 42703: column "foo_id" does not exist
LINE 1: SELECT foo_id FROM foo WHERE name='QA';
Instead, it basically selected EVERYTHING in the bar table. Why?
I posted this dilemma in the PostgreSQL Slack channel, and others were similarly surprised by this. Ryan Guill tested and confirmed the same behavior not only in Postgres, but also in Oracle, MS SQL, & MySQL.
Cindy Wise observed that it is probably using the foo_id field from the bar table in the outer query, which does make sense. It's comparing foo_id to itself (while also running the almost-but-not-quite-entirely-pointless subquery to foo table), which will of course return true, so it grabs every row in the bar table.
This seems like a very easy trap to fall into if you're not careful with your column names. Considering this was originally in the form of an UPDATE query, it can be a destructive mistake that would execute successfully and could be rather hard to trace back.
Update
Since I lost some insightful comments in my recent blog migration, I just want to point out that the solution is to always qualify column names with the table name or alias. Martin Preiss writes, "using qualified names is certainly a good idea to avoid this kind of problems. Martin Berger gives an explanation (for Oracle and with the use of cbo trace event 10053) in http://berxblog.blogspot.de/2016/02/if-you-write-sql-be-specific.html. In his case the IN is internally transformed into an exists and the join column is compared with itself."