[DERBY-6357] CREATE TRIGGER doesn't detect all illegal references to SESSION schema

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.10.1.1
• Fix Version/s: 10.10.2.0, 10.11.1.1
• Component/s: SQL
• Urgency: Normal
• Issue & fix info: Repro attached
• Assignee: Knut Anders Hatlen
• Reporter: Knut Anders Hatlen
• Created: 30/Sep/13 12:22
• Updated: 09/Sep/16 09:54
• Resolved: 01/Oct/13 09:08

Description

The reference manual's section on the DECLARE GLOBAL TEMPORARY TABLE statement says: Temporary tables cannot be referenced in a triggered-SQL-statement

It detects most cases and reports:

ERROR XCL51: The requested function can not reference tables in SESSION schema.

However, some references are not detected if they are in a subquery. For example:

ij version 10.10
ij> connect 'jdbc:derby:memory:db;create=true';
ij> declare global temporary table temptable(x int) not logged;
0 rows inserted/updated/deleted
ij> create table t1(x int);
0 rows inserted/updated/deleted
ij> create table t2(b boolean);
0 rows inserted/updated/deleted
ij> create trigger tr1 after insert on t1 insert into t2 values exists(select * from session.temptable);
0 rows inserted/updated/deleted

This CREATE TRIGGER statement should have raised an error.

Issue Links

• is related to: DERBY-6705 Triggers should not allow MERGE statements that reference temporary tables
• relates to: DERBY-534 Support use of the WHEN clause in CREATE TRIGGER statements