[
https://issues.apache.org/jira/browse/DERBY-6543?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13970615#comment-13970615
]
Knut Anders Hatlen commented on DERBY-6543:
-------------------------------------------
The bug is caused by the rewrite of the SQL statement that happens in
DataDictionaryImpl.getTriggerActionString(), where the original trigger action
is supposed to be rewritten to:
{noformat}
insert into t1 select x from t2 where x = CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(1)
AS INTEGER)
{noformat}
The logic is broken and instead rewrites it to:
{noformat}
insert into t1 select x from t2 where x = CAST
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(1)
AS INTEGER) x
{noformat}
Notice the trailing "x" that makes the statement invalid and causes the syntax
error.
I believe it is the following line at the end of the for loop in said method
that's doing the wrong thing:
{code}
start = tokEndOffset- actionOffset + columnLength + 2;
{code}
It is supposed to make the start variable point after the end of the column
reference. That is, just after the "x" in "new . x".
The buggy expression contains the magic number 2. It would probably have been
clearer if it had been split up like this:
{code}
start = (tokEndOffset + 1) - actionOffset + 1 +
columnLength;
{code}
The first +1 is to adjust the end offset so that it points just after the last
character of the "new" token, instead of pointing at the last character. This
is fine.
The second +1 is to jump past the period sign to the beginning of the column
name. If there are whitespace characters around the period sign, adding 1 isn't
enough to jump to the beginning of the column name. This means parts of the
column name are also seen in the next iteration, and the resulting SQL text
gets garbled.
> Syntax error when reference to transition variable has whitespace around it
> ---------------------------------------------------------------------------
>
> Key: DERBY-6543
> URL: https://issues.apache.org/jira/browse/DERBY-6543
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.2.0
> Reporter: Knut Anders Hatlen
> Assignee: Knut Anders Hatlen
>
> If a trigger action references a transition variable and has whitespace
> around the period sign that separates the transition variable name from the
> column name, it fails with a syntax error.
> For example:
> {noformat}
> ij> create table t1(x int);
> 0 rows inserted/updated/deleted
> ij> create table t2(x int);
> 0 rows inserted/updated/deleted
> ij> --fails
> create trigger tr1 after insert on t1 referencing new as new
> for each row insert into t1 select x from t2 where x = new . x;
> ERROR 42X01: Syntax error: Encountered "x" at line 1, column 150.
> 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.
> ij> --succeeds
> create trigger tr2 after insert on t1 referencing new as new
> for each row insert into t1 select x from t2 where x = new.x;
> 0 rows inserted/updated/deleted
> {noformat}
> The only difference between the failing trigger definition and the successful
> one, is that the former says "new . x" and the latter "new.x".
> Both should succeed.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
