Re: String comparison and the SQL standard

Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> 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?

BTW, the last entry for PG surprised me a bit, because I would've
expected the varchar semantics to "win". Some experimentation shows
that you're correct about comparing char and varchar: we coerce the
varchar to char and use bpchareq, which ignores trailing blanks.
But if you compare char and text, we coerce the char to text (stripping
any trailing blanks as we do so) and then apply texteq. So in that
scenario, trailing blanks in the char datum are ignored, but trailing
blanks in the text datum are not, which is the behavior I was
remembering. It's a bit surprising that the two cases are resolved
differently --- I think that's coming out of the "most exact matches"
rule for ambiguous-operator resolution, because the available operators
are declared char = char and text = text.

regards, tom lane