[jira] [Updated] (DERBY-6705) Triggers should not allow MERGE statements that reference temporary tables

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

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

Knut Anders Hatlen updated DERBY-6705:
--------------------------------------

    Attachment: d6705-1a.diff

The attached patch [^d6705-1a.diff] makes referencesSessionSchema() descend into MergeNode and its children, so that CREATE TRIGGER detects that the temporary table is referenced.

Strictly speaking, it would be enough for referencesSessionSchema() to inspect the source table and target table of MergeNode, since we don't support subqueries in the ON clause or the matching clauses yet. I chose to make it inspect those clauses too so that the code is ready for it when support for subqueries is added some time in the future.

I have started the regression test suite on the patch.

I guess a more robust and future-proof solution might be to eliminate QTN.referencesSessionSchema() and all its overrides, and instead use a visitor to look for references to the session schema, so that we don't have to remember to duplicate the visiting logic for every new node type that is added. That would be a bigger change, though.

> Triggers should not allow MERGE statements that reference temporary tables
> --------------------------------------------------------------------------
>
>                 Key: DERBY-6705
>                 URL: https://issues.apache.org/jira/browse/DERBY-6705
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.11.1.1
>            Reporter: Knut Anders Hatlen
>            Assignee: Knut Anders Hatlen
>         Attachments: d6705-1a.diff
>
>
> The reference manual topic on the DECLARE GLOBAL TEMPORARY TABLE statement says: "Temporary tables cannot be referenced in a triggeredSQLStatement or in a WHEN clause"
> Still, a CREATE TRIGGER statement succeeds if the triggered SQL statement is a MERGE statement that references a temporary table:
> {noformat}
> ij> connect 'jdbc:derby:memory:db;create=true';
> ij> declare global temporary table temp (x int) not logged;
> 0 rows inserted/updated/deleted
> ij> create table t1(x int);
> 0 rows inserted/updated/deleted
> ij> create trigger tr after insert on t1 insert into session.temp values 1;
> ERROR XCL51: The requested function can not reference tables in SESSION schema.
> ij> create trigger tr after insert on t1 merge into session.temp using t1 on session.temp.x=t1.x when matched then delete;
> 0 rows inserted/updated/deleted
> ij> insert into t1 values 1,2,3;
> 3 rows inserted/updated/deleted
> {noformat}
> The second CREATE TRIGGER statement above should have failed the same way as the first.



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