[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.

     [ 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.

     [ 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.

    [ 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.