"Dann Corbit" <DCorbit@xxxxxxxxx> 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@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 6: explain analyze is your friend
