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, 16 Oct 2013 20:12:43 GMT

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

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

My first thought was to add a field to CheckConstraintDescriptor. That approach would require
the addition of a new column in SYS.SYSCHECKS, which entails some upgrade work.

I like the idea of rewriting the CHECK condition and making all unqualified names qualified.
Such a solution might be useful for generated columns, views and triggers as well, since qualified
names would make it easier to produce correct dblook output for them (see for example DERBY-6370).

Not sure how easy it is to do the rewrite in a reliable way, though. It might be possible
to generalize the code in DataDictionaryImpl.getTriggerActionString(), which replaces old/new
transition variables and transition tables with Java method calls or VTIs in trigger actions.
But I haven't looked into that.

Adding a new column to the system table and pushing the compilation schema might be more robust,
since the rewrite may easily miss some references if we're not careful, or if we extend the
allowed syntax later. But it's not as elegant as rewriting.

> 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