Re: [pgsql-advocacy] Oracle buys Innobase

Okay, since the standard explicitly says that whether 'a' = 'a ' is a
well-defined characteristic of a character datatype (NO PAD) I'm happy with
both Oracle and PostgreSQL. If you want a certain behavior, choose your
datatypes wisely. Cool. I didn't in a recent port. Uncool. I went from
CHAR() in Interbase to VARCHAR2() in Oracle. I shot myself in the foot,
and then complained about it before understanding the standard. I'm now
better educated, thanks to all.

But, I still need to research the conditions under which Oracle converts ''
(zero length string) and ' ' (all blank string) to NULL. Then, before
complaining about it, I'll read the standard again. Since everybody
complains about it, I can't believe it is standard, but I have (very
recently) been wrong before.

Cheers,

Rick

Richard D Levine/US/Raytheon wrote on 10/19/2005 04:07:03 PM:

> 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(at)connx(dot)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(at)ashtonwoodshomes(dot)com]
> > > Sent: Wednesday, October 19, 2005 2:02 PM
> > > To: Dann Corbit
> > > Cc: Marc G. Fournier; Richard_D_Levine(at)raytheon(dot)com; pgsql-
> > > general(at)postgresql(dot)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(at)ashtonwoodshomes(dot)com]
> > > >>Sent: Wednesday, October 19, 2005 1:39 PM
> > > >>To: Dann Corbit
> > > >>Cc: Marc G. Fournier; Richard_D_Levine(at)raytheon(dot)com; pgsql-
> > > >>general(at)postgresql(dot)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(at)postgresql(dot)org [mailto:pgsql-general-
> > > >>>>owner(at)postgresql(dot)org] On Behalf Of Terry Fielder
> > > >>>>Sent: Wednesday, October 19, 2005 12:37 PM
> > > >>>>To: Marc G. Fournier
> > > >>>>Cc: Richard_D_Levine(at)raytheon(dot)com; pgsql-general(at)postgresql(dot)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(at)raytheon(dot)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(at)hub(dot)org Yahoo!: yscrappy
> > ICQ:
> > > >>>>
> > > >>>>7615664
> > > >>>>
> > > >>>>
> > > >>>>>---------------------------(end of
> > > >>>
> > > >>>broadcast)---------------------------
> > > >>>
> > > >>>
> > > >>>>>TIP 4: Have you searched our list archives?
> > > >>>>>
> > > >>>>> http://archives.postgresql.org
> > > >>>>>
> > > >>>>
> > > >>>>--
> > > >>>>Terry Fielder
> > > >>>>terry(at)greatgulfhomes(dot)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(at)greatgulfhomes(dot)com
> > > >>Associate Director Software Development and Deployment
> > > >>Great Gulf Homes / Ashton Woods Homes
> > > >>Fax: (416) 441-9085
> > > >
> > > >
> > >
> > > --
> > > Terry Fielder
> > > terry(at)greatgulfhomes(dot)com
> > > Associate Director Software Development and Deployment
> > > Great Gulf Homes / Ashton Woods Homes
> > > Fax: (416) 441-9085