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 <[email protected]> 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; >
