db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: more on system schema vs. user schema and character constants.
Date Sun, 03 Jun 2007 23:07:07 GMT
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 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?

thanks,
Mamta

On 5/31/07, Mamta Satoor <msatoor@gmail.com> wrote:
>
> Thanks. This sure helps understand the difference between compilation
> schema vs current schema.
>
> Mamta
>
>
>  On 5/31/07, Daniel John Debrunner <djd@apache.org> wrote:
> >
> > Mamta Satoor wrote:
> > > Dan, I am trying to understand the difference between connection's
> > > current schema vs compilation schema of a statement. It will help me a
> >
> > > lot if you could talk about the 2 schemas in reference to an example.
> >
> > set schema A
> > create view V as select * from T
> > set schema B
> > select * from A.V
> >
> > When executing the last statement (select from the view) the
> > connection's current schema  and the compilation schema for the
> > statement is B, but the views select needs to be compiled in schema A to
> > ensure that T resolves to A.T as the creator of V intended. Derby
> > supports nesting of compilation contexts and that is where the current
> > compilation schema is defined.
> >
> > Views are compiled when used, the tree for the view is created and then
> > merged with the tree containing the view. There is no pre-compiled plan
> > for a view.
> >
> > Trigger action statements do have a pre-compiled plan but can be
> > recompiled when used due to factors such as an additional index being
> > created. In that case the trigger's action statement much be compiled
> > against its compilation schema and not the current schema of the
> > connection. This ensures consistency of the trigger action.
> >
> > Similar for stored prepared plans (SPS) which is how the meta-data
> > queries (and trigger action statements :-) are implemented.
> >
> > HTH,
> > Dan.
> >
> > >
> > > thanks,
> > > Mamta
> > >
> > >
> > > On 5/30/07, *Daniel John Debrunner* <djd@apache.org
> > > <mailto:djd@apache.org>> wrote:
> > >
> > >     Mike Matrigali wrote:
> > >      > I am sort of confused at this point, what we expect to be
> > working
> > >      > now and what should be.  What is the right way to write the
> > >      > following query against a the system schema?  Is there anyway
> > to
> > >      > write the query without changing the default schema? :
> > >
> > >     Not sure if it's related or not but I just noticed this code in
> > CastNode
> > >     and there is other similar fragments elsewhere in ValueNodes.
> > >
> > >         //set the collation type to be same as the current schema's
> > >         //collation type. Collation derivation is already initialized
> > >         //to correct value by default which is "IMPLICIT"
> > >         getTypeServices().setCollationType(
> > >
> > getLanguageConnectionContext().getDefaultSchema().getCollationType());
> > >
> > >     I think this is incorrect. During compilation the correct value to
> > get
> > >     the current schema is using the getSchemaDescriptor(null) call for
> > any
> > >     node. The current schema for compilation will not match the
> > connection's
> > >     current schema in some situations, such as compiling an action
> > statement
> > >     for a trigger, a sps for metadata queries and possibly for views
> > as
> > >     well.
> > >
> > >     If this code is frequent it might be worth creating a utility
> > method in
> > >     ValueNode as
> > >
> > >     setSchemaCollation() {
> > >         getTypeServices().setCollationType(
> > >          getSchemaDescriptor(null).getCollationType());
> > >     }
> > >
> > >     Dan.
> > >
> > >
> >
> >
>

Mime
View raw message