, ...) fails
[ https://issues.apache.org/jira/browse/TRAFODION-1590?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14992219#comment-14992219 ]
Suresh Subbiah commented on TRAFODION-1590:
-------------------------------------------
upsert on a table with index is transformed to merge. merge does not support
subquery in on, set and insert values clause in general. In this change
we do 2 things
a) make sure that when upsert is transformed to merge there will always
be a using clause. In other words, the row to be upserted will be flowed
to merge operator through a TSJ. This is now true even if there is only
one row to be upserted. Previously the one row case was treated differently.
But that approach cannot handle subqueries in that one row
b) merge restrictions for subquery and udf are relaxed if we know the
merge was created for an upsert. This is valid because an upsert creates a
specific type of merge where the ON, SET and INSERT VALUES clauses use only
vids from the using clause. This is a specific and simpler situation of the
general MERGE statement. With upsert and the idea mentioned in item a) above
we can be sure that subquery will always be evaluated before we get to
the merge statement. Even optimizer will not rearrange the joins because
the merge has specified the subquery as its characteristic input
Ideas for this change are due to Dave, Selva and Hans.
> UPSERT into <table> VALUES (<subquery>, ...) fails
> --------------------------------------------------
>
> Key: TRAFODION-1590
> URL: https://issues.apache.org/jira/browse/TRAFODION-1590
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 1.2-incubating
> Reporter: Suresh Subbiah
> Assignee: Suresh Subbiah
> Fix For: 1.3-incubating
>
>
> UPSERT into a table with an index and a subquery in the values clause fails with errorcode 3241.
> This will show the problem
> create table t015t7 (a int not null, b largeint generated by default as identity not null not droppable unique, c int, primary key(a)) ;
> upsert into t015t7 values (1, NVL((select b from t015t7 where a = 1),DEFAULT),10) ;
> select * from t015t7;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)