public void testOuterOuterJoin() {
    AbstractPlanNode pn =
        compile("select * FROM R1 LEFT JOIN R2 ON R1.A = R2.A LEFT JOIN R3 ON R3.C = R1.C");
    AbstractPlanNode n = pn.getChild(0).getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    NestLoopPlanNode nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);

    pn = compile("select * FROM R1 LEFT JOIN R2 ON R1.A = R2.A RIGHT JOIN R3 ON R3.C = R1.C");
    n = pn.getChild(0).getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(1);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);

    pn = compile("select * FROM R1 RIGHT JOIN R2 ON R1.A = R2.A RIGHT JOIN R3 ON R3.C = R2.C");
    n = pn.getChild(0).getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(1);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);

    pn = compile("select * FROM R1 RIGHT JOIN R2 ON R1.A = R2.A LEFT JOIN R3 ON R3.C = R1.C");
    n = pn.getChild(0).getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    n = nlj.getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());

    pn =
        compile(
            "select * FROM R1 RIGHT JOIN R2 ON R1.A = R2.A LEFT JOIN R3 ON R3.C = R1.C WHERE R1.A > 0");
    n = pn.getChild(0).getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    n = nlj.getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.INNER == nlj.getJoinType());
  }
 public void testMultiTableJoinExpressions() {
   AbstractPlanNode pn =
       compile(
           "select * FROM R1, R2 LEFT JOIN R3 ON R3.A = R2.C OR R3.A = R1.A WHERE R1.C = R2.C");
   AbstractPlanNode n = pn.getChild(0).getChild(0);
   assertTrue(n instanceof NestLoopPlanNode);
   NestLoopPlanNode nlj = (NestLoopPlanNode) n;
   assertTrue(JoinType.LEFT == nlj.getJoinType());
   assertTrue(nlj.getJoinPredicate() != null);
   AbstractExpression p = nlj.getJoinPredicate();
   assertEquals(ExpressionType.CONJUNCTION_OR, p.getExpressionType());
 }
  public void testOuterSimplificationJoin() {
    // NULL_rejection simplification is the first transformation -
    // before the LEFT-to-RIGHT and the WHERE expressions push down

    AbstractPlanNode pn =
        compile("select * FROM R1, R3 RIGHT JOIN R2 ON R1.A = R2.A WHERE R3.C = R1.C");
    AbstractPlanNode n = pn.getChild(0).getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    NestLoopPlanNode nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.INNER == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.INNER == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);

    // The second R3.C = R2.C join condition is NULL-rejecting for the first LEFT join
    pn = compile("select * FROM R1 LEFT JOIN R2 ON R1.A = R2.A LEFT JOIN R3 ON R3.C = R2.C");
    n = pn.getChild(0).getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);

    // The second R3.C = R2.C join condition is NULL-rejecting for the first LEFT join
    pn = compile("select * FROM R1 LEFT JOIN R2 ON R1.A = R2.A RIGHT JOIN R3 ON R3.C = R2.C");
    n = pn.getChild(0).getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(1);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.INNER == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
  }
  public void testPushDownExprJoin() {
    // R3.A > 0 gets pushed down all the way to the R3 scan node and used as an index
    AbstractPlanNode pn =
        compile("select * FROM R3, R2 LEFT JOIN R1 ON R1.C = R2.C WHERE R3.C = R2.C AND R3.A > 0");
    AbstractPlanNode n = pn.getChild(0).getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    NestLoopPlanNode nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.INNER == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(0);
    assertTrue(n instanceof IndexScanPlanNode);

    // R3.A > 0 is now outer join expresion and must stay at the LEF join
    pn =
        compile("select * FROM R3, R2 LEFT JOIN R1 ON R1.C = R2.C  AND R3.A > 0 WHERE R3.C = R2.C");
    n = pn.getChild(0).getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.INNER == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(0);
    assertTrue(n instanceof SeqScanPlanNode);

    pn =
        compile(
            "select * FROM R3 JOIN R2 ON R3.C = R2.C RIGHT JOIN R1 ON R1.C = R2.C  AND R3.A > 0");
    n = pn.getChild(0).getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(1);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.INNER == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(0);
    assertTrue(n instanceof SeqScanPlanNode);

    // R3.A > 0 gets pushed down all the way to the R3 scan node and used as an index
    pn = compile("select * FROM R2, R3 LEFT JOIN R1 ON R1.C = R2.C WHERE R3.C = R2.C AND R3.A > 0");
    n = pn.getChild(0).getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.INNER == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(1);
    assertTrue(n instanceof IndexScanPlanNode);

    // R3.A = R2.C gets pushed down to the R2, R3 join node scan node and used as an index
    pn = compile("select * FROM R2, R3 LEFT JOIN R1 ON R1.C = R2.C WHERE R3.A = R2.C");
    n = pn.getChild(0).getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);
    n = nlj.getChild(0);
    assertTrue(n instanceof NestLoopIndexPlanNode);
    NestLoopIndexPlanNode nlij = (NestLoopIndexPlanNode) n;
    assertTrue(JoinType.INNER == nlij.getJoinType());
  }