SQL Server WTF

danixdefcon5

I just found out something that has to be an SQL Server 2008 bug. I was sent an SQL script with the following statement

UPDATE WTF_REMS SET
S_ID =28
WHERE R_ID IN (
SELECT R_ID FROM #REMS
)

which ran without giving any error. Looks right, doesn't it? Except that while the R_ID column exists in WTF_REMS, the temporary table #REMS has no such field. The weird thing is that SQL Server didn't barf at this offending piece of SQL.

So nothing should've been affected, right? Right?

(28546 row(s) affected)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

The entire fucking table was updated by the broken statement!!!!

So SQL Server fails to do a SELECT, then fails to raise an error ... then silently runs the UPDATE which ignores the WHERE statement. WTF???

OzPeter

@danixdefcon5 said:

I just found out something that has to be an SQL Server 2008 bug.

Whilst I really do feel your pain (that behaviour would really piss me off as well) I'd bet that the failure is probably in misunderstanding MS-SQL rather than a bug in the product itself. This is too much of a fundamental problem to not have been tested. If it is a bug I will eat my words () and say that I am truly WTF. But yes, I am impressed and amazed at this.

()For words written using Alphabet soup

PsychoCoder

Wheres the part of the script that creates #REM? It strikes me as odd that this ran without puking and I use SQL 2008 on a daily basis and have never ran into anything like this.

blakeyrat

I could be wrong, but I believe it's because there's no FROM clause, making your statement is equivalent to:

UPDATE WTF_REMS SET
S_ID =28

Although it seems that having the WHERE clause there without a proper FROM clause would trigger some kind of syntax error. To ask a stupid question, are you sure the entire query was highlighted when you hit "run?" (SSMS will run partial queries if they're only partially highlighted.)

Whenever I write something like that, I write it like:

UPDATE WTF_REMS
SET S_ID =28
FROM WTF_REMS W
JOIN #REMS R on R.R_ID = W.R_ID

That makes it explicit. (And using a join instead of a subquery makes it significantly faster. Well, in most cases.)

danixdefcon5

@PsychoCoder said:

Wheres the part of the script that creates #REM? It strikes me as odd that this ran without puking and I use SQL 2008 on a daily basis and have never ran into anything like this.

The statement is part of a script that creates three #temp tables, fills them up, does the update and then drops the #temp tebles. The entire thing ran, no highlighted statement screwup here.

And yes, the initial WTF was that the guy who sent this script didn't check that the column didn't exist... but SQL should've barfed on this piece of art, instead of swallowing the error and just matching everything to the WHERE statement.

Oh, and I had a policy on placing such scripts inside a BEGIN/ROLLBACK block, so that any kind of "oh no I fucked up" mistakes would be detected before they are permanent. Didn't check this particular script, and the guy who made it also forgot to put it there. At least they are the ones who had to fix it...

Interesting. I'm seeing this same behavior.

Incidentally, it only happens if you use the same column name in both the where condition and the embedded select. For example, this has the behavior you described:

select top 1 Id as Id1, Value into #Test from MyTable

update MyTable
set Value = 'TEST5'
where Id in
(select Id from #Test)

drop table #Test

This, on the other hand, throws a proper exception and doesn't update the data:

select top 1 Id, Value into #Test from MyTable

update MyTable
set Value = 'TEST5'
where Id in
(select Id1 from #Test)

drop table #Test

anondrifice

I think this is getting parsed as

UPDATE WTF_REMS SET

WTF_REMS.S_ID = 28

WHERE WTF_REMS.R_ID IN (

SELECT WTF_REMS.R_ID FROM #REMS

)

RTapeLoadingError

I was able to recreate this behaviour in SQL Server 2008 and 2005. In fact I was able to get SQL Server to do this when the subquery refered to a normal (i.e. non-temporary) table.

Seems odd to me.

Right, that actually makes sense. When there's no definition of R_ID in the scope of the select, it goes up a level. Which is the behavior you'd want in some cases, like

UPDATE foo SET
foo1 = 28
WHERE foo2 IN (
SELECT func1(foo3,bar1) FROM bar);

renalexam

I can't see how this is desired behavior at all. It does NOT occur when the column name prior to "in" differs from the column name specified in the sub-query (judging from my quick test). I think this is a defect.

create table Test1 (id varchar(1), other varchar(10))
insert into Test1 (id, other) values ('1', 'test')
insert into Test1 (id, other) values ('2', 'test')
select * into #Temp1 from Test1
update Test1 set id = '3' where id in (select id1 from #Temp1)

--Msg 207, Level 16, State 1, Line 1
--Invalid column name 'id1'.

alter table #temp1 drop column id
update Test1 set id = '3' where id in (select id from #Temp1)
--(2 row(s) affected)

The_Assimilator

That's nothing compared to this ballsup, which has been open for over 2 years without a fix:

Lights, camera, New York!

'These streets will make you feel brand new, big l

WTF

@HonoreDB said:

Right, that actually makes sense. When there's no definition of R_ID in the scope of the select, it goes up a level. Which is the behavior you'd want in some cases, like UPDATE foo SET foo1 = 28 WHERE foo2 IN ( SELECT func1(foo3,bar1) FROM bar);

That's exactly what it does. The mantra: Always qualify your field names to avoid ambiguity. This post is a classic example of why.

db2

You're just inadvertently writing a correlated subquery. Typically a correlated subquery will have the correlated columns somewhere in the WHERE clause or a join condition, but there's no reason you can't have it somewhere in the SELECT column list. After all, you might need to pass it to a function, like HonoreDB demonstrated. Your "broken" query just selects WTF_REMS.R_ID as many times as there are rows in #REMS, and of course it finds WTF_REMS.R_ID in that list, so...

Though as a safety feature, it might be good to require a FROM clause and table aliases for any UPDATE that uses a subquery to prevent any foot-shooting.

renalexam

Apparantly this is WAD... http://support.microsoft.com/kb/298674

Qualify, and it works as expected

update Test1 set id = '3' where Test1.id in (select #Temp1.id from #Temp1)
Msg 207, Level 16, State 1, Line 1
Invalid column name 'id'.

blakeyrat

@Zagyg said:

@HonoreDB said:
Right, that actually makes sense. When there's no definition of R_ID in the scope of the select, it goes up a level. Which is the behavior you'd want in some cases, like UPDATE foo SET foo1 = 28 WHERE foo2 IN ( SELECT func1(foo3,bar1) FROM bar);

That's exactly what it does. The mantra: Always qualify your field names to avoid ambiguity. This post is a classic example of why.

Yupyupyup!

The behavior is confusing, but correct coding style would have prevented the bug.

smbarbour

@blakeyrat said:

@Zagyg said:
@HonoreDB said:
Right, that actually makes sense. When there's no definition of R_ID in the scope of the select, it goes up a level. Which is the behavior you'd want in some cases, like UPDATE foo SET foo1 = 28 WHERE foo2 IN ( SELECT func1(foo3,bar1) FROM bar);

That's exactly what it does. The mantra: Always qualify your field names to avoid ambiguity. This post is a classic example of why.

Yupyupyup!

The behavior is confusing, but correct coding style would have prevented the bug.

As long as he needs to change the code anyway, he should probably switch to table variables for improved performance (depending on how much data is actually in the temporary tables).

hoodaticus

How very counter-intuitive! THANK YOU FOR SHARING, seriously!

jpolonsk

This is entirely how it is supposed to work. If this didn't work then you also couldn't do correlated subqueries. http://en.wikipedia.org/wiki/Correlated_subquery

Your version

select a from tblA where a in (select a from tblB)

Intended version - not correlated

select a from tblA where a in (select b from tblB)

possibly intended version correlated

select a from tblA where a in (select b from tblB where b = a)

hoodaticus

I've always written those this way:

SELECT a FROM tblA WHERE a IN (SELECT b a FROM tblB)

hoodaticus

And apparently, it was me being a dumbass, since the column alias has no effect.