
> I guess the strangest part was that both a.foo = 'S' and b.foo = 'S' but
> not a.foo=b.foo;  (a.foo is varchar(5) , b.foo is char(5) )
> 
> I guess that tha 'S' that b.foo gets compared to is converted to 'S    '
> before comparison but when comparing varchar(5) and char(5) they are
> both compared by converting them to varchar which keeps the trailing
> spaces from char(5). 

Yes, I think this is inconvenient/unintuitive. If it is doable according to 
standards, this should imho be fixed.

> If the conversion where varchar(5) --> char(5) then
> they would compare equal.

I am not sure, since, if the varchar stored 'S  ' then the comparison to a char 'S' 
should probably still fail, since those spaces in the varchar are significant. 
Informix compares them equal, so I guess argumentation can be made in that direction
too (that currently evades my understanding of intuitive reasoning :-). 

Andreas

Fri, 12 Jul 2002 09:35:26 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: string cast/compare broken?
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
>> If the conversion where varchar(5) --> char(5) then
>> they would compare equal.

> I am not sure, since, if the varchar stored 'S  ' then the comparison
> to a char 'S'  should probably still fail,

There is no comparison of varchar to char:

regression=# select 'z'::char = 'z'::varchar;
ERROR:  Unable to identify an operator '=' for types 'character' and 'character varying'
        You will have to retype this query using an explicit cast
regression=#

I consider this a feature, not a bug, since it's quite unclear which
semantics ought to be used.

The cases Scott originally posted all involved various forms of
coercion to force both sides to be the same type; I'm not sure
that he quite understood why he had to do that, but perhaps it's now
becoming clear.

I wonder whether it would be a good idea to stop considering char
as binary-compatible to varchar and text.  Instead we could set
things up so that there is a coercion function involved, namely
rtrim().  But that would probably make us diverge even further
from the spec.

Has anyone studied how other DBMSs handle CHAR vs VARCHAR?  Judging
from the number of questions we get on this point, I have to wonder
if we are not out of step with the way other systems do it.

			regards, tom lane

Fri, 12 Jul 2002 09:16:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: string cast/compare broken? 
On Fri, 12 Jul 2002, Tom Lane wrote:

> "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> >> If the conversion where varchar(5) --> char(5) then
> >> they would compare equal.
>
> > I am not sure, since, if the varchar stored 'S  ' then the comparison
> > to a char 'S'  should probably still fail,
>
> There is no comparison of varchar to char:
>
> regression=# select 'z'::char = 'z'::varchar;
> ERROR:  Unable to identify an operator '=' for types 'character' and 'character varying'
>         You will have to retype this query using an explicit cast
> regression=#
>
> I consider this a feature, not a bug, since it's quite unclear which
> semantics ought to be used.
>
> The cases Scott originally posted all involved various forms of
> coercion to force both sides to be the same type; I'm not sure
> that he quite understood why he had to do that, but perhaps it's now
> becoming clear.
>
> I wonder whether it would be a good idea to stop considering char
> as binary-compatible to varchar and text.  Instead we could set
> things up so that there is a coercion function involved, namely
> rtrim().  But that would probably make us diverge even further
> from the spec.
>
> Has anyone studied how other DBMSs handle CHAR vs VARCHAR?  Judging
> from the number of questions we get on this point, I have to wonder
> if we are not out of step with the way other systems do it.

I don't think it's just a CHAR vs VARCHAR issue.  AFAICT the spec defines
all of this in terms of the collations used and there are (imho arcane)
rules about converting between them for comparisons and operations.

Technically I think varcharcol=charcol *is* illegal if we are
saying that char has a collation with PAD SPACE and varchar
has a collation with NO PAD, because they're different collations
and character value expressions from column reference are implicit
and that doesn't allow comparison between two different collations.
Of course I could also be misreading it.


Fri, 12 Jul 2002 07:16:58 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: string cast/compare broken? 