[
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.
