Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <621871454.1229468085034.JavaMail.jira@brutus>
Date: Tue, 16 Dec 2008 14:54:45 -0800 (PST)
From: "Knut Anders Hatlen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-3975) SELECT DISTINCT may return duplicates
 with territory-based collation
In-Reply-To: <4724974.1228495124259.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


     [ https://issues.apache.org/jira/browse/DERBY-3975?page=3Dcom.atlassia=
n.jira.plugin.system.issuetabpanels:all-tabpanel ]

Knut Anders Hatlen updated DERBY-3975:
--------------------------------------

    Attachment: derby-3975-1a.stat
                derby-3975-1a.diff

The attached patch attempts to fix the problem by implementing a hashCode()=
 method in CollatorSQLChar, CollatorSQLVarchar, CollatorSQLLongvarchar and =
CollatorSQLClob based on CollationKey.hashCode(). It also extends Collation=
Test.compareAgrave() with a test case for SELECT DISTINCT, and makes it tes=
t both CHAR and VARCHAR (previously it only tested VARCHAR). CollationTest =
fails without the fix and passes with the fix. The test is based on the fac=
t that in the French locale, =C3=80 (Unicode code point 00C0) is the same a=
s A=CC=80 ('A' + Unicode code point 0300), whereas they are different in UC=
S_BASIC.

I will start the regression tests now.

> SELECT DISTINCT may return duplicates with territory-based collation
> --------------------------------------------------------------------
>
>                 Key: DERBY-3975
>                 URL: https://issues.apache.org/jira/browse/DERBY-3975
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.2.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Knut Anders Hatlen
>         Attachments: AisBCollatorProvider.java, derby-3975-1a.diff, derby=
-3975-1a.stat
>
>
> I defined my own locale (en_US_aisb) where the collation rules said that =
a=3Db. When I tried queries with SELECT DISTINCT, they didn't always elimin=
ate all duplicates. Here's an example:
> ij> connect 'jdbc:derby:db;create=3Dtrue;territory=3Den_US_aisb;collation=
=3DTERRITORY_BASED';
> ij> create table t (x varchar(10));
> 0 rows inserted/updated/deleted
> ij> insert into t values 'a','b','abba','baab','ABBA';
> 5 rows inserted/updated/deleted
> ij> select distinct * from t;
> X        =20
> ----------
> ABBA     =20
> b        =20
> a        =20
> abba     =20
> 4 rows selected
> ij> select distinct * from t order by x;
> X        =20
> ----------
> a        =20
> abba     =20
> ABBA     =20
> 3 rows selected
> The first query did eliminate the duplicate "abba"/"baab", but it did not=
 eliminate the duplicate "a"/"b". When an ORDER BY clause was added (the se=
cond query), all the duplicates were eliminated.

--=20
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.