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