[
https://issues.apache.org/jira/browse/TRAFODION-2247?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15530096#comment-15530096
]
Anoop Sharma commented on TRAFODION-2247:
-----------------------------------------
Another case where self referencing upsert with missing values into aligned
tables return
an error if cqd traf_upsert_mode is set to 'MERGE' (default value). The plan
doesn't have
a blocking operator.
>>create table t (a int not null primary key, b int, c int) attribute aligned
>>format;
--- SQL operation complete.
>>set transaction autocommit off;
--- SQL operation complete.
>>upsert into t (a,c) select a,c from t;
*** ERROR[8107] Self-referencing INSERT statement without blocking operator is
not allowed with AUTOCOMMIT OFF.
--- 0 row(s) inserted.
>>explain options 'f' upsert into t (a,c) select a,c from t;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
3 . 4 root x 1.00E+002
1 2 3 tuple_flow 1.00E+002
. . 2 trafodion_merge T 1.00E+000
. . 1 trafodion_scan T 1.00E+002
--- SQL operation complete.
>>cqd traf_upsert_mode 'REPLACE';
--- SQL operation complete.
>>
>>explain options 'f' upsert into t (a,c) select a,c from t;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
4 . 5 root x 1.00E+002
2 3 4 tuple_flow 1.00E+002
. . 3 trafodion_vsbb_upser T 1.00E+000
1 . 2 sort 1.00E+002
. . 1 trafodion_scan T 1.00E+002
--- SQL operation complete.
>>
>>upsert into t (a,c) select a,c from t;
--- 0 row(s) inserted.
>>
> upsert on altered aligned format table with missing columns inserts incorrect
> values
> --------------------------------------------------------------------------------------
>
> Key: TRAFODION-2247
> URL: https://issues.apache.org/jira/browse/TRAFODION-2247
> Project: Apache Trafodion
> Issue Type: Bug
> Reporter: Anoop Sharma
> Assignee: Selvaganesan Govindarajan
> Priority: Minor
>
> If following conditions are present, incorrect values are inserted/returned.
> -- table is an aligned format table
> -- a column is added to it
> -- upsert is used with missing columns
> -- cqd traf_upsert_mode is 'MERGE'
> If cqd is changed to 'REPLACE', correct values are inserted.
> Example 1: (incorrect value inserted)
> >>cqd traf_upsert_mode 'MERGE';
> --- SQL operation complete.
> >>drop table ta;
> --- SQL operation complete.
> >>create table ta
> +>(a char(15) not null primary key,b int)
> +>attribute aligned format;
> --- SQL operation complete.
> >>alter table ta add c int;
> --- SQL operation complete.
> >>upsert into ta (a, c) values ('0001', 2);
> --- 1 row(s) inserted.
> >>select c from ta;
> C
> -----------
> -1340071904
> --- 1 row(s) selected.
> >>
> >>
> Example 2: (correct value inserted)
> >>cqd traf_upsert_mode 'REPLACE';
> --- SQL operation complete.
> >>drop table ta;
> --- SQL operation complete.
> >>create table ta
> +>(a char(15) not null primary key,b int)
> +>attribute aligned format;
> --- SQL operation complete.
> >>alter table ta add c int;
> --- SQL operation complete.
> >>upsert into ta (a, c) values ('0001', 2);
> --- 1 row(s) inserted.
> >>select c from ta;
> C
> -----------
> 2
> --- 1 row(s) selected.
> >>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
