Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1374900137.1226446064252.JavaMail.jira@brutus>
Date: Tue, 11 Nov 2008 15:27:44 -0800 (PST)
From: "Andrew McIntyre (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-3944) CHECK constraints involving
 user-coded functions may return different results depending on who performs
 the trigging INSERT/UPDATE
In-Reply-To: <76556103.1226435984478.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Andrew McIntyre commented on DERBY-3944:
----------------------------------------

DB2 assigns it to the current schema. In the default case, this is the schema corresponding to the user id.

FWIW, the copy of the SQL standard I have says in sec. 5.4, 4a that the unqualified identifiers should resolve to the current schema for the SQL session and the definition of routine invocation has no language to contradict that and simply says that it's a qualified identifier. While it seems logical to me that an unqualified function name in a check constraint would resolve to the schema containing the table with the constraint, it could be useful to have the function resolve to different functions in each schema, e.g. allowing different users to insert data into a table with different constraints.

> CHECK constraints involving user-coded functions may return different results depending on who performs the trigging INSERT/UPDATE
> ----------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-3944
>                 URL: https://issues.apache.org/jira/browse/DERBY-3944
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.0.0
>            Reporter: Rick Hillegas
>
> When compiling a CHECK constraint on behalf of an INSERT/UPDATE statement, Derby uses the current schema in order to resolve unqualified function names which appear in the CHECK constraint. This means that the CHECK constraint may evaluate true for some users, false for others, and for others the CHECK constraint may raise an error saying that Derby can't resolve the function reference. This behavior violates the "retrospective determinacy" of CHECK constraints as specified by part 2 of the ANSI/ISO standard:
> 1) section 11.9 (<check constraint definition>), syntax rule 5
> 2) same section, general rule 1
> 3) section 11.6 (<table constraint definition>), general rule 3
> 4) section 4.16 (Determinism)
> For more discussion, please see this email thread: http://www.nabble.com/Problem-with-CHECK-constraints-td20445344.html#a20445344
> The following script demonstrates this problem:
> connect 'jdbc:derby:derbyauth;create=true;user=test_dbo;password=test_dbopassword' as test_dbo_conn;
> drop table t_bp_2;
> drop function f_fp_minus;
> create function f_fp_minus
> (
>     a int
> )
> returns int
> language java
> deterministic
> parameter style java
> no sql
> external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.minus'
> ;
> create table t_bp_2( a int, constraint t_bp_2_check check ( f_fp_minus( a ) < 0 ) );
> grant insert on t_bp_2 to public;
> insert into test_dbo.t_bp_2( a ) values ( 100 );
> connect 'jdbc:derby:derbyauth;create=true;user=janet;password=janetpassword' as janet_conn;
> insert into test_dbo.t_bp_2( a ) values ( 100 );

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.