[
https://issues.apache.org/jira/browse/TRAFODION-1562?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15652758#comment-15652758
]
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_r87315442
--- 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();
--- End diff --
Since we didn't add a new scope, could it be that there already was another
sequence node in this scope, e.g. near the top of the select query? I've seen
this assert elsewhere in the code (commented out):
CMPASSERT(!bindWA->getCurrentScope()->getSequenceNode());
This may be good to add before line 10702 above. Or, create a new scope.
Then try an upsert with a sequence function in the select.
> 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)
