Examples with AbstractJoinPlanNode org.voltdb.plannodes.AbstractJoinPlanNode used on opensource projects

Example 1 with AbstractJoinPlanNode

use of org.voltdb.plannodes.AbstractJoinPlanNode in project voltdb by VoltDB.

the class PushdownLimits method recursivelyApply.

@Override
protected AbstractPlanNode recursivelyApply(AbstractPlanNode plan) {
    assert (plan != null);
    // depth first:
    //     find LimitPlanNodes with exactly one child
    //     where that child is an AbstractScanPlanNode
    //     disconnect the LimitPlanNode
    //     and inline the LimitPlanNode in to the AbstractScanPlanNode
    ArrayList<AbstractPlanNode> children = new ArrayList<AbstractPlanNode>();
    for (int i = 0; i < plan.getChildCount(); i++) children.add(plan.getChild(i));
    plan.clearChildren();
    for (AbstractPlanNode child : children) {
        // TODO this will break when children feed multiple parents
        child = recursivelyApply(child);
        child.clearParents();
        plan.addAndLinkChild(child);
    }
    if (!(plan instanceof LimitPlanNode)) {
        return plan;
    }
    if (plan.getChildCount() != 1) {
        assert (plan.getChildCount() == 1);
        return plan;
    }
    AbstractPlanNode child = plan.getChild(0);
    // push into Scans
    if (child instanceof AbstractScanPlanNode) {
        // in future, this limit can be aggregate inline node.
        if (AggregatePlanNode.getInlineAggregationNode(child) != null) {
            return plan;
        }
        plan.clearChildren();
        child.clearParents();
        child.addInlinePlanNode(plan);
        return child;
    }
    // == child/projection . recursivelyApply(plan/limit . leaf/whatever)
    if (child instanceof ProjectionPlanNode) {
        assert (child.getChildCount() == 1);
        AbstractPlanNode leaf = child.getChild(0);
        leaf.clearParents();
        plan.clearChildren();
        plan.addAndLinkChild(leaf);
        child.clearChildren();
        child.clearParents();
        child.addAndLinkChild(plan);
        return recursivelyApply(child);
    }
    // push into JOINs
    if (child instanceof AbstractJoinPlanNode) {
        plan.clearChildren();
        child.clearParents();
        child.addInlinePlanNode(plan);
        // }
        return child;
    }
    return plan;
}

Example 2 with AbstractJoinPlanNode

use of org.voltdb.plannodes.AbstractJoinPlanNode 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;
}

Example 3 with AbstractJoinPlanNode

use of org.voltdb.plannodes.AbstractJoinPlanNode in project voltdb by VoltDB.

the class TestPlansGroupBy method checkMVReaggregateFeature.

// topNode, reAggNode
private void checkMVReaggregateFeature(List<AbstractPlanNode> pns, boolean needFix, int numGroupByOfTopAggNode, int numAggsOfTopAggNode, int numGroupByOfReaggNode, int numAggsOfReaggNode, boolean aggPushdown, boolean aggInline) {
    assertEquals(2, pns.size());
    AbstractPlanNode p = pns.get(0);
    assertTrue(p instanceof SendPlanNode);
    p = p.getChild(0);
    if (p instanceof ProjectionPlanNode) {
        p = p.getChild(0);
    }
    if (p instanceof LimitPlanNode) {
        // No limit pushed down.
        p = p.getChild(0);
    }
    if (p instanceof OrderByPlanNode) {
        p = p.getChild(0);
    }
    HashAggregatePlanNode reAggNode = null;
    List<AbstractPlanNode> nodes = p.findAllNodesOfClass(AbstractReceivePlanNode.class);
    assertEquals(1, nodes.size());
    AbstractPlanNode receiveNode = nodes.get(0);
    // Indicates that there is no top aggregation node.
    if (numGroupByOfTopAggNode == -1) {
        if (needFix) {
            p = receiveNode.getParent(0);
            assertTrue(p instanceof HashAggregatePlanNode);
            reAggNode = (HashAggregatePlanNode) p;
            assertEquals(numGroupByOfReaggNode, reAggNode.getGroupByExpressionsSize());
            assertEquals(numAggsOfReaggNode, reAggNode.getAggregateTypesSize());
            p = p.getChild(0);
        }
        assertTrue(p instanceof ReceivePlanNode);
        p = pns.get(1);
        assertTrue(p instanceof SendPlanNode);
        p = p.getChild(0);
        assertTrue(p instanceof AbstractScanPlanNode);
        return;
    }
    if (p instanceof ProjectionPlanNode) {
        p = p.getChild(0);
    }
    //
    // Hash top aggregate node
    //
    AggregatePlanNode topAggNode = null;
    if (p instanceof AbstractJoinPlanNode) {
        // Inline aggregation with join
        topAggNode = AggregatePlanNode.getInlineAggregationNode(p);
    } else {
        assertTrue(p instanceof AggregatePlanNode);
        topAggNode = (AggregatePlanNode) p;
        p = p.getChild(0);
    }
    assertEquals(numGroupByOfTopAggNode, topAggNode.getGroupByExpressionsSize());
    assertEquals(numAggsOfTopAggNode, topAggNode.getAggregateTypesSize());
    if (needFix) {
        p = receiveNode.getParent(0);
        assertTrue(p instanceof HashAggregatePlanNode);
        reAggNode = (HashAggregatePlanNode) p;
        assertEquals(numGroupByOfReaggNode, reAggNode.getGroupByExpressionsSize());
        assertEquals(numAggsOfReaggNode, reAggNode.getAggregateTypesSize());
        p = p.getChild(0);
    }
    assertTrue(p instanceof ReceivePlanNode);
    // Test the second part
    p = pns.get(1);
    assertTrue(p instanceof SendPlanNode);
    p = p.getChild(0);
    if (aggPushdown) {
        assertTrue(!needFix);
        if (aggInline) {
            assertNotNull(AggregatePlanNode.getInlineAggregationNode(p));
        } else {
            assertTrue(p instanceof AggregatePlanNode);
            p = p.getChild(0);
        }
    }
    if (needFix) {
        assertTrue(p instanceof AbstractScanPlanNode);
    } else {
        assertTrue(p instanceof AbstractScanPlanNode || p instanceof AbstractJoinPlanNode);
    }
}

Example 4 with AbstractJoinPlanNode

use of org.voltdb.plannodes.AbstractJoinPlanNode in project voltdb by VoltDB.

the class TestPlansInExistsSubQueries method testExistsSimplification.

public void testExistsSimplification() {
    AbstractPlanNode pn;
    AbstractJoinPlanNode jpn;
    // LIMIT is 0 EXISTS => FALSE
    pn = compile("select a from r1 where exists " + " (select a, c  from r2 limit 0) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyCVEPredicate(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), false);
    // LIMIT is 0 EXISTS => FALSE
    pn = compile("select a from r1 where exists " + " (select count(*)  from r2 limit 0) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyCVEPredicate(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), false);
    //EXISTS => TRUE, join predicate is TRUE or EXPR = > TRUE and dropped
    pn = compile("select r1.a from r1 join r2 on (exists " + " (select max(a)  from r2) or r2.a > 0)");
    assertTrue(pn.getChild(0).getChild(0) instanceof AbstractJoinPlanNode);
    jpn = (AbstractJoinPlanNode) pn.getChild(0).getChild(0);
    assertTrue(jpn.getWherePredicate() == null);
    //EXISTS => FALSE, join predicate is retained
    pn = compile("select r1.a from r1 join r2 on exists " + " (select max(a)  from r2 offset 1) ");
    assertTrue(pn.getChild(0).getChild(0) instanceof NestLoopPlanNode);
    jpn = (NestLoopPlanNode) pn.getChild(0).getChild(0);
    verifyCVEPredicate(jpn.getJoinPredicate(), false);
    // table-agg-without-having-groupby OFFSET > 0 => FALSE
    pn = compile("select a from r1 where exists " + " (select count(*)  from r2 offset 1) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyCVEPredicate(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), false);
    // table-agg-without-having-groupby  => TRUE
    pn = compile("select a from r1 where exists " + " (select max(a)  from r2) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    assertTrue(((SeqScanPlanNode) pn.getChild(0)).getPredicate() == null);
    // table-agg-without-having-groupby by limit is a parameter => EXISTS
    pn = compile("select a from r1 where exists " + " (select max(a)  from r2 limit ?) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    AbstractExpression pred = ((SeqScanPlanNode) pn.getChild(0)).getPredicate();
    assertNotNull(pred);
    assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
    // Subquery => select 1 from r2 limit 1 offset 2
    pn = compile("select a from r1 where exists " + " (select a, c  from r2 order by a offset 2) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyTrivialSchemaLimitOffset(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), 1, 2);
    // User's limit ?
    // Subquery => EXISTS (select 1 from r2 limit ?)
    pn = compile("select a from r1 where exists " + " (select a, c  from r2 order by a limit ?) ");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyTrivialSchemaLimitOffset(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), -1, 0);
    // Subquery subquery-without-having with group by and no limit
    //   => select a, max(c) from r2 group by a limit 1
    pn = compile("select a from r1 where exists " + " (select a, max(c) from r2 group by a order by max(c))");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyAggregateSubquery(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), 2, 1, false);
    // Subquery subquery-without-having with group by and offset 3 => subquery-without-having with group by and offset 3
    pn = compile("select a from r1 where exists " + " (select a, max(c) from r2 group by a order by max(c) offset 2)");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    verifyAggregateSubquery(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), 2, 1, false);
    // Subquery subquery-with-having with group by => subquery-with-having with group by
    pn = compile("select a from r1 where exists " + " (select a, max(c) from r2 group by a having max(c) > 2 order by max(c))");
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    // weakened for now around the unification of the input column to the HAVING clause:
    verifyAggregateSubquery(((SeqScanPlanNode) pn.getChild(0)).getPredicate(), 2, 1, true);
//verifyAggregateSubquery(((SeqScanPlanNode)pn.getChild(0)).getPredicate(), 3, 1, true);
}

Example 5 with AbstractJoinPlanNode

use of org.voltdb.plannodes.AbstractJoinPlanNode in project voltdb by VoltDB.

the class PlanAssembler method isOrderByNodeRequired.

/**
     * Determine if an OrderByPlanNode is needed.  This may return false if the
     * statement has no ORDER BY clause, or if the subtree is already producing
     * rows in the correct order.  Note that a hash aggregate node will cause this
     * to return true, and a serial or partial aggregate node may cause this
     * to return true.
     *
     * @param parsedStmt    The statement whose plan may need an OrderByPlanNode
     * @param root          The subtree which may need its output tuples ordered
     * @return true if the plan needs an OrderByPlanNode, false otherwise
     */
private static boolean isOrderByNodeRequired(AbstractParsedStmt parsedStmt, AbstractPlanNode root) {
    // Only sort when the statement has an ORDER BY.
    if (!parsedStmt.hasOrderByColumns()) {
        return false;
    }
    // Skip the explicit ORDER BY plan step if an IndexScan is already providing the equivalent ordering.
    // Note that even tree index scans that produce values in their own "key order" only report
    // their sort direction != SortDirectionType.INVALID
    // when they enforce an ordering equivalent to the one requested in the ORDER BY
    // or window function clause.  Even an intervening non-hash aggregate will not interfere
    // in this optimization.
    // Is there a window function between the root and the
    // scan or join nodes?  Also, does this window function
    // use the index.
    int numberWindowFunctions = 0;
    int numberReceiveNodes = 0;
    int numberHashAggregates = 0;
    // EE keeps the insertion ORDER so that ORDER BY could apply before DISTINCT.
    // However, this probably is not optimal if there are low cardinality results.
    // Again, we have to replace the TVEs for ORDER BY clause for these cases in planning.
    //
    // Find the scan or join node.
    AbstractPlanNode probe;
    for (probe = root; !((probe instanceof AbstractJoinPlanNode) || (probe instanceof AbstractScanPlanNode)) && (probe != null); probe = (probe.getChildCount() > 0) ? probe.getChild(0) : null) {
        // we will have recorded it in the scan or join node.
        if (probe.getPlanNodeType() == PlanNodeType.WINDOWFUNCTION) {
            numberWindowFunctions += 1;
        }
        // needs them.
        if (probe.getPlanNodeType() == PlanNodeType.RECEIVE) {
            numberReceiveNodes += 1;
        }
        // the ordering, but a serial aggregation does not.
        if ((probe.getPlanNodeType() == PlanNodeType.HASHAGGREGATE) || (probe.getPlanNodeType() == PlanNodeType.PARTIALAGGREGATE)) {
            numberHashAggregates += 1;
        }
    }
    if (probe == null) {
        // to be right.  Maybe this should be an assert?
        return true;
    }
    //
    if (!(probe instanceof IndexSortablePlanNode)) {
        return true;
    }
    IndexUseForOrderBy indexUse = ((IndexSortablePlanNode) probe).indexUse();
    if (indexUse.getSortOrderFromIndexScan() == SortDirectionType.INVALID) {
        return true;
    }
    // an ORDERBY node.
    if (numberHashAggregates > 0) {
        return true;
    }
    if (numberWindowFunctions == 0) {
        if (indexUse.getWindowFunctionUsesIndex() == SubPlanAssembler.NO_INDEX_USE) {
            return true;
        }
        assert (indexUse.getWindowFunctionUsesIndex() == SubPlanAssembler.STATEMENT_LEVEL_ORDER_BY_INDEX);
        // false for SP (numberReceiveNodes == 0);
        return numberReceiveNodes > 0;
    }
    if (numberWindowFunctions == 1) {
        // will return 0.
        if ((indexUse.getWindowFunctionUsesIndex() != 0) || (!indexUse.isWindowFunctionCompatibleWithOrderBy())) {
            return true;
        }
        // does not need one.  So this is a false.
        return false;
    }
    // because we only support one window function.
    return true;
}