[
https://issues.apache.org/jira/browse/DERBY-3944?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12647041#action_12647041
]
Andrew McIntyre commented on DERBY-3944:
----------------------------------------
I checked yesterday and contrary to the way the docs read, DB2 doesn't actually
allow user defined functions in SQL in check constraints either. I suspect
this may simply be an overlooked corner in the SQL spec, since none of the
major vendors support user-defined functions in check constraints and built-in
functions never need a schema qualifier.
> 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.
