SQL union Gotcha! Its a feature.

I spoke last time about the strange behaviour if SQL where if you run

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

you will only get 1 value returned.

Well this is not a bug apparently it's a feature of the ANSI SQL-92 standard.

From section 8.2

 If the length in characters of X is not equal to the length
in characters of Y, then the shorter string is effectively
replaced, for the purposes of comparison, with a copy of
itself that has been extended to the length of the longer
string by concatenation on the right of one or more pad char-
acters, where the pad character is chosen based on CS. If
CS has the NO PAD attribute, then the pad character is an
implementation-dependent character different from any char-
acter in the character set of X and Y that collates less
than any string under CS. Otherwise, the pad character is a
.

Blimey so SQL pads the shorter string with spaces!

Thanks to StackOverflow for that one.