3

Why is this query deleting all users when the user_id column does not exist at the profile table?

The initial query:

DELETE FROM users
    WHERE user_id IN (
        SELECT user_id FROM profile
        )
    AND user_id != 35
    AND user_id != 6;

To be more specific.

The IN statement query returns: 

SELECT user_id FROM profile;

ERROR:  column "user_id" does not exist
LINE 1: SELECT user_id FROM profile;

Running the initial query returns

DELETE 100

Why is that happening?

Shouldn't it have stopped the execution upon error or return false or null?

Running PostgreSQL Server 9.0

Jimmy Kane
  • 16,223
  • 11
  • 86
  • 117
  • possible duplicate of [sql server 2008 management studio not checking the syntax of my query](http://stackoverflow.com/questions/4594733/sql-server-2008-management-studio-not-checking-the-syntax-of-my-query) – Martin Smith May 06 '13 at 09:20
  • 9
    This is correct behaviour. It is perfectly valid for a sub query to reference a column from the outer query. Use two part names if you want to avoid this kind of error. `DELETE FROM users WHERE user_id IN (SELECT p.user_id FROM profile p)` would give you an invalid column name error. – Martin Smith May 06 '13 at 09:22
  • If however the table `profile` would contain 0 rows, then it wouldn't delete any rows from `users`. – Colin 't Hart May 06 '13 at 09:46
  • @MartinSmith Would you be so kind as to answer the question so I can close it? – Jimmy Kane May 06 '13 at 14:34

1 Answers1

8

This behavior is correct per ANSI standards.

If the unqualified column name doesn't resolve in the inner scope then the outer scope will be considered. So effectively you are doing an unintentional correlated sub query.

As long as the table profile contains at least one row then

 FROM users
 WHERE user_id IN (
        SELECT user_id FROM profile
        )

will end up matching all rows in users (except any where users.user_id IS NULL as WHERE NULL IN (NULL) does not evaluate to true). To avoid this possible issue you can use two part names.

DELETE FROM users
WHERE  user_id IN (SELECT p.user_id
                   FROM   profile p)

Would give the error

column p.user_id does not exist:

Martin Smith
  • 438,706
  • 87
  • 741
  • 845