[ 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)