SQL Server WTF



  • 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???



  • @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



  • 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.



  • 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.)



  • @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



  •  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

    )

    <input class="_iDc8ZCzr_gm_expandable_thumbnails_elem" value="▣" type="button"><input class="_iDc8ZCzr_gm_expandable_thumbnails_elem" value="▫" type="button">



  • 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);



  • 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. 

    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>

    create</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>table</FONT></FONT><FONT size=2> Test1</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2> </FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT size=2>id </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>varchar</FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT size=2>1</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>),</FONT></FONT><FONT size=2> other </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>varchar</FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT size=2>10</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>))
    </FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>insert</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>into</FONT></FONT><FONT size=2> Test1</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2> </FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT size=2>id</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2> other</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>)</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>values </FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT color=#ff0000 size=2><FONT color=#ff0000 size=2>'1'</FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2><FONT color=#ff0000 size=2>'test'</FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>)
    </FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>insert</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>into</FONT></FONT><FONT size=2> Test1</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2> </FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT size=2>id</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2> other</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>)</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>values </FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT color=#ff0000 size=2><FONT color=#ff0000 size=2>'2'</FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2><FONT color=#ff0000 size=2>'test'</FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>)
    </FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>select</FONT></FONT><FONT size=2> </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>*</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>into</FONT></FONT><FONT size=2> #Temp1 </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>from</FONT></FONT><FONT size=2> Test1
    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>update</FONT></FONT><FONT size=2> Test1 </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>set</FONT></FONT><FONT size=2> id </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>=</FONT></FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2><FONT color=#ff0000 size=2>'3'</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>where</FONT></FONT><FONT size=2> id </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>in</FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2> </FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>select</FONT></FONT><FONT size=2> id1 </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>from</FONT></FONT><FONT size=2> #Temp1</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>)

    </FONT></FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>

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

    </FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>

    alter</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>table</FONT></FONT><FONT size=2> #temp1 </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>drop</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>column</FONT></FONT><FONT size=2> id
    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>update</FONT></FONT><FONT size=2> Test1 </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>set</FONT></FONT><FONT size=2> id </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>=</FONT></FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2><FONT color=#ff0000 size=2>'3'</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>where</FONT></FONT><FONT size=2> id </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>in</FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2> </FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>select</FONT></FONT><FONT size=2> id </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>from</FONT></FONT><FONT size=2> #Temp1</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>)
    </FONT></FONT><FONT color=#008000 size=2><FONT color=#008000 size=2>--(2 row(s) affected)

    </FONT></FONT>


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

    http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value



  • @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.



  • 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.



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

     Qualify, and it works as expected

    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>

    update</FONT></FONT><FONT size=2> Test1 </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>set</FONT></FONT><FONT size=2> id </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>=</FONT></FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2><FONT color=#ff0000 size=2>'3'</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>where</FONT></FONT><FONT size=2> Test1</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>id </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>in</FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2> </FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>select</FONT></FONT><FONT size=2> #Temp1</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>id </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>from</FONT></FONT><FONT size=2> #Temp1</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>)
    <FONT size=1>Msg 207, Level 16, State 1, Line 1
    Invalid column name 'id'.

    </FONT></FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2></FONT></FONT>


  • @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.



  • @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).


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



  • 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)



  • I've always written those this way:

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



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


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.