6

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

shA.t
  • 16,580
  • 5
  • 54
  • 111
Brett Green
  • 3,535
  • 1
  • 22
  • 29
  • 1
    At least you will not do a query like this again :-) Never submit a `DELETE` or `UPDATE` without testing. Change `DELETE` to `SELECT *` and see what' returned. – dnoeth May 25 '15 at 13:55
  • 1
    There is no ambiguity here because JobID exists only on the outer `test` table. You are right that scope is not obvious, which is why it's a good practice to make a habit of qualifying column names with the table name or alias in multi-table queries. – Dan Guzman May 25 '15 at 14:11
  • Whenever you are messing with UPDATE/DELETE/MERGE/INSERT, always wrap with BEGIN TRAN and ROLLBACK TRAN. One of the single most important pieces of advice for production Ops guys. – Greg May 26 '15 at 18:47

1 Answers1

10

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The logical error in what was a simple clean-up script was very hard to spot. Fortunately, it only removed data from an inconsequential table but I was a little bewildered for awhile until I spotted the issue. I will always use aliases from now on, or use a JOIN for the delete rather than an IN clause. – Brett Green May 25 '15 at 14:31