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

Example 26 with NestLoopPlanNode

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

the class TestPlansSubQueries method testPartitionedGroupBy.

public void testPartitionedGroupBy() {
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    AbstractPlanNode nlpn;
    // (1) Single partition query, filter on outer query.
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 GROUP BY A, C) T1 " + "where T1.A = 1 ");
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "A", "C");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    // Because it group by the partition column, we can drop the group by column on coordinator
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    // (2) Single partition query, filter in inner sub-query.
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 WHERE A = 1 GROUP BY A, C) T1");
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "A", "C");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    // (3) Sub-query with replicated table group by
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM R1 GROUP BY A, C) T1, P1 " + "where T1.A = P1.A ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopIndexPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopIndexPlanNode) nlpn).getJoinType());
    pn = nlpn.getInlinePlanNode(PlanNodeType.INDEXSCAN);
    checkPrimaryKeyIndexScan(pn, "P1");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkSeqScan(pn, "R1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    // Top aggregation node on coordinator
    planNodes = compileToFragments("SELECT -8, T1.NUM FROM SR4 T0, " + "(select max(RATIO) RATIO, sum(NUM) NUM, DESC from SP4 group by DESC) T1 " + "WHERE (T1.NUM + 5 ) > 44");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(1);
    checkPrimaryKeyIndexScan(pn, "SR4");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "NUM");
    pn = pn.getChild(0);
    assertTrue(pn instanceof AggregatePlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    checkPrimaryKeyIndexScan(pn, "SP4");
    //
    // (4) Sub-query with partitioned table group by
    //
    // optimize the group by case to join on distributed node.
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopIndexPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopIndexPlanNode) nlpn).getJoinType());
    pn = nlpn.getInlinePlanNode(PlanNodeType.INDEXSCAN);
    checkPrimaryKeyIndexScan(pn, "P2");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    // Add aggregate inside of subquery
    planNodes = compileToFragments("SELECT * FROM (SELECT A, COUNT(*) CT FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopIndexPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopIndexPlanNode) nlpn).getJoinType());
    pn = nlpn.getInlinePlanNode(PlanNodeType.INDEXSCAN);
    checkPrimaryKeyIndexScan(pn, "P2");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    // Add distinct option to aggregate inside of subquery
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C, SUM(distinct D) FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopIndexPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopIndexPlanNode) nlpn).getJoinType());
    pn = nlpn.getInlinePlanNode(PlanNodeType.INDEXSCAN);
    checkPrimaryKeyIndexScan(pn, "P2");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    // single partition filter inside subquery
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 WHERE A = 3 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A ");
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopIndexPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopIndexPlanNode) nlpn).getJoinType());
    pn = nlpn.getInlinePlanNode(PlanNodeType.INDEXSCAN);
    checkPrimaryKeyIndexScan(pn, "P2");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    // single partition filter outside subquery
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A and P2.A = 3");
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    pn = nlpn.getChild(1);
    checkPrimaryKeyIndexScan(pn, "P2");
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A and T1.A = 3");
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    pn = nlpn.getChild(1);
    checkPrimaryKeyIndexScan(pn, "P2");
    // Group by C, A instead of A, C
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 GROUP BY C, A) T1, P2 " + "where T1.A = P2.A and T1.A = 3");
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    pn = nlpn.getChild(1);
    checkPrimaryKeyIndexScan(pn, "P2");
}

Example 27 with NestLoopPlanNode

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

the class TestPlansSubQueries method checkEdgeCases.

private void checkEdgeCases(String sql, String[] outputColumns, String table1, String column1, String table2, String column2, String subquery1, String subQueryColumn1, String subquery2, String subQueryColumn2) {
    AbstractPlanNode pn;
    pn = compile(sql);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    checkOutputSchema(pn.getOutputSchema(), outputColumns);
    pn = pn.getChild(0);
    assertTrue(pn instanceof NestLoopPlanNode);
    checkSeqScan(pn.getChild(0), table1, column1);
    if (subquery1 != null) {
        checkSeqScan(pn.getChild(0).getChild(0), subquery1, column1);
    }
    checkSeqScan(pn.getChild(1), table2, column2);
    if (subquery2 != null)
        checkSeqScan(pn.getChild(1).getChild(0), subquery2, column2);
}

Example 28 with NestLoopPlanNode

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

the class TestPlansSubQueries method testTableAggSubquery.

public void testTableAggSubquery() {
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    AbstractPlanNode nlpn;
    planNodes = compileToFragments("SELECT * FROM (SELECT sum(C) AS SC FROM P1) T1");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    checkSeqScan(pn, "T1", "SC");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    assertTrue(pn instanceof AggregatePlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.AGGREGATE));
    failToCompile("SELECT * FROM (SELECT sum(C) AS SC FROM P1) T1, P2 " + "where P2.A = T1.SC", joinErrorMsg);
    failToCompile("SELECT * FROM (SELECT count(A) as A FROM P1) T1, P2 " + "where P2.A = T1.A", joinErrorMsg);
    // Special non-push-down-able join case where the join must follow the
    // agg which must follow the send/receive.
    planNodes = compileToFragments("SELECT * FROM (SELECT sum(C) AS SC FROM P1) T1, R1 " + "where R1.A = T1.SC");
    assertEquals(2, planNodes.size());
    //* enable to debug */ System.out.println(planNodes.get(0).toExplainPlanString());
    //* enable to debug */ System.out.println(planNodes.get(1).toExplainPlanString());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "R1");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    assertTrue(pn instanceof AggregatePlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1).getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.AGGREGATE));
}

Example 29 with NestLoopPlanNode

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

the class TestPlansSubQueries method testJoinsSimple.

public void testJoinsSimple() {
    AbstractPlanNode pn;
    AbstractPlanNode nlpn;
    String sql, sqlNoSimplification, equivalentSql;
    sql = "select A, C FROM (SELECT A FROM R1) T1, (SELECT C FROM R2) T2 WHERE T1.A = T2.C";
    sqlNoSimplification = "select A, C FROM (SELECT A FROM R1 LIMIT 10) T1, (SELECT C FROM R2 LIMIT 10) T2 WHERE T1.A = T2.C";
    equivalentSql = "select T1.A, T2.C FROM R1 T1, R2 T2 WHERE T1.A = T2.C";
    pn = compile(sqlNoSimplification);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(2, nlpn.getChildCount());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "A");
    pn = pn.getChild(0);
    checkSeqScan(pn, "R1", "A");
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "T2", "C");
    pn = pn.getChild(0);
    checkSeqScan(pn, "R2", "C");
    checkSubquerySimplification(sql, equivalentSql);
    // sub-selected table joins
    sql = "select A, C FROM (SELECT A FROM R1) T1, (SELECT C FROM R2) T2 WHERE A = C";
    sqlNoSimplification = "select A, C FROM (SELECT A FROM R1 LIMIT 10) T1, (SELECT C FROM R2 LIMIT 10) T2 WHERE A = C";
    equivalentSql = "select T1.A, T2.C FROM R1 T1, R2 T2 WHERE T1.A = T2.C";
    pn = compile(sqlNoSimplification);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(2, nlpn.getChildCount());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "A");
    pn = pn.getChild(0);
    checkSeqScan(pn, "R1", "A");
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "T2", "C");
    pn = pn.getChild(0);
    checkSeqScan(pn, "R2", "C");
    checkSubquerySimplification(sql, equivalentSql);
}

Example 30 with NestLoopPlanNode

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

the class TestPlansJoin method perJoinOpTestTransitiveValueEquivalenceConditions.

private void perJoinOpTestTransitiveValueEquivalenceConditions(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    SeqScanPlanNode seqScan;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    boolean theConstantIsOnTheLeft;
    // R1.A" + joinOp + "R2.A AND R2.A = 1 => R1.A = 1 AND R2.A = 1
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.A" + joinOp + "R2.A AND R2.A = 1 ";
    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();
    theConstantIsOnTheLeft = (predicate != null) && (predicate.getLeft() != null) && (predicate.getLeft().getExpressionType() == ExpressionType.VALUE_CONSTANT);
    if (theConstantIsOnTheLeft) {
        assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_CONSTANT, ExpressionType.VALUE_TUPLE);
    } else {
        assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    }
    assertNull(nlj.getJoinPredicate());
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertNull(seqScan.getPredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    // Same test but now R2 is outer table R1.A " +
    // joinOp + "R2.A AND R2.A = 1 => R1.A = 1 AND R2.A = 1
    query = "SELECT * FROM R2 LEFT JOIN R1 ON R1.A" + joinOp + "R2.A AND R2.A = 1 ";
    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_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    assertNull(nlj.getJoinPredicate());
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertNull(seqScan.getPredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    // R1.A" + joinOp + "R2.A AND R2.C = 1 => R1.A " +
    // joinOp + "R2.A AND R2.C = 1
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.A" + joinOp + "R2.A AND R2.C = 1 ";
    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.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    // R1.A" + joinOp + "R2.A AND ABS(R2.C) = 1 => R1.A " +
    // joinOp + "R2.A AND ABS(R2.C) = 1
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.A" + joinOp + "R2.A AND ABS(R2.C) = 1 ";
    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.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    // R1.A" + joinOp + "R3.A - NLIJ
    query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.A" + joinOp + "R3.A";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, // PlanNodeType.NESTLOOPINDEX,
    null);
    // PlanNodeType.SEQSCAN); weakened for now
    if (joinOp == JoinOp.EQUAL) {
        // weaken test for now
        node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    }
    // R1.A" + joinOp + "R3.A AND R1.A = 4 =>  R3.A = 4 AND R1.A = 4  -- NLJ/IndexScan
    query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.A" + joinOp + "R3.A AND R1.A = 4";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, // weakened for now
    null);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    predicate = nlj.getPreJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    assertNull(nlj.getJoinPredicate());
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertEquals("R1", seqScan.getTargetTableName());
    assertNull(seqScan.getPredicate());
    if (joinOp == JoinOp.EQUAL) {
        // weakened for now
        indexScan = (IndexScanPlanNode) nlj.getChild(1);
        assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
        predicate = indexScan.getEndExpression();
        assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
        assertNull(indexScan.getPredicate());
        assertEquals("R3", indexScan.getTargetTableName());
    }
    // R1.A" + joinOp + "R3.A AND R3.A = 4 =>  R3.A = 4 AND R1.A = 4  -- NLJ/IndexScan
    query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.A" + joinOp + "R3.A AND R3.A = 4";
    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;
    predicate = nlj.getPreJoinPredicate();
    theConstantIsOnTheLeft = (predicate != null) && (predicate.getLeft() != null) && (predicate.getLeft().getExpressionType() == ExpressionType.VALUE_CONSTANT);
    if (theConstantIsOnTheLeft) {
        assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_CONSTANT, ExpressionType.VALUE_TUPLE);
    } else {
        assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    }
    assertNull(nlj.getJoinPredicate());
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertEquals("R1", seqScan.getTargetTableName());
    assertNull(seqScan.getPredicate());
    //        predicate = seqScan.getPredicate();
    //        assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL,
    //                ExpressionType.VALUE_TUPLE,
    //                ExpressionType.VALUE_CONSTANT);
    indexScan = (IndexScanPlanNode) nlj.getChild(1);
    assertEquals("R3", indexScan.getTargetTableName());
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    assertNull(indexScan.getPredicate());
}