How to accidentally delete all rows in a table

Question

Someone wants to strike a blow at explaining the mechanics of this ... this little quirk of the query analyzer almost did me serious damage today.

Create a test pattern with 100 rows from 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 temporary table with integers 1-50 in it:

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

Now do the deletion using the IN clause, but with the wrong column name in the inner query:

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

This last delete operator, in appearance, gives the appearance of deleting 50 rows ... However, #deletions does not have a JobID , so it distracts it from the external request and ends up deleting all rows in the test.

My question is how does it interpret this internal query ... #deletions has only 50 rows, so how does it pull all 100 identifiers from an external table? This type of typo / error almost caused me serious damage today.

In my opinion, this should cause some kind of syntax error or some kind of ambiguity error.

Here's the SQL Fiddle Demo

Answer 1

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

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

This makes sense, but will generate a syntax error because JobId does not exist in #deletions . Thus, SQL scope rules 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 remove all values ​​other than NULL JobId .

Moral: Always use qualified column names.