Re: [pgsql-advocacy] Oracle buys Innobase

I'm CC'ng this over to -hackers ... Tom? Comments?

On Wed, 19 Oct 2005, Dann Corbit wrote:

Yes, clearly that is the wrong result according to the SQL standard.

Here is a SQL*Server query:
select 1 where 'a' = 'a ' AND 'a' = 'a ' AND 'a ' = 'a '

It returns (correctly): 1

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Marc G. Fournier
Sent: Wednesday, October 19, 2005 11:41 AM
To: Richard_D_Levine@raytheon.com
Cc: pgsql-general@postgresql.org
Subject: Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase

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

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664

On Wed, 19 Oct 2005, Dann Corbit wrote:

Yes, clearly that is the wrong result according to the SQL standard.

Here is a SQL*Server query:
select 1 where 'a' = 'a ' AND 'a' = 'a ' AND 'a ' = 'a '

It returns (correctly): 1

Doesn't that depend on the collating sequence in use, or is a NO PAD
collating sequence not allowed here?

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Wednesday, October 19, 2005 12: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

On Wed, 19 Oct 2005, Dann Corbit wrote:

Yes, clearly that is the wrong result according to the SQL standard.

Here is a SQL*Server query:
select 1 where 'a' = 'a ' AND 'a' = 'a ' AND 'a ' = 'a '

It returns (correctly): 1

Doesn't that depend on the collating sequence in use, or is a NO PAD
collating sequence not allowed here?

If the implementation defines constants as NO PAD and the implementation
defined pad character is something other than space, then they could
compare unequal.

I would find that implementation disturbing. But I am easily bent out
of shape.

The attached HTML file in my earlier post is the official quote from the
SQL 99 standard. That is the formal and correct definition, far
superior to my off the cuff approximations.

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)---------------------------

On Wed, 19 Oct 2005, Dann Corbit wrote:

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Wednesday, October 19, 2005 12: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

On Wed, 19 Oct 2005, Dann Corbit wrote:

Yes, clearly that is the wrong result according to the SQL standard.

Here is a SQL*Server query:
select 1 where 'a' = 'a ' AND 'a' = 'a ' AND 'a ' = 'a '

It returns (correctly): 1

Doesn't that depend on the collating sequence in use, or is a NO PAD
collating sequence not allowed here?

If the implementation defines constants as NO PAD and the implementation
defined pad character is something other than space, then they could
compare unequal.

I would find that implementation disturbing. But I am easily bent out
of shape.

The attached HTML file in my earlier post is the official quote from the
SQL 99 standard. That is the formal and correct definition, far
superior to my off the cuff approximations.

'k, if I'm reading the right section (you say its bolded, but I'm using
pine which doesn't seem to do a good job of reading HTML):

===========
d) Depending on the collating sequence, two strings may compare as
equal even if they are of different lengths or contain different
sequences of characters. When any of the operations MAX, MIN, and
DISTINCT reference a grouping column, and the UNION, EXCEPT, and
INTERSECT operators refer to character strings, the specific value
selected by these operations from a set of such equal values is
implementation-dependent.
===========

I think the key part of that 'clause' is "two strings *may* compare as
equal" ... sounds implementation dependent to me, depending on how the
implementor interprets it ... or am I reading the wrong section?

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664

Given this part of that same rule applied to the strings:
"b) If the length in characters of X is not equal to the length in
characters of Y, then the shorter string is effectively replaced, for
the purposes of comparison, with a copy of itself that has been extended
to the length of the longer string by concatenation on the right of one
or more pad characters, where the pad character is chosen based on CS.
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.
Otherwise, the pad character is a <space>."

I do not see how two strings which are otherwise equal (except for
length or blank padding) can possibly compare unequal unless the NO PAD
property is applied and the implementation defined pad character is also
something other than a <space>. Is that the case for PostgreSQL? Even
if it is, is seems truly bizarre that the NO PAD attribute would be
applied to string constants.

-----Original Message-----
From: Marc G. Fournier [mailto:scrappy@postgresql.org]
Sent: Wednesday, October 19, 2005 12:53 PM
To: Dann Corbit
Cc: Stephan Szabo; Marc G. Fournier; Richard_D_Levine@raytheon.com;

pgsql-

hackers@postgresql.org; pgsql-general@postgresql.org
Subject: RE: [pgsql-advocacy] [GENERAL] Oracle buys Innobase

On Wed, 19 Oct 2005, Dann Corbit wrote:

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Wednesday, October 19, 2005 12: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

On Wed, 19 Oct 2005, Dann Corbit wrote:

Yes, clearly that is the wrong result according to the SQL

standard.

Here is a SQL*Server query:
select 1 where 'a' = 'a ' AND 'a' = 'a ' AND 'a ' = 'a '

It returns (correctly): 1

Doesn't that depend on the collating sequence in use, or is a NO

PAD

collating sequence not allowed here?

If the implementation defines constants as NO PAD and the

implementation

defined pad character is something other than space, then they could
compare unequal.

I would find that implementation disturbing. But I am easily bent

out

of shape.

The attached HTML file in my earlier post is the official quote from

the

SQL 99 standard. That is the formal and correct definition, far
superior to my off the cuff approximations.

'k, if I'm reading the right section (you say its bolded, but I'm

using

Am Mittwoch, den 19.10.2005, 16:29 -0300 schrieb Marc G. Fournier:

I'm CC'ng this over to -hackers ... Tom? Comments?

On Wed, 19 Oct 2005, Dann Corbit wrote:

Yes, clearly that is the wrong result according to the SQL standard.

Here is a SQL*Server query:
select 1 where 'a' = 'a ' AND 'a' = 'a ' AND 'a ' = 'a '

It returns (correctly): 1

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Marc G. Fournier
Sent: Wednesday, October 19, 2005 11:41 AM
To: Richard_D_Levine@raytheon.com
Cc: pgsql-general@postgresql.org
Subject: Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase

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)

experiment=# SELECT 'a '::char = 'a '::char;
?column?
----------
t

Tino Wildenhain wrote:

Then we are broken too :)

# select 'a ' = 'a ';
?column?
----------
f
(1 row)

experiment=# SELECT 'a '::char = 'a '::char;
?column?
----------
t

This does't show anything useful, because the ::char casting simply
takes the first char of any string:

select 'abc'::char = 'axy'::char

Also results in 'true'. Hopefully no one in this discussion would want
those two strings to be equal.

--
Guy Rouillier

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

"Guy Rouillier" <guyr@masergy.com> writes:

Tino Wildenhain wrote:

experiment=# SELECT 'a '::char = 'a '::char;
?column?
----------
t

This does't show anything useful, because the ::char casting simply
takes the first char of any string:

select 'abc'::char = 'axy'::char

Also results in 'true'. Hopefully no one in this discussion would want
those two strings to be equal.

A better experiment:

db=> select 'a '::char(3) = 'a '::char(3);
?column?
----------
t
(1 row)

--
greg

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:

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:

Doug Quale wrote:

"Marc G. Fournier" <scrappy@postgresql.org> writes:

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)

# select 'a'::char(8) = 'a '::char(8);
?column?
----------
t
(1 row)

Trailing blanks aren't significant in fixed-length strings, so the
question is whether Postgresql treats comparison of varchars right.

This result is being misinterpreted.

select length('a'::char(8)) ==> 1
select length('a '::char(8)) ==> 1

So it isn't that the two different strings are comparing equal. The
process of casting them to char(8) is trimming the blanks, so by the
time they become fixed length strings, they are indeed equal.

Addressing Dan's subsequent note here, I tried this in Oracle:

select length(cast('a ' as varchar2(30))) from dual ==> 2

So Oracle does not trim when it casts. The subject of whether casting
should trim trailing blanks is yet another tangential discussion. I
would say that if the user hasn't specifically asked for a trim, it
shouldn't happen, but I have not read the standard on this.

We're all discussing these intricacies from a comp sci perspective.

From a normal user's perspective, of course, they would prefer the DBMS

to hide these types of nuances.

--
Guy Rouillier

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

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@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Richard_D_Levine@raytheon.com
Sent: Wednesday, October 19, 2005 2:07 PM
To: pgsql-general@postgresql.org
Subject: Re: [pgsql-advocacy] [GENERAL] 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@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)---------------------------

"Dann Corbit" <DCorbit@connx.com> 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.

On 10/19/2005 3:46 PM, Dann Corbit wrote:

Would you want varchar(30) 'Dann Corbit' to compare equal to bpchar(30)
'Dann Corbit'?

I would.

wieck=# select 'Jan'::varchar(20) = 'Jan'::char(20);
?column?
----------
t
(1 row)

wieck=# select 'Jan'::char(20) = 'Jan'::varchar(20);
?column?
----------
t
(1 row)

wieck=# select version();
version
---------------------------------------------------------------------
PostgreSQL 8.0.4 on i386-unknown-freebsd4.9, compiled by GCC 2.95.4
(1 row)

Did I miss anything?

Jan

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

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

Dann Corbit wrote:

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.

Sounds locale-specific then... So then the real question is what to
extent the implimentation has free reign regarding which locales use
which collation sequences....

Best Wishes,
Chris Travers
Metatron Technology Consulting