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

Example 21 with NestLoopPlanNode

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

the class TestPlansJoin method perJoinOpDistributedIndexJoinConditions.

private void perJoinOpDistributedIndexJoinConditions(JoinOp joinOp) {
    // Distributed outer table, replicated inner -NLIJ/inlined IndexScan
    String query;
    List<AbstractPlanNode> lpn;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    NestLoopIndexPlanNode nlij;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    //        query = "SELECT * FROM P1 LEFT JOIN R3 ON P1.C" +
    //                joinOp + "R3.A";
    //        lpn = compileToFragments(query);
    //        assertEquals(2, lpn.size());
    //        pn = lpn.get(1).getChild(0);
    //        assertTrue(node instanceof NestLoopIndexPlanNode);
    //        assertEquals(1, pn.getChildCount());
    //        assertTrue(n.getChild(0) instanceof SeqScanPlanNode);
    // Distributed inner and replicated outer tables -NLJ/IndexScan
    query = "SELECT * FROM R3 LEFT JOIN P2 ON R3.A" + joinOp + "P2.A AND P2.A < 0 AND P2.E > 3 WHERE P2.A IS NULL";
    lpn = compileToFragments(query);
    assertEquals(2, lpn.size());
    //*enable to debug*/printExplainPlan(lpn);
    assertReplicatedLeftJoinCoordinator(lpn, "R3");
    pn = lpn.get(0);
    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);
    predicate = nlj.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
    pn = lpn.get(1);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.INDEXSCAN);
    indexScan = (IndexScanPlanNode) node;
    assertEquals(IndexLookupType.LT, indexScan.getLookupType());
    assertNull(indexScan.getEndExpression());
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT, ExpressionType.OPERATOR_NOT, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
    // Distributed inner and outer tables -NLIJ/inlined IndexScan
    query = "SELECT * FROM P2 RIGHT JOIN P3 ON P3.A" + joinOp + "P2.A AND P2.A < 0 WHERE P2.A IS NULL";
    lpn = compileToFragments(query);
    assertProjectingCoordinator(lpn);
    pn = lpn.get(1);
    assertTopDownTree(pn, PlanNodeType.SEND, PlanNodeType.NESTLOOPINDEX, PlanNodeType.INDEXSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertEquals(JoinType.LEFT, nlij.getJoinType());
    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, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_LESSTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
}

Example 22 with NestLoopPlanNode

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

the class TestPlansJoin method perJoinOpTestMultiColumnJoin.

private void perJoinOpTestMultiColumnJoin(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    NestLoopIndexPlanNode nlij;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    query = "SELECT R1.A, R2.A FROM R2 JOIN R1 ON R1.A" + joinOp + "R2.A AND R1.C" + joinOp + "R2.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, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    if (joinOp != JoinOp.EQUAL) {
        // weaken test for now
        return;
    }
    query = "SELECT R3.A, R2.A FROM R2 JOIN R3 ON R3.A" + joinOp + "R2.A";
    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, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(indexScan.getPredicate());
    query = "SELECT R3.A, R2.A FROM R3 JOIN R2 ON R3.A" + joinOp + "R2.A AND R3.C" + joinOp + "R2.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, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
}

Example 23 with NestLoopPlanNode

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

the class TestPlansJoin method perJoinOpTestIndexOuterJoinConditions.

private void perJoinOpTestIndexOuterJoinConditions(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopIndexPlanNode nlij;
    NestLoopPlanNode nlj;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    SeqScanPlanNode seqScan;
    // R1.C" + joinOp + "R3.A Inner-Outer index join Expr. NLIJ/Inlined IndexScan
    // R3.C > 0 Inner Join Expr is pushed down to the inlined IndexScan node as a predicate
    // R2.A < 6 Outer Join Expr is a pre-join predicate for NLIJ
    query = "SELECT * FROM R2 LEFT JOIN R3 ON R3.A" + joinOp + "R2.A AND R3.C > 0 AND R2.A < 6";
    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(JoinType.LEFT, nlij.getJoinType());
    predicate = nlij.getPreJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_LESSTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    assertNull(nlij.getJoinPredicate());
    assertNull(nlij.getWherePredicate());
    seqScan = (SeqScanPlanNode) node.getChild(0);
    assertNull(seqScan.getPredicate());
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    // R1.C" + joinOp + "R3.A Inner-Outer non-index join Expr. NLJ/IndexScan
    // R3.A > 0 Inner index Join Expr is pushed down to the inner IndexScan node as an index
    // R3.C != 0 Non-index Inner Join Expression is pushed down to the inner IndexScan node as a predicate
    // R2.A < 6 Outer Join Expr is a pre-join predicate for NLJ
    query = "SELECT * FROM R2 LEFT JOIN R3 ON R3.C" + joinOp + "R2.A AND R3.A > 0 AND R3.C != 0 AND R2.A < 6";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.INDEXSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(JoinType.LEFT, nlj.getJoinType());
    predicate = nlj.getPreJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_LESSTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertNull(seqScan.getPredicate());
    indexScan = (IndexScanPlanNode) nlj.getChild(1);
    assertEquals(IndexLookupType.GT, indexScan.getLookupType());
    assertNull(indexScan.getEndExpression());
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_NOTEQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    // R2.A" + joinOp + "R3.A Inner-Outer index join Expr. NLIJ/Inlined IndexScan
    // R3.A IS NULL Inner where expr - part of the NLIJ where predicate
    // R2.A < 6 OR R3.C IS NULL Inner-Outer where expr - part of the NLIJ where predicate
    // R2.A > 3 Outer where expr - pushed down to the outer node
    query = "SELECT * FROM R2 LEFT JOIN R3 ON R3.A" + joinOp + "R2.A WHERE R3.A IS NULL AND R2.A > 3 AND (R2.A < 6 OR R3.C 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.CONJUNCTION_AND, ExpressionType.CONJUNCTION_OR, ExpressionType.COMPARE_LESSTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(indexScan.getPredicate());
}

Example 24 with NestLoopPlanNode

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

the class TestPlansJoin method perJoinOpTestFunctionJoinConditions.

private void perJoinOpTestFunctionJoinConditions(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    AbstractExpression predicate;
    query = "SELECT * FROM R1 JOIN R2 ON ABS(R1.A) " + joinOp + " ABS(R2.A) ";
    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;
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.FUNCTION, ExpressionType.VALUE_TUPLE, ExpressionType.FUNCTION, ExpressionType.VALUE_TUPLE);
    query = "SELECT * FROM R1, R2 WHERE ABS(R1.A) " + joinOp + " ABS(R2.A) ";
    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;
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.FUNCTION, ExpressionType.VALUE_TUPLE, ExpressionType.FUNCTION, ExpressionType.VALUE_TUPLE);
}

Example 25 with NestLoopPlanNode

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

the class TestPlansJoin method perJoinOpTestSeqScanOuterJoinCondition.

private void perJoinOpTestSeqScanOuterJoinCondition(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    AbstractExpression predicate;
    SeqScanPlanNode seqScan;
    IndexScanPlanNode indexScan;
    // R1.C" + joinOp + "R2.C Inner-Outer join Expr stays at the NLJ as Join predicate
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "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();
    assertExprTopDownTree(predicate, 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());
    // R1.C" + joinOp + "R2.C Inner-Outer join Expr stays at the NLJ as Join predicate
    // R1.A > 0 Outer Join Expr stays at the the NLJ as pre-join predicate
    // R2.A < 0 Inner Join Expr is pushed down to the inner SeqScan node
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C AND R1.A > 0 AND R2.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;
    predicate = nlj.getPreJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertNull(seqScan.getPredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_LESSTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    // R1.C" + joinOp + "R2.C Inner-Outer join Expr stays at the NLJ as Join predicate
    // (R1.A > 0 OR R2.A < 0) Inner-Outer join Expr stays at the NLJ as Join predicate
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C AND (R1.A > 0 OR R2.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();
    boolean theOrIsOnTheLeft = (predicate != null) && (predicate.getLeft() != null) && (ExpressionType.CONJUNCTION_OR == predicate.getLeft().getExpressionType());
    if (theOrIsOnTheLeft) {
        assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.CONJUNCTION_OR, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT, ExpressionType.COMPARE_LESSTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    } else {
        assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.CONJUNCTION_OR, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT, ExpressionType.COMPARE_LESSTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    }
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertNull(seqScan.getPredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertNull(seqScan.getPredicate());
    // R1.C" + joinOp + "R2.C Inner-Outer join Expr stays at the NLJ as Join predicate
    // R1.A > 0 Outer Where Expr is pushed down to the outer SeqScan node
    // R2.A IS NULL Inner Where Expr stays at the the NLJ as post join (where) predicate
    // (R1.C > R2.C OR R2.C IS NULL) Inner-Outer Where stays at the the NLJ as post join (where) predicate
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE R1.A > 0 AND R2.A IS NULL AND (R1.C > R2.C OR R2.C IS 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(JoinType.LEFT, nlj.getJoinType());
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = nlj.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.CONJUNCTION_OR, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
    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());
    // R3.A" + joinOp + "R2.A Inner-Outer index join Expr. NLJ predicate.
    // R3.A > 3 Index Outer where expr pushed down to IndexScanPlanNode
    // R3.C < 0 non-index Outer where expr pushed down to IndexScanPlanNode as a predicate
    query = "SELECT * FROM R3 LEFT JOIN R2 ON R3.A" + joinOp + "R2.A WHERE R3.A > 3 AND R3.C < 0";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.INDEXSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(JoinType.LEFT, nlj.getJoinType());
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    indexScan = (IndexScanPlanNode) nlj.getChild(0);
    assertEquals(IndexLookupType.GT, indexScan.getLookupType());
    assertNull(indexScan.getEndExpression());
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_LESSTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    // R3.C" + joinOp + "R2.C Inner-Outer non-index join Expr. NLJ predicate.
    // R3.A > 3 Index null rejecting inner where expr pushed down to IndexScanPlanNode
    // NLJ is simplified to be INNER
    query = "SELECT * FROM R2 LEFT JOIN R3 ON R3.C" + joinOp + "R2.C WHERE R3.A > 3";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.INDEXSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(JoinType.INNER, nlj.getJoinType());
    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("R2", seqScan.getTargetTableName());
    assertNull(seqScan.getPredicate());
    indexScan = (IndexScanPlanNode) nlj.getChild(1);
    assertEquals(IndexLookupType.GT, indexScan.getLookupType());
    assertNull(indexScan.getEndExpression());
    assertNull(indexScan.getPredicate());
    if (joinOp != JoinOp.EQUAL) {
        // weaken test for now
        return;
    }
    query = "SELECT * FROM R2 LEFT JOIN R3 ON R3.A" + joinOp + "R2.C WHERE R3.A > 3";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    NestLoopIndexPlanNode nlij = (NestLoopIndexPlanNode) node;
    assertEquals(JoinType.INNER, nlij.getJoinType());
}