Derby
  1. Derby
  2. DERBY-4110

When deleting rows from a table name with its synonym name, Derby throws SQLSTATE 42X04.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.4.1.3
    • Fix Version/s: 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal

      Description

      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?

      1. patch.diff
        2 kB
        Bryan Pendleton
      2. repro.sql
        0.4 kB
        Bryan Pendleton

        Activity

        Hide
        Rick Hillegas added a comment -

        Triaged July 2, 2009: Downgrading urgency to normal.

        Show
        Rick Hillegas added a comment - Triaged July 2, 2009: Downgrading urgency to normal.
        Hide
        Bryan Pendleton added a comment -

        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

        Show
        Bryan Pendleton added a comment - 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
        Hide
        Boris Just added a comment -

        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.

        Show
        Boris Just added a comment - 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.
        Hide
        Bryan Pendleton added a comment -

        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?

        Show
        Bryan Pendleton added a comment - 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?
        Hide
        Boris Just added a comment -

        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.

        Show
        Boris Just added a comment - 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.
        Hide
        Bryan Pendleton added a comment -

        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.

        Show
        Bryan Pendleton added a comment - 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.
        Hide
        Bryan Pendleton added a comment -

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

        Show
        Bryan Pendleton added a comment - 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...
        Hide
        Bryan Pendleton added a comment -

        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,
        Show
        Bryan Pendleton added a comment - 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,
        Hide
        Bryan Pendleton added a comment -

        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.

        Show
        Bryan Pendleton added a comment - 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.
        Hide
        Knut Anders Hatlen added a comment -

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

        Show
        Knut Anders Hatlen added a comment - +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...
        Hide
        Bryan Pendleton added a comment -

        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.

        Show
        Bryan Pendleton added a comment - 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.
        Hide
        Knut Anders Hatlen added a comment -

        [bulk update] Close all resolved issues that haven't been updated for more than one year.

        Show
        Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.

          People

          • Assignee:
            Bryan Pendleton
            Reporter:
            Lakshmi Prasanna
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development