Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
Message-ID: <4EEA31E1.5080505@oracle.com>
Date: Thu, 15 Dec 2011 09:44:01 -0800
From: Rick Hillegas <rick.hillegas@oracle.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US;
 rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
To: "derby-dev@db.apache.org" <derby-dev@db.apache.org>
Subject: some questions about collations
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I have a couple questions about collations in Derby. As I understand it, 
Derby supports two collations: UCS_BASIC and TERRITORY_BASED.

By default, all string types have UCS_BASIC collation and the string 
columns in system catalogs always have UCS_BASIC collation regardless of 
whether user tables use a different collation.

If the database is created with the collation=TERRITORY_BASED attribute, 
then all string types created by users have TERRITORY_BASED collation. 
This includes the types of string columns in user tables, string args in 
user routines, and string constants used in queries.

I believe that the following two statements are true. I will explain why 
shortly. Please let me know if you think that I am missing something.

1) Although you can cast a UCS_BASIC string to a TERRITORY_BASED string, 
you can't perform the reverse cast.

2) In a database which was created with TERRITORY_BASED collation, that 
is the collation of the string return types and args of all system 
procedures and functions.

I believe (1) because I can't find any examples of how to perform the 
reverse cast. I don't see any examples in CollationTest and 
CollationTest2. The <COLLATION> and <COLLATE> tokens are not actually 
used anywhere in Derby's SQL grammar.

I believe (2) because that is the behavior of the sample procedures and 
functions which I ran: syscs_util.syscs_check_table, 
syscs_util.syscs_set_database_property, and 
syscs_util.syscs_get_database_property. I haven't systematically tested 
all of Derby's system routines, but it seems likely to me that they all 
behave the same way.

I think that this current behavior is reasonable. In particular, I don't 
think that we should use UCS_BASIC collation for the string return types 
and args of system routines. That would make the routines very awkward 
to use.

Am I tracking? Does this summary sound correct?

Thanks,
-Rick