Re: String comparison and the SQL standard

Tom Lane wrote:
>> 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 |
>> -----------+-----------------+-----------------------+--------------------+
>
> Interesting. Did you determine which type is assigned to an
> unmarked literal string by each system?

In Oracle it is treated like a CHAR(n):
http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements002.htm#SQLRF51039

"Oracle uses blank-padded comparison semantics only when
both values in the comparison are either expressions of
data type CHAR, NCHAR, text literals, or values returned
by the USER function."

I don't know about MySQL and SQL Server, but since they pad
strings with space for comparison in all cases, it probably
does not make a difference.

Yours,
Laurenz Albe