Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <30855396.1179474196172.JavaMail.jira@brutus>
Date: Fri, 18 May 2007 00:43:16 -0700 (PDT)
From: "Mike Matrigali (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-2377) Document language based ordering which
 will be implemented by code related sub-tasks of DERBY-1478.
In-Reply-To: <12099102.1172592065504.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-2377?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mike Matrigali updated DERBY-2377:
----------------------------------


looked at latest html.

The 3rd line about setting collation with no territory is interesting, I am not sure what the system should do.  mamta and I need to figure out what the system should do.  Seems like we should at least print a warning if we are not going to have basic collation in this case.

The last table is confusing to me.  What are you trying to say by listing the statements?  Also personally I would find it easier to
understand if you just grouped CHAR and VARCHAR together, and CLOB and LONG VARCHAR together.  I found myself trying 
to figure out what is different between CHAR and VARCHAR - when the anser is none.  Collation can also affect any statement that can have a comparison operator  (ie. select, update, delete).  

For me, I found it easiest to understand with a few simple examples.  Using basic vs english locale , I found the following 
explanation useful.  

In basic collation the numerical values of the unicode encoding of the characters is used directly for ordering.  So given a 
dataset such as:  CREATE TABLE  FRUIT (NAME VARCHAR(20))

apple
orange
Banana
Grape

In default collation where all lower case letters sort before upper case letters, SELECT * FROM FRUIT ORDER BY NAME gives:
apple
orange
Banana
Grape

In english teritory, ordering is different.  So in database created with english territory and territory based collation, the same 
query: SELECT * FROM FRUIT ORDER BY NAME gives:
apple 
Banana
Grape
orange

The collation also will affect comparison operators on the character datatypes.  So in default:
SELECT * FROM FRUIT WHERE NAME > Banana
Grape

But in the the english territory, territory based collation
SELECT * FROM FRUIT WHERE NAME > Banana
Grape
orange

TODO - provide LIKE example using norway example from kathy, a like query looking for _ should match aa:

I think ascii is the same as the default except for my favorite norwegian character 'aa' which is one character and sorts after z in Norway and is two and sorts before z in the default encoding.

ij> connect 'jdbc:derby:wombat;create=true;collation=TERRITORY_BASED;territory=no';
WARNING 01J01: Database 'wombat' not created, connection made to existing database instead.
ij> create table t (vc varchar(30));
0 rows inserted/updated/deleted
ij> insert into t values('acorn');
1 row inserted/updated/deleted
ij> insert into t values('aacorn');
1 row inserted/updated/deleted
ij> insert into t values('zebra');
1 row inserted/updated/deleted
ij> select * from t order by vc;
VC
------------------------------
acorn
zebra
aacorn


> Document language based ordering which will be implemented by code related sub-tasks of DERBY-1478.
> ---------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-2377
>                 URL: https://issues.apache.org/jira/browse/DERBY-2377
>             Project: Derby
>          Issue Type: Sub-task
>          Components: Documentation
>    Affects Versions: 10.3.0.0
>            Reporter: Mamta A. Satoor
>         Assigned To: Laura Stewart
>         Attachments: cdevcollation.html, cdevdvlp51654.html, rrefattribcollation.html, tdevdvlpcollation.html
>
>
> There is an effort going on to implement language based ordering. This will be implemented by various code-related subtasks of DERBY-1478. The functional spec is attached to DERBY-1478. This feature will need accompanying documentation changes and this Jira entry is to track that work.

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