Re: 'a' == 'a ' (Was: RE: [pgsql-advocacy] Oracle buysInnobase)

> -----Original Message-----
> From: Terry Fielder [mailto:terry(at)ashtonwoodshomes(dot)com]
> Sent: Wednesday, October 19, 2005 2:05 PM
> To: Dann Corbit
> Cc: Tino Wildenhain; Marc G. Fournier; Richard_D_Levine(at)raytheon(dot)com;
> pgsql-hackers(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)org
> Subject: Re: 'a' == 'a ' (Was: RE: [pgsql-advocacy] [GENERAL] Oracle
> buysInnobase)
>
>
>
> Dann Corbit wrote:
> > Try this query in Oracle, SQL*Server, DB/2, Informix, etc.:
> >
> > connxdatasync=# select 1 where cast('a' as varchar(30)) = cast('a '
as
> > varchar(30));
> > ?column?
> > ----------
> > (0 rows)
> >
> > I see how you can interpret the SQL Standard to make the above
response
> > a correct one. But is it the response that you would like?
>
> When the compared datatypes are VARCHAR: YES

What is the value of doing that?

I can see plenty of harm and absolutely no return. We are talking about
blank padding before comparison. Do you really want 'Danniel '
considered distinct from 'Danniel ' in a comparison? In real life,
what does that buy you?

And even if people think it is a good idea, it seems to be at odds with
the way that I read the standard (though I am easily confused by simple
enough language at times).

It could well be that I am jaded from years of doing it the wrong way (I
expect two character strings with all leading non-blanks in agreement to
compare equal).

Perhaps this is old hat to the long-timers around here and there is a
good explanation as to why varchar should have non-blank padding when
comparisons are performed. Can someone point me to documentation that
explains it?

[snip]