While researching a problem with a different database system,
I came across the following in the SQL standard ISO/IEC 9075-2:2003,
Section 8.2 (<comparison predicate>), General Rules:
3) The comparison of two character strings is determined as follows:
a) Let CS be the collation as determined by Subclause 9.13,
"Collation determination", for the declared types of the
two character strings.
b) 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 characters,
where the pad character is chosen based on CS.
If CS has the NO PAD characteristic, then the pad character is
an implementation-dependent character different from
any character in the character set of X and Y that collates
less than any string under CS.
Otherwise, the pad character is a <space>.
That would effectively mean that 'a'='a ' is TRUE for
all character string types.
Of the DBMS I tested, Microsoft SQL Server and MySQL gave me
that very result, while PostgreSQL and Oracle gave me FALSE.
Does anybody know if we deviate from the standard on purpose
in this case? I searched the archives, but all I could find was
http://www.postgresql.org/message-id/20051019154026.X995@ganymede.hub.org
Yours,
Laurenz Albe
