SQL Server WTF



  • For those of you with access to a SQL Server database, fire up Query Analyzer and check the results of this query:

    SELECT CASE WHEN 'a' = 'a\

    ' THEN 1 ELSE 0 END

     

    The results aren't what you'd expect. 



  • what do you mean....  I put it in and got 0 as the result...    which means that 'a' = 'a'   is false, which is correct. Please elaborate on what you mean.. if you do 'a' = 'a' then you get 1.  Like it is now... it works like a ternary operator.



  • Returns 1 for me. Returns 0 if the \ is removed. I can't say that I expected that.


  • Considered Harmful

    I got 0 from SQL Server 2005.  Seems to have been fixed already.


  • Considered Harmful

    I got that result when I removed the extra line break.  I'm fairly certain that newlines are included in strings by default, unless they are escaped with a backslash.  So you're telling SQL Server that the newline is just for formatting the query and not actually part of the string.



  • If that's the case, then how would you escape a backslash?  Two backslashes in the middle of a line is interpretted as two backslashes, rather than one backslash escaping the other.  If anyone knows for sure that this is expected behavior, can you point me to the documentation?



  • joe, which sql are you using... maybe there was a bug in an older query analyzer as far as the data it translated into sql. In more correct terms, the newer ones let us get lazy.



  • @jcoehoorn said:

    If that's the case, then how would you escape a backslash?  Two backslashes in the middle of a line is interpretted as two backslashes, rather than one backslash escaping the other.

    Presumably by placing it at the start of the next line. I vaguely recall that one of the other programming languages behaved like this, so it's probably emulating that. It's still a retarded idea, though. It's not like there aren't any number of sane ways to implement an escaping scheme.


  • Considered Harmful

    @jcoehoorn said:

    If that's the case, then how would you escape a backslash?  Two backslashes in the middle of a line is interpretted as two backslashes, rather than one backslash escaping the other.  If anyone knows for sure that this is expected behavior, can you point me to the documentation?

    You don't need to escape a backslash.  It only has special meaning when it directly precedes a newline.  If you need a backslash and a newline in your string... um.  Well, you could concatenate two strings for that.



  • This will give you a backslash followed by a newline...

    <FONT color=#0000ff size=1>

    SELECT</FONT><FONT size=1> </FONT><FONT color=#ff0000 size=1>'a'</FONT><FONT size=1> </FONT><FONT color=#808080 size=1>+</FONT><FONT size=1> <FONT color=#ff00ff size=1>CAST</FONT><FONT color=#808080 size=1>(</FONT><FONT size=1>0x5C0D0A </FONT><FONT color=#0000ff size=1>AS</FONT><FONT size=1> </FONT><FONT color=#0000ff size=1>char</FONT><FONT color=#808080 size=1>(</FONT><FONT size=1>3</FONT><FONT color=#808080 size=1>))</FONT></FONT><FONT size=1> </FONT><FONT color=#808080 size=1>+</FONT><FONT size=1> </FONT><FONT color=#ff0000 size=1>'bc'

    </FONT>

     

     



  • You can use the print statement to see what the strings are interpreted as, easier to see why the statement acts as it does.

     print 'a';
    print 'a\
    ';

    This gives output:

    a
    a

     If you want a slash followed by a newline you can simply just add an extra newline in the string.

    print 'a\\

    b';

    Output:

    a\
    b



  • Gives me a 1 on SQL 2000 SP4. Perhaps 2005 behaves differently.



  • @afjohansson said:

    You can use the print statement to see what the strings are interpreted as, easier to see why the statement acts as it does.

     print 'a';
    print 'a\
    ';

    This gives output:

    a
    a

     If you want a slash followed by a newline you can simply just add an extra newline in the string.

    print 'a\\

    b';

    Output:

    a\
    b

    You can do the same thing with a SELECT, if you change Management Studio to output to text (CTRL-T) instead of the grid (CTRL-D).



  • @jcoehoorn said:

    For those of you with access to a SQL Server database, fire up Query Analyzer and check the results of this query:

    SELECT CASE WHEN 'a' = 'a</span>

    ' THEN 1 ELSE 0 END

     

    The results aren't what you'd expect. 

     

    The WTF here is posting a WTF about a piece of software, but not referring to which version of that software. Not sure, but I think there are quite a few versions/SPs of SQL server out there.

     

    Saying "Fire up SQL server and look at X" is like saying "Go get in your chevy and look underneath for the X". What if my chevy is a 2 ton pickup and yours is an Aveo? 



  • Funny you should mention- I do happen to own an Aveo.  However, before posting I tested this vs SQL Server 2000 Enterprise and SQL Server 2005 Express.  And if you're running a server without the latest service packs thats a WTF in itself.


Log in to reply