SQL Server whitespace UNION bug

pod7payv 于 2023-03-22 发布在其他

关注(0)|答案(1)|浏览(113)

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?

1条答案

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

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"