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-3944) CHECK constraints involving user-coded functions may return different results depending on who performs the trigging INSERT/UPDATE
Date Thu, 13 Nov 2008 20:02:44 GMT

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

Rick Hillegas commented on DERBY-3944:
--------------------------------------

Thanks for the extra information, Andrew. In the meantime, discussion has continued on the
email thread mentioned above. Dag's eagle eyes spotted the following chapter and verse, which
appears to settle the spec issues:

section 4.27.2 Characteristics of SQL-invoked routines:

> > If a <routine invocation> is contained in a <query expression> of a
> > view, a check constraint, or an assertion, the <triggered action> of a
        ****************
> > trigger, or in an <SQL-invoked routine>, then the subject routine for
> > that invocation is determined at the time the view is created, the
> > check constraint is defined, the assertion is created, the trigger is
> > created, or the SQL-invoked routine is created.


> 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.


Mime
View raw message