Re: [pgsql-advocacy] Oracle buys Innobase

"Dann Corbit" <DCorbit@connx.com> wrote on 10/19/2005 04:33:23 PM:

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

Yep.  Back to the mental drawing board.

BTW, the ordering of posts when they come to my mailbox is really weird.

>
> 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