You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Lakshmi Prasanna (JIRA)" <ji...@apache.org> on 2009/03/23 11:17:50 UTC
[jira] Created: (DERBY-4110) When deleting rows from a table name
with its synonym name, Derby throws SQLSTATE 42X04.
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
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.
[jira] Updated: (DERBY-4110) When deleting rows from a table name
with its synonym name, Derby throws SQLSTATE 42X04.
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4110?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton updated DERBY-4110:
-----------------------------------
Attachment: repro.sql
Thanks Boris! Adding the CREATE UNIQUE INDEX did the trick.
I can now reproduce the strange behavior in both the 10.4 release and in the trunk.
Attached is 'repro.sql', which causes the strange message to appear.
I'll see if I can figure out what is going on under the covers.
> 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.
[jira] Updated: (DERBY-4110) When deleting rows from a table name
with its synonym name, Derby throws SQLSTATE 42X04.
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4110?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton updated DERBY-4110:
-----------------------------------
Attachment: patch.diff
Attached is a patch proposal containing the code change and a regression
test based on the repro script.
The full regression test was clean.
Any suggestions for additional regression tests, particularly w.r.t. synonym
handling, are welcome.
I'm proposing to commit this patch, since it appears to resolve the
repro script and doesn't break any other tests.
> 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: patch.diff, 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.
[jira] Commented: (DERBY-4110) When deleting rows from a table name
with its synonym name, Derby throws SQLSTATE 42X04.
Posted by "Boris Just (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4110?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12782515#action_12782515 ]
Boris Just commented on DERBY-4110:
-----------------------------------
Hi Bryan!
There is a difference between your script and my definition. In addition I have defined an unique index on the table badax.r058100.
Without index it also works on my database.
IJ Version 10.5
ij> connect 'jdbc:derby:d:\temp\testDB;create=true';
ij> create schema BADAX;
0 Zeilen eingef³gt/aktualisiert/gel÷scht
ij> create table BADAX.R058100 (CHGTSP TIMESTAMP, DELFLAG CHAR(1), LIZENZ INTEGER, KUNDNR INTEGER, ERSTTSP TIMESTAMP, LFDNR SMALLINT, KONTAKTART CHAR(2), KONTAKTDAT INTEGER, SCHLAGW CHAR(60), TXT VARCHAR(2048), ERSTSPARTE CHAR(4), ERSTPRODNR CHAR(11), ERSTPGM CHAR(8), VERSION SMALLINT);
0 Zeilen eingef³gt/aktualisiert/gel÷scht
ij> create synonym BADAX.Q058100 FOR BADAX.R058100;
0 Zeilen eingef³gt/aktualisiert/gel÷scht
ij> CREATE UNIQUE INDEX XR0581B ON BADAX.R058100(LIZENZ,KUNDNR,LFDNR,ERSTTSP);
0 Zeilen eingef³gt/aktualisiert/gel÷scht
ij> insert into "BADAX"."R058100" ("CHGTSP", "DELFLAG", "LIZENZ", "KUNDNR", "ERS
TTSP", "LFDNR", "KONTAKTART", "KONTAKTDAT", "SCHLAGW", "TXT", "ERSTSPARTE", "ERS
TPRODNR", "ERSTPGM", "VERSION") values ('2009-10-16 13:38:21.781', 'A', 105911,1
377917, '2009-10-16 13:38:21.781', 999, 'F ', 20091016, 'Fonds-Polizze/08
', '<dokumente><dokument logicalID="2009-10-16-13
.38.21.734000" physicalID="P1255693101734.pdf" status="V"/></dokumente>', ' '
, ' ', ' ', 0);
1 Zeile eingef³gt/aktualisiert/gel÷scht
ij> delete from BADAX.Q058100;
FEHLER 42X04: Die Spalte 'BADAX.R058100.LIZENZ' ist in keiner Tabelle der FROM-L
iste enthalten, erscheint in einer Verkn³pfungsspezifikation und befindet sich a
u▀erhalb des Geltungsbereichs derselben oder erscheint in einer HAVING-Klausel u
nd ist nicht in der 'GROUP BY'-Liste enthalten. Wenn es sich um eine Anweisung C
REATE oder ALTER TABLE handelt, ist 'BADAX.R058100.LIZENZ' keine Spalte in der Z
ieltabelle.
> 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
>
> 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.
[jira] Assigned: (DERBY-4110) When deleting rows from a table name
with its synonym name, Derby throws SQLSTATE 42X04.
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4110?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton reassigned DERBY-4110:
--------------------------------------
Assignee: Bryan Pendleton
> 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
>
> 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.
[jira] Commented: (DERBY-4110) When deleting rows from a table name
with its synonym name, Derby throws SQLSTATE 42X04.
Posted by "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.
[jira] Commented: (DERBY-4110) When deleting rows from a table name
with its synonym name, Derby throws SQLSTATE 42X04.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4110?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12794119#action_12794119 ]
Knut Anders Hatlen commented on DERBY-4110:
-------------------------------------------
+1 to commit. This looks like an improvement to me. I won't claim that I have understood if/how normalizeSynonymColumns() should come into play here, though...
> 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: patch.diff, 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.
[jira] Commented: (DERBY-4110) When deleting rows from a table name
with its synonym name, Derby throws SQLSTATE 42X04.
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4110?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12781211#action_12781211 ]
Bryan Pendleton commented on DERBY-4110:
----------------------------------------
I ran the script from DERBY-1784 against both the 10.4.1.3 release as well as against the trunk,
and the script runs fine, so it appears that DERBY-1784 is definitely fixed both in 10.4.1.3
as well as in the trunk. I also checked the subversion logs, and they show that the DERBY-1784
fix is present in both places. So I think that the DERBY-1784 fix is fine.
Below is the output of the script I ran.
Is there a script which can reproduce this problem more exactly, either in 10.4.1.3 or in the trunk?
C:\temp\d1784>java -cp \tools\derby\db-derby-10.4.1.3-bin\lib\derbyrun.jar org.apache.derby.tools.ij
ij version 10.4
ij> run '/temp/d1784.sql';
ij> connect 'jdbc:derby:wombat;create=true' user 'user1' as user1;
ij> create schema test1;
0 rows inserted/updated/deleted
ij> create schema test2;
0 rows inserted/updated/deleted
ij> create table test1.t1 ( id bigint not null );
0 rows inserted/updated/deleted
ij> insert into test1.t1 values (1),(2);
2 rows inserted/updated/deleted
ij> create synonym test2.t1 for test1.t1;
0 rows inserted/updated/deleted
ij> set schema test1;
0 rows inserted/updated/deleted
ij> select t1.id from t1;
ID
--------------------
1
2
2 rows selected
ij> set schema test2;
0 rows inserted/updated/deleted
ij> select t1.id from t1;
ID
--------------------
1
2
2 rows selected
ij> select id from t1;
ID
--------------------
1
2
2 rows selected
ij> delete from t1 where id = 2;
1 row inserted/updated/deleted
> 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
>
> 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.
[jira] Resolved: (DERBY-4110) When deleting rows from a table name
with its synonym name, Derby throws SQLSTATE 42X04.
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4110?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton resolved DERBY-4110.
------------------------------------
Resolution: Fixed
Fix Version/s: 10.6.0.0
Thanks for the code review, Knut. I don't have any further improvements
to make to this patch at this time, so I'm committing it to the trunk for
further shakeout and usage.
Committed to the trunk as revision 896722.
I'm not planning to backport this fix to earlier branches at this time,
although I don't think it would be hard to do, if somebody else wants to.
> 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
> Fix For: 10.6.0.0
>
> Attachments: patch.diff, 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.
[jira] Commented: (DERBY-4110) When deleting rows from a table name
with its synonym name, Derby throws SQLSTATE 42X04.
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4110?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12782450#action_12782450 ]
Bryan Pendleton commented on DERBY-4110:
----------------------------------------
Thanks Boris.
Do you have a script you can contribute which demonstrates the problem?
Can you run the script that I posted above? Does it fail for you in 10.4.1.3?
> 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
>
> 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.
[jira] Commented: (DERBY-4110) When deleting rows from a table name
with its synonym name, Derby throws SQLSTATE 42X04.
Posted by "Boris Just (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4110?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12782340#action_12782340 ]
Boris Just commented on DERBY-4110:
-----------------------------------
I have the same Problem. We are already working with Derby 10.1.3.1. We upgraded to Version 10.5.3.0 and encrypted the database with dataEncryption=true. Everything works fine. Only deletes on synonym tables fail with ERROR 42X04 and the mentioned message. I have also tried 10.4.1.3 and it also failed.
> 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
>
> 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.
[jira] Updated: (DERBY-4110) When deleting rows from a table name
with its synonym name, Derby throws SQLSTATE 42X04.
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4110?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-4110:
---------------------------------
Urgency: Normal (was: Urgent)
Triaged July 2, 2009: Downgrading urgency to normal.
> 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
>
> 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.
[jira] Commented: (DERBY-4110) When deleting rows from a table name
with its synonym name, Derby throws SQLSTATE 42X04.
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4110?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12782573#action_12782573 ]
Bryan Pendleton commented on DERBY-4110:
----------------------------------------
I see that UpdateNode and InsertNode have some special code in their
bindStatement() methods to call normalizeSynonymColumns, but
DeleteNode does not have such special code. That looks intriguing,
so I'll chase that theory for a while...
> 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.