[jira] [Created] (TRAFODION-151) LP Bug: 1250681 - UPDATE with self-referencing subquery ends up with a corrupted value

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-151:
------------------------------------

             Summary: LP Bug: 1250681 - UPDATE with self-referencing subquery ends up with a corrupted value
                 Key: TRAFODION-151
                 URL: https://issues.apache.org/jira/browse/TRAFODION-151
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Anoop Sharma
            Priority: Critical


The following example shows that the UPDATE stmt with a self-referencing SELECT subquery updates column j to 0 (or sometimes a random corrupted value) while it should be updated to 1.  This seems to happen only when column i is declared as a primary key.  The example shows 2 sequences.  The 1st sequence declares column i as a primary key and UPDATE column j results in a wrong value.  The 2nd sequence does not declare column i as a primary key, and UPDATE column j results in the right value.

>>set schema seabase.mytest;

--- SQL operation complete.
>>create table t (i int not null primary key, j int);

--- SQL operation complete.
>>insert into t values(1, 2);

--- 1 row(s) inserted.
>>update t set j=i where i in (select i from t);

--- 1 row(s) updated.
>>select * from t;

I            J
-----------  -----------

          1            0

--- 1 row(s) selected.
>>drop table t;

--- SQL operation complete.
>>create table t (i int, j int);

--- SQL operation complete.
>>insert into t values(1, 2);

--- 1 row(s) inserted.
>>update t set j=i where i in (select i from t);

--- 1 row(s) updated.
>>select * from t;

I            J
-----------  -----------

          1            1

--- 1 row(s) selected.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)