Search code examples
sql-serverwhitespaceansi-sql

whitespace UNION bug

This one got me the other day. What would you expect the following to return?

SELECT 'X' AS line
UNION
SELECT 'X ' AS line

Notice the space in the second SELECT. Well apparently SQL 2000 and 2005 both return 1 result. Even though its a UNION (and not a UNION ALL).

There is nothing I can see in Books on line about this. Why does it happen? I'm guessing it's a bug. Is it or is there a valid reason?

Solution

  • Trailing space is specified to be handled in a special way in the ANSI SQL Standard:

    http://support.microsoft.com/default.aspx/kb/316626

    http://support.microsoft.com/kb/154886/EN-US/

    I'm not sure that a binary collation will solve this:

    IF 'X ' COLLATE Latin1_General_BIN = 'X' COLLATE Latin1_General_BIN 
    PRINT 'Equal'

    This prints "Equal"