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-6705) Triggers should not allow MERGE statements that reference temporary tables
Date Fri, 15 Aug 2014 13:17:18 GMT

     [ 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-1b.diff

What I said above about subqueries, wasn't quite right. Subqueries aren't supported in matching
clauses, but they are supported in the ON clause. The patch [^d6705-1b.diff] updates the regression
test with a test case for a statement that has an ON clause with a subquery that references
a temporary table. No changes outside of the tests from the 1a patch, since the 1a patch had
already added code to descend into the ON clause.

> 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, d6705-1b.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)

Mime
View raw message