[
https://issues.apache.org/jira/browse/DERBY-6351?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13776178#comment-13776178
]
Knut Anders Hatlen commented on DERBY-6351:
-------------------------------------------
The bug is caused by a flaw in the code that transforms the trigger action text
for statement triggers and replaces references to transition tables with
references to VTIs (org.apache.derby.catalog.TriggerOldTransitionRows and
org.apache.derby.catalog.TriggerNewTransitionRows).
{code:java|title=CreateTriggerNode.bindReferencesClause()}
/*
** If the user supplied a correlation, then just
** pick it up automatically; otherwise, supply
** the default.
*/
if (refTableName.equals(baseTableName))
{
newText.append(baseTableName).append("
");
}
{code}
This code is supposed to add a correlation name to the transformed SQL text
only if no correlation name is specified in the original SQL text. However, it
does not distinguish between the case where no correlation name is specified
and the case where the correlation name is equal to the base table name. If a
correlation name equal to the base table name is specified, the transformed SQL
text ends up with two correlation names for the table, which causes the syntax
error.
In the CREATE TRIGGER statement in the bug description,
{{insert into t2 select x from n as n}}
is rewritten to
{{insert into t2 select x from new
org.apache.derby.catalog.TriggerNewTransitionRows() N as n}}
whereas it should have been rewritten to
{{insert into t2 select x from new
org.apache.derby.catalog.TriggerNewTransitionRows() as n}}
> Syntax error on equal transition table name and correlation name in statement
> trigger
> -------------------------------------------------------------------------------------
>
> Key: DERBY-6351
> URL: https://issues.apache.org/jira/browse/DERBY-6351
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.5.1.1, 10.6.1.0, 10.7.1.1, 10.8.1.2,
> 10.9.1.0, 10.10.1.1
> Reporter: Knut Anders Hatlen
> Assignee: Knut Anders Hatlen
> Priority: Minor
>
> If a reference to a transition table in a statement trigger has a correlation
> name, and that correlation name is equal to the name of the transition table,
> the CREATE TRIGGER statement fails with a syntax error.
> To reproduce:
> {noformat}
> ij version 10.10
> ij> connect 'jdbc:derby:db;create=true';
> ij> create table t1(x int);
> 0 rows inserted/updated/deleted
> ij> create table t2(x int);
> 0 rows inserted/updated/deleted
> ij> create trigger tr1 after insert on t1 referencing new table as n insert
> into t2 select x from n as n;
> ERROR 42X01: Syntax error: Encountered "as" at line 1, column 89.
> Issue the 'help' command for general information on IJ command syntax.
> Any unrecognized commands are treated as potential SQL commands and executed
> directly.
> Consult your DBMS server reference documentation for details of the SQL
> syntax supported by your server.
> {noformat}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
