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());
}
@Override
protected AbstractPlanNode recursivelyApply(AbstractPlanNode planNode) {
assert (planNode != null);
// breadth first:
// find AggregatePlanNode with exactly one child
// where that child is an AbstractScanPlanNode.
// Inline any qualifying AggregatePlanNode to its AbstractScanPlanNode.
Queue<AbstractPlanNode> children = new LinkedList<AbstractPlanNode>();
children.add(planNode);
while (!children.isEmpty()) {
AbstractPlanNode plan = children.remove();
AbstractPlanNode newPlan = inlineAggregationApply(plan);
if (newPlan != plan) {
if (plan == planNode) {
planNode = newPlan;
} else {
planNode.replaceChild(plan, newPlan);
}
}
for (int i = 0; i < newPlan.getChildCount(); i++) {
children.add(newPlan.getChild(i));
}
}
return planNode;
}
/**
* Check if the aggregate node is pushed-down in the given plan. If the pushDownTypes is null, it
* assumes that the aggregate node should NOT be pushed-down.
*
* @param np The generated plan
* @param isMultiPart Whether or not the plan is distributed
* @param aggTypes The expected aggregate types for the original aggregate node.
* @param pushDownTypes The expected aggregate types for the top aggregate node after pushing the
* original aggregate node down.
*/
private void checkPushedDown(
List<AbstractPlanNode> pn,
boolean isMultiPart,
ExpressionType[] aggTypes,
ExpressionType[] pushDownTypes) {
assertTrue(pn.size() > 0);
AbstractPlanNode p = pn.get(0).getChild(0);
assertTrue(p instanceof AggregatePlanNode);
String fragmentString = p.toJSONString();
ExpressionType[] topTypes = (pushDownTypes != null) ? pushDownTypes : aggTypes;
for (ExpressionType type : topTypes) {
assertTrue(fragmentString.contains("\"AGGREGATE_TYPE\":\"" + type.toString() + "\""));
}
if (isMultiPart) {
assertTrue(pn.size() == 2);
p = pn.get(1).getChild(0);
} else {
p = p.getChild(0);
}
if (pushDownTypes == null) {
assertTrue(p instanceof AbstractScanPlanNode);
return;
}
assertTrue(p instanceof AggregatePlanNode);
fragmentString = p.toJSONString();
for (ExpressionType type : aggTypes) {
assertTrue(fragmentString.contains("\"AGGREGATE_TYPE\":\"" + type.toString() + "\""));
}
}
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 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());
}
private int resetPlanNodeIds(AbstractPlanNode node, int nextId) {
nextId = node.overrideId(nextId);
for (AbstractPlanNode inNode : node.getInlinePlanNodes().values()) {
// Inline nodes also need their ids to be overridden to make sure
// the subquery node ids are also globaly unique
nextId = resetPlanNodeIds(inNode, nextId);
}
for (int i = 0; i < node.getChildCount(); i++) {
AbstractPlanNode child = node.getChild(i);
assert (child != null);
nextId = resetPlanNodeIds(child, nextId);
}
return nextId;
}
/**
* Given a list of Class objects for plan node subclasses, asserts if the given plan doesn't
* contain instances of those classes.
*/
protected static void assertClassesMatchNodeChain(
List<Class<? extends AbstractPlanNode>> expectedClasses, AbstractPlanNode actualPlan) {
AbstractPlanNode pn = actualPlan;
for (Class<? extends AbstractPlanNode> c : expectedClasses) {
assertFalse("Actual plan shorter than expected", pn == null);
assertTrue(
"Expected plan to contain an instance of "
+ c.getSimpleName()
+ ", "
+ "instead found "
+ pn.getClass().getSimpleName(),
c.isInstance(pn));
if (pn.getChildCount() > 0) pn = pn.getChild(0);
else pn = null;
}
assertTrue("Actual plan longer than expected", pn == null);
}
@Override
protected AbstractPlanNode recursivelyApply(AbstractPlanNode plan) {
assert (plan != null);
// depth first:
// find AggregatePlanNode with exactly one child
// where that child is an AbstractScanPlanNode.
// Replace any qualifying AggregatePlanNode / AbstractScanPlanNode pair
// with an IndexCountPlanNode or TableCountPlanNode
ArrayList<AbstractPlanNode> children = new ArrayList<AbstractPlanNode>();
for (int i = 0; i < plan.getChildCount(); i++) children.add(plan.getChild(i));
for (AbstractPlanNode child : children) {
// TODO this will break when children feed multiple parents
AbstractPlanNode newChild = recursivelyApply(child);
// Do a graft into the (parent) plan only if a replacement for a child was found.
if (newChild == child) {
continue;
}
boolean replaced = plan.replaceChild(child, newChild);
assert (true == replaced);
}
// check for an aggregation of the right form
if ((plan instanceof AggregatePlanNode) == false) return plan;
assert (plan.getChildCount() == 1);
AggregatePlanNode aggplan = (AggregatePlanNode) plan;
// ENG-6131 fixed here.
if (!(aggplan.isTableCountStar()
|| aggplan.isTableNonDistinctCountConstant()
|| aggplan.isTableCountNonDistinctNullableColumn())) {
return plan;
}
AbstractPlanNode child = plan.getChild(0);
// A table count can replace a seq scan only if it has no predicates.
if (child instanceof SeqScanPlanNode) {
if (((SeqScanPlanNode) child).getPredicate() != null) {
return plan;
}
AbstractExpression postPredicate = aggplan.getPostPredicate();
if (postPredicate != null) {
List<AbstractExpression> aggList =
postPredicate.findAllSubexpressionsOfClass(AggregateExpression.class);
boolean allCountStar = true;
for (AbstractExpression expr : aggList) {
if (expr.getExpressionType() != ExpressionType.AGGREGATE_COUNT_STAR) {
allCountStar = false;
break;
}
}
if (allCountStar) {
return plan;
}
}
if (hasInlineLimit(aggplan)) {
// table count EE executor does not handle inline limit stuff
return plan;
}
return new TableCountPlanNode((AbstractScanPlanNode) child, aggplan);
}
// Otherwise, optimized counts only replace particular cases of index scan.
if ((child instanceof IndexScanPlanNode) == false) return plan;
IndexScanPlanNode isp = (IndexScanPlanNode) child;
// Guard against (possible future?) cases of indexable subquery.
if (((IndexScanPlanNode) child).isSubQuery()) {
return plan;
}
// An index count or table count can replace an index scan only if it has no (post-)predicates
// except those (post-)predicates are artifact predicates we added for reverse scan purpose only
if (isp.getPredicate() != null && !isp.isPredicatesOptimizableForAggregate()) {
return plan;
}
// With no start or end keys, there's not much a counting index can do.
if (isp.getEndExpression() == null && isp.getSearchKeyExpressions().size() == 0) {
// An indexed query without a where clause can fall back to a plain old table count.
// This can only happen when a confused query like
// "select count(*) from table order by index_key;"
// meets a naive planner that doesn't just cull the no-op ORDER BY. Who, us?
if (hasInlineLimit(aggplan)) {
return plan;
}
return new TableCountPlanNode(isp, aggplan);
}
// check for the index's support for counting
Index idx = isp.getCatalogIndex();
if (!idx.getCountable()) {
return plan;
}
// The core idea is that counting index needs to know the start key and end key to
// jump to to get counts instead of actually doing any scanning.
// Options to be determined are:
// - whether each of the start/end keys is missing, partial (a prefix of a compund key), or
// complete,
// - whether the count should include or exclude entries exactly matching each of the start/end
// keys.
// Not all combinations of these options are supported;
// unsupportable cases cause the factory method to return null.
IndexCountPlanNode countingPlan = IndexCountPlanNode.createOrNull(isp, aggplan);
if (countingPlan == null) {
return plan;
}
return countingPlan;
}
public void testMultitableDistributedJoin() {
// One distributed table
List<AbstractPlanNode> lpn =
compileToFragments("select * FROM R3,R1 LEFT JOIN P2 ON R3.A = P2.A WHERE R3.A=R1.A ");
assertTrue(lpn.size() == 2);
AbstractPlanNode n = lpn.get(0).getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
assertTrue(JoinType.LEFT == ((NestLoopPlanNode) n).getJoinType());
AbstractPlanNode c = n.getChild(0);
assertTrue(c instanceof NestLoopIndexPlanNode);
// R3.A and P2.A have an index. P2,R1 is NLIJ/inlined IndexScan because it's an inner join even
// P2 is distributed
lpn = compileToFragments("select * FROM P2,R1 LEFT JOIN R3 ON R3.A = P2.A WHERE P2.A=R1.A ");
assertTrue(lpn.size() == 2);
n = lpn.get(0).getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
n = lpn.get(1).getChild(0);
assertTrue(n instanceof NestLoopIndexPlanNode);
assertTrue(JoinType.LEFT == ((NestLoopIndexPlanNode) n).getJoinType());
c = n.getChild(0);
assertTrue(c instanceof NestLoopIndexPlanNode);
// R3.A has an index. R3,P2 is NLJ because it's an outer join and P2 is distributed
lpn = compileToFragments("select * FROM R3,R1 LEFT JOIN P2 ON R3.A = P2.A WHERE R3.A=R1.A ");
assertTrue(lpn.size() == 2);
// to debug */ System.out.println("DEBUG 0.0: " + lpn.get(0).toExplainPlanString());
// to debug */ System.out.println("DEBUG 0.1: " + lpn.get(1).toExplainPlanString());
n = lpn.get(0).getChild(0).getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
assertTrue(JoinType.LEFT == ((NestLoopPlanNode) n).getJoinType());
c = n.getChild(0);
assertTrue(c instanceof NestLoopIndexPlanNode);
assertTrue(JoinType.INNER == ((NestLoopIndexPlanNode) c).getJoinType());
c = n.getChild(1);
assertTrue(c instanceof ReceivePlanNode);
n = lpn.get(1).getChild(0);
// For determinism reason
assertTrue(n instanceof IndexScanPlanNode);
// R3.A has an index. P2,R1 is NLJ because P2 is distributed and it's an outer join
lpn = compileToFragments("select * FROM R1 LEFT JOIN P2 ON R1.A = P2.A, R3 WHERE R1.A=R3.A ");
assertTrue(lpn.size() == 2);
// to debug */ System.out.println("DEBUG 1.0: " + lpn.get(0).toExplainPlanString());
// to debug */ System.out.println("DEBUG 1.1: " + lpn.get(1).toExplainPlanString());
n = lpn.get(0).getChild(0).getChild(0);
assertTrue(n instanceof NestLoopIndexPlanNode);
assertTrue(JoinType.INNER == ((NestLoopIndexPlanNode) n).getJoinType());
n = n.getChild(0);
assertTrue(n instanceof NestLoopPlanNode);
c = n.getChild(0);
assertTrue(c instanceof SeqScanPlanNode);
c = n.getChild(1);
assertTrue(c instanceof ReceivePlanNode);
n = lpn.get(1).getChild(0);
// For determinism reason
assertTrue(n instanceof IndexScanPlanNode);
// Two distributed table
lpn = compileToFragments("select * FROM R3,P1 LEFT JOIN P2 ON R3.A = P2.A WHERE R3.A=P1.A ");
assertTrue(lpn.size() == 2);
n = lpn.get(0).getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
n = lpn.get(1).getChild(0);
assertTrue(JoinType.LEFT == ((NestLoopIndexPlanNode) n).getJoinType());
c = n.getChild(0);
assertTrue(c instanceof NestLoopIndexPlanNode);
assertTrue(JoinType.INNER == ((NestLoopIndexPlanNode) c).getJoinType());
}
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());
}
public void testBasicUpdateAndDelete() {
// select * with ON clause should return all columns from all tables
AbstractPlanNode n;
AbstractPlanNode pn;
pns = compileToFragments("UPDATE R1 SET C = 1 WHERE C = 0");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
pn = pns.get(1);
n = pn.getChild(0);
assertTrue(n instanceof UpdatePlanNode);
pns = compileToFragments("DELETE FROM R1 WHERE C = 0");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
pn = pns.get(1);
n = pn.getChild(0);
assertTrue(n instanceof DeletePlanNode);
pns = compileToFragments("INSERT INTO R1 VALUES (1, 2, 3)");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
pn = pns.get(1);
n = pn.getChild(0);
assertTrue(n instanceof InsertPlanNode);
pns = compileToFragments("UPDATE P1 SET C = 1 WHERE C = 0");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
pn = pns.get(1);
n = pn.getChild(0);
assertTrue(n instanceof UpdatePlanNode);
pns = compileToFragments("DELETE FROM P1 WHERE C = 0");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
n = pn.getChild(0).getChild(0);
assertTrue(n instanceof ReceivePlanNode);
pn = pns.get(1);
n = pn.getChild(0);
assertTrue(n instanceof DeletePlanNode);
pns = compileToFragments("UPDATE P1 SET C = 1 WHERE A = 0");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
// n = pn.getChild(0);
assertTrue(pn instanceof UpdatePlanNode);
pns = compileToFragments("DELETE FROM P1 WHERE A = 0");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
// n = pn.getChild(0);
assertTrue(pn instanceof DeletePlanNode);
pns = compileToFragments("INSERT INTO P1 VALUES (1, 2)");
pn = pns.get(0);
System.out.println(pn.toExplainPlanString());
// n = pn.getChild(0).getChild(0);
assertTrue(pn instanceof InsertPlanNode);
}