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.

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

"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

Am Mittwoch, den 19.10.2005, 22:04 +0200 schrieb Tino Wildenhain:

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

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

...

Then we are broken too :)

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

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

Sorry, copied wrong line :)

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

and:

SELECT '|' || 'foo '::char(10) || '|';
?column?
----------
|foo|

vs.

SELECT '|' || 'foo ' || '|';
?column?
----------
|foo |

Dann Corbit wrote:

Try this query in Oracle, SQL*Server, DB/2, Informix, etc.:

connxdatasync=# select 1 where cast('a' as varchar(30)) = cast('a ' as
varchar(30));
?column?
----------
(0 rows)

For what it's worth, on Sybase ASE I get:

-----------
1
(1 row affected)