Re: [pgsql-advocacy] Oracle buys Innobase

Converting '   ' to '', well, that depends on the definition of the
datatype PAD/NOPAD ad nasuem.

Converting '' to NULL, that's just wrong, and here's some examples of why:

In oracle, there is no easy way to determine the difference between
"there is no value" and "the value the user entered was the empty
string".  It's as simple as that.

Virtually any other database, NULL means "not defined" aka "absence of
value".  Whereas '' means "a string of zero characters"

Bah humbug, you may say.  But consider, should:
rtrim('   ') = ''

The answer is simple: YES, the 2 are equal.

Oracle has always had the '' ==> NULL flaw
And it may have been to compensate for that flaw that they added:
'   ' ==> NULL flaw
Although it may be in the background that what is really happening is:
'   ' ==> '' ==> NULL

Guess mommy Oracle forgot to mention that 2 wrongs don't make a right.  :)


Terry

Richard_D_Levine@raytheon.com wrote:
> 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@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 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

--
Terry Fielder
terry@greatgulfhomes.com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085