Re: [pgsql-advocacy] Oracle buys Innobase

Doesn't NO PAD connect to the collating sequence (CS) rather than the
data type?

ISO/IEC 9075-2:1999 (E) (c)ISO/IEC
4.2 Character strings
A character set is described by a character set descriptor. A character
set descriptor includes:
- The name of the character set.
- The name of the default collation for the character set.
For every character set, there is at least one collation. A collation is
described by a collation descriptor. A collation descriptor includes:
- The name of the collation.
- The name of the character set on which the collation operates.
- Whether the collation has the NO PAD or the PAD SPACE characteristic.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Richard_D_Levine@raytheon.com
> Sent: Wednesday, October 19, 2005 2:07 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
>
> This is the salient sentence from the standard (that I've never
> personnally
> thought much about before now).
>
> "If CS has the NO PAD characteristic, then the pad character is an
> implementation-dependent character different from any character in the
> character set of X and Y that collates less than any string under CS."
>
> It boils down to saying "NO PAD strings of different length are never
> equal".  So the correctness of any DB depends on whether the type in
> question has the NO PAD characteristic.  So,  is varchar NO PAD?
That's
> the real question.
>
> Rick
>
> "Dann Corbit" <DCorbit@connx.com> wrote on 10/19/2005 03:57:26 PM:
>
> > create table fooa (col1 varchar(30))
> > go
> > create table bara (col1 varchar(300))
> > go
> > insert into fooa values ('Danniel ')
> > go
> > insert into bara values ('Danniel   ')
> > go
> > select * from fooa,bara where fooa.col1=bara.col1
> > go
> >
> > Returns:
> > Danniel    Danniel
> >
> > I think that the issue is:
> > Does PostgreSQL use something other than <space> as the pad
character?
> > If so, what character is that (and why choose it over <space>)?
> > If not, then PostgreSQL is clearly returning the wrong results.
> >
> > > -----Original Message-----
> > > From: Terry Fielder [mailto:terry@ashtonwoodshomes.com]
> > > Sent: Wednesday, October 19, 2005 2:02 PM
> > > To: Dann Corbit
> > > Cc: Marc G. Fournier; Richard_D_Levine@raytheon.com; pgsql-
> > > general@postgresql.org
> > > Subject: Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
> > >
> > > Hi Dann
> > >
> > > Without looking at the internals to see if the 1 column or the
other
> > is
> > > being converted to the other columns type before the compare, it
> > really
> > > demonstrates nothing.
> > >
> > > It could perhaps be used to help demonstrate that when comparing a
> > > datatype of CHAR to VARCHAR that
> > > MS-SQL converts the VARCHAR to CHAR and then does the compare
> > > Postgres converts the CHAR to VARCHAR and then does the compare
> > >
> > > But there isn't even enough evidence here to support that.
> > >
> > > Terry
> > >
> > > Dann Corbit wrote:
> > > > create table foo (col1 varchar(30))
> > > > go
> > > > create table bar (col1 char(30))
> > > > go
> > > > insert into foo values ('Danniel ')
> > > > go
> > > > insert into bar values ('Danniel   ')
> > > > go
> > > > select * from foo,bar where foo.col1=bar.col1
> > > > go
> > > >
> > > > Result set:
> > > > Danniel    Danniel
> > > >
> > > >
> > > >>-----Original Message-----
> > > >>From: Terry Fielder [mailto:terry@ashtonwoodshomes.com]
> > > >>Sent: Wednesday, October 19, 2005 1:39 PM
> > > >>To: Dann Corbit
> > > >>Cc: Marc G. Fournier; Richard_D_Levine@raytheon.com; pgsql-
> > > >>general@postgresql.org
> > > >>Subject: Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
> > > >>
> > > >>I agree with you, but...
> > > >>
> > > >>Actually that's not how the compare works usually.
> > > >>
> > > >>Generally one of the operands is converted to the same datatype
as
> > the
> > > >>other, and THEN the compare is performed.
> > > >>
> > > >>I expect MS SQL is converting a 'sdas' typeless string to be
assumed
> > > >>CHAR and Postgresql is converting a 'sdas' typeless string to be
> > > >
> > > > assumed
> > > >
> > > >>VARCHAR.
> > > >>
> > > >>Hence, the different behaviour.
> > > >>
> > > >>Terry
> > > >>
> > > >>Dann Corbit wrote:
> > > >>
> > > >>>Would you want varchar(30) 'Dann Corbit' to compare equal to
> > > >
> > > > bpchar(30)
> > > >
> > > >>>'Dann Corbit'?
> > > >>>
> > > >>>I would.
> > > >>>
> > > >>>If both are considered character types by the language, then
they
> > > >
> > > > must
> > > >
> > > >>>compare that way.
> > > >>>
> > > >>>Perhaps there are some nuances that I am not aware of.  But
that is
> > > >
> > > > how
> > > >
> > > >>>things ought to behave, if I were king of the forest.
> > > >>>
> > > >>>
> > > >>>
> > > >>>>-----Original Message-----
> > > >>>>From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-
> > > >>>>owner@postgresql.org] On Behalf Of Terry Fielder
> > > >>>>Sent: Wednesday, October 19, 2005 12:37 PM
> > > >>>>To: Marc G. Fournier
> > > >>>>Cc: Richard_D_Levine@raytheon.com;
pgsql-general@postgresql.org
> > > >>>>Subject: Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
> > > >>>>
> > > >>>>OK, I am not an expert on the SQL standard, but I thought the
> > > >>>
> > > >>>definition
> > > >>>
> > > >>>
> > > >>>>varied by data type e.g. varchar <> bpchar
> > > >>>>
> > > >>>>Terry
> > > >>>>
> > > >>>>Marc G. Fournier wrote:
> > > >>>>
> > > >>>>
> > > >>>>>On Wed, 19 Oct 2005, Richard_D_Levine@raytheon.com wrote:
> > > >>>>>
> > > >>>>>
> > > >>>>>
> > > >>>>>>I was referring to trailing blanks, but did not explicitly
say
> > it,
> > > >>>>>>though showed it in the examples.  I am pretty sure that the
SQL
> > > >>>>>>standard says that trailing whitespace is insignificant in
> > string
> > > >>>>>>comparison.
> > > >>>>>
> > > >>>>>
> > > >>>>>Then we are broken too :)
> > > >>>>>
> > > >>>>># select 'a ' = 'a  ';
> > > >>>>>?column?
> > > >>>>>----------
> > > >>>>>f
> > > >>>>>(1 row)
> > > >>>>>
> > > >>>>>----
> > > >>>>>Marc G. Fournier           Hub.Org Networking Services
> > > >>>>
> > > >>>>(http://www.hub.org)
> > > >>>>
> > > >>>>
> > > >>>>>Email: scrappy@hub.org           Yahoo!: yscrappy
> > ICQ:
> > > >>>>
> > > >>>>7615664
> > > >>>>
> > > >>>>
> > > >>>>>---------------------------(end of
> > > >>>
> > > >>>broadcast)---------------------------
> > > >>>
> > > >>>
> > > >>>>>TIP 4: Have you searched our list archives?
> > > >>>>>
> > > >>>>>             http://archives.postgresql.org
> > > >>>>>
> > > >>>>
> > > >>>>--
> > > >>>>Terry Fielder
> > > >>>>terry@greatgulfhomes.com
> > > >>>>Associate Director Software Development and Deployment
> > > >>>>Great Gulf Homes / Ashton Woods Homes
> > > >>>>Fax: (416) 441-9085
> > > >>>>
> > > >>>>---------------------------(end of
> > > >>>
> > > >>>broadcast)---------------------------
> > > >>>
> > > >>>
> > > >>>>TIP 6: explain analyze is your friend
> > > >>>
> > > >>>
> > > >>--
> > > >>Terry Fielder
> > > >>terry@greatgulfhomes.com
> > > >>Associate Director Software Development and Deployment
> > > >>Great Gulf Homes / Ashton Woods Homes
> > > >>Fax: (416) 441-9085
> > > >
> > > >
> > >
> > > --
> > > Terry Fielder
> > > terry@greatgulfhomes.com
> > > Associate Director Software Development and Deployment
> > > Great Gulf Homes / Ashton Woods Homes
> > > Fax: (416) 441-9085
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings