[jira] [Updated] (DERBY-6362) CHECK constraint uses wrong schema for unqualified routine invocations

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6362?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Knut Anders Hatlen updated DERBY-6362:
--------------------------------------

    Attachment: d6362-3b.diff

The failures seen when the patches are applied on head of trunk, were caused by some changes to the way column references are bound (in DERBY-3155, revision 1545343). Now column references in the CHECK constraint node tree may reference TableName nodes that originate from outside the CHECK constraint. These TableName nodes are found by the visitor during the rewriting, but since they point to tokens outside of the actual CHECK constraint, trying to replace them in the CHECK constraint text will end in index out of bounds.

I'm uploading a new patch, d6362-3b.diff, that replaces the 3a patch. It should be applied on top of 1a and 2a. The only change in the updated patch is that OffsetOrderVisitor now ignores nodes whose getBeginOffset() and getEndOffset() methods return values that point to tokens outside of the particular SQL fragment that we want to rewrite.

All regression tests passed with 1a+2a+3b applied on head of trunk.

> 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
>            Assignee: Knut Anders Hatlen
>         Attachments: d6362-1a-visit-tablename.diff, d6362-2a-fix-tablename-offset.diff, d6362-3a-rewrite-checks.diff, d6362-3b.diff
>
>
> 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)