//----------------------------------------------------------------------------
// Next, do the other columns (AVG, STDDEV, VARIANCE), using the already
// calculated and bound COUNT and SUM columns.
// There are 3 dependent columns here (found using MVInfo):
// dep1 is COUNT(a),
// dep2 is SUM(a),
// dep3 is SUM(a*a) (only for STDDEV and VARIANCE)
// depCount is COUNT(a)
// In the case of STDDEV with a weight parameter - STDDEV(a,b), then
// dep1 is SUM(b),
// dep2 is SUM(a*b),
// dep3 is SUM(a*a*b)
// depCount is COUNT(a*b) and is the dependent column of dep2.
//
// For col=AVG(a), the new expr is:
// CASE
// WHEN (SYS_CALC.depCount = 0) THEN NULL
// ELSE SYS_CALC.dep2 / SYS_CALC.dep1
// END
//
// For STDDEV and VARIANCE its:
// CASE
// WHEN (SYS_CALC.depCount = 0) THEN NULL
// ELSE ScalarVariance(SYS_CALC.dep3, SYS_CALC.dep2, SYS_CALC.dep1)
// END
// depCount is the COUNT dependent column of dep2. This works also in the
// case of STDDEV with a weight parameter
//----------------------------------------------------------------------------
RelRoot *MavRelRootBuilder::buildRootForOthers(RelExpr *topNode)
{
// Start the select list with *
ItemExprList selectList(heap_);
addStarToSelectList(selectList);
// For each column in the COUNT list
for (CollIndex i=0; i<otherCols_.entries(); i++)
{
MVColumnInfo *currentCol = otherCols_[i];
const NAString& colName = currentCol->getColName();
const NAType *desiredType = new(heap_) SQLDoublePrecision(TRUE);
// Find the dependent SYS_CALC COUNT and SUM columns.
ItemExpr *calcDep1 =
buildDepColExpr(calcCorrName_, currentCol->getDepCol1());
ItemExpr *calcDep2 =
buildDepColExpr(calcCorrName_, currentCol->getDepCol2());
ItemExpr *calcDep3 = NULL;
ItemExpr *calcDepCount = NULL;
if (currentCol->getOperatorType() == ITM_AVG)
{
calcDepCount = calcDep1;
}
else
{
// dep3 and depCount are needed only for STDDEV and VARIANCE.
calcDep3 = buildDepColExpr(calcCorrName_, currentCol->getDepCol3());
// depCount is the dereferenced dep1 column of dep2.
MVColumnInfo *dep2ColInfo =
mavCols_.getMvColInfoByIndex(currentCol->getDepCol2());
calcDepCount =
buildDepColExpr(calcCorrName_, dep2ColInfo->getDepCol1());
}
// Build the condition bottom-up - do the ELSE clause first.
ItemExpr *elseClause = NULL;
if (currentCol->getOperatorType() == ITM_AVG)
{
// For AVG, the ELSE clause is (SYS_CALC.dep2 / SYS_CALC.dep1).
elseClause = new(heap_)
BiArith(ITM_DIVIDE, calcDep2, calcDep1);
}
else
{
// For STDDEV and VARIANCE, the ELSE clause is
// ScalarVariance(SYS_CALC.dep3, SYS_CALC.dep2, SYS_CALC.dep1)
elseClause = new(heap_)
ScalarVariance(currentCol->getOperatorType(),
new(heap_) Cast(calcDep3, desiredType),
new(heap_) Cast(calcDep2, desiredType),
new(heap_) Cast(calcDep1, desiredType));
}
// The WHEN clause is the same for AVG, STDDEV and VARIANCE:
// WHEN (SYS_CALC.depCount = 0) THEN NULL
ItemExpr *whenClause = new(heap_)
IfThenElse(new(heap_) BiRelat(ITM_EQUAL,
calcDepCount,
new(heap_) SystemLiteral(0)),
new(heap_) SystemLiteral(), // NULL constant
elseClause);
// Put the CASE on top.
ItemExpr *newCalcExpr = new(heap_) Case(NULL, whenClause);
// Add the col expression as SYS_CALC.col
ColRefName *calcColName = new(heap_) ColRefName(colName, calcCorrName_);
ItemExpr *newCalcCol = new(heap_) RenameCol(newCalcExpr, calcColName);
selectList.insert(newCalcCol);
}
// The select list is ready. Create the root over topNode.
RelRoot *newRoot = new(heap_)
RelRoot(topNode, REL_ROOT, selectList.convertToItemExpr());
newRoot->setDontOpenNewScope();
selectList.clear();
return newRoot;
} // MavRelRootBuilder::buildRootForOthers()
//----------------------------------------------------------------------------
// Next, do the SUM, MIN and MAX columns, using the already calculated
// and bound COUNT columns (SYS_CALC.dep1).
// For col=SUM(a), the new expr is:
// CASE
// 1) WHEN (SYS_CALC.dep1 = 0) THEN NULL
// 2) WHEN (SYS_MAV.col IS NULL) THEN SYS_DELTA.col
// 3) WHEN (SYS_DELTA.col IS NULL) THEN SYS_MAV.col
// ELSE SYS_MAV.col + SYS_DELTA.col
// END
// For col=MIN(a), the else clause is replaced by (no MIN ItemExpr):
// WHEN (SYS_MAV.col < SYS_DELTA.col) THEN SYS_MAV.col
// ELSE SYS_DELTA.col
// For col=MAX(a), the else clause is:
// WHEN (SYS_MAV.col > SYS_DELTA.col) THEN SYS_MAV.col
// ELSE SYS_DELTA.col
//----------------------------------------------------------------------------
RelRoot *MavRelRootBuilder::buildRootForSum(RelExpr *topNode)
{
// Start the select list with *
ItemExprList selectList(heap_);
addStarToSelectList(selectList);
// For each column in the COUNT list
for (CollIndex i=0; i<sumCols_.entries(); i++)
{
MVColumnInfo *currentCol = sumCols_[i];
const NAString& colName = currentCol->getColName();
// Find the column in the SYS_DELTA and SYS_MAV lists.
ItemExpr *sysDeltaColExpr = new(heap_)
ColReference(new(heap_) ColRefName(colName, deltaCorrName_));
ItemExpr *sysMavColExpr = new(heap_)
ColReference(new(heap_) ColRefName(colName, mavCorrName_));
// Find the dependent SYS_CALC COUNT column.
ItemExpr *calcDep1 =
buildDepColExpr(calcCorrName_, currentCol->getDepCol1());
// Build the condition bottom-up - do the ELSE clause first.
ItemExpr *elseClause = NULL;
if (currentCol->getOperatorType() == ITM_SUM)
{
// The ELSE clause for SUM is (SYS_MAV.col + SYS_DELTA.col).
elseClause = new(heap_)
BiArith(ITM_PLUS, sysDeltaColExpr, sysMavColExpr);
}
else
{
// The ELSE clause for MIN is:
// WHEN (SYS_MAV.col < SYS_DELTA.col) THEN SYS_MAV.col
// ELSE SYS_DELTA.col
// For MAX the < operator is replaced by >.
OperatorTypeEnum sign =
(currentCol->getOperatorType()==ITM_MIN ? ITM_LESS : ITM_GREATER);
elseClause = new(heap_)
IfThenElse(new(heap_) BiRelat(sign, sysMavColExpr, sysDeltaColExpr),
sysMavColExpr,
sysDeltaColExpr);
}
// The 3rd WHEN clause: WHEN (SYS_DELTA.col IS NULL) THEN SYS_MAV.col
ItemExpr *when3Clause = new(heap_)
IfThenElse(new(heap_) UnLogic(ITM_IS_NULL, sysDeltaColExpr),
sysMavColExpr,
elseClause);
// The 2nd WHEN clause: WHEN (SYS_MAV.col IS NULL) THEN SYS_DELTA.col
ItemExpr *when2Clause = new(heap_)
IfThenElse(new(heap_) UnLogic(ITM_IS_NULL, sysMavColExpr),
sysDeltaColExpr,
when3Clause);
// The 1st WHEN clause: WHEN (SYS_CALC.dep1 = 0) THEN NULL
ItemExpr *when1Clause = new(heap_)
IfThenElse(new(heap_) BiRelat(ITM_EQUAL,
calcDep1,
new(heap_) SystemLiteral(0)),
new(heap_) SystemLiteral(), // NULL constant
when2Clause);
// Build the CASE on top.
ItemExpr *newCalcExpr = new(heap_) Case(NULL, when1Clause);
// Add the col expression as SYS_CALC.col
ColRefName *calcColName = new(heap_) ColRefName(colName, calcCorrName_);
ItemExpr *newCalcCol = new(heap_) RenameCol(newCalcExpr, calcColName);
selectList.insert(newCalcCol);
}
// The select list is ready. Create the root over topNode.
RelRoot *newRoot = new(heap_)
RelRoot(topNode, REL_ROOT, selectList.convertToItemExpr());
newRoot->setDontOpenNewScope();
selectList.clear();
return newRoot;
} // MavRelRootBuilder::buildRootForSum()
