Examples with JoinNode org.voltdb.planner.parseinfo.JoinNode used on opensource projects

Example 1 with JoinNode

use of org.voltdb.planner.parseinfo.JoinNode in project voltdb by VoltDB.

the class WriterSubPlanAssembler method nextPlan.

/**
     * Pull a join order out of the join orders deque, compute all possible plans
     * for that join order, then append them to the computed plans deque.
     */
@Override
AbstractPlanNode nextPlan() {
    if (!m_generatedPlans) {
        assert (m_parsedStmt.m_joinTree != null);
        // Clone the node to make make sure that analyze expression is called
        // only once on the node.
        JoinNode tableNode = (JoinNode) m_parsedStmt.m_joinTree.clone();
        // Analyze join conditions
        tableNode.analyzeJoinExpressions(m_parsedStmt.m_noTableSelectionList);
        // these just shouldn't happen right?
        assert (m_parsedStmt.m_noTableSelectionList.size() == 0);
        m_generatedPlans = true;
        // This is either UPDATE or DELETE statement. Consolidate all expressions
        // into the WHERE list.
        tableNode.m_whereInnerList.addAll(tableNode.m_joinInnerList);
        tableNode.m_joinInnerList.clear();
        tableNode.m_accessPaths.addAll(getRelevantAccessPathsForTable(tableNode.getTableScan(), null, tableNode.m_whereInnerList, null));
        for (AccessPath path : tableNode.m_accessPaths) {
            tableNode.m_currentAccessPath = path;
            AbstractPlanNode plan = getAccessPlanForTable(tableNode);
            m_plans.add(plan);
        }
    }
    return m_plans.poll();
}

Example 2 with JoinNode

use of org.voltdb.planner.parseinfo.JoinNode in project voltdb by VoltDB.

the class SelectSubPlanAssembler method generateInnerJoinOrdersForTree.

/**
     * Helper method to generate join orders for a join tree containing only INNER joins that
     * can be obtained by the permutation of the original tables.
     *
     * @param subTree join tree
     * @return list of valid join orders
     */
private static List<JoinNode> generateInnerJoinOrdersForTree(JoinNode subTree) {
    // Get a list of the leaf nodes(tables) to permute them
    List<JoinNode> tableNodes = subTree.generateLeafNodesJoinOrder();
    List<List<JoinNode>> joinOrders = PermutationGenerator.generatePurmutations(tableNodes);
    List<JoinNode> newTrees = new ArrayList<>();
    for (List<JoinNode> joinOrder : joinOrders) {
        newTrees.add(JoinNode.reconstructJoinTreeFromTableNodes(joinOrder, JoinType.INNER));
    }
    //Collect all the join/where conditions to reassign them later
    AbstractExpression combinedWhereExpr = subTree.getAllFilters();
    List<JoinNode> treePermutations = new ArrayList<>();
    for (JoinNode newTree : newTrees) {
        if (combinedWhereExpr != null) {
            newTree.setWhereExpression(combinedWhereExpr.clone());
        }
        // The new tree root node id must match the original one to be able to reconnect the
        // subtrees
        newTree.setId(subTree.getId());
        treePermutations.add(newTree);
    }
    return treePermutations;
}

Example 3 with JoinNode

use of org.voltdb.planner.parseinfo.JoinNode 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 4 with JoinNode

use of org.voltdb.planner.parseinfo.JoinNode in project voltdb by VoltDB.

the class PlanAssembler method simplifyOuterJoinRecursively.

private static void simplifyOuterJoinRecursively(BranchNode joinNode, List<AbstractExpression> exprs) {
    assert (joinNode != null);
    JoinNode leftNode = joinNode.getLeftNode();
    JoinNode rightNode = joinNode.getRightNode();
    if (joinNode.getJoinType() == JoinType.LEFT) {
        // see if the expression is NULL-rejecting for any of them
        if (isNullRejecting(rightNode.generateTableJoinOrder(), exprs)) {
            joinNode.setJoinType(JoinType.INNER);
        }
    } else if (joinNode.getJoinType() == JoinType.RIGHT) {
        // see if the expression is NULL-rejecting for any of them
        if (isNullRejecting(leftNode.generateTableJoinOrder(), exprs)) {
            joinNode.setJoinType(JoinType.INNER);
        }
    } else if (joinNode.getJoinType() == JoinType.FULL) {
        // see if the expression is NULL-rejecting for any of them
        if (isNullRejecting(leftNode.generateTableJoinOrder(), exprs)) {
            joinNode.setJoinType(JoinType.LEFT);
        }
        // see if the expression is NULL-rejecting for any of them
        if (isNullRejecting(rightNode.generateTableJoinOrder(), exprs)) {
            if (JoinType.FULL == joinNode.getJoinType()) {
                joinNode.setJoinType(JoinType.RIGHT);
            } else {
                // LEFT join was just removed
                joinNode.setJoinType(JoinType.INNER);
            }
        }
    }
    // because they simplify both inner and outer nodes.
    if (leftNode.getWhereExpression() != null) {
        exprs.add(leftNode.getWhereExpression());
    }
    if (rightNode.getWhereExpression() != null) {
        exprs.add(rightNode.getWhereExpression());
    }
    // The JOIN expressions (ON) are only applicable
    // to the INNER node of an outer join.
    List<AbstractExpression> exprsForInnerNode = new ArrayList<>(exprs);
    if (leftNode.getJoinExpression() != null) {
        exprsForInnerNode.add(leftNode.getJoinExpression());
    }
    if (rightNode.getJoinExpression() != null) {
        exprsForInnerNode.add(rightNode.getJoinExpression());
    }
    List<AbstractExpression> leftNodeExprs;
    List<AbstractExpression> rightNodeExprs;
    switch(joinNode.getJoinType()) {
        case INNER:
            leftNodeExprs = exprsForInnerNode;
            rightNodeExprs = exprsForInnerNode;
            break;
        case LEFT:
            leftNodeExprs = exprs;
            rightNodeExprs = exprsForInnerNode;
            break;
        case RIGHT:
            leftNodeExprs = exprsForInnerNode;
            rightNodeExprs = exprs;
            break;
        case FULL:
            leftNodeExprs = exprs;
            rightNodeExprs = exprs;
            break;
        default:
            // shouldn't get there
            leftNodeExprs = null;
            rightNodeExprs = null;
            assert (false);
    }
    if (leftNode instanceof BranchNode) {
        simplifyOuterJoinRecursively((BranchNode) leftNode, leftNodeExprs);
    }
    if (rightNode instanceof BranchNode) {
        simplifyOuterJoinRecursively((BranchNode) rightNode, rightNodeExprs);
    }
}

Example 5 with JoinNode

use of org.voltdb.planner.parseinfo.JoinNode in project voltdb by VoltDB.

the class PlanAssembler method simplifyOuterJoin.

/**
     * Outer join simplification using null rejection.
     * http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.43.2531
     * Outerjoin Simplification and Reordering for Query Optimization
     * by Cesar A. Galindo-Legaria , Arnon Rosenthal
     * Algorithm:
     * Traverse the join tree top-down:
     *  For each join node n1 do:
     *    For each expression expr (join and where) at the node n1
     *      For each join node n2 descended from n1 do:
     *          If expr rejects nulls introduced by n2 inner table, then
     *              - convert LEFT OUTER n2 to an INNER join.
     *              - convert FULL OUTER n2 to RIGHT OUTER join
     *          If expr rejects nulls introduced by n2 outer table, then
     *              - convert RIGHT OUTER n2 to an INNER join.
     *              - convert FULL OUTER n2 to LEFT OUTER join
     */
private static void simplifyOuterJoin(BranchNode joinTree) {
    assert (joinTree != null);
    List<AbstractExpression> exprs = new ArrayList<>();
    JoinNode leftNode = joinTree.getLeftNode();
    JoinNode rightNode = joinTree.getRightNode();
    // WHERE expressions need to be evaluated for NULL-rejection
    if (leftNode.getWhereExpression() != null) {
        exprs.add(leftNode.getWhereExpression());
    }
    if (rightNode.getWhereExpression() != null) {
        exprs.add(rightNode.getWhereExpression());
    }
    simplifyOuterJoinRecursively(joinTree, exprs);
}