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@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Richard_D_Levine@xxxxxxxxxxxx > Sent: Wednesday, October 19, 2005 2:07 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: [pgsql-advocacy] 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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxx] > > > Sent: Wednesday, October 19, 2005 2:02 PM > > > To: Dann Corbit > > > Cc: Marc G. Fournier; Richard_D_Levine@xxxxxxxxxxxx; pgsql- > > > general@xxxxxxxxxxxxxx > > > Subject: Re: [pgsql-advocacy] 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@xxxxxxxxxxxxxxxxxxxx] > > > >>Sent: Wednesday, October 19, 2005 1:39 PM > > > >>To: Dann Corbit > > > >>Cc: Marc G. Fournier; Richard_D_Levine@xxxxxxxxxxxx; pgsql- > > > >>general@xxxxxxxxxxxxxx > > > >>Subject: Re: [pgsql-advocacy] 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@xxxxxxxxxxxxxx [mailto:pgsql-general- > > > >>>>owner@xxxxxxxxxxxxxx] On Behalf Of Terry Fielder > > > >>>>Sent: Wednesday, October 19, 2005 12:37 PM > > > >>>>To: Marc G. Fournier > > > >>>>Cc: Richard_D_Levine@xxxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx > > > >>>>Subject: Re: [pgsql-advocacy] 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@xxxxxxxxxxxx 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@xxxxxxx Yahoo!: yscrappy > > ICQ: > > > >>>> > > > >>>>7615664 > > > >>>> > > > >>>> > > > >>>>>---------------------------(end of > > > >>> > > > >>>broadcast)--------------------------- > > > >>> > > > >>> > > > >>>>>TIP 4: Have you searched our list archives? > > > >>>>> > > > >>>>> http://archives.postgresql.org > > > >>>>> > > > >>>> > > > >>>>-- > > > >>>>Terry Fielder > > > >>>>terry@xxxxxxxxxxxxxxxxxx > > > >>>>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@xxxxxxxxxxxxxxxxxx > > > >>Associate Director Software Development and Deployment > > > >>Great Gulf Homes / Ashton Woods Homes > > > >>Fax: (416) 441-9085 > > > > > > > > > > > > > > -- > > > Terry Fielder > > > terry@xxxxxxxxxxxxxxxxxx > > > 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
