db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6362) CHECK constraint uses wrong schema for unqualified routine invocations
Date Wed, 02 Oct 2013 15:56:42 GMT

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

Knut Anders Hatlen commented on DERBY-6362:
-------------------------------------------

I agree that the email thread doesn't get completely to the bottom of it, so more reading
of the standard is needed in order to find out what's the correct behaviour.

I think it is possible to make dblook have deterministic behaviour even with option C. We
could make it create DDL that uses a schema qualified name for the routine, if it doesn't
already.

If we conclude that option B is correct, we still need to make changes to CHECK constraint,
as it currently seems to use a mix of option B and option C. See for example this ij transcript:

{noformat}
ij version 10.10
ij> connect 'jdbc:derby:memory:db;create=true';
ij> create function bin.f(i int) returns char(32) language java parameter style java external
name 'java.lang.Integer.toBinaryString' no sql;
0 rows inserted/updated/deleted
ij> create function hex.f(i int) returns char(4) language java parameter style java external
name 'java.lang.Integer.toHexString' no sql;
0 rows inserted/updated/deleted
ij> values (hex.f(10), bin.f(10));
1   |2                               
-------------------------------------
a   |1010                            

1 row selected
ij> set schema bin;
0 rows inserted/updated/deleted
ij> create table hex.t(x int, constraint cc check(f(x) = 'a'));
0 rows inserted/updated/deleted
ij> insert into hex.t(x) values 10;
1 row inserted/updated/deleted
ij> insert into hex.t(x) values 11;
ERROR 23513: The check constraint 'CC' was violated while performing an INSERT or UPDATE on
table '"HEX"."T"'.
ij> drop function hex.f;
0 rows inserted/updated/deleted
ij> drop function bin.f;
ERROR X0Y25: Operation 'DROP ROUTINE' cannot be performed on object 'F' because CONSTRAINT
'CC' is dependent on that object.
{noformat}

Here, the constraint CC apparently uses the function HEX.F. Still, there is no error or warning
when HEX.F is dropped. But if you try to drop BIN.F, which is not used in constraint CC, you
get an error saying that CC depends on it.

Also, if you go back to the example in the bug description, I think that the error saying
TO_HEX didn't exist should have happened at CREATE TABLE time, not when performing the INSERT.

> 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