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());
}