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:[EMAIL PROTECTED]
Sent: Wednesday, October 19, 2005 12:53 PM
To: Dann Corbit
Cc: Stephan Szabo; Marc G. Fournier; [EMAIL PROTECTED];
pgsql-
[EMAIL PROTECTED]; 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:[EMAIL PROTECTED]
Sent: Wednesday, October 19, 2005 12:39 PM
To: Dann Corbit
Cc: Marc G. Fournier; [EMAIL PROTECTED]; pgsql-
[EMAIL PROTECTED]
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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ:
7615664
---(end of broadcast)---
TIP 6: explain analyze is your friend
