[jira] [Created] (TRAFODION-130) LP Bug: 1244027 - UPSERT with specified column list changes unspecified column to NULL

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

Alice Chen created TRAFODION-130:
------------------------------------

             Summary: LP Bug: 1244027 - UPSERT with specified column list changes unspecified column to NULL
                 Key: TRAFODION-130
                 URL: https://issues.apache.org/jira/browse/TRAFODION-130
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-general
            Reporter: Weishiun Tsai
            Assignee: Anoop Sharma
            Priority: Critical


When specifying a list of columns to UPSERT, and when the statement updates records instead of inserting records, it somehow changes a column that is not on the list to NULL.   In the following example, the table has 4 columns: a, b, c, d. The UPSERT statement is given the column list (b, a, c).  The statement essentially updates column c in every row to the value of column d from the same row.  But it somehow also changes all values in column d back to its default value as NULL:

>>set schema seabase.phoenix;

--- SQL operation complete.
>>create table t (a char(15) no default not null not droppable, b char(12) no de
fault not null not droppable, c varchar(5) default null, d varchar(5) default nu
ll, constraint pk_t primary key (a, b));

--- SQL operation complete.
>>insert into t values ('TOPL', '1111', null, 'a'),('TOPL', '2222', null, 'a')
,('TOPL', '3333', null, 'a');

--- 3 row(s) inserted.
>>select * from t;

A                B             C      D
---------------  ------------  -----  -----

TOPL             1111          ?      a
TOPL             2222          ?      a
TOPL             3333          ?      a

--- 3 row(s) selected.
>>upsert into t (b, a, c) select b, a, d from t where a='TOPL';

--- 3 row(s) inserted.
>>select * from t;

A                B             C      D
---------------  ------------  -----  -----

TOPL             1111          a      ?
TOPL             2222          a      ?
TOPL             3333          a      ?

--- 3 row(s) selected.



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