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 Mike Matrigali <mi...@sbcglobal.net> on 2007/05/31 02:23:49 UTC
more on system schema vs. user schema and character constants.
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? :
select COLUMNNAME from SYS.SYSCOLUMNS where SYS.SYSCOLUMNS.COLUMNNAME =
'fred';
I tried the following:
ij> connect
'jdbc:derby:endb;create=true;territory=en;collation=TERRITORY_BASED'
;
ij(CONNECTION1)> set schema APP;
0 rows inserted/updated/deleted
ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where
SYS.SYSCOLUMNS.COLU
MNNAME = 'fred';
ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where COLUMNNAME
= 'fred'
;
ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where
SYS.SYSCOLUMNS.COLU
MNNAME = CAST('fred' as CHAR);
ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where COLUMNNAME
= CAST('
fred' as CHAR);
ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
ij(CONNECTION1)> set schema SYS;
0 rows inserted/updated/deleted
ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where
SYS.SYSCOLUMNS.COLU
MNNAME = 'fred';
COLUMNNAME
--------------------------------------------------------------------------------
------------------------------------------------
0 rows selected
ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where COLUMNNAME
= 'fred'
;
COLUMNNAME
--------------------------------------------------------------------------------
------------------------------------------------
0 rows selected
ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where
SYS.SYSCOLUMNS.COLU
MNNAME = CAST('fred' as CHAR);
COLUMNNAME
--------------------------------------------------------------------------------
------------------------------------------------
0 rows selected
ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where COLUMNNAME
= CAST('
fred' as CHAR);
COLUMNNAME
--------------------------------------------------------------------------------
------------------------------------------------
0 rows selected
Re: more on system schema vs. user schema and character constants.
Posted by Mamta Satoor <ms...@gmail.com>.
Mike, you can rewrite the query as shown below using CAST
select COLUMNNAME from SYS.SYSCOLUMNS where
CAST(SYS.SYSCOLUMNS.COLUMNNAMEas CHAR(10) = 'fred';
The result of CAST will take the collation of the current schema which will
be same the collation taken by 'fred' which is the collation of the current
schema. This way, the query will work in any schema.
Please let me know if this does not answer your question. Just an FYI, there
are examples of such queries in CollationTest.java
HTH,
Mamta
On 5/30/07, Mike Matrigali <mi...@sbcglobal.net> 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? :
>
> select COLUMNNAME from SYS.SYSCOLUMNS where SYS.SYSCOLUMNS.COLUMNNAME =
> 'fred';
>
>
> I tried the following:
> ij> connect
> 'jdbc:derby:endb;create=true;territory=en;collation=TERRITORY_BASED'
> ;
>
> ij(CONNECTION1)> set schema APP;
> 0 rows inserted/updated/deleted
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where
> SYS.SYSCOLUMNS.COLU
> MNNAME = 'fred';
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where COLUMNNAME
> = 'fred'
> ;
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where
> SYS.SYSCOLUMNS.COLU
> MNNAME = CAST('fred' as CHAR);
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where COLUMNNAME
> = CAST('
> fred' as CHAR);
> ERROR 42818: Comparisons between 'VARCHAR' and 'CHAR' are not supported.
> ij(CONNECTION1)> set schema SYS;
> 0 rows inserted/updated/deleted
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where
> SYS.SYSCOLUMNS.COLU
> MNNAME = 'fred';
> COLUMNNAME
>
>
> --------------------------------------------------------------------------------
> ------------------------------------------------
>
> 0 rows selected
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where COLUMNNAME
> = 'fred'
> ;
> COLUMNNAME
>
>
> --------------------------------------------------------------------------------
> ------------------------------------------------
>
> 0 rows selected
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where
> SYS.SYSCOLUMNS.COLU
> MNNAME = CAST('fred' as CHAR);
> COLUMNNAME
>
>
> --------------------------------------------------------------------------------
> ------------------------------------------------
>
> 0 rows selected
> ij(CONNECTION1)> select COLUMNNAME from SYS.SYSCOLUMNS where COLUMNNAME
> = CAST('
> fred' as CHAR);
> COLUMNNAME
>
>
> --------------------------------------------------------------------------------
> ------------------------------------------------
>
> 0 rows selected
>
Re: more on system schema vs. user schema and character constants.
Posted by Daniel John Debrunner <dj...@apache.org>.
Daniel John Debrunner wrote:
> If this code is frequent it might be worth creating a utility method in
> ValueNode as
>
> setSchemaCollation() {
> getTypeServices().setCollationType(
> getSchemaDescriptor(null).getCollationType());
> }
Might even be worth passing the derivation to the method as well since
everytime both need to be set. (A couple of cases depend on the
derivation defaulting to implicit, but it might be clearer and safer to
explicitly set it).
setSchemaCollation(int derivation) {
getTypeServices().setCollationDerivation(derivation);
getTypeServices().setCollationType(
getSchemaDescriptor(null).getCollationType());
}
Dan.
Re: more on system schema vs. user schema and character constants.
Posted by Daniel John Debrunner <dj...@apache.org>.
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.
Re: more on system schema vs. user schema and character constants.
Posted by Mamta Satoor <ms...@gmail.com>.
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 <ms...@gmail.com> wrote:
>
> Thanks. This sure helps understand the difference between compilation
> schema vs current schema.
>
> Mamta
>
>
> On 5/31/07, Daniel John Debrunner <dj...@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
> > > <ma...@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.
> > >
> > >
> >
> >
>
Re: more on system schema vs. user schema and character constants.
Posted by Mamta Satoor <ms...@gmail.com>.
Thanks. This sure helps understand the difference between compilation schema
vs current schema.
Mamta
On 5/31/07, Daniel John Debrunner <dj...@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
> > <ma...@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.
> >
> >
>
>
Re: more on system schema vs. user schema and character constants.
Posted by Daniel John Debrunner <dj...@apache.org>.
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
> <ma...@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.
>
>
Re: more on system schema vs. user schema and character constants.
Posted by Mamta Satoor <ms...@gmail.com>.
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.
thanks,
Mamta
On 5/30/07, Daniel John Debrunner <dj...@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.
>
>
Re: more on system schema vs. user schema and character constants.
Posted by Daniel John Debrunner <dj...@apache.org>.
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.