You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Rick Hillegas <ri...@oracle.com> on 2011/12/15 18:44:01 UTC
some questions about collations
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
Re: some questions about collations
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Rick,
Yes, I think the script provided by you exhibites Derby behavior.
thanks,
Mamta
On Fri, Dec 16, 2011 at 12:58 PM, Rick Hillegas
<ri...@oracle.com> wrote:
> Thanks, Mamta. That was very helpful. I think I am beginning to understand
> this better. At the end of this message I include a script which I believe
> demonstrates the following:
>
> 1) The collation of string literals changes depending on your compilation
> schema.
>
> 2) However, the collation of columns, routine args, and routine return types
> is invariant, regardless of your compilation schema.
>
> 3) Collation is only consulted when comparing two string values. The values
> must have the same collation.
>
> 4) Collation is unimportant when passing a string value as a routine
> argument or inserting a string value into a column. The string value and the
> argument type can have different collations.
>
> Thanks,
> -Rick
>
> connect
> 'jdbc:derby:memory:db;create=true;collation=TERRITORY_BASED;user=test_dbo;password=test_dbopassword';
>
> create table t( a varchar( 128 ) );
> create function f( a varchar( 128 ) ) returns int language java parameter
> style java no sql external name 'java.lang.Integer.parseInt';
>
> --
> -- Test return types of system function in territory based schema
> --
>
> -- fails because 'foo' is territory based but the function returns a
> UCS_BASIC value
> select a from test_dbo.t where 'foo' =
> syscs_util.syscs_get_database_property( 'derby.database.collation' );
>
> -- succeeds because the column and function return value are both UCS_BASIC
> select tablename from sys.systables where tablename =
> syscs_util.syscs_get_database_property( 'derby.database.collation' );
>
> -- fails because the column is territory based but the return value is
> UCS_BASIC
> select a from test_dbo.t where a = syscs_util.syscs_get_database_property(
> 'derby.database.collation' );
>
> --
> -- Test argument types of system function in territory based schema
> --
> --
> -- All of these succeed. Collation of argument type doesn't matter.
> --
> select a from test_dbo.t where syscs_util.syscs_get_database_property( a )
> is not null;
> select tablename from sys.systables where
> syscs_util.syscs_get_database_property( tablename ) is not null;
> select a from test_dbo.t where syscs_util.syscs_get_database_property(
> 'derby.database.collation' ) is null;
>
> --
> -- Test argument types of user function in territory based schema
> --
> --
> -- All of these succeed. Collation of argument type doesn't matter.
> --
> select a from test_dbo.t where test_dbo.f( '1' ) is null;
> select tablename from sys.systables where tablename is null and test_dbo.f(
> tablename ) is null;
> select a from test_dbo.t where a is null and test_dbo.f( a ) is null;
>
> --
> -- Test insertions into territory based column in territory based schema
> --
> -- All of these succeed. Collation of inserted string doesn't matter.
> --
> insert into test_dbo.t( a ) values ( 'foo' );
> insert into test_dbo.t( a ) select tablename from sys.systables;
> insert into test_dbo.t( a ) select a from t;
>
> set schema sys;
>
> --
> -- Test return types of system function in UCS_BASIC schema
> --
>
> -- succeeds because the string and return value are both UCS_BASIC
> select a from test_dbo.t where 'foo' =
> syscs_util.syscs_get_database_property( 'derby.database.collation' );
>
> -- succeeds because the column and return value are both UCS_BASIC
> select tablename from sys.systables where tablename =
> syscs_util.syscs_get_database_property( 'derby.database.collation' );
>
> -- fails because the column is territory based but the return value is
> UCS_BASIC
> select a from test_dbo.t where a = syscs_util.syscs_get_database_property(
> 'derby.database.collation' );
>
> --
> -- Test argument types of system function in UCS_BASIC schema
> --
> --
> -- All of these succeed. Collation of argument type doesn't matter.
> --
> select a from test_dbo.t where syscs_util.syscs_get_database_property( a )
> is not null;
> select tablename from sys.systables where
> syscs_util.syscs_get_database_property( tablename ) is not null;
> select a from test_dbo.t where syscs_util.syscs_get_database_property(
> 'derby.database.collation' ) is null;
>
> --
> -- Test argument types of user function in UCS_BASIC schema
> --
> --
> -- All of these succeed. Collation of argument type doesn't matter.
> --
> select a from test_dbo.t where test_dbo.f( '1' ) is null;
> select tablename from sys.systables where tablename is null and test_dbo.f(
> tablename ) is null;
> select a from test_dbo.t where a is null and test_dbo.f( a ) is null;
>
> --
> -- Test insertions into territory based column in UCS_BASIC schema
> --
> --
> -- All of these succeed. Collation of inserted string doesn't matter.
> --
> insert into test_dbo.t( a ) values ( 'foo' );
> insert into test_dbo.t( a ) select tablename from sys.systables;
> insert into test_dbo.t( a ) select a from test_dbo.t;
>
Re: some questions about collations
Posted by Rick Hillegas <ri...@oracle.com>.
Thanks, Mamta. That was very helpful. I think I am beginning to
understand this better. At the end of this message I include a script
which I believe demonstrates the following:
1) The collation of string literals changes depending on your
compilation schema.
2) However, the collation of columns, routine args, and routine return
types is invariant, regardless of your compilation schema.
3) Collation is only consulted when comparing two string values. The
values must have the same collation.
4) Collation is unimportant when passing a string value as a routine
argument or inserting a string value into a column. The string value and
the argument type can have different collations.
Thanks,
-Rick
connect
'jdbc:derby:memory:db;create=true;collation=TERRITORY_BASED;user=test_dbo;password=test_dbopassword';
create table t( a varchar( 128 ) );
create function f( a varchar( 128 ) ) returns int language java
parameter style java no sql external name 'java.lang.Integer.parseInt';
--
-- Test return types of system function in territory based schema
--
-- fails because 'foo' is territory based but the function returns a
UCS_BASIC value
select a from test_dbo.t where 'foo' =
syscs_util.syscs_get_database_property( 'derby.database.collation' );
-- succeeds because the column and function return value are both UCS_BASIC
select tablename from sys.systables where tablename =
syscs_util.syscs_get_database_property( 'derby.database.collation' );
-- fails because the column is territory based but the return value is
UCS_BASIC
select a from test_dbo.t where a =
syscs_util.syscs_get_database_property( 'derby.database.collation' );
--
-- Test argument types of system function in territory based schema
--
--
-- All of these succeed. Collation of argument type doesn't matter.
--
select a from test_dbo.t where syscs_util.syscs_get_database_property( a
) is not null;
select tablename from sys.systables where
syscs_util.syscs_get_database_property( tablename ) is not null;
select a from test_dbo.t where syscs_util.syscs_get_database_property(
'derby.database.collation' ) is null;
--
-- Test argument types of user function in territory based schema
--
--
-- All of these succeed. Collation of argument type doesn't matter.
--
select a from test_dbo.t where test_dbo.f( '1' ) is null;
select tablename from sys.systables where tablename is null and
test_dbo.f( tablename ) is null;
select a from test_dbo.t where a is null and test_dbo.f( a ) is null;
--
-- Test insertions into territory based column in territory based schema
--
-- All of these succeed. Collation of inserted string doesn't matter.
--
insert into test_dbo.t( a ) values ( 'foo' );
insert into test_dbo.t( a ) select tablename from sys.systables;
insert into test_dbo.t( a ) select a from t;
set schema sys;
--
-- Test return types of system function in UCS_BASIC schema
--
-- succeeds because the string and return value are both UCS_BASIC
select a from test_dbo.t where 'foo' =
syscs_util.syscs_get_database_property( 'derby.database.collation' );
-- succeeds because the column and return value are both UCS_BASIC
select tablename from sys.systables where tablename =
syscs_util.syscs_get_database_property( 'derby.database.collation' );
-- fails because the column is territory based but the return value is
UCS_BASIC
select a from test_dbo.t where a =
syscs_util.syscs_get_database_property( 'derby.database.collation' );
--
-- Test argument types of system function in UCS_BASIC schema
--
--
-- All of these succeed. Collation of argument type doesn't matter.
--
select a from test_dbo.t where syscs_util.syscs_get_database_property( a
) is not null;
select tablename from sys.systables where
syscs_util.syscs_get_database_property( tablename ) is not null;
select a from test_dbo.t where syscs_util.syscs_get_database_property(
'derby.database.collation' ) is null;
--
-- Test argument types of user function in UCS_BASIC schema
--
--
-- All of these succeed. Collation of argument type doesn't matter.
--
select a from test_dbo.t where test_dbo.f( '1' ) is null;
select tablename from sys.systables where tablename is null and
test_dbo.f( tablename ) is null;
select a from test_dbo.t where a is null and test_dbo.f( a ) is null;
--
-- Test insertions into territory based column in UCS_BASIC schema
--
--
-- All of these succeed. Collation of inserted string doesn't matter.
--
insert into test_dbo.t( a ) values ( 'foo' );
insert into test_dbo.t( a ) select tablename from sys.systables;
insert into test_dbo.t( a ) select a from test_dbo.t;
Re: some questions about collations
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Rick,
I haven't looked at the code but made a quick glance through the past
emails(from 2007) about collation discussion. And I think the
conclusion was to pick up the collation of the compilation schema for
character columns not coming from a table directly. I wrote test cases
for the 2 scenarios you described below and they both seem to pick up
the collation of compilation schema. I did my tests in ij,. Following
is what I tried
Case 1)
java -Dij.exceptionTrace=true org.apache.derby.tools.ij
connect 'jdbc:derby:db1;create=true;territory=en_US;collation=TERRITORY_BASED:SECONDARY';
set schema app;
create table t1(c11 int, c12 char(20));
insert into t1 values (1,'CUSTOMER');
SELECT 1 FROM t1 where c12='CUSTOMER';
set schema SYS;
--following fails because 'CUSTOMER' is UCS_BASIC and c12 is TERRITORY_BASED
SELECT 1 FROM t1 where c12='CUSTOMER';
--followinbg casting of TERRITORY_BASED to UCS_BASIC fixes the problem
select * from app.t1 where cast(c12 as char(15))='CUSTOMER';
Case 2)
java -Dij.exceptionTrace=true org.apache.derby.tools.ij
connect 'jdbc:derby:db1;create=true;territory=en_US;collation=TERRITORY_BASED:SECONDARY';
set schema app;
create table t1(c11 int, c12 char(20));
set schema SYS;
--following fails becasue c12 is TERRITORY_BASED but the system
function is returning UCS_BASIC
select * from app.t1 where c12 =
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.collation');
--following passes after casting c12 from TERRITORY_BASED to UCS_BASIC
select * from app.t1 where cast(c12 as char(15)) =
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.database.collation');
We should add such test cases to our junit suite.
Mamta
On Thu, Dec 15, 2011 at 9:44 AM, Rick Hillegas <ri...@oracle.com> wrote:
> 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
>