[
https://issues.apache.org/jira/browse/TRAFODION-1562?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15652763#comment-15652763
]
ASF GitHub Bot commented on TRAFODION-1562:
-------------------------------------------
Github user zellerh commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/826#discussion_r87316246
--- Diff: core/sql/optimizer/BindRelExpr.cpp ---
@@ -10383,6 +10423,310 @@ NABoolean
Insert::isUpsertThatNeedsMerge(NABoolean isAlignedRowFormat, NABoolean
return FALSE;
}
+#ifdef __ignore
+// take an insert(src) node and transform it into
+// a tuple_flow with old/new rows flowing to the IM tree.
+// with a newly created input_scan
+RelExpr* Insert::xformUpsertToEfficientTreeNoDup(BindWA *bindWA)
+{
+ NATable *naTable = bindWA->getNATable(getTableName());
+ if (bindWA->errStatus())
+ return NULL;
+ if ((naTable->getViewText() != NULL) && (naTable->getViewCheck()))
+ {
+ *CmpCommon::diags() << DgSqlCode(-3241)
+ << DgString0(" View with check option not allowed.");
+ bindWA->setErrStatus();
+ return NULL;
+ }
+
+ RelExpr *topNode = this;
+ // Create a new BindScope, to encompass the new nodes
+ // upsert(left_join(input_scan, tuple))
+ // and any inlining nodes that will be created. Any values the upsert
+ // and children will need from src will be marked as outer references in
that
+ // new BindScope. We assume that "src" is already bound.
+ ValueIdSet currOuterRefs = bindWA->getCurrentScope()->getOuterRefs();
+
+ CMPASSERT(child(0)->nodeIsBound());
+
+ BindScope *upsertScope = bindWA->getCurrentScope();
+
+ // columns of the target table
+ const ValueIdList &tableCols = updateToSelectMap().getTopValues();
+ const ValueIdList &sourceVals = updateToSelectMap().getBottomValues();
+
+ // create a Join node - left join of the base table columns with the
columns to be upserted.
+ // columns of the target table
+ CMPASSERT(child(0)->nodeIsBound());
+
+ Scan * targetTableScan =
+ new (bindWA->wHeap())
+ Scan(CorrName(getTableDesc()->getCorrNameObj(), bindWA->wHeap()));
+
+
+ //join predicate between source columns and target table.
+ ItemExpr * keyPred = NULL;
+ ItemExpr * keyPredPrev = NULL;
+ BaseColumn* baseCol;
+ ColReference * targetColRef;
+ int predCount = 0;
+ ValueIdSet newOuterRefs;
+ ItemExpr * pkeyValPrev;
+ ItemExpr * pkeyVals;
+ for (CollIndex i = 0; i < tableCols.entries(); i++)
+ {
+ baseCol = (BaseColumn *)(tableCols[i].getItemExpr()) ;
+ if (baseCol->getNAColumn()->isSystemColumn())
+ continue;
+
+ targetColRef = new(bindWA->wHeap()) ColReference(
+ new(bindWA->wHeap())
ColRefName(
+
baseCol->getNAColumn()->getFullColRefName(), bindWA->wHeap()));
+
+
+ if (baseCol->getNAColumn()->isClusteringKey())
+ {
+ // create a join/key predicate between source and target table,
+ // on the clustering key columns of the target table, making
+ // ColReference nodes for the target table, so that we can bind
+ // those to the new scan
+ keyPredPrev = keyPred;
+ keyPred = new (bindWA->wHeap())
+ BiRelat(ITM_EQUAL, targetColRef,
+ sourceVals[i].getItemExpr(),
+ baseCol->getType().supportsSQLnull());
+ predCount++;
+ if (predCount > 1)
+ {
+ keyPred = new(bindWA->wHeap()) BiLogic(ITM_AND,
+ keyPredPrev,
+ keyPred);
+ }
+ pkeyValPrev = pkeyVals;
+
+ pkeyVals = tableCols[i].getItemExpr();
+ if (i > 0)
+ {
+ pkeyVals = new(bindWA->wHeap()) ItemList(pkeyVals,pkeyValPrev);
+
+ }
+ }
+
+ }
+
+ // Map the table's primary key values to the source lists key values
+ ValueIdList tablePKeyVals = NULL;
+ ValueIdList sourcePKeyVals = NULL;
+
+ pkeyVals->convertToValueIdList(tablePKeyVals,bindWA,ITM_ITEM_LIST);
+ updateToSelectMap().mapValueIdListDown(tablePKeyVals,sourcePKeyVals);
+
+ Join *lj = new(bindWA->wHeap())
Join(child(0),targetTableScan,REL_LEFT_JOIN,keyPred);
+ lj->doNotTransformToTSJ();
+ lj->setTSJForWrite(TRUE);
+ bindWA->getCurrentScope()->xtnmStack()->createXTNM();
+ RelExpr *boundLJ = lj->bindNode(bindWA);
+ if (bindWA->errStatus())
+ return NULL;
+ bindWA->getCurrentScope()->xtnmStack()->removeXTNM();
+ setChild(0,boundLJ);
+ topNode = handleInlining(bindWA,topNode);
+
+
+ return topNode;
+}
+#endif
+// take an insert(src) node and transform it into
+// a tuple_flow with old/new rows flowing to the IM tree.
+// with a newly created sequence node used to eliminate duplicates.
+/*
+ NJ
+ / \
+ Sequence NJ
+ / \
+ Left Join IM Tree
+ / \
+ / \
+Input Tuplelist Target Table Scan
+or select list
+*/
+
+RelExpr* Insert::xformUpsertToEfficientTree(BindWA *bindWA)
+{
+ NATable *naTable = bindWA->getNATable(getTableName());
+ if (bindWA->errStatus())
+ return NULL;
+ if ((naTable->getViewText() != NULL) && (naTable->getViewCheck()))
+ {
+ *CmpCommon::diags() << DgSqlCode(-3241)
+ << DgString0(" View with check option not allowed.");
+ bindWA->setErrStatus();
+ return NULL;
+ }
+
+ RelExpr *topNode = this;
+
+ CMPASSERT(child(0)->nodeIsBound());
+
+ BindScope *upsertScope = bindWA->getCurrentScope();
+ // Create a new BindScope, to encompass the new nodes
+ // upsert(left_join(input_scan, tuple))
+ // and any inlining nodes that will be created. Any values the upsert
+ // and children will need from src will be marked as outer references in
that
+ // new BindScope. We assume that "src" is already bound.
+ ValueIdSet currOuterRefs = bindWA->getCurrentScope()->getOuterRefs();
+ // Save the current RETDesc.
+ RETDesc *prevRETDesc = bindWA->getCurrentScope()->getRETDesc();
+
+ // columns of the target table
+ const ValueIdList &tableCols = updateToSelectMap().getTopValues();
+ const ValueIdList &sourceVals = updateToSelectMap().getBottomValues();
+
+ // create a Join node - left join of the base table columns with the
columns to be upserted.
+ // columns of the target table
+ CMPASSERT(child(0)->nodeIsBound());
+
+ Scan * targetTableScan =
+ new (bindWA->wHeap())
+ Scan(CorrName(getTableDesc()->getCorrNameObj(), bindWA->wHeap()));
+
+
+ //join predicate between source columns and target table.
+ ItemExpr * keyPred = NULL;
+ ItemExpr * keyPredPrev = NULL;
+ BaseColumn* baseCol;
+ ColReference * targetColRef;
+ int predCount = 0;
+ ValueIdSet newOuterRefs;
+ ItemExpr * pkeyValPrev;
+ ItemExpr * pkeyVals = NULL;
+ for (CollIndex i = 0; i < tableCols.entries(); i++)
+ {
+ baseCol = (BaseColumn *)(tableCols[i].getItemExpr()) ;
+ if (baseCol->getNAColumn()->isSystemColumn())
+ continue;
+
+ targetColRef = new(bindWA->wHeap()) ColReference(
+ new(bindWA->wHeap())
ColRefName(
+
baseCol->getNAColumn()->getFullColRefName(), bindWA->wHeap()));
+
+
+ if (baseCol->getNAColumn()->isClusteringKey())
+ {
+ // create a join/key predicate between source and target table,
+ // on the clustering key columns of the target table, making
+ // ColReference nodes for the target table, so that we can bind
+ // those to the new scan
+ keyPredPrev = keyPred;
+ keyPred = new (bindWA->wHeap())
+ BiRelat(ITM_EQUAL, targetColRef,
+ sourceVals[i].getItemExpr(),
+ baseCol->getType().supportsSQLnull());
+ predCount++;
+ if (predCount > 1)
+ {
+ keyPred = new(bindWA->wHeap()) BiLogic(ITM_AND,
+ keyPredPrev,
+ keyPred);
+ }
+ pkeyValPrev = pkeyVals;
+
+ pkeyVals = tableCols[i].getItemExpr();
+
+ if (i > 0)
+ {
+ pkeyVals = new(bindWA->wHeap()) ItemList(pkeyVals,pkeyValPrev);
+
+ }
+ }
+
+ }
+
+ // Map the table's primary key values to the source lists key values
+ ValueIdList tablePKeyVals = NULL;
+ ValueIdList sourcePKeyVals = NULL;
+
+ pkeyVals->convertToValueIdList(tablePKeyVals,bindWA,ITM_ITEM_LIST);
+ updateToSelectMap().mapValueIdListDown(tablePKeyVals,sourcePKeyVals);
+
+
+
+ Join *lj = new(bindWA->wHeap())
Join(child(0),targetTableScan,REL_LEFT_JOIN,keyPred);
+ lj->doNotTransformToTSJ();
+ lj->setTSJForWrite(TRUE);
+ bindWA->getCurrentScope()->xtnmStack()->createXTNM();
+
+
+ RelExpr *boundLJ = lj->bindNode(bindWA);
+ if (bindWA->errStatus())
+ return NULL;
+ bindWA->getCurrentScope()->xtnmStack()->removeXTNM();
+
+
+ ValueIdSet sequenceFunction = NULL;
+
+ ItemExpr *constOne = new (bindWA->wHeap()) ConstValue(1);
+
+ //Retrieve all the system and user columns of the left join output
+ ValueIdList ljOutCols = NULL;
+ boundLJ->getRETDesc()->getValueIdList(ljOutCols);
+ //Retrieve the null instantiated part of the LJ output
+ ValueIdList ljNullInstColumns = lj->nullInstantiatedOutput();
+
+ //Create the olap node and use the primary key of the table as the
+ //"partition by" columns for the olap node.
+ RelSequence *seqNode = new(bindWA->wHeap()) RelSequence(boundLJ,
sourcePKeyVals.rebuildExprTree(ITM_ITEM_LIST), (ItemExpr *)NULL);
+
+
+ // Create a LEAD Item Expr for a random value 999.
+ // Use this to eliminate rows which have a NULL for this LEAD value
within
+ // a particular partition range.
+ ItemExpr *leadItem, *boundLeadItem = NULL;
+ ItemExpr *constLead999 = new (bindWA->wHeap()) ConstValue( 999);
+
+ leadItem = new(bindWA->wHeap()) ItmLeadOlapFunction(constLead999,1);
+ ((ItmLeadOlapFunction *)leadItem)->setIsOLAP(TRUE);
+ boundLeadItem = leadItem->bindNode(bindWA);
+ if (bindWA->errStatus()) return this;
+ boundLeadItem->convertToValueIdSet(sequenceFunction);
+ seqNode->setSequenceFunctions(sequenceFunction);
+
+ // Add a selection predicate (post predicate) to check if the LEAD item
is NULL
+ ItemExpr *selPredOnLead = NULL;
+ selPredOnLead = new (bindWA->wHeap()) UnLogic(ITM_IS_NULL,leadItem);
+ selPredOnLead->bindNode(bindWA);
+ if (bindWA->errStatus()) return this;
+ seqNode->selectionPred() += selPredOnLead->getValueId();
+
+ RelExpr *boundSeqNode = seqNode->bindNode(bindWA);
+ boundSeqNode->setChild(0,boundLJ);
+
+
+ setChild(0,boundSeqNode);
+
+ // Fixup the newRecExpr() and newRecExprArray() to refer to the new
+ // valueIds of the new child - i.e RelSequence. Use the saved off
valueIdMap
+ // from the current bindScope for this.
+ ValueIdSet newNewRecExpr;
+ ValueIdMap notCoveredMap = bindWA->getCurrentScope()->getNcToOldMap();
+ notCoveredMap.rewriteValueIdSetDown(newRecExpr(),newNewRecExpr);
+ newRecExpr() = newNewRecExpr;
+
+ ValueIdList oldRecArrList(newRecExprArray());
+ ValueIdList newRecArrList;
+ notCoveredMap.rewriteValueIdListDown(oldRecArrList,newRecArrList);
--- End diff --
See comment below, this should do a rewriteValueIdListUp, not down, since
we are mapping from a child to the parent. So, the map would need to be made
with top and bottom values flipped.
> Changes in query tree when the upsert command is transformed into merge
> -----------------------------------------------------------------------
>
> Key: TRAFODION-1562
> URL: https://issues.apache.org/jira/browse/TRAFODION-1562
> Project: Apache Trafodion
> Issue Type: Sub-task
> Components: sql-cmp
> Reporter: Selvaganesan Govindarajan
> Assignee: Sandhya Sundaresan
> Attachments: BatchUpsertTransformation.pdf
>
>
> to improve the performance as explained in the main JIRA
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
