Re: String comparison and the SQL standard

Tom Lane wrote:
>> 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>.
>
> The PAD case is specifying the way that CHAR(n) comparison should work.
> (We don't expose the PAD/NO PAD distinction in any other way than
> CHAR vs VARCHAR/TEXT types.)
>
> AFAICS, the NO PAD case is ignorable BS: [...]

> In any case, the most significant word in that whole paragraph is
> "effectively", which means you can do it however you want as long
> as you get an equivalent comparison result.
>
>> That would effectively mean that 'a'='a ' is TRUE for
>> all character string types.
>
> In the PAD case, yes. Else no.

Thanks for the clarification.

>> Of the DBMS I tested, Microsoft SQL Server and MySQL gave me
>> that very result, while PostgreSQL and Oracle gave me FALSE.
>
> This probably has more to do with what these systems think the
> data type of an undecorated literal is, than with whether they do
> trailing-space-insensitive comparison all the time.

I tested not only with string literals, but also comparing
table columns of the respective types.

I came up with the following table of semantics used for
comparisons:

| CHAR(n)=CHAR(n) | VARCHAR(n)=VARCHAR(n) | CHAR(n)=VARCHAR(n) |
-----------+-----------------+-----------------------+--------------------+
Oracle | PAD SPACE | NO PAD | NO PAD |
-----------+-----------------+-----------------------+--------------------+
PostgreSQL | PAD SPACE | NO PAD | PAD SPACE |
-----------+-----------------+-----------------------+--------------------+
MySQL | PAD SPACE | PAD SPACE | PAD SPACE |
-----------+-----------------+-----------------------+--------------------+
SQL Server | PAD SPACE | PAD SPACE | PAD SPACE |
-----------+-----------------+-----------------------+--------------------+

Yours,
Laurenz Albe