public void testInnerOuterJoin() {
    AbstractPlanNode pn =
        compile("select * FROM R1 INNER JOIN R2 ON R1.A = R2.A LEFT JOIN R3 ON R3.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);
    n = nlj.getChild(0);
    assertTrue(n instanceof NestLoopPlanNode);
    nlj = (NestLoopPlanNode) n;
    assertTrue(JoinType.INNER == nlj.getJoinType());
    assertTrue(nlj.getJoinPredicate() != null);

    pn = compile("select * FROM R1, R2 LEFT JOIN R3 ON R3.C = R2.C WHERE R1.A = R2.A");
    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);
  }
Esempio n. 2
0
 protected String buildExplainPlan(List<AbstractPlanNode> planNodes) {
   String explain = "";
   for (AbstractPlanNode apn : planNodes) {
     explain += apn.toExplainPlanString() + '\n';
   }
   return explain;
 }
Esempio n. 3
0
  /// Extract a sorted de-duped vector of all the bound parameter indexes in a plan. Or null if
  // none.
  public int[] boundParamIndexes() {
    if (parameters.length == 0) {
      return null;
    }

    BitSet ints = new BitSet();
    ArrayList<AbstractPlanNode> ixscans = rootPlanGraph.findAllNodesOfType(PlanNodeType.INDEXSCAN);
    if (subPlanGraph != null) {
      ixscans.addAll(subPlanGraph.findAllNodesOfType(PlanNodeType.INDEXSCAN));
    }
    for (AbstractPlanNode apn : ixscans) {
      assert (apn instanceof IndexScanPlanNode);
      IndexScanPlanNode ixs = (IndexScanPlanNode) apn;
      setParamIndexes(ints, ixs.getBindings());
    }

    ArrayList<AbstractPlanNode> ixcounts =
        rootPlanGraph.findAllNodesOfType(PlanNodeType.INDEXCOUNT);
    if (subPlanGraph != null) {
      ixcounts.addAll(subPlanGraph.findAllNodesOfType(PlanNodeType.INDEXCOUNT));
    }
    for (AbstractPlanNode apn : ixcounts) {
      assert (apn instanceof IndexCountPlanNode);
      IndexCountPlanNode ixc = (IndexCountPlanNode) apn;
      setParamIndexes(ints, ixc.getBindings());
    }
    return bitSetToIntVector(ints);
  }
Esempio n. 4
0
  @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;
  }
Esempio n. 5
0
  /**
   * 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 testEng585Plan() {
   AbstractPlanNode pn = null;
   pn =
       compile(
           "select max(s.int2) as foo from s, t where s.s_pk = t.s_pk and t.t_pk1 = ?;", 1, true);
   if (pn != null) {
     System.out.println(pn.toJSONString());
   }
 }
Esempio n. 7
0
  public void testMultiPartLimitPushdownByOne() {
    List<AbstractPlanNode> pn =
        compileToFragments("select A1, count(*) as tag from T1 group by A1 order by 1 limit 1");

    for (AbstractPlanNode nd : pn) {
      System.out.println("PlanNode Explain string:\n" + nd.toExplainPlanString());
      assertTrue(nd.toExplainPlanString().contains("LIMIT"));
    }
  }
Esempio n. 8
0
  /**
   * Find all the aggregate nodes in a fragment, whether they are hash, serial or partial.
   *
   * @param fragment Fragment to search for aggregate plan nodes
   * @return a list of all the nodes we found
   */
  protected static List<AbstractPlanNode> findAllAggPlanNodes(AbstractPlanNode fragment) {
    List<AbstractPlanNode> aggNodes = fragment.findAllNodesOfType(PlanNodeType.AGGREGATE);
    List<AbstractPlanNode> hashAggNodes = fragment.findAllNodesOfType(PlanNodeType.HASHAGGREGATE);
    List<AbstractPlanNode> partialAggNodes =
        fragment.findAllNodesOfType(PlanNodeType.PARTIALAGGREGATE);

    aggNodes.addAll(hashAggNodes);
    aggNodes.addAll(partialAggNodes);
    return aggNodes;
  }
 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());
 }
Esempio n. 10
0
  /**
   * Check if the distinct node is pushed-down in the given plan.
   *
   * @param np The generated plan
   * @param isMultiPart Whether or not the plan is distributed
   */
  private void checkPushedDownDistinct(List<AbstractPlanNode> pn, boolean isMultiPart) {
    assertTrue(pn.size() > 0);

    AbstractPlanNode p = pn.get(0).getChild(0).getChild(0);
    assertTrue(p instanceof DistinctPlanNode);
    assertTrue(p.toJSONString().contains("\"DISTINCT\""));

    if (isMultiPart) {
      assertTrue(pn.size() == 2);
      p = pn.get(1).getChild(0);
      assertTrue(p instanceof DistinctPlanNode);
      assertTrue(p.toJSONString().contains("\"DISTINCT\""));
    }
  }
Esempio n. 11
0
  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;
  }
Esempio n. 12
0
 @Override
 public String toString() {
   if (rootPlanGraph != null) {
     return "CompiledPlan: \n" + rootPlanGraph.toExplainPlanString();
   } else {
     return "CompiledPlan: [null plan graph]";
   }
 }
Esempio n. 13
0
  AbstractPlanNode inlineAggregationApply(AbstractPlanNode plan) {
    // check for an aggregation of the right form
    if ((plan instanceof AggregatePlanNode) == false || (plan instanceof WindowFunctionPlanNode)) {
      return plan;
    }
    assert (plan.getChildCount() == 1);
    AggregatePlanNode aggplan = (AggregatePlanNode) plan;

    // Assuming all AggregatePlanNode has not been inlined before this microoptimization
    AbstractPlanNode child = aggplan.getChild(0);

    // EE Currently support: seqscan + indexscan
    if (child.getPlanNodeType() != PlanNodeType.SEQSCAN
        && child.getPlanNodeType() != PlanNodeType.INDEXSCAN
        && child.getPlanNodeType() != PlanNodeType.NESTLOOP
        && child.getPlanNodeType() != PlanNodeType.NESTLOOPINDEX) {
      return plan;
    }

    if (child.getPlanNodeType() == PlanNodeType.INDEXSCAN) {
      // Currently do not conflict with the optimized MIN/MAX
      // because of the big amount of tests changed.

      IndexScanPlanNode isp = (IndexScanPlanNode) child;
      LimitPlanNode limit = (LimitPlanNode) isp.getInlinePlanNode(PlanNodeType.LIMIT);
      if (limit != null && (aggplan.isTableMin() || aggplan.isTableMax())) {
        // Optimized MIN/MAX
        if (limit.getLimit() == 1 && limit.getOffset() == 0) {
          return plan;
        }
      }
    }

    // Inline aggregate node
    AbstractPlanNode parent = null;
    if (aggplan.getParentCount() == 1) {
      parent = aggplan.getParent(0);
    }
    child.addInlinePlanNode(aggplan);
    child.clearParents();
    if (parent != null) {
      parent.replaceChild(aggplan, child);
    }
    return child;
  }
Esempio n. 14
0
  /**
   * 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);
  }
Esempio n. 15
0
 public int countSeqScans() {
   int total = rootPlanGraph.findAllNodesOfType(PlanNodeType.SEQSCAN).size();
   if (subPlanGraph != null) {
     total += subPlanGraph.findAllNodesOfType(PlanNodeType.SEQSCAN).size();
   }
   // add full index scans
   ArrayList<AbstractPlanNode> indexScanNodes =
       rootPlanGraph.findAllNodesOfType(PlanNodeType.INDEXSCAN);
   if (subPlanGraph != null) {
     indexScanNodes.addAll(subPlanGraph.findAllNodesOfType(PlanNodeType.INDEXSCAN));
   }
   for (AbstractPlanNode node : indexScanNodes) {
     if (((IndexScanPlanNode) node).getSearchKeyExpressions().isEmpty()) {
       total++;
     }
   }
   return total;
 }
  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);
  }
Esempio n. 17
0
  public static AbstractPlanNode replaceInsertPlanNodeWithUpsert(AbstractPlanNode root) {
    if (root == null) {
      return null;
    }

    List<AbstractPlanNode> inserts = root.findAllNodesOfType(PlanNodeType.INSERT);
    if (inserts.size() == 1) {
      InsertPlanNode insertNode = (InsertPlanNode) inserts.get(0);
      insertNode.setUpsert(true);
    }

    return root;
  }
Esempio n. 18
0
  public static boolean hasInlineLimit(AbstractPlanNode node) {
    AbstractPlanNode inlineNode = node.getInlinePlanNode(PlanNodeType.LIMIT);
    if (inlineNode != null) {
      assert (inlineNode instanceof LimitPlanNode);

      // Table count with limit greater than 0 will not make a difference.
      // The better way is to check m_limit and return true ONLY for m_limit == 0.
      // However, the parameterized plan make is more complicated.
      // Be conservative about the silly query without wrong answers now.
      return true;
    }
    return false;
  }
Esempio n. 19
0
  /**
   * Parse a SQL literal statement into an unplanned, intermediate representation. This is normally
   * followed by a call to {@link this#plan(AbstractCostModel, String, String, String, String, int,
   * ScalarValueHints[]) }, but splitting these two affords an opportunity to check a cache for a
   * plan matching the auto-parameterized parsed statement.
   */
  public void parse() throws PlanningErrorException {
    // reset any error message
    m_recentErrorMsg = null;

    // Reset plan node ids to start at 1 for this plan
    AbstractPlanNode.resetPlanNodeIds();

    // determine the type of the query
    //
    // (Hmmm...  seems like this pre-processing of the SQL text
    // and subsequent placement of UPSERT_TAG should be pushed down
    // into getXMLCompiledStatement)
    m_sql = m_sql.trim();
    if (m_sql.length() > 6
        && m_sql.substring(0, 6).toUpperCase().startsWith("UPSERT")) { // ENG-7395
      m_isUpsert = true;
      m_sql = "INSERT" + m_sql.substring(6);
    }

    // use HSQLDB to get XML that describes the semantics of the statement
    // this is much easier to parse than SQL and is checked against the catalog
    try {
      m_xmlSQL = m_HSQL.getXMLCompiledStatement(m_sql);
    } catch (HSQLParseException e) {
      // XXXLOG probably want a real log message here
      throw new PlanningErrorException(e.getMessage());
    }

    if (m_isUpsert) {
      assert (m_xmlSQL.name.equalsIgnoreCase("INSERT"));
      // for AdHoc cache distinguish purpose which is based on the XML
      m_xmlSQL.attributes.put(UPSERT_TAG, "true");
    }

    m_planSelector.outputCompiledStatement(m_xmlSQL);
  }
Esempio n. 20
0
  @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;
  }
Esempio n. 21
0
 protected void printExplainPlan(List<AbstractPlanNode> planNodes) {
   for (AbstractPlanNode apn : planNodes) {
     System.out.println(apn.toExplainPlanString());
   }
 }
Esempio n. 22
0
  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);
  }
  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 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());
  }
Esempio n. 25
0
 /**
  * Accessor for description of plan non-determinism. Note that we prefer the content determinism
  * message to the rootPlanGraph's message.
  *
  * @return the corresponding value from the first fragment
  */
 public String nondeterminismDetail() {
   if (!isContentDeterministic()) {
     return m_contentDeterminismDetail;
   }
   return rootPlanGraph.nondeterminismDetail();
 }
Esempio n. 26
0
 /**
  * Accessor for flag marking the plan as guaranteeing an identical result/effect when "replayed"
  * against the same database state, such as during replication or CL recovery.
  *
  * @return the corresponding value from the first fragment
  */
 public boolean isOrderDeterministic() {
   if (m_statementIsOrderDeterministic) {
     return true;
   }
   return rootPlanGraph.isOrderDeterministic();
 }