[jira] Updated: (DERBY-4348) Copy table content with "INSERT INTO table SELECT FROM (...)" statement leads to corrupt data

["Stefan Huber (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-4348?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Stefan Huber updated DERBY-4348:
--------------------------------

    Attachment: rmdb.zip

> Copy table content with "INSERT INTO table SELECT FROM (...)" statement leads to corrupt data
> ---------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4348
>                 URL: https://issues.apache.org/jira/browse/DERBY-4348
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.1.1
>         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
>            Reporter: Stefan Huber
>            Priority: Critical
>         Attachments: rmdb.zip
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.