use of org.voltdb.plannodes.NestLoopPlanNode in project voltdb by VoltDB.
the class SelectSubPlanAssembler method getSelectSubPlanForJoin.
/**
* Given a join node and plan-sub-graph for outer and inner sub-nodes,
* construct the plan-sub-graph for that node.
*
* @param joinNode A parent join node.
* @param outerPlan The outer node plan-sub-graph.
* @param innerPlan The inner node plan-sub-graph.
* @return A completed plan-sub-graph
* or null if a valid plan can not be produced for given access paths.
*/
private IndexSortablePlanNode getSelectSubPlanForJoin(BranchNode joinNode, AbstractPlanNode outerPlan, AbstractPlanNode innerPlan) {
// Filter (post-join) expressions
ArrayList<AbstractExpression> whereClauses = new ArrayList<>();
whereClauses.addAll(joinNode.m_whereInnerList);
whereClauses.addAll(joinNode.m_whereInnerOuterList);
if (joinNode.getJoinType() == JoinType.FULL) {
// For all other join types, the whereOuterList expressions were pushed down to the outer node
whereClauses.addAll(joinNode.m_whereOuterList);
}
assert (joinNode.getRightNode() != null);
JoinNode innerJoinNode = joinNode.getRightNode();
AccessPath innerAccessPath = innerJoinNode.m_currentAccessPath;
// We may need to add a send/receive pair to the inner plan for the special case.
// This trick only works once per plan, BUT once the partitioned data has been
// received on the coordinator, it can be treated as replicated data in later
// joins, which MAY help with later outer joins with replicated data.
boolean needInnerSendReceive = m_partitioning.requiresTwoFragments() && !innerPlan.hasReplicatedResult() && outerPlan.hasReplicatedResult() && joinNode.getJoinType() != JoinType.INNER;
// When the inner plan is an IndexScan, there MAY be a choice of whether to join using a
// NestLoopJoin (NLJ) or a NestLoopIndexJoin (NLIJ). The NLJ will have an advantage over the
// NLIJ in the cases where it applies, since it does a single access or iteration over the index
// and caches the result, where the NLIJ does an index access or iteration for each outer row.
// The NestLoopJoin applies when the inner IndexScan is driven only by parameter and constant
// expressions determined at the start of the query. That requires that none of the IndexScan's
// various expressions that drive the index access may reference columns from the outer row
// -- they can only reference columns of the index's base table (the indexed expressions)
// as well as constants and parameters. The IndexScan's "otherExprs" expressions that only
// drive post-filtering are not an issue since the NestLoopJoin does feature per-outer-tuple
// post-filtering on each pass over the cached index scan result.
// The special case of an OUTER JOIN of replicated outer row data with a partitioned inner
// table requires that the partitioned data be sent to the coordinator prior to the join.
// This limits the join option to NLJ. The index scan must make a single index access on
// each partition and cache the result at the coordinator for post-filtering.
// This requires that the index access be based on parameters or constants only
// -- the replicated outer row data will only be available later at the coordinator,
// so it can not drive the per-partition index scan.
// If the NLJ option is precluded for the usual reason (outer-row-based indexing) AND
// the NLIJ is precluded by the special case (OUTER JOIN of replicated outer rows and
// partitioned inner rows) this method returns null, effectively rejecting this indexed
// access path for the inner node. Other access paths or join orders may prove more successful.
boolean canHaveNLJ = true;
boolean canHaveNLIJ = true;
if (innerPlan instanceof IndexScanPlanNode) {
if (hasInnerOuterIndexExpression(joinNode.getRightNode().getTableAlias(), innerAccessPath.indexExprs, innerAccessPath.initialExpr, innerAccessPath.endExprs)) {
canHaveNLJ = false;
}
} else {
canHaveNLIJ = false;
}
if (needInnerSendReceive) {
canHaveNLIJ = false;
}
// partition columns
if (joinNode.getJoinType() == JoinType.FULL && m_partitioning.requiresTwoFragments() && !outerPlan.hasReplicatedResult() && innerPlan.hasReplicatedResult()) {
canHaveNLIJ = false;
canHaveNLJ = false;
}
AbstractJoinPlanNode ajNode = null;
if (canHaveNLJ) {
NestLoopPlanNode nljNode = new NestLoopPlanNode();
// get all the clauses that join the applicable two tables
// Copy innerAccessPath.joinExprs to leave it unchanged,
// avoiding accumulation of redundant expressions when
// joinClauses gets built up for various alternative plans.
ArrayList<AbstractExpression> joinClauses = new ArrayList<>(innerAccessPath.joinExprs);
if ((innerPlan instanceof IndexScanPlanNode) || (innerPlan instanceof NestLoopIndexPlanNode && innerPlan.getChild(0) instanceof MaterializedScanPlanNode)) {
// InnerPlan is an IndexScan OR an NLIJ of a MaterializedScan
// (IN LIST) and an IndexScan. In this case, the inner and
// inner-outer non-index join expressions (if any) are in the
// indexScan's otherExpr. The former should stay as IndexScanPlan
// predicates but the latter need to be pulled up into NLJ
// predicates because the IndexScan is executed once, not once
// per outer tuple.
ArrayList<AbstractExpression> otherExprs = new ArrayList<>();
// PLEASE do not update the "innerAccessPath.otherExprs", it may be reused
// for other path evaluation on the other outer side join.
List<AbstractExpression> innerExpr = filterSingleTVEExpressions(innerAccessPath.otherExprs, otherExprs);
joinClauses.addAll(otherExprs);
IndexScanPlanNode scanNode = null;
if (innerPlan instanceof IndexScanPlanNode) {
scanNode = (IndexScanPlanNode) innerPlan;
} else {
assert (innerPlan instanceof NestLoopIndexPlanNode);
scanNode = ((NestLoopIndexPlanNode) innerPlan).getInlineIndexScan();
}
scanNode.setPredicate(innerExpr);
} else if (innerJoinNode instanceof BranchNode && joinNode.getJoinType() != JoinType.INNER) {
// If the innerJoinNode is a LEAF node OR if the join type is an INNER join,
// the conditions that apply to the inner side
// have been applied as predicates to the inner scan node already.
// otherExpr of innerAccessPath comes from its parentNode's joinInnerList.
// For Outer join (LEFT or FULL), it could mean a join predicate on the table of
// the inner node ONLY, that can not be pushed down.
joinClauses.addAll(innerAccessPath.otherExprs);
}
nljNode.setJoinPredicate(ExpressionUtil.combinePredicates(joinClauses));
// combine the tails plan graph with the new head node
nljNode.addAndLinkChild(outerPlan);
// right child node.
if (needInnerSendReceive) {
// This trick only works once per plan.
if (outerPlan.hasAnyNodeOfClass(AbstractReceivePlanNode.class) || innerPlan.hasAnyNodeOfClass(AbstractReceivePlanNode.class)) {
return null;
}
innerPlan = addSendReceivePair(innerPlan);
}
nljNode.addAndLinkChild(innerPlan);
ajNode = nljNode;
} else if (canHaveNLIJ) {
NestLoopIndexPlanNode nlijNode = new NestLoopIndexPlanNode();
IndexScanPlanNode innerNode = (IndexScanPlanNode) innerPlan;
// Set IndexScan predicate. The INNER join expressions for a FULL join come from
// the innerAccessPath.joinExprs and need to be combined with the other join expressions
innerNode.setPredicate(innerAccessPath.joinExprs, innerAccessPath.otherExprs);
nlijNode.addInlinePlanNode(innerPlan);
// combine the tails plan graph with the new head node
nlijNode.addAndLinkChild(outerPlan);
ajNode = nlijNode;
} else {
m_recentErrorMsg = "Unsupported special case of complex OUTER JOIN between replicated outer table and partitioned inner table.";
return null;
}
ajNode.setJoinType(joinNode.getJoinType());
ajNode.setPreJoinPredicate(ExpressionUtil.combinePredicates(joinNode.m_joinOuterList));
ajNode.setWherePredicate(ExpressionUtil.combinePredicates(whereClauses));
ajNode.resolveSortDirection();
return ajNode;
}
use of org.voltdb.plannodes.NestLoopPlanNode in project voltdb by VoltDB.
the class PlanAssembler method getNextSelectPlan.
private CompiledPlan getNextSelectPlan() {
assert (m_subAssembler != null);
// A matview reaggregation template plan may have been initialized
// with a post-predicate expression moved from the statement's
// join tree prior to any subquery planning.
// Since normally subquery planning is driven from the join tree,
// any subqueries that are moved out of the join tree would need
// to be planned separately.
// This planning would need to be done prior to calling
// m_subAssembler.nextPlan()
// because it can have query partitioning implications.
// Under the current query limitations, the partitioning implications
// are very simple -- subqueries are not allowed in multipartition
// queries against partitioned data, so detection of a subquery in
// the same query as a matview reaggregation can just return an error,
// without any need for subquery planning here.
HashAggregatePlanNode reAggNode = null;
HashAggregatePlanNode mvReAggTemplate = m_parsedSelect.m_mvFixInfo.getReAggregationPlanNode();
if (mvReAggTemplate != null) {
reAggNode = new HashAggregatePlanNode(mvReAggTemplate);
AbstractExpression postPredicate = reAggNode.getPostPredicate();
if (postPredicate != null && postPredicate.hasSubquerySubexpression()) {
// For now, this is just a special case violation of the limitation on
// use of subquery expressions in MP queries on partitioned data.
// That special case was going undetected when we didn't flag it here.
m_recentErrorMsg = IN_EXISTS_SCALAR_ERROR_MESSAGE;
return null;
}
// // Something more along these lines would have to be enabled
// // to allow expression subqueries to be used in multi-partition
// // matview queries.
// if (!getBestCostPlanForExpressionSubQueries(subqueryExprs)) {
// // There was at least one sub-query and we should have a compiled plan for it
// return null;
// }
}
AbstractPlanNode subSelectRoot = m_subAssembler.nextPlan();
if (subSelectRoot == null) {
m_recentErrorMsg = m_subAssembler.m_recentErrorMsg;
return null;
}
AbstractPlanNode root = subSelectRoot;
boolean mvFixNeedsProjection = false;
/*
* If the access plan for the table in the join order was for a
* distributed table scan there must be a send/receive pair at the top
* EXCEPT for the special outer join case in which a replicated table
* was on the OUTER side of an outer join across from the (joined) scan
* of the partitioned table(s) (all of them) in the query. In that case,
* the one required send/receive pair is already in the plan below the
* inner side of a NestLoop join.
*/
if (m_partitioning.requiresTwoFragments()) {
boolean mvFixInfoCoordinatorNeeded = true;
boolean mvFixInfoEdgeCaseOuterJoin = false;
ArrayList<AbstractPlanNode> receivers = root.findAllNodesOfClass(AbstractReceivePlanNode.class);
if (receivers.size() == 1) {
// Edge cases: left outer join with replicated table.
if (m_parsedSelect.m_mvFixInfo.needed()) {
mvFixInfoCoordinatorNeeded = false;
AbstractPlanNode receiveNode = receivers.get(0);
if (receiveNode.getParent(0) instanceof NestLoopPlanNode) {
if (subSelectRoot.hasInlinedIndexScanOfTable(m_parsedSelect.m_mvFixInfo.getMVTableName())) {
return getNextSelectPlan();
}
List<AbstractPlanNode> nljs = receiveNode.findAllNodesOfType(PlanNodeType.NESTLOOP);
List<AbstractPlanNode> nlijs = receiveNode.findAllNodesOfType(PlanNodeType.NESTLOOPINDEX);
// This is like a single table case.
if (nljs.size() + nlijs.size() == 0) {
mvFixInfoEdgeCaseOuterJoin = true;
}
root = handleMVBasedMultiPartQuery(reAggNode, root, mvFixInfoEdgeCaseOuterJoin);
}
}
} else {
if (receivers.size() > 0) {
throw new PlanningErrorException("This special case join between an outer replicated table and " + "an inner partitioned table is too complex and is not supported.");
}
root = SubPlanAssembler.addSendReceivePair(root);
// Root is a receive node here.
assert (root instanceof ReceivePlanNode);
if (m_parsedSelect.mayNeedAvgPushdown()) {
m_parsedSelect.switchOptimalSuiteForAvgPushdown();
}
if (m_parsedSelect.m_tableList.size() > 1 && m_parsedSelect.m_mvFixInfo.needed() && subSelectRoot.hasInlinedIndexScanOfTable(m_parsedSelect.m_mvFixInfo.getMVTableName())) {
// So, in-lined index scan of Nested loop index join can not be possible.
return getNextSelectPlan();
}
}
root = handleAggregationOperators(root);
// Process the re-aggregate plan node and insert it into the plan.
if (m_parsedSelect.m_mvFixInfo.needed() && mvFixInfoCoordinatorNeeded) {
AbstractPlanNode tmpRoot = root;
root = handleMVBasedMultiPartQuery(reAggNode, root, mvFixInfoEdgeCaseOuterJoin);
if (root != tmpRoot) {
mvFixNeedsProjection = true;
}
}
} else {
/*
* There is no receive node and root is a single partition plan.
*/
// If there is no receive plan node and no distributed plan has been generated,
// the fix set for MV is not needed.
m_parsedSelect.m_mvFixInfo.setNeeded(false);
root = handleAggregationOperators(root);
}
// add a PartitionByPlanNode here.
if (m_parsedSelect.hasWindowFunctionExpression()) {
root = handleWindowedOperators(root);
}
if (m_parsedSelect.hasOrderByColumns()) {
root = handleOrderBy(m_parsedSelect, root);
if (m_parsedSelect.isComplexOrderBy() && root instanceof OrderByPlanNode) {
AbstractPlanNode child = root.getChild(0);
AbstractPlanNode grandChild = child.getChild(0);
// swap the ORDER BY and complex aggregate Projection node
if (child instanceof ProjectionPlanNode) {
root.unlinkChild(child);
child.unlinkChild(grandChild);
child.addAndLinkChild(root);
root.addAndLinkChild(grandChild);
// update the new root
root = child;
} else if (m_parsedSelect.hasDistinctWithGroupBy() && child.getPlanNodeType() == PlanNodeType.HASHAGGREGATE && grandChild.getPlanNodeType() == PlanNodeType.PROJECTION) {
AbstractPlanNode grandGrandChild = grandChild.getChild(0);
child.clearParents();
root.clearChildren();
grandGrandChild.clearParents();
grandChild.clearChildren();
grandChild.addAndLinkChild(root);
root.addAndLinkChild(grandGrandChild);
root = child;
}
}
}
// node.
if (mvFixNeedsProjection || needProjectionNode(root)) {
root = addProjection(root);
}
if (m_parsedSelect.hasLimitOrOffset()) {
root = handleSelectLimitOperator(root);
}
CompiledPlan plan = new CompiledPlan();
plan.rootPlanGraph = root;
plan.setReadOnly(true);
boolean orderIsDeterministic = m_parsedSelect.isOrderDeterministic();
boolean hasLimitOrOffset = m_parsedSelect.hasLimitOrOffset();
String contentDeterminismMessage = m_parsedSelect.getContentDeterminismMessage();
plan.statementGuaranteesDeterminism(hasLimitOrOffset, orderIsDeterministic, contentDeterminismMessage);
// Apply the micro-optimization:
// LIMIT push down, Table count / Counting Index, Optimized Min/Max
MicroOptimizationRunner.applyAll(plan, m_parsedSelect);
return plan;
}
use of org.voltdb.plannodes.NestLoopPlanNode in project voltdb by VoltDB.
the class TestPlansJoin method perJoinOpTestScanJoinConditions.
private void perJoinOpTestScanJoinConditions(JoinOp joinOp) {
String query;
AbstractPlanNode pn;
AbstractPlanNode node;
NestLoopPlanNode nlj;
SeqScanPlanNode seqScan;
AbstractExpression predicate;
boolean theOpIsOnTheLeft;
query = "SELECT * FROM R1, R2 WHERE R1.A" + joinOp + "R2.A AND R1.C > 0";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) node.getChild(0);
assertEquals("R1", seqScan.getTargetTableName());
predicate = seqScan.getPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
query = "SELECT * FROM R1, R2 WHERE R1.A" + joinOp + "R2.A AND R1.C > R2.C";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
theOpIsOnTheLeft = (predicate != null) && (predicate.getLeft() != null) && predicate.getLeft().getExpressionType() == joinOp.toOperator();
assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, (theOpIsOnTheLeft ? joinOp.toOperator() : ExpressionType.COMPARE_LESSTHAN), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, (theOpIsOnTheLeft ? ExpressionType.COMPARE_LESSTHAN : joinOp.toOperator()), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertNull(seqScan.getPredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertNull(seqScan.getPredicate());
query = "SELECT * FROM R1 JOIN R2 ON R1.A" + joinOp + "R2.A WHERE R1.C > 0";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertEquals("R1", seqScan.getTargetTableName());
predicate = seqScan.getPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R2", seqScan.getTargetTableName());
assertNull(seqScan.getPredicate());
query = "SELECT * FROM R1 JOIN R2 ON R1.A" + joinOp + "R2.A WHERE R1.C > R2.C";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
theOpIsOnTheLeft = (predicate != null) && (predicate.getLeft() != null) && predicate.getLeft().getExpressionType() == joinOp.toOperator();
assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, (theOpIsOnTheLeft ? joinOp.toOperator() : ExpressionType.COMPARE_LESSTHAN), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, (theOpIsOnTheLeft ? ExpressionType.COMPARE_LESSTHAN : joinOp.toOperator()), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertNull(seqScan.getPredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertNull(seqScan.getPredicate());
query = "SELECT * FROM R1, R2, R3 WHERE R1.A" + joinOp + "R2.A AND R1.C" + joinOp + "R3.C AND R1.A > 0";
pn = compileToTopDownTree(query, 7, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
// Validate trivial child 1 before child 0 to free up local variable nlj.
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R3", seqScan.getTargetTableName());
assertNull(seqScan.getPredicate());
nlj = (NestLoopPlanNode) nlj.getChild(0);
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertEquals("R1", seqScan.getTargetTableName());
predicate = seqScan.getPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R2", seqScan.getTargetTableName());
assertNull(seqScan.getPredicate());
query = "SELECT * FROM R1 JOIN R2 ON R1.A" + joinOp + "R2.A AND R1.C" + joinOp + "R2.C WHERE R1.A > 0";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertEquals("R1", seqScan.getTargetTableName());
predicate = seqScan.getPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R2", seqScan.getTargetTableName());
assertNull(seqScan.getPredicate());
query = "SELECT A, C FROM R1 JOIN R2 USING (A, C)";
pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
query = "SELECT A, C FROM R1 JOIN R2 USING (A, C) WHERE A > 0";
pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
predicate = seqScan.getPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertNull(seqScan.getPredicate());
query = "SELECT * FROM R1 JOIN R2 ON R1.A" + joinOp + "R2.A JOIN R3 ON R1.C" + joinOp + "R3.C WHERE R1.A > 0";
pn = compileToTopDownTree(query, 7, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
// Validate trivial child 1 before child 0 to free up local variable nlj.
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R3", seqScan.getTargetTableName());
assertNull(seqScan.getPredicate());
nlj = (NestLoopPlanNode) nlj.getChild(0);
assertNull(nlj.getPreJoinPredicate());
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(nlj.getWherePredicate());
seqScan = (SeqScanPlanNode) nlj.getChild(0);
assertEquals("R1", seqScan.getTargetTableName());
predicate = seqScan.getPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
seqScan = (SeqScanPlanNode) nlj.getChild(1);
assertEquals("R2", seqScan.getTargetTableName());
assertNull(seqScan.getPredicate());
}
use of org.voltdb.plannodes.NestLoopPlanNode in project voltdb by VoltDB.
the class TestPlansJoin method testFunctionJoinConditions.
public void testFunctionJoinConditions() {
String query;
String pattern;
AbstractPlanNode pn;
AbstractPlanNode node;
NestLoopPlanNode nlj;
AbstractExpression predicate;
query = "SELECT * FROM R1, R2";
pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
assertNull(nlj.getJoinPredicate());
for (JoinOp joinOp : JoinOp.JOIN_OPS) {
perJoinOpTestFunctionJoinConditions(joinOp);
}
// USING expression can have only comma separated list of column names
query = "SELECT * FROM R1 JOIN R2 USING (ABS(A))";
pattern = "user lacks privilege or object not found: ABS";
failToCompile(query, pattern);
}
use of org.voltdb.plannodes.NestLoopPlanNode in project voltdb by VoltDB.
the class TestPlansJoin method testMultiColumnJoin.
public void testMultiColumnJoin() {
String query;
AbstractPlanNode pn;
AbstractPlanNode node;
NestLoopPlanNode nlj;
NestLoopIndexPlanNode nlij;
IndexScanPlanNode indexScan;
AbstractExpression predicate;
// Test multi column condition on non index columns
query = "SELECT A, C FROM R2 JOIN R1 USING(A, C)";
pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
nlj = (NestLoopPlanNode) node;
predicate = nlj.getJoinPredicate();
assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
query = "SELECT A, C FROM R3 JOIN R2 USING(A, C)";
pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
nlij = (NestLoopIndexPlanNode) node;
indexScan = nlij.getInlineIndexScan();
assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
predicate = indexScan.getEndExpression();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
predicate = indexScan.getPredicate();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
// Test multi column condition on index columns
query = "SELECT A FROM R2 JOIN R3 USING(A)";
pn = compileToTopDownTree(query, 1, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
nlij = (NestLoopIndexPlanNode) node;
indexScan = nlij.getInlineIndexScan();
assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
predicate = indexScan.getEndExpression();
assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
assertNull(indexScan.getPredicate());
for (JoinOp joinOp : JoinOp.JOIN_OPS) {
perJoinOpTestMultiColumnJoin(joinOp);
}
}
Aggregations