Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4110

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

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.4.1.3
    • 10.6.1.0
    • SQL
    • None
    • 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?

      Attachments

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

        Activity

          Triaged July 2, 2009: Downgrading urgency to normal.

          rhillegas Richard N. Hillegas added a comment - Triaged July 2, 2009: Downgrading urgency to normal.

          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

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

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

          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?

          bryanpendleton 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?
          bjustx 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.

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

          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.

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

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

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

          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,
          bryanpendleton 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,

          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.

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

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

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

          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.

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

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

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

          People

            bryanpendleton Bryan Pendleton
            prasanna_pramati Lakshmi Prasanna
            Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: