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

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo(at)megazone(dot)bigpanda(dot)com]
> Sent: Wednesday, October 19, 2005 2:34 PM
> To: Dann Corbit
> Cc: Terry Fielder; 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: [HACKERS] 'a' == 'a ' (Was: RE: [pgsql-advocacy]
[GENERAL]
> Oracle buysInnobase)
>
> On Wed, 19 Oct 2005, Dann Corbit wrote:
>
> > > -----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?
>
> It buys you the ability to store things where trailing spaces are
> signficant (for example passwords) within the existing limitations of
not
> having a full set of the collation behavior.

I suppose that there may be some value in having 'J&^% ' be different
from 'J&^% '.

I would expect to insert a password with trailing blanks to be the same
as inserting a password with no trailing blanks.

I think that whatever is done ought to be whatever the standard says.
If I misinterpret the standard and PostgreSQL is doing it right, then
that is fine. It is just that PostgreSQL is very counter-intuitive
compared to other database systems that I have used in this one
particular area. When I read the standard, it looked to me like
PostgreSQL was not performing correctly. It is not unlikely that I read
it wrong.