whitespace UNION bug

Question

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?

Answer 1

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"