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