[
https://issues.apache.org/jira/browse/TRAFODION-1791?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah resolved TRAFODION-1791.
---------------------------------------
Resolution: Fixed
> UPSERT with bare one row subquery in VALUES clause fails
> --------------------------------------------------------
>
> Key: TRAFODION-1791
> URL: https://issues.apache.org/jira/browse/TRAFODION-1791
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 1.2-incubating
> Reporter: Suresh Subbiah
> Assignee: Suresh Subbiah
> Fix For: 2.0-incubating
>
>
> When a table with an index is upserted into using a statement of this form
> UPSERT INTO <table> VALUES(...<bare-subquery>);
> we get a compiler error in the generator phase with error code 7000.
> CREATE TABLE DEC
> (
> ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 5000) NOT NULL,
> codeValue varchar(1000),
> title varchar(100),
> shortName varchar(200),
> description varchar(1000),
> path varchar(1000),
> synonyms varchar(1000),
> objectClassCode varchar(100),
> propertyCode varchar(100),
> PRIMARY KEY (ID)
> );
> CREATE INDEX IDX3_DEC_T_CodeValue ON DEC(codeValue);
> CREATE TABLE DE
> (
> ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 5000) NOT NULL,
> dataElementConceptID int unsigned,
> valueDomainID int unsigned,
> PRIMARY KEY (ID)
> );
> ALTER TABLE DE
> ADD CONSTRAINT FK1_DE_DEC
> FOREIGN KEY (dataElementConceptID)
> REFERENCES DEC(ID);
> CREATE INDEX IDX1_DE ON DE(dataElementConceptID, valueDomainID);
> --- gives error 7000
> prepare s9 from UPSERT INTO DE (ID, dataElementConceptID, valueDomainID)
> VALUES (1, (select d.id from DEC d where d.codeValue = 'aa'), 3 ) ;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
