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 "Rick Hillegas (JIRA)" <ji...@apache.org> on 2008/11/11 21:39:44 UTC
[jira] Created: (DERBY-3944) CHECK constraints involving user-coded
functions may return different results depending on who performs the
trigging INSERT/UPDATE
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
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.
[jira] Updated: (DERBY-3944) CHECK constraints involving user-coded
functions may return different results depending on who performs the
triggering INSERT/UPDATE
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3944?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-3944:
---------------------------------
Issue & fix info: [Known fix, Patch Available, Repro attached] (was: [Known fix, Repro attached])
> CHECK constraints involving user-coded functions may return different results depending on who performs the triggering 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.1.1
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: derby-3944-01-aa-useOriginalSchema.diff
>
>
> 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.
[jira] Commented: (DERBY-3944) CHECK constraints involving
user-coded functions may return different results depending on who performs
the trigging INSERT/UPDATE
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3944?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12646906#action_12646906 ]
Rick Hillegas commented on DERBY-3944:
--------------------------------------
Thanks, Andrew. I agree that there are applications which might want CHECK constraints to behave as you describe. That behavior, however, violates my understanding of "retrospective determinacy". It may be that the standard did a poor job of integrating user-defined functions with CHECK constraints. I will ask around for more guidance.
> 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.
[jira] Commented: (DERBY-3944) CHECK constraints involving
user-coded functions may return different results depending on who performs
the trigging INSERT/UPDATE
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Assigned: (DERBY-3944) CHECK constraints involving
user-coded functions may return different results depending on who performs
the triggering INSERT/UPDATE
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3944?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas reassigned DERBY-3944:
------------------------------------
Assignee: Rick Hillegas
> CHECK constraints involving user-coded functions may return different results depending on who performs the triggering 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.1.1
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: derby-3944-01-aa-useOriginalSchema.diff
>
>
> 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.
[jira] Updated: (DERBY-3944) CHECK constraints involving user-coded
functions may return different results depending on who performs the
triggering INSERT/UPDATE
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3944?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-3944:
---------------------------------
Attachment: derby-3944-01-aa-useOriginalSchema.diff
Attaching derby-3944-01-aa-useOriginalSchema.diff. This causes CHECK constraints compile in the schema of the target table rather than in the current schema of the INSERT/UPDATE statement. I will run tests.
Touches the following files:
-----------
M java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
Changes the current schema while compiling check constraints.
-----------
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsPermsTest.java
Adds a test for this behavior.
> CHECK constraints involving user-coded functions may return different results depending on who performs the triggering 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.1.1
> Reporter: Rick Hillegas
> Attachments: derby-3944-01-aa-useOriginalSchema.diff
>
>
> 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.
[jira] Closed: (DERBY-3944) CHECK constraints involving user-coded
functions may return different results depending on who performs the
triggering INSERT/UPDATE
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3944?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas closed DERBY-3944.
--------------------------------
Issue & fix info: [Known fix, Repro attached] (was: [Repro attached, Patch Available, Known fix])
Fix Version/s: 10.7.0.0
Resolution: Fixed
Tests passed cleanly for me except for the known regression having to do with port numbers. Committed at subversion revision 964402.
> CHECK constraints involving user-coded functions may return different results depending on who performs the triggering 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.1.1
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Fix For: 10.7.0.0
>
> Attachments: derby-3944-01-aa-useOriginalSchema.diff, derby-3944-01-ab-useOriginalSchema.diff
>
>
> 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.
[jira] Updated: (DERBY-3944) CHECK constraints involving user-coded
functions may return different results depending on who performs the
triggering INSERT/UPDATE
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3944?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-3944:
---------------------------------
Bug behavior facts: [Data corruption]
> CHECK constraints involving user-coded functions may return different results depending on who performs the triggering 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.1.1
> 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.
[jira] Updated: (DERBY-3944) CHECK constraints involving user-coded
functions may return different results depending on who performs the
trigging INSERT/UPDATE
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3944?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-3944:
---------------------------------
Affects Version/s: 10.5.0.0
> 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.
[jira] Updated: (DERBY-3944) CHECK constraints involving user-coded
functions may return different results depending on who performs the
triggering INSERT/UPDATE
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3944?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-3944:
---------------------------------
Summary: CHECK constraints involving user-coded functions may return different results depending on who performs the triggering INSERT/UPDATE (was: CHECK constraints involving user-coded functions may return different results depending on who performs the trigging INSERT/UPDATE)
> CHECK constraints involving user-coded functions may return different results depending on who performs the triggering 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.
[jira] Updated: (DERBY-3944) CHECK constraints involving user-coded
functions may return different results depending on who performs the
triggering INSERT/UPDATE
Posted by "Kristian Waagan (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3944?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kristian Waagan updated DERBY-3944:
-----------------------------------
Issue & fix info: [Known fix, Repro attached]
Urgency: Normal
Triaged July 3, 2009: Assigned normal urgency, marked Known fix and Repro attached.
> CHECK constraints involving user-coded functions may return different results depending on who performs the triggering 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.1.1
> 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.
[jira] Commented: (DERBY-3944) CHECK constraints involving
user-coded functions may return different results depending on who performs
the trigging INSERT/UPDATE
Posted by "Andrew McIntyre (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3944?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12646683#action_12646683 ]
Andrew McIntyre commented on DERBY-3944:
----------------------------------------
Did a quick check as I happened to have several different DBs handy. DB2 v9 only allows user defined functions that can be expressed in SQL, and IDS 11, Oracle 9, and MS SQL Server 2005 all disallow user-defined functions in check 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.
[jira] Commented: (DERBY-3944) CHECK constraints involving
user-coded functions may return different results depending on who performs
the trigging INSERT/UPDATE
Posted by "Andrew McIntyre (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-3944) CHECK constraints involving user-coded
functions may return different results depending on who performs the
triggering INSERT/UPDATE
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3944?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-3944:
---------------------------------
Attachment: derby-3944-01-ab-useOriginalSchema.diff
Attaching second rev of the patch, derby-3944-01-ab-useOriginalSchema.diff. This rev adds a test case to verify that DERBY-3953 has also been fixed.
DERBY-3953 appears to have been fixed by some other patch. This additional test case merely tracks that fact. I will close DERBY-3953 when I commit this patch.
> CHECK constraints involving user-coded functions may return different results depending on who performs the triggering 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.1.1
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: derby-3944-01-aa-useOriginalSchema.diff, derby-3944-01-ab-useOriginalSchema.diff
>
>
> 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.
[jira] Commented: (DERBY-3944) CHECK constraints involving
user-coded functions may return different results depending on who performs
the trigging INSERT/UPDATE
Posted by "Andrew McIntyre (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-3944) CHECK constraints involving
user-coded functions may return different results depending on who performs
the trigging INSERT/UPDATE
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3944?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12646685#action_12646685 ]
Rick Hillegas commented on DERBY-3944:
--------------------------------------
Thanks, Andrew. When you've got a moment, can you let us know what schema DB2 assigns to the function references if they aren't qualified with an explicit schema name?
> 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.