Terry Fielder wrote:
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.
I actually saw someone argue once that Oracle got this one right. I was
greatly saddened when I read that argument. It is one thing to see
MySQL AB saying that their incompetence is right, but with Oracle users,
it is much more discouraging because you really think they should know
better...
Virtually any other database, NULL means "not defined" aka "absence of
value". Whereas '' means "a string of zero characters"
Oracle is wrong here for a more basic reason than you mention though you
seem to touch on it. They are wrong for basic reasons of mathematics
and the basic concepts of data logic inherent in the SQL
quasi-relational model.
Basically, everything in SQL is dependant on a trinary evaluation:
TRUE, FALSE, or Unknown (i.e. NULL). If we know what a value is, it is
not unknown... Indeed the only justification for this braindead
behavior is that it makes it easy to get reasonable data out of
braindead applications (ones that insert empty strings instead of NULL
values).
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@xxxxxxxxxxxx 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@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 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
