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

Example 6 with NestLoopIndexPlanNode

use of org.voltdb.plannodes.NestLoopIndexPlanNode 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 7 with NestLoopIndexPlanNode

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

the class TestIndexSelection method testGeoIndex.

public void testGeoIndex() {
    AbstractPlanNode pn;
    IndexScanPlanNode indexScan;
    String jsonicIdxScan;
    pn = compile("select polys.point " + "from polypoints polys " + "where contains(polys.poly, ?);");
    pn = pn.getChild(0);
    /* enable to debug */
    System.out.println("DEBUG: " + pn.toExplainPlanString());
    assertTrue(pn instanceof IndexScanPlanNode);
    indexScan = (IndexScanPlanNode) pn;
    assertEquals(IndexLookupType.GEO_CONTAINS, indexScan.getLookupType());
    jsonicIdxScan = indexScan.toJSONString();
    /* enable to debug */
    System.out.println("DEBUG: " + jsonicIdxScan);
    assertEquals("POLYPOINTSPOLY", indexScan.getTargetIndexName());
    // Expecting one index search key expression
    // that is a parameter (31) of type GEOGRAPHY_POINT (26).
    assertEquals(1, indexScan.getSearchKeyExpressions().size());
    assertTrue(jsonicIdxScan.contains("\"SEARCHKEY_EXPRESSIONS\":[{\"TYPE\":31,\"VALUE_TYPE\":26"));
    pn = compile("select polys.poly, points.point " + "from polypoints polys, polypoints points " + "where contains(polys.poly, points.point);");
    pn = pn.getChild(0);
    pn = pn.getChild(0);
    /* enable to debug */
    System.out.println("DEBUG: " + pn.toExplainPlanString());
    assertTrue(pn instanceof NestLoopIndexPlanNode);
    indexScan = ((NestLoopIndexPlanNode) pn).getInlineIndexScan();
    assertEquals(IndexLookupType.GEO_CONTAINS, indexScan.getLookupType());
    jsonicIdxScan = indexScan.toJSONString();
    assertEquals("POLYPOINTSPOLY", indexScan.getTargetIndexName());
    // Expecting one index search key expression
    // that is a TVE (32) of type GEOGRAPHY_POINT (26).
    assertEquals(1, indexScan.getSearchKeyExpressions().size());
    assertTrue(jsonicIdxScan.contains("\"SEARCHKEY_EXPRESSIONS\":[{\"TYPE\":32,\"VALUE_TYPE\":26"));
    pn = pn.getChild(0);
    // A non-geography index scan over a unique key for the
    // outer scan of "points" gets injected strictly for determinism.
    assertTrue(pn instanceof IndexScanPlanNode);
    indexScan = (IndexScanPlanNode) pn;
    assertEquals(IndexLookupType.GTE, indexScan.getLookupType());
    pn = compile("select polys.point " + "from polypoints polys " + "where contains(polys.poly, ?);");
    pn = pn.getChild(0);
    //* enable to debug */ System.out.println("DEBUG: " + pn.toExplainPlanString());
    assertTrue(pn instanceof IndexScanPlanNode);
    indexScan = (IndexScanPlanNode) pn;
    assertEquals(IndexLookupType.GEO_CONTAINS, indexScan.getLookupType());
    jsonicIdxScan = indexScan.toJSONString();
    //* enable to debug */ System.out.println("DEBUG: " + jsonicIdxScan);
    assertEquals("POLYPOINTSPOLY", indexScan.getTargetIndexName());
    // Expecting one index search key expression
    // that is a parameter (31) of type GEOGRAPHY_POINT (26).
    assertEquals(1, indexScan.getSearchKeyExpressions().size());
    assertTrue(jsonicIdxScan.contains("\"SEARCHKEY_EXPRESSIONS\":[{\"TYPE\":31,\"VALUE_TYPE\":26"));
}

Example 8 with NestLoopIndexPlanNode

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

Example 9 with NestLoopIndexPlanNode

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

the class TestPlansJoin method perJoinOpTestOuterJoinSimplification.

private void perJoinOpTestOuterJoinSimplification(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    SeqScanPlanNode seqScan;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    NestLoopIndexPlanNode nlij;
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE R2.C IS NOT NULL";
    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;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE R2.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;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 RIGHT JOIN R2 ON R1.C" + joinOp + "R2.C 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;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.C" + joinOp + "R3.C WHERE R3.A > 0";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.INDEXSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.C" + joinOp + "R3.A WHERE R3.A > 0";
    if (joinOp == JoinOp.EQUAL) {
        // weaken test for now
        pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
        node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
        nlij = (NestLoopIndexPlanNode) node;
        assertEquals(nlij.getJoinType(), JoinType.INNER);
    }
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) < 10";
    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;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 RIGHT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) < 10";
    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;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) < 10";
    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;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    query = "SELECT * FROM R1 RIGHT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) < 10";
    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;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) < 10 AND R1.C = 3";
    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;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) <  10 OR R2.C IS NOT NULL";
    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;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) <  10 AND R1.C > 3";
    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;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) <  10 OR R2.C IS NOT NULL";
    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;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    // Test with seqscan with different filers.
    query = "SELECT R2.A, R1.* FROM R1 LEFT OUTER JOIN R2 ON R2.A" + joinOp + "R1.A WHERE R2.A > 3";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    //* enable for debug */ System.out.println(pn.toExplainPlanString());
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    query = "SELECT R2.A, R1.* FROM R1 LEFT OUTER JOIN R2 ON R2.A" + joinOp + "R1.A WHERE R2.A IS NULL";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = nlj.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertNull(seqScan.getPredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertNull(seqScan.getPredicate());
    if (joinOp != JoinOp.EQUAL) {
        // weaken test for now
        return;
    }
    query = "SELECT b.A, a.* FROM R1 a LEFT OUTER JOIN R4 b ON b.A" + joinOp + "a.A AND b.C " + joinOp + " a.C AND a.D " + joinOp + " b.D WHERE b.A IS NULL";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    //* enable for debug */ System.out.println(pn.toExplainPlanString());
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertEquals(nlij.getJoinType(), JoinType.LEFT);
    assertNull(nlij.getPreJoinPredicate());
    assertNull(nlij.getJoinPredicate());
    predicate = nlij.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(indexScan.getPredicate());
    query = "SELECT b.A, a.* FROM R1 a LEFT OUTER JOIN R4 b ON b.A" + joinOp + "a.A AND b.C " + joinOp + " a.C AND a.D " + joinOp + " b.D WHERE b.B + b.A IS NULL";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertEquals(nlij.getJoinType(), JoinType.LEFT);
    assertNull(nlij.getPreJoinPredicate());
    assertNull(nlij.getJoinPredicate());
    predicate = nlij.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.OPERATOR_PLUS, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    query = "SELECT a.* FROM R1 a LEFT OUTER JOIN R5 b ON b.A" + joinOp + "a.A WHERE b.A IS NULL";
    pn = compileToTopDownTree(query, 3, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertEquals(nlij.getJoinType(), JoinType.LEFT);
    assertNull(nlij.getPreJoinPredicate());
    assertNull(nlij.getJoinPredicate());
    predicate = nlij.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
}

Example 10 with NestLoopIndexPlanNode

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

the class TestPlansJoin method perJoinOpTestJoinOrders.

private void perJoinOpTestJoinOrders(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    SeqScanPlanNode seqScan;
    NestLoopIndexPlanNode nlij;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    if (joinOp != JoinOp.EQUAL) {
        // weaken test for now
        return;
    }
    // Index Join (R3.A) still has a lower cost compare to a Loop Join
    // despite the R3.C = 0 equality filter on the inner node
    query = "SELECT * FROM R1 JOIN R3 ON R3.A" + joinOp + "R1.A WHERE R3.C = 0";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    seqScan = (SeqScanPlanNode) node;
    assertEquals("R1", seqScan.getTargetTableName());
    // R3.A is an INDEX. Both children are IndexScans. With everything being equal,
    // the Left table (L) has fewer filters and should be an inner node
    query = "SELECT L.A, R.A FROM R3 L JOIN R3 R ON L.A" + joinOp + "R.A WHERE R.A > 3 AND R.C  = 3 AND L.A > 2 ;";
    pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.INDEXSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.INDEXSCAN);
    indexScan = (IndexScanPlanNode) node;
    assertEquals("R", indexScan.getTargetTableAlias());
    // NLIJ with inline inner IndexScan over R2 using its partial index is a winner
    // over the NLJ with R2 on the outer side
    query = "SELECT * FROM R3 JOIN R2 ON R3.C" + joinOp + "R2.C WHERE R2.C > 100;";
    pn = compileToTopDownTree(query, 4, 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, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(indexScan.getPredicate());
    assertEquals("PARTIAL_IND2", indexScan.getTargetIndexName());
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    assertEquals("R3", seqScan.getTargetTableName());
}