How to accidentally delete all rows in a table

Question

Someone wants to take a stab at explaining the mechanics of this... this little quirk of the query parser almost caused major damage for me today.

Create a test table with 100 rows, with 1-100.

create table test( JobID int primary key);

;with numbers as (
    select 1 as n
    union all
    select n + 1 as n 
    from numbers
    where n < 100
)
insert into test
select n from numbers

Create a temp table with integers 1-50 in it:

select jobid as number into #deletions
from test 
where jobid <= 50

Now do a delete using an IN clause but with the wrong column name in the inner query:

delete from test where JobID in (select JobID from #deletions)

That last delete statement, from appearances, gives the appearance of deleting 50 rows... However, there is no JobID in #deletions, so it kind-of pulls that from the outer query and ends up, somehow, deleting all the rows in test.

My question is, how on earth is it interpreting that inner query... #deletions only has 50 rows, so how is it pulling all 100 id's from the outer table? This type of typo/mistake almost caused major damage to me today.

In my opinion, this should throw some kind of parsing/syntax error or some kind of ambiguity error.

Here's a SQL Fiddle Demo

Answer 1

If you use table aliases, the logic would be clear. You think you are writing:

delete from test
    where test.JobID in (select d.JobID from #deletions d);

That makes sense, but it would generate a syntax error because JobId does not exist in #deletions. So, scoping rules of SQL go to the next level to find JobId and interpret the query as:

delete from test
    where test.JobID in (select test.JobID from #deletions d);

This will delete all non-NULL values of JobId.

The moral: Always use qualified column names.