[jira] Commented: (DERBY-4110) When deleting rows from a table name with its synonym name, Derby throws SQLSTATE 42X04.

["Bryan Pendleton (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-4110?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12784252#action_12784252 ] 

Bryan Pendleton commented on DERBY-4110:
----------------------------------------

The patch below appears to make the repro script pass, and appears to cause
no other ill effects (in simple testing). The idea behind the patch is that when
building the ResultColumnList that is used to tie together the search results
from the WHERE clause with the processing of the DELETE, the result columns
will be bound against the exposed table name of the table named in the DELETE
statement, so if the statement used a synonymTableName, then the result
column list should manufacture column references which use a synonymTableName as well.

I'm not sure about the operation of the 'normalizeSynonymColumns' code that I
commented on last week; I don't understand exactly when that code comes into play,
but it does NOT appear to be relevant to this particular case. I think it has more to
do with the fact that INSERT and UPDATE can refer to a named set of columns which
are to be affected by the statement, whereas DELETE operates on the entire row.

Anyway, here's the patch I'm currently pursuing:

Index: java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java  (revision 885667)
+++ java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java  (working copy)
@@ -361,7 +361,7 @@
      (FromBaseTable)
        (getNodeFactory().getNode(
                    C_NodeTypes.FROM_BASE_TABLE,
-                   targetTableName,
+                   synonymTableName != null ? synonymTableName : targetTableName,
                    null,
                    null,
                    null,


> When deleting rows from a table name with its synonym name, Derby throws SQLSTATE 42X04.
> ----------------------------------------------------------------------------------------
>
>                 Key: DERBY-4110
>                 URL: https://issues.apache.org/jira/browse/DERBY-4110
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.1.3
>            Reporter: Lakshmi Prasanna
>            Assignee: Bryan Pendleton
>         Attachments: repro.sql
>
>
> Getting an error (using Derby 10.4.1.3) when deleting records from a table using a qualified synonymn name.
> Table Name is ABC_PROCESS_INVOCATION_XML and the synonmyn name is SYN_ABC_P_I_X
> The query being fired was
> "Delete from SYN_ABC_P_I_X where ID = ? "
> from the error log
> ---------------
> Caused by: ERROR 42X04: Column 'APP.ABC_PROCESS_INVOCATION_XML.ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'APP.ABC_PROCESS_INVOCATION_XML.ID' is not a column in the target table.
> ---------------
> After going thru few earlier bugs, a similar bug has been logged earlier bug DERBY-1784 
> http://issues.apache.org/jira/browse/DERBY-1784
> But looks like the fix did not make its way to 10.4.1.3?

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.