[
https://issues.apache.org/jira/browse/TRAFODION-130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14696147#comment-14696147
]
Atanu Mishra commented on TRAFODION-130:
----------------------------------------
Weishiun Tsai (wei-shiun-tsai) wrote on 2013-11-02: #1
Verified on 11/2/2013, this bug has been fixed:
>>set schema seabase.mytest;
--- 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 a
TOPL 2222 a a
TOPL 3333 a a
--- 3 row(s) selected.
Weishiun Tsai (wei-shiun-tsai) on 2013-11-05
Changed in trafodion:
status: New → Fix Released
> 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
> Fix For: 1.0 (pre-incubation)
>
>
> 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)
