db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Stefan Huber (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4348) Copy table content with "INSERT INTO table SELECT FROM (...)" statement leads to corrupt data
Date Wed, 12 Aug 2009 13:23:14 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4348?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12742343#action_12742343
] 

Stefan Huber commented on DERBY-4348:
-------------------------------------

Orignal table create statements:

CREATE TABLE STRINGRANGENEW
(
    CLASSNAME VARCHAR(200) NOT NULL,
    FIRMWAREID INTEGER NOT NULL,
    RVALUE LONG VARCHAR NOT NULL,
    PRIMARY KEY (CLASSNAME, FIRMWAREID)
);
CREATE INDEX IDX_CLASSNAME_05 ON STRINGRANGENEW (CLASSNAME);


Statements for second table:

CREATE TABLE STRINGRANGENEW_
(
    CLASSNAME VARCHAR(200) NOT NULL,
    FIRMWAREID INTEGER NOT NULL,
    INSTANCENR INTEGER DEFAULT -1 NOT NULL,
    RVALUE LONG VARCHAR NOT NULL,
    PRIMARY KEY (CLASSNAME, FIRMWAREID, INSTANCENR)
);

(All statements are created by DDLUTILS automatically)


There are no explicit indexes created for table STRINGRANGENEW_.

The orignial table STRINGRANGENEW has additional foreign keys (one on column CLASSNAME and
another one on column FIRMWAREID) in production environment. If you need the complete production
database for further examination, please let me know.


> 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: d4348.sql, 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.


Mime
View raw message