db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6362) CHECK constraint uses wrong schema for unqualified routine invocations
Date Tue, 08 Oct 2013 14:30:44 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13789244#comment-13789244
] 

Rick Hillegas commented on DERBY-6362:
--------------------------------------

I have exchanged email with some other members of the SQL committee. They have pointed me
at the portions of the SQL Standard which discuss the SQL-path. It sheds some light on this
discussion.

The SQL-path is introduced by part 2, section 4.29 (SQL-paths). Fully evolved, a SQL-path
is a search order for schema resolution. It's akin to the search order in a Unix path or Java
classpath. The CURRENT_PATH variable (implemented in some databases) shows the value of the
SQL-path...

    values CURRENT_PATH

...much as the following Unix commands show the value of the shell's path and classpath variables:

    echo $PATH
    echo $CLASSPATH

According to part 2, section 4.29 (SQL-paths), the SQL-path is used to resolve routine and
type references which lack a schema qualifier and which appear in "preparable statements".
These statements, in turn, include the standalone DDL statements like CREATE/ALTER TABLE.
So SQL-path should be used to resolve unqualified routine/type references in CHECK constraints
and generation clauses.

The value of SQL-path is implementation-defined. The Standard lets you change the value of
SQL-path via a SET PATH command, which Derby does not implement. SET PATH is independent of
the SET SCHEMA command which Derby does implement. Note that the SET SCHEMA command (part
2, section 19.6) changes how unqualified names are resolved in "preparable statements". However,
that section does not claim that SET SCHEMA has any affect on SQL-path.

The Derby code never speaks about SQL-path. This is a part of the Standard which Derby ignores.
So Derby's inconsistent treatment of SQL-path is not surprising. That said, now that we are
aware of SQL-path, it would be good to make Derby's behavior consistent.

For most statements, Derby behaves as though CURRENT_PATH is a list with one value in it (CURRENT_SCHEMA).
So, unqualified routine references in DML statements are resolved to CURRENT_SCHEMA. The same
is true for unqualified routine references in generation clauses and unqualified type references
in CREATE TABLE statements.

If we treated CHECK constraints the same way, then we would have behavior (C) described above.
This seems reasonable to me. That is, it makes sense to me that we should formalize our understand
of SQL-path and apply it consistently going forward. Derby's implementation of SQL-path would
be simple, viz., CURRENT_PATH = CURRENT_SCHEMA.

The following script shows the current behavior of CURRENT_PATH:

---------------------------------------------

connect 'jdbc:derby:memory:db;create=true';

create schema neutralSchema;

create schema ddlSessionSchema;
set schema ddlSessionSchema;

create type myType external name 'java.util.HashMap' language java;

create function myCheckFunc( doubleVal double ) returns double
language java parameter style java deterministic no sql
external name 'java.lang.Math.sin';

create function myGenColFunc( doubleVal double ) returns double
language java parameter style java deterministic no sql
external name 'java.lang.Math.abs';

-- --------------------------------

create schema tableSchema;
set schema tableSchema;

create type myType external name 'java.util.ArrayList' language java;

create function myCheckFunc( doubleVal double ) returns double
language java parameter style java deterministic no sql
external name 'java.lang.Math.cos';

create function myGenColFunc( doubleVal double ) returns double
language java parameter style java deterministic no sql
external name 'java.lang.Math.exp';

-- --------------------------------

set schema ddlSessionSchema;

create table tableSchema.t
(
   a myType,
   b double check ( myCheckFunc( b ) > 0 ),
   c generated always as ( myGenColFunc( b ) )
);

-- --------------------------------

set schema neutralSchema;

values
(
    ddlSessionSchema.myCheckFunc( -1 ), ddlSessionSchema.myGenColFunc( -1 )
);
values
(
    tableSchema.myCheckFunc( -1 ), tableSchema.myGenColFunc( -1 )
);

-- fails because myType resolves to ddlSessionSchema.myType
insert into tableSchema.t( a ) values ( cast (null as tableSchema.myType) );

--
-- myCheckFunc resolves to tableSchema.myCheckFunc
--
-- myGenColFunc resolves to ddlSessionSchema.myGenColFunc
--
insert into tableSchema.t( b ) values ( -1 );
select * from tableSchema.t;

-- --------------------------------

set schema ddlSessionSchema;

-- behaves as though CURRENT_PATH = ddlSessionSchema
values
(
    myCheckFunc( -1 ), myGenColFunc( -1 )
);

set schema tableSchema;

-- behaves as though CURRENT_PATH = tableSchema
values
(
    myCheckFunc( -1 ), myGenColFunc( -1 )
);


> CHECK constraint uses wrong schema for unqualified routine invocations
> ----------------------------------------------------------------------
>
>                 Key: DERBY-6362
>                 URL: https://issues.apache.org/jira/browse/DERBY-6362
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.10.1.1
>            Reporter: Knut Anders Hatlen
>
> DERBY-3944 fixed the problem with CHECK constraints invoking different routines depending
on who performed the triggering INSERT or UPDATE statement.
> The discussion leading up to DERBY-3944 can be found here: http://mail-archives.apache.org/mod_mbox/db-derby-dev/200811.mbox/%3C4919CD4A.5010408@sun.com%3E
> Three alternatives are discussed in the thread:
> A) The schema that holds the CHECK constraint?
> B) The schema that holds the table?
> C) The current schema when the CREATE TABLE statement was issued?
> The conclusion in the thread was that option C was the correct one. However, what was
implemented, was option B.
> I cannot find any information in DERBY-3944 about why option B ended up being chosen,
so I assume that it was unintended.
> Here's an ij script that shows how the CHECK constraint tries to invoke the TO_HEX function
in the schema of the target table (S2) instead of the schema that was the current schema at
the time of CREATE TABLE:
> ij version 10.10
> ij> connect 'jdbc:derby:memory:db;create=true';
> ij> create schema s1;
> 0 rows inserted/updated/deleted
> ij> create schema s2;
> 0 rows inserted/updated/deleted
> ij> create function s1.to_hex(i int) returns char(4) language java parameter style
java external name 'java.lang.Integer.toHexString' no sql;
> 0 rows inserted/updated/deleted
> ij> set schema s1;
> 0 rows inserted/updated/deleted
> ij> create table s2.t(x int, constraint cc check(to_hex(x) <> '80'));
> 0 rows inserted/updated/deleted
> ij> insert into s2.t values 1;
> ERROR 42Y03: 'TO_HEX' is not recognized as a function or procedure. (errorCode = 30000)
> ij> create function s2.to_hex(i int) returns char(4) language java parameter style
java external name 'java.lang.Integer.toHexString' no sql;
> 0 rows inserted/updated/deleted
> ij> insert into s2.t values 1;
> 1 row inserted/updated/deleted



--
This message was sent by Atlassian JIRA
(v6.1#6144)

Mime
View raw message