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] [Updated] (DERBY-6362) CHECK constraint uses wrong schema for unqualified routine invocations
Date Tue, 26 Nov 2013 12:17:35 GMT

     [ 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-3a-rewrite-checks.diff

Fetching all the TableName nodes and using them to rewrite the CHECK constraint definition
before storing it in SYSCHECKS, seems to be working. As suggested earlier, many accept methods
need to be changed in order to make the visitors actually see the TableName nodes. To make
it easier to separate these mechanical changes from the new rewrite logic, I've split the
fix into three patches that should be applied on top of each other. The regression tests ran
cleanly after each individual patch.

Here's a description of the changes made by each of the patches:

d6362-1a-visit-tablename.diff: This patch makes the acceptChildren() methods of all QueryTreeNode
classes descend into any TableName field, including lists/arrays of TableNames. Since only
a very limited subset of the language is allowed in CHECK constraints, we don't need all of
these for CHECK constraints, but I found it easier to convince myself that no essential ones
were forgotten if all were visited.

d6362-2a-fix-tablename-offset.diff: This patch fixes a problem with qualified table names,
where the begin offset was set to the beginning of the table name portion of the TableName
node, whereas it should have been set to the beginning of the schema name portion. It also
makes some synthetic TableName nodes have unspecified offsets (that is, -1) instead of fake
offset values.

- This patch makes CHECK constraint definitions replace unqualified names with qualified names
before storing them in SYS.SYSCHECKS.
- Since some of the rewrite logic is very similar to the one used to replace references to
transition variables in triggers with Java method calls, the common logic was factored out
into a shared visitor class (OffsetOrderVisitor, which collects all nodes of a certain type
and returns them in offset order).
- In order to make UDT names in CAST expressions qualified, it had to modify the parser so
that it adds a reference to the original TableName node from the CastNode. Without the original
TableName node, the rewrite logic wouldn't know there was a name that needed qualification.
- It also adds test case.

The test case for this issue tests that type names (in casts), function names and table names
are rewritten as qualified identifiers. I'm not aware of any other identifiers that could
be used in CHECK constraints. Let me know if you can think of others, and I'll add tests for

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

View raw message