db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: more on system schema vs. user schema and character constants.
Date Mon, 04 Jun 2007 00:54:46 GMT
Mamta Satoor wrote:
> OK, so maybe I spoke too soon when I said that I understand the 
> difference between compilation schema vs current schema.
>  
> Let me take following eg for a database with collation=territory_based
> create schema a;
> set schema a;
> create table t (id int, type char(10));
> insert into t values (1,'CAR'),(2,'SUV');
> set schema sys;
> select * from A.T where TYPE = CAST('CAR' AS CHAR(10));
>  
> For the last sql statement, I thought the compilation schema will be A 

Why? The previous statement sets the schema to sys. Imagine if the 
statement was instead

    select * FROM A.T, B.T ...

then how would the system pick A or B as the compilaton schema?

> and hence the result of the CAST will have collation type of territory 
> based because result of CAST is supposed to pick up it's collation from 
> the compilation schema. But when I try the above set of sql statements 
> inside ij, the last sql statement fails because the collation type of 
> column "TYPE" is not same as collation type of CAST. It appears that the 
> result of CAST has picked up collation of SYS schema which is UCS_BASIC. 
> So, for character constants, CAST etc, does the compilation schema end 
> up being the the current connection schema since they are not tied to 
> any physical schema?

No, a statement has a single compilation schema. Usually it is the same 
as the current schema, but in cases like views, trigger action 
statements and sps (meta data queries) it isn't.

I think that constants, parameters etc. picking up their collation from 
the compilation schema is going to cause issues, it may not be what an 
app developer is expecting.

Dan.

Mime
View raw message