You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by se...@apache.org on 2016/01/31 02:26:03 UTC
[1/2] incubator-trafodion git commit: [TRAFODION-1791] UPSERT with
bare one row subquery in VALUES clause fails [TRAFODION-1792] UPSERT with
parameters in VALUES clause fails
Repository: incubator-trafodion
Updated Branches:
refs/heads/master 77581c107 -> 7a6360264
[TRAFODION-1791] UPSERT with bare one row subquery in VALUES clause fails
[TRAFODION-1792] UPSERT with parameters in VALUES clause fails
Please see JIRA for explanation of the fix. Fix for 1791 is in NormRelExpr.cpp.
While change in BindRelExpr.cpp is for 1792. Regression tests have been added with a
few different variations.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/c0a22527
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/c0a22527
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/c0a22527
Branch: refs/heads/master
Commit: c0a2252743958236e060de6ce8287582ea16ab0d
Parents: 88a5cb4
Author: Suresh Subbiah <su...@apache.org>
Authored: Thu Jan 28 23:43:15 2016 +0000
Committer: Suresh Subbiah <su...@apache.org>
Committed: Thu Jan 28 23:43:15 2016 +0000
----------------------------------------------------------------------
core/sql/optimizer/BindRelExpr.cpp | 17 ++-
core/sql/optimizer/BindWA.cpp | 31 +++---
core/sql/optimizer/BindWA.h | 5 +-
core/sql/optimizer/NormRelExpr.cpp | 37 ++++++-
core/sql/optimizer/RelExpr.cpp | 1 +
core/sql/optimizer/RelJoin.h | 10 ++
core/sql/regress/executor/EXPECTED015.SB | 144 ++++++++++++++++++++++++++
core/sql/regress/executor/TEST015 | 110 ++++++++++++++++++++
8 files changed, 329 insertions(+), 26 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c0a22527/core/sql/optimizer/BindRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindRelExpr.cpp b/core/sql/optimizer/BindRelExpr.cpp
index 963b609..6852237 100644
--- a/core/sql/optimizer/BindRelExpr.cpp
+++ b/core/sql/optimizer/BindRelExpr.cpp
@@ -8611,7 +8611,7 @@ RelExpr *RenameReference::bindNode(BindWA *bindWA)
bindWA->getCurrentScope()->setRETDesc(prevRETDesc);
// Now merge the outer references into the previous scope.
- bindWA->getCurrentScope()->mergeOuterRefs(myOuterRefs);
+ bindWA->getCurrentScope()->mergeOuterRefs(myOuterRefs, FALSE);
return boundNode;
} // RenameReference::bindNode()
@@ -10236,7 +10236,8 @@ RelExpr* Insert::xformUpsertToMerge(BindWA *bindWA)
setAssign = new(bindWA->wHeap()) ItemList(setAssign,setAssignPrev);
}
}
- myOuterRefs += sourceVals[i];
+ if (sourceVals[i].getItemExpr()->getOperatorType() != ITM_CONSTANT)
+ myOuterRefs += sourceVals[i];
insertValPrev = insertVal;
insertColPrev = insertCol ;
@@ -10272,8 +10273,14 @@ RelExpr* Insert::xformUpsertToMerge(BindWA *bindWA)
((Join*)re)->doNotTransformToTSJ();
((Join*)re)->setTSJForMerge(TRUE);
((Join*)re)->setTSJForMergeWithInsert(TRUE);
+ ((Join*)re)->setTSJForMergeUpsert(TRUE);
((Join*)re)->setTSJForWrite(TRUE);
- if (bindWA->hasDynamicRowsetsInQuery())
+
+ // if Inputs of current insert are empty (i.e. we have no params/rowsets)
+ // then there will be no pull up of inputs during transform and the join will
+ // not see the inputs of the mergeUpdate due to intermediate nodes. So
+ // add inputs directly to join and use elimination to remove extra inputs
+ if (NOT getGroupAttr()->getCharacteristicInputs().isEmpty())
mu->getGroupAttr()->addCharacteristicInputs(myOuterRefs);
else
re->getGroupAttr()->addCharacteristicInputs(myOuterRefs);
@@ -10738,7 +10745,7 @@ RelExpr *MergeUpdate::bindNode(BindWA *bindWA)
getGroupAttr()->addCharacteristicInputs
(bindWA->getCurrentScope()->getOuterRefs());
}
- bindWA->removeCurrentScope();
+ bindWA->removeCurrentScope(xformedUpsert()); // keepLocalRefs for Upsert
bindWA->setMergeStatement(TRUE);
@@ -11097,7 +11104,7 @@ RelExpr *MergeDelete::bindNode(BindWA *bindWA)
if (checkForMergeRestrictions(bindWA))
return NULL;
- bindWA->removeCurrentScope();
+ bindWA->removeCurrentScope();
bindWA->setMergeStatement(TRUE);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c0a22527/core/sql/optimizer/BindWA.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindWA.cpp b/core/sql/optimizer/BindWA.cpp
index b80edff..b81af8b 100644
--- a/core/sql/optimizer/BindWA.cpp
+++ b/core/sql/optimizer/BindWA.cpp
@@ -93,21 +93,24 @@ BindScope::~BindScope()
// ***********************************************************************
// BindScope::mergeOuterRefs()
// ***********************************************************************
-void BindScope::mergeOuterRefs(const ValueIdSet& other)
+void BindScope::mergeOuterRefs(const ValueIdSet& other, NABoolean keepLocalRefs)
{
outerRefs_ += other;
- // Get the list of valueIds that this scope exposes and remove
- // them from the local references
- ValueIdList localRefList;
- if (RETDesc_)
- RETDesc_->getValueIdList(localRefList,USER_AND_SYSTEM_COLUMNS);
-
- ValueIdSet localRefSet = localRefList;
- outerRefs_ -= localRefSet;
-
- // subtract any other local references
- outerRefs_ -= localRefs_;
+ if (!keepLocalRefs)
+ {
+ // Get the list of valueIds that this scope exposes and remove
+ // them from the local references
+ ValueIdList localRefList;
+ if (RETDesc_)
+ RETDesc_->getValueIdList(localRefList,USER_AND_SYSTEM_COLUMNS);
+
+ ValueIdSet localRefSet = localRefList;
+ outerRefs_ -= localRefSet;
+
+ // subtract any other local references
+ outerRefs_ -= localRefs_;
+ }
} // BindScope::mergeOuterRefs()
@@ -373,7 +376,7 @@ BindScope* BindWA::getSubqueryScope (BindScope *currentScope) const
// ***********************************************************************
// BindWA::removeCurrentScope()
// ***********************************************************************
-void BindWA::removeCurrentScope()
+void BindWA::removeCurrentScope(NABoolean keepLocalRefs)
{
//
// Remove the current scope from the BindScope list. If there is a parent
@@ -385,7 +388,7 @@ void BindWA::removeCurrentScope()
BindScope *currScope;
scopes_.getLast(currScope);
if (NOT scopes_.isEmpty())
- getCurrentScope()->mergeOuterRefs(currScope->getOuterRefs());
+ getCurrentScope()->mergeOuterRefs(currScope->getOuterRefs(),keepLocalRefs);
delete currScope;
} // BindWA::removeCurrentScope()
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c0a22527/core/sql/optimizer/BindWA.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindWA.h b/core/sql/optimizer/BindWA.h
index 92406c5..61715c0 100644
--- a/core/sql/optimizer/BindWA.h
+++ b/core/sql/optimizer/BindWA.h
@@ -641,10 +641,11 @@ public:
// parent's outer references +=
// current's outer references
// - parent's local references
+ // when keepLocalRefs = TRUE, they are retained (for merge)
// Note that the + and - operators correspond to the set union and
// set difference operations.
// --------------------------------------------------------------------
- void mergeOuterRefs(const ValueIdSet &other);
+ void mergeOuterRefs(const ValueIdSet &other, NABoolean keepLocalRefs);
// --------------------------------------------------------------------
// Accessor/mutator method for unresolvedAggregates_
@@ -1203,7 +1204,7 @@ public:
// --------------------------------------------------------------------
BindScope *getCurrentScope() const;
BindScope *getPreviousScope(BindScope *currentScope) const;
- void removeCurrentScope();
+ void removeCurrentScope(NABoolean keepLocalRefs = FALSE);
BindScope *findNextScopeWithTriggerInfo(BindScope *currentScope = NULL);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c0a22527/core/sql/optimizer/NormRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/NormRelExpr.cpp b/core/sql/optimizer/NormRelExpr.cpp
index af02df2..d7d0eab 100644
--- a/core/sql/optimizer/NormRelExpr.cpp
+++ b/core/sql/optimizer/NormRelExpr.cpp
@@ -1207,8 +1207,20 @@ void Join::transformNode(NormWA & normWARef,
}
// Make values available to the childs
ValueIdSet availableValues = getGroupAttr()->getCharacteristicInputs();
- child(0)->getGroupAttr()->addCharacteristicInputs(availableValues);
child(1)->getGroupAttr()->addCharacteristicInputs(availableValues);
+ if (isTSJForMergeUpsert())
+ {
+ ValueIdSet subqVids;
+ for (ValueId vid = availableValues.init();
+ availableValues.next(vid); availableValues.advance(vid)) {
+ if (vid.getItemExpr()->getOperatorType() == ITM_ROW_SUBQUERY)
+ subqVids.insert(vid);
+ }
+ availableValues -= subqVids;
+ //remove subqueries
+
+ }
+ child(0)->getGroupAttr()->addCharacteristicInputs(availableValues);
// ---------------------------------------------------------------------
@@ -1697,11 +1709,26 @@ void Join::recomputeOuterReferences()
// If it is a TSJ don't add the outputs of the left child to
// the needed inputs.
exprSet = child(1).getPtr()->getGroupAttr()->getCharacteristicInputs();
- if (isTSJ())
+ if (isTSJForMergeUpsert())
+ {
+ ValueIdSet exprSet2;
+ ValueId vid;
+ for (vid = exprSet.init();
+ exprSet.next(vid);
+ exprSet.advance(vid))
+ exprSet2.insert(vid.getItemExpr()->
+ getReplacementExpr()->getValueId());
+ exprSet2.removeCoveredExprs(child(0).getPtr()->getGroupAttr()->getCharacteristicOutputs());
+ outerRefs += exprSet2;
+ }
+ else
+ {
+ if (isTSJ())
{
exprSet.removeCoveredExprs(child(0).getPtr()->getGroupAttr()->getCharacteristicOutputs());
}
- outerRefs += exprSet;
+ outerRefs += exprSet;
+ }
getGroupAttr()->setCharacteristicInputs(outerRefs);
}
@@ -7050,8 +7077,8 @@ void RelRoot::recomputeOuterReferences()
child(0)->getGroupAttr()->getCharacteristicInputs().
getLeafValuesForCoverTest(leafValues, emptyGA, emptySet);
CMPASSERT((getGroupAttr()->getCharacteristicInputs().contains
- (child(0)->getGroupAttr()->getCharacteristicInputs())) ||
- (getGroupAttr()->getCharacteristicInputs().contains (leafValues)));
+ (child(0)->getGroupAttr()->getCharacteristicInputs())) ||
+ (getGroupAttr()->getCharacteristicInputs().contains (leafValues)));
ValueIdSet outerRefs = getGroupAttr()->getCharacteristicInputs();
// Remove from outerRefs those valueIds that are not needed
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c0a22527/core/sql/optimizer/RelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelExpr.cpp b/core/sql/optimizer/RelExpr.cpp
index 9f031de..7a4bbc4 100644
--- a/core/sql/optimizer/RelExpr.cpp
+++ b/core/sql/optimizer/RelExpr.cpp
@@ -5189,6 +5189,7 @@ RelExpr * Join::copyTopNode(RelExpr *derivedNode, CollHeap* outHeap)
result->tsjForSetNFError_ = tsjForSetNFError_;
result->tsjForMerge_ = tsjForMerge_;
result->tsjForMergeWithInsert_ = tsjForMergeWithInsert_;
+ result->tsjForMergeUpsert_ = tsjForMergeUpsert_;
result->tsjForSideTreeInsert_ = tsjForSideTreeInsert_;
result->enableTransformToSTI_ = enableTransformToSTI_;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c0a22527/core/sql/optimizer/RelJoin.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelJoin.h b/core/sql/optimizer/RelJoin.h
index 5f87fae..f7e087b 100644
--- a/core/sql/optimizer/RelJoin.h
+++ b/core/sql/optimizer/RelJoin.h
@@ -135,6 +135,7 @@ public:
, tsjForSetNFError_(FALSE)
, tsjForMerge_(FALSE)
, tsjForMergeWithInsert_(FALSE)
+ , tsjForMergeUpsert_(FALSE)
, derivedFromRoutineJoin_(FALSE)
, joinFromPTRule_(FALSE)
, joinFromMJSynthLogProp_(FALSE)
@@ -598,6 +599,11 @@ public:
void setTSJForMergeWithInsert(NABoolean tsjForMergeWithInsert)
{ tsjForMergeWithInsert_ = tsjForMergeWithInsert; }
+ NABoolean isTSJForMergeUpsert() const { return tsjForMergeUpsert_; }
+ // Mutator for the tsjForMergeUpsert flag.
+ void setTSJForMergeUpsert(NABoolean tsjForMergeUpsert)
+ { tsjForMergeUpsert_ = tsjForMergeUpsert; }
+
// Accessor for the tsjForSideTree flag.
NABoolean isTSJForSideTreeInsert() const { return tsjForSideTreeInsert_; }
// Mutator for the tsjForSideTreeInsert flag.
@@ -1068,6 +1074,10 @@ private:
// and the merge statement has an INSERT clause.
NABoolean tsjForMergeWithInsert_;
+ // this tsj is used to flow rows from source RelExpr to merge that is
+ // implementing an upsert.
+ NABoolean tsjForMergeUpsert_;
+
NABoolean derivedFromRoutineJoin_;
// this tsj is used to insert rows using the sidetree insert method.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c0a22527/core/sql/regress/executor/EXPECTED015.SB
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/EXPECTED015.SB b/core/sql/regress/executor/EXPECTED015.SB
index a478603..a209bba 100755
--- a/core/sql/regress/executor/EXPECTED015.SB
+++ b/core/sql/regress/executor/EXPECTED015.SB
@@ -84,6 +84,84 @@
--- SQL operation complete.
>>
+>>CREATE TABLE DEC1
++>(
++>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)
++>);
+
+--- SQL operation complete.
+>>CREATE INDEX IDX3_DEC1_T_CodeValue ON DEC1(codeValue);
+
+--- SQL operation complete.
+>>
+>>
+>>CREATE TABLE DE
++>(
++>ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 5000) NOT NULL,
++>dataElementConceptID int unsigned,
++>valueDomainID int unsigned,
++>PRIMARY KEY (ID)
++>);
+
+--- SQL operation complete.
+>>
+>>CREATE TABLE ODT
++>(
++>ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 5000) NOT NULL,
++>objectTypeID int unsigned NOT NULL,
++>dataElementID int unsigned NOT NULL,
++>objectCategoryTypeCode varchar(20) NOT NULL,
++>nounObjectDataTypeID int unsigned,
++>PRIMARY KEY (ID)
++>);
+
+--- SQL operation complete.
+>>
+>>ALTER TABLE DE
++>ADD CONSTRAINT FK1_DE_DEC1
++>FOREIGN KEY (dataElementConceptID)
++>REFERENCES DEC1(ID);
+
+--- SQL operation complete.
+>>
+>>CREATE INDEX IDX1_DE ON DE(dataElementConceptID, valueDomainID);
+
+--- SQL operation complete.
+>>
+>>ALTER TABLE ODT
++>ADD CONSTRAINT FK2_ODT_DE
++>FOREIGN KEY (dataElementID)
++>REFERENCES DE(ID);
+
+--- SQL operation complete.
+>>
+>>CREATE INDEX IDX1_ODT ON ODT(objectTypeId,objectCategoryTypeCode, dataElementID);
+
+--- SQL operation complete.
+>>
+>>CREATE TABLE OT
++>(
++>ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 1000) NOT NULL,
++>objectCode varchar(100),
++>version varchar(2),
++>PRIMARY KEY (ID)
++>);
+
+--- SQL operation complete.
+>>
+>>ALTER TABLE OT
++>ADD CONSTRAINT UI1_OT UNIQUE (objectCode, version);
+
+--- SQL operation complete.
>>
>>insert into t015t1 values (1,1), (2,2);
@@ -1721,4 +1799,70 @@ A B C
--- 1 row(s) selected.
>>
+>>-- guarding against error 7000 and compiler asserts
+>>prepare s1 from UPSERT INTO DEC1
++>(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode)
++>VALUES (NVL((select a.id from DEC1 a where a.codeValue = ?a[10]), DEFAULT),
++>?b[10], ?c[10], ?d[10], ?e[10], null, ?f[10], null) ;
+
+--- SQL command prepared.
+>>
+>>prepare s2 from UPSERT INTO DEC1
++>(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode)
++>VALUES (?a, ?b, ?c, ?d, ?e, null, ?f, null) ;
+
+--- SQL command prepared.
+>>
+>>prepare s3 from UPSERT INTO DEC1
++>(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode)
++>VALUES (NVL((select a.id from DEC1 a where a.codeValue = ?a), DEFAULT),
++>?b, ?c, ?d, ?e, null, ?f, null) ;
+
+--- SQL command prepared.
+>>
+>>prepare s4 from UPSERT INTO DE
++>(ID, dataElementConceptID, valueDomainID) VALUES
++>(NVL((select a.id from DE a where a.dataElementConceptID =
++>(select b.id from DEC1 b where b.codeValue = ?) and a.valueDomainID = ? ), DEFAULT),
++>NVL((select d.id from DEC1 d where d.codeValue = ?), NULL), ? ) ;
+
+--- SQL command prepared.
+>>
+>>prepare s5 from UPSERT INTO ODT
++>(ID, objectTypeID, dataElementID, objectCategoryTypeCode)
++>VALUES (NVL((select a.id from ODT a where a.objectTypeID =
++>(select b.id from OT b where b.objectCode = ? and b.version = ?) and
++>a.objectCategoryTypeCode = ? and a.dataElementID =
++>(select d.id from DE d where d.dataElementConceptID =
++>(select e.id from DEC1 e where e.codeValue = ?) and d.valueDomainID = ? ) ), DEFAULT),
++>NVL((select g.id from OT g where g.objectCode = ? and g.version = ?), NULL),
++>NVL((select h.id from DE h where h.dataElementConceptID =
++>(select i.id from DEC1 i where i.codeValue = ?) and h.valueDomainID = ? ), NULL), ? );
+
+--- SQL command prepared.
+>>
+>>prepare s6 from UPSERT INTO DE
++>(ID, dataElementConceptID, valueDomainID) VALUES
++>(NVL((select a.id from DE a where a.dataElementConceptID =
++>(select b.id from DEC1 b where b.codeValue = ?) and a.valueDomainID = ? ), DEFAULT),
++>(select d.id from DEC1 d where d.codeValue = ?), ? ) ;
+
+--- SQL command prepared.
+>>
+>>prepare s7 from UPSERT INTO DE (ID, dataElementConceptID, valueDomainID)
++>VALUES (1, (select d.id from DEC1 d where d.codeValue = ?), 3 ) ;
+
+--- SQL command prepared.
+>>
+>>prepare s8 from UPSERT INTO DE (ID, dataElementConceptID, valueDomainID)
++>VALUES (?[10], ?[10], ?[10] ) ;
+
+--- SQL command prepared.
+>>
+>>prepare s9 from UPSERT INTO DE (ID, dataElementConceptID, valueDomainID)
++>VALUES (1, (select d.id from DEC1 d where d.codeValue = 'aa'), 3 ) ;
+
+--- SQL command prepared.
+>>
+>>
>>log;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c0a22527/core/sql/regress/executor/TEST015
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/TEST015 b/core/sql/regress/executor/TEST015
index 7512d0e..dd3b3e5 100755
--- a/core/sql/regress/executor/TEST015
+++ b/core/sql/regress/executor/TEST015
@@ -44,6 +44,10 @@ drop table t015t13 cascade ;
drop table t015t15 cascade ;
drop table t015t14 cascade ;
drop table temp cascade;
+drop table ot;
+drop table odt cascade;
+drop table de cascade ;
+drop table dec1 cascade;
--control query default POS 'OFF';
@@ -197,6 +201,64 @@ create index t015t12i2 on t015t12(c) salt like table;
create index t015t13i1 on t015t13(c) ;
+CREATE TABLE DEC1
+(
+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_DEC1_T_CodeValue ON DEC1(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)
+);
+
+CREATE TABLE ODT
+(
+ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 5000) NOT NULL,
+objectTypeID int unsigned NOT NULL,
+dataElementID int unsigned NOT NULL,
+objectCategoryTypeCode varchar(20) NOT NULL,
+nounObjectDataTypeID int unsigned,
+PRIMARY KEY (ID)
+);
+
+ALTER TABLE DE
+ADD CONSTRAINT FK1_DE_DEC1
+FOREIGN KEY (dataElementConceptID)
+REFERENCES DEC1(ID);
+
+CREATE INDEX IDX1_DE ON DE(dataElementConceptID, valueDomainID);
+
+ALTER TABLE ODT
+ADD CONSTRAINT FK2_ODT_DE
+FOREIGN KEY (dataElementID)
+REFERENCES DE(ID);
+
+CREATE INDEX IDX1_ODT ON ODT(objectTypeId,objectCategoryTypeCode, dataElementID);
+
+CREATE TABLE OT
+(
+ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 1000) NOT NULL,
+objectCode varchar(100),
+version varchar(2),
+PRIMARY KEY (ID)
+);
+
+ALTER TABLE OT
+ADD CONSTRAINT UI1_OT UNIQUE (objectCode, version);
insert into t015t1 values (1,1), (2,2);
insert into t015t3 values (1,1,1), (2,2,2), (3,3,3);
@@ -738,5 +800,53 @@ select * from t015t7;
upsert into t015t7 values (1, NVL((select b+1 from t015t7 where a = 1),DEFAULT),20) ;
select * from t015t7;
+-- guarding against error 7000 and compiler asserts
+prepare s1 from UPSERT INTO DEC1
+(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode)
+VALUES (NVL((select a.id from DEC1 a where a.codeValue = ?a[10]), DEFAULT),
+?b[10], ?c[10], ?d[10], ?e[10], null, ?f[10], null) ;
+
+prepare s2 from UPSERT INTO DEC1
+(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode)
+VALUES (?a, ?b, ?c, ?d, ?e, null, ?f, null) ;
+
+prepare s3 from UPSERT INTO DEC1
+(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode)
+VALUES (NVL((select a.id from DEC1 a where a.codeValue = ?a), DEFAULT),
+?b, ?c, ?d, ?e, null, ?f, null) ;
+
+prepare s4 from UPSERT INTO DE
+(ID, dataElementConceptID, valueDomainID) VALUES
+(NVL((select a.id from DE a where a.dataElementConceptID =
+(select b.id from DEC1 b where b.codeValue = ?) and a.valueDomainID = ? ), DEFAULT),
+NVL((select d.id from DEC1 d where d.codeValue = ?), NULL), ? ) ;
+
+prepare s5 from UPSERT INTO ODT
+(ID, objectTypeID, dataElementID, objectCategoryTypeCode)
+VALUES (NVL((select a.id from ODT a where a.objectTypeID =
+(select b.id from OT b where b.objectCode = ? and b.version = ?) and
+a.objectCategoryTypeCode = ? and a.dataElementID =
+(select d.id from DE d where d.dataElementConceptID =
+(select e.id from DEC1 e where e.codeValue = ?) and d.valueDomainID = ? ) ), DEFAULT),
+NVL((select g.id from OT g where g.objectCode = ? and g.version = ?), NULL),
+NVL((select h.id from DE h where h.dataElementConceptID =
+(select i.id from DEC1 i where i.codeValue = ?) and h.valueDomainID = ? ), NULL), ? );
+
+prepare s6 from UPSERT INTO DE
+(ID, dataElementConceptID, valueDomainID) VALUES
+(NVL((select a.id from DE a where a.dataElementConceptID =
+(select b.id from DEC1 b where b.codeValue = ?) and a.valueDomainID = ? ), DEFAULT),
+(select d.id from DEC1 d where d.codeValue = ?), ? ) ;
+
+prepare s7 from UPSERT INTO DE (ID, dataElementConceptID, valueDomainID)
+VALUES (1, (select d.id from DEC1 d where d.codeValue = ?), 3 ) ;
+
+prepare s8 from UPSERT INTO DE (ID, dataElementConceptID, valueDomainID)
+VALUES (?[10], ?[10], ?[10] ) ;
+
+prepare s9 from UPSERT INTO DE (ID, dataElementConceptID, valueDomainID)
+VALUES (1, (select d.id from DEC1 d where d.codeValue = 'aa'), 3 ) ;
+
+
log;
[2/2] incubator-trafodion git commit: Merge [TRAFODION-1791] PR-291
UPSERT with bare one row subquery in VALUES clause fails
Posted by se...@apache.org.
Merge [TRAFODION-1791] PR-291 UPSERT with bare one row subquery in VALUES clause fails
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/7a636026
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/7a636026
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/7a636026
Branch: refs/heads/master
Commit: 7a6360264392300dfa822e0b0cddb74a42e1a055
Parents: 77581c1 c0a2252
Author: selvaganesang <se...@apache.org>
Authored: Sun Jan 31 01:24:25 2016 +0000
Committer: selvaganesang <se...@apache.org>
Committed: Sun Jan 31 01:24:25 2016 +0000
----------------------------------------------------------------------
core/sql/optimizer/BindRelExpr.cpp | 17 ++-
core/sql/optimizer/BindWA.cpp | 31 +++---
core/sql/optimizer/BindWA.h | 5 +-
core/sql/optimizer/NormRelExpr.cpp | 37 ++++++-
core/sql/optimizer/RelExpr.cpp | 1 +
core/sql/optimizer/RelJoin.h | 10 ++
core/sql/regress/executor/EXPECTED015.SB | 144 ++++++++++++++++++++++++++
core/sql/regress/executor/TEST015 | 110 ++++++++++++++++++++
8 files changed, 329 insertions(+), 26 deletions(-)
----------------------------------------------------------------------