DELETE query executes even sub-query RETURNED an ERROR

Question

Before I start my inquiry, I just had a mini heart attack. My script is near to go in production site. Luckily, it was ran first in our patch box. phew

So I had this backup table where I use it as source for the records to delete.

CREATE TABLE some_backup_table AS
SELECT name, -- text
       other_column -- text
  FROM original_table
 WHERE 1=1 AND some_condition_which_are_true;

Then I have this DELETE QUERY, fetching the IDs from the created table. WHICH column does not exists.

DELETE FROM original_table 
WHERE original_table_id IN (SELECT original_table_id 
                            FROM some_backup_table);

This is the issue.

All of the data from original_table are DELETED even the subquery returned an error about column does not exists. This can't stopped either using a transaction.

Can someone explain to me why is this happening? And how to see this coming? I know that I should be mindful about the columns of my backup table. But normally, I'm backing-up with all columns of table.

We're using psql 9.4.

Thank you!

Answer 1

If a column in a sub-query doesn't exist in the tables of the sub-query, but exists in the outer query (in your case the DELETE statement) the SQL name resolution specifies that the outer column is used. So even if the sub-query is invalid as a standalone query, it's valid as a sub-query.

You can avoid this by always prefixing column names by their table (alias). Then the following will throw an error rather than deleting all rows:

DELETE FROM original_table 
WHERE original_table_id IN (SELECT bt.original_table_id 
                            FROM some_backup_table bt);