Derby
  1. Derby
  2. DERBY-4348

Copy table content with "INSERT INTO table SELECT FROM (...)" statement leads to corrupt data

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.1, 10.1.1.0, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.4.2.0, 10.5.1.1, 10.6.1.0
    • Fix Version/s: 10.5.3.1, 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Derby: embedded driver 10.5.1.1 - (764942); testet with 10.4 and client driver also
      OS: Windows XP
      SQL statements executed using SQuirrel SQL Client, but behavior is the same with ij
    • Urgency:
      Urgent
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Data corruption

      Description

      I had to change a the primary key of a table and used ddlutils to do so. Ddlutils recreated the table to perform this task.
      After the schema conversion the row data of the changed table were corrupted.
      The values of the last table column were filled with values from other rows!

      After performing a few tests I could break down the problem to the SQL statement "INSERT INTO table SELECT FROM (...)"

      To reprocude the effect do the following:

      1. unpack attached database 'rmdb.zip'

      2. connect to the database with embedded driver
      User: IGEL
      Password: test

      3. read data of an example row from database
      select * from stringrangenew where classname = 'x.xserver%.colordepth';

      result is
      x.xserver%.colordepth 2 [16] [24] [32]

      4. now copy the data to the second table (column INSTANCENR has been added to this table)
      INSERT INTO STRINGRANGENEW_ (CLASSNAME,FIRMWAREID,RVALUE) SELECT CLASSNAME,FIRMWAREID,RVALUE FROM STRINGRANGENEW;

      5. select data of example row from second table
      select * from stringrangenew_ where classname = 'x.xserver%.colordepth';

      result is
      x.xserver%.colordepth 2 -1 [CCW][CW][XX]

      -> value of last column is not the same as in orignal table!

      Here some additional information i worked out during my tests:

      • if you change the copy statement to include the additional column INSTANCENR, the copied data are correct.
        delete from STRINGRANGENEW_;
        INSERT INTO STRINGRANGENEW_ (CLASSNAME,FIRMWAREID, INSTANCENR, RVALUE) SELECT CLASSNAME,FIRMWAREID, -1, RVALUE FROM STRINGRANGENEW;
      • if you select the rows only 'SELECT CLASSNAME,FIRMWAREID,RVALUE FROM STRINGRANGENEW', the result shows correct data

      Note:
      The effect is not restricted to this row but also applies to other rows. But it's always the same rows, that get corrupted.

      1. rmdb.zip
        391 kB
        Stefan Huber
      2. d4348.sql
        0.6 kB
        Kristian Waagan
      3. out.dat
        152 kB
        Kristian Waagan
      4. d4348-import.sql
        2 kB
        Kristian Waagan
      5. D4348.java
        1 kB
        Knut Anders Hatlen
      6. derby-4348-1a.diff
        8 kB
        Knut Anders Hatlen
      7. derby-4348-1a.stat
        0.2 kB
        Knut Anders Hatlen

        Activity

        No work has yet been logged on this issue.

          People

          • Assignee:
            Knut Anders Hatlen
            Reporter:
            Stefan Huber
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development