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-6370) dblook doesn't schema-qualify identifiers in trigger actions
Date Wed, 30 Apr 2014 08:31:15 GMT

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

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

Thanks, Dag.

One thing to note is that some table names are not being qualified even with this patch. For
example

  update t set t.x = t.y

will be rewritten to

  update "APP"."T" set t.x = "T".y

and similarly

  insert into t (t.x) values (1)

will be rewritten to

  insert into "APP"."T" (t.x) values (1)

I guess this is because target columns of insert and update statements are bound in an odd
way, which results in bugs such as DERBY-6558.

It doesn't currently cause any problems that these table references are unqualified, since
Derby currently ignores the schema in these statements and just assumes it's the same schema
as the target table. We may need to revisit this when DERBY-6558 is fixed. If we're lucky,
though, the fix for DERBY-6558 will fix the rewriting of these statements as a side effect.

> dblook doesn't schema-qualify identifiers in trigger actions
> ------------------------------------------------------------
>
>                 Key: DERBY-6370
>                 URL: https://issues.apache.org/jira/browse/DERBY-6370
>             Project: Derby
>          Issue Type: Bug
>          Components: Tools
>    Affects Versions: 10.10.1.1
>            Reporter: Knut Anders Hatlen
>            Assignee: Knut Anders Hatlen
>         Attachments: d6370-1a.diff
>
>
> dblook doesn't qualify identifiers in the trigger action if they weren't explicitly qualified
in the original CREATE TRIGGER statement.
> Example:
> ij version 10.10
> ij> connect 'jdbc:derby:db;create=true';  
> ij> create table s1.t1(x int);
> 0 rows inserted/updated/deleted
> ij> create table s2.t2(x int);
> 0 rows inserted/updated/deleted
> ij> set schema s1;
> 0 rows inserted/updated/deleted
> ij> create trigger tr1 after insert on t1 insert into s2.t2 select * from t1;
> 0 rows inserted/updated/deleted
> ij> set schema s2;
> 0 rows inserted/updated/deleted
> ij> create trigger tr2 after insert on s1.t1 insert into t2 select * from s1.t1;
> 0 rows inserted/updated/deleted
> ij> exit;
> Then run dblook on the created database:
> -- Timestamp: 2013-10-04 12:46:14.974
> -- Source database is: db
> -- Connection URL is: jdbc:derby:db
> -- appendLogs: false
> -- ----------------------------------------------
> -- DDL Statements for schemas
> -- ----------------------------------------------
> CREATE SCHEMA "S1";
> CREATE SCHEMA "S2";
> -- ----------------------------------------------
> -- DDL Statements for tables
> -- ----------------------------------------------
> CREATE TABLE "S1"."T1" ("X" INTEGER);
> CREATE TABLE "S2"."T2" ("X" INTEGER);
> -- ----------------------------------------------
> -- DDL Statements for triggers
> -- ----------------------------------------------
> CREATE TRIGGER "S1"."TR1" AFTER INSERT ON "S1"."T1" FOR EACH STATEMENT insert into s2.t2
select * from t1;
> CREATE TRIGGER "S2"."TR2" AFTER INSERT ON "S1"."T1" FOR EACH STATEMENT insert into t2
select * from s1.t1;
> dblook should either qualify all identifiers in the trigger actions, or contain SET SCHEMA
statements before each of the CREATE TRIGGER statements to ensure the correct implicit schema
is chosen.



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message