Examples with ReceivePlanNode org.voltdb.plannodes.ReceivePlanNode used on opensource projects

Example 1 with ReceivePlanNode

use of org.voltdb.plannodes.ReceivePlanNode in project voltdb by VoltDB.

the class InlineOrderByIntoMergeReceive method applyOptimization.

/**
     * For MP queries, the coordinator's OrderBy node can be replaced with
     * a specialized Receive node that merges individual partitions results
     * into a final result set if the partitions result set is sorted
     * in the order matching the ORDER BY order
     *
     * @param orderbyNode - ORDER BY node to optimize
     * @return optimized plan
     */
AbstractPlanNode applyOptimization(OrderByPlanNode orderbyNode) {
    // Find all child RECEIVE nodes. We are not interested in the MERGERECEIVE nodes there
    // because they could only come from subqueries.
    List<AbstractPlanNode> receives = orderbyNode.findAllNodesOfType(PlanNodeType.RECEIVE);
    if (receives.isEmpty()) {
        return orderbyNode;
    }
    assert (receives.size() == 1);
    ReceivePlanNode receive = (ReceivePlanNode) receives.get(0);
    // Make sure that this receive node belongs to the same coordinator fragment that
    // the ORDER BY node does. Alternatively, it could belong to a distributed subquery.
    // Walk up the tree starting at the receive node until we hit either a scan node
    // (distributed subquery) or the original order by node (distributed order by)
    // Collect all nodes that are currently in between ORDER BY and RECEIVE nodes
    // If the optimization is possible, they will be converted to inline nodes of
    // the MERGE RECEIVE node. The expected node types are:
    //      LIMIT, AGGREGATE/PARTIALAGGREGATE/HASHAGGREGATE
    // The HASHAGGREGATE must be convertible to AGGREGATE or PARTIALAGGREGATE for optimization
    // to be applicable.
    // LIMIT can be already inline with ORDER BY node
    AbstractPlanNode limitNode = orderbyNode.getInlinePlanNode(PlanNodeType.LIMIT);
    AbstractPlanNode aggregateNode = null;
    AbstractPlanNode inlineCandidate = receive.getParent(0);
    while (orderbyNode != inlineCandidate) {
        if (inlineCandidate instanceof AbstractScanPlanNode) {
            // it's a subquery
            return orderbyNode;
        }
        PlanNodeType nodeType = inlineCandidate.getPlanNodeType();
        if (nodeType == PlanNodeType.LIMIT && limitNode == null) {
            limitNode = inlineCandidate;
        } else if ((nodeType == PlanNodeType.AGGREGATE || nodeType == PlanNodeType.PARTIALAGGREGATE) && aggregateNode == null) {
            aggregateNode = inlineCandidate;
        } else if (nodeType == PlanNodeType.HASHAGGREGATE && aggregateNode == null) {
            aggregateNode = convertToSerialAggregation(inlineCandidate, orderbyNode);
            if (PlanNodeType.HASHAGGREGATE == aggregateNode.getPlanNodeType()) {
                return orderbyNode;
            }
        } else {
            // Don't know how to handle this node or there is already a node of this type
            return orderbyNode;
        }
        // move up one node
        assert (inlineCandidate.getParentCount() == 1);
        inlineCandidate = inlineCandidate.getParent(0);
    }
    assert (receive.getChildCount() == 1);
    AbstractPlanNode partitionRoot = receive.getChild(0);
    if (!partitionRoot.isOutputOrdered(orderbyNode.getSortExpressions(), orderbyNode.getSortDirections())) {
        // Partition results are not ordered
        return orderbyNode;
    }
    // the new MERGERECIEVE node.. All in-between nodes will be inlined
    assert (orderbyNode.getParentCount() <= 1);
    AbstractPlanNode rootNode = (orderbyNode.getParentCount() == 1) ? orderbyNode.getParent(0) : null;
    MergeReceivePlanNode mergeReceive = new MergeReceivePlanNode();
    assert (receive.getChildCount() == 1);
    mergeReceive.addAndLinkChild(receive.getChild(0));
    receive.removeFromGraph();
    if (rootNode == null) {
        rootNode = mergeReceive;
    } else {
        rootNode.clearChildren();
        rootNode.addAndLinkChild(mergeReceive);
    }
    // Add inline ORDER BY node and remove inline LIMIT node if any
    mergeReceive.addInlinePlanNode(orderbyNode);
    if (limitNode != null) {
        orderbyNode.removeInlinePlanNode(PlanNodeType.LIMIT);
    }
    // Add inline aggregate
    if (aggregateNode != null) {
        if (limitNode != null) {
            // Inline LIMIT with aggregate
            aggregateNode.addInlinePlanNode(limitNode);
        }
        mergeReceive.addInlinePlanNode(aggregateNode);
    }
    // Add LIMIT if it is exist and wasn't inline with aggregate node
    if (limitNode != null && aggregateNode == null) {
        mergeReceive.addInlinePlanNode(limitNode);
    }
    // return the new root
    return rootNode;
}

Example 2 with ReceivePlanNode

use of org.voltdb.plannodes.ReceivePlanNode in project voltdb by VoltDB.

the class InlineOrderByIntoMergeReceive method applyOptimization.

/**
     * Convert ReceivePlanNodes into MergeReceivePlanNodes when the
     * RECEIVE node's nearest parent is a window function.  We won't
     * have any inline limits or aggregates here, so this is somewhat
     * simpler than the order by case.
     *
     * @param plan
     * @return
     */
private AbstractPlanNode applyOptimization(WindowFunctionPlanNode plan) {
    assert (plan.getChildCount() == 1);
    assert (plan.getChild(0) != null);
    AbstractPlanNode child = plan.getChild(0);
    assert (child != null);
    // an order by node.
    if (!(child instanceof OrderByPlanNode)) {
        return plan;
    }
    OrderByPlanNode onode = (OrderByPlanNode) child;
    child = onode.getChild(0);
    // for this optimization to work.
    if (!(child instanceof ReceivePlanNode)) {
        return plan;
    }
    ReceivePlanNode receiveNode = (ReceivePlanNode) child;
    assert (receiveNode.getChildCount() == 1);
    child = receiveNode.getChild(0);
    // The Receive node needs a send node child.
    assert (child instanceof SendPlanNode);
    SendPlanNode sendNode = (SendPlanNode) child;
    child = sendNode.getChild(0);
    // returns the number in the plan node.
    if (!(child instanceof IndexSortablePlanNode)) {
        return plan;
    }
    IndexSortablePlanNode indexed = (IndexSortablePlanNode) child;
    if (indexed.indexUse().getWindowFunctionUsesIndex() != 0) {
        return plan;
    }
    // Remove the Receive node and the Order by node
    // and replace them with a MergeReceive node.  Leave
    // the order by node inline in the MergeReceive node,
    // since we need it to calculate the merge.
    plan.clearChildren();
    receiveNode.removeFromGraph();
    MergeReceivePlanNode mrnode = new MergeReceivePlanNode();
    mrnode.addInlinePlanNode(onode);
    mrnode.addAndLinkChild(sendNode);
    plan.addAndLinkChild(mrnode);
    return plan;
}

Example 3 with ReceivePlanNode

use of org.voltdb.plannodes.ReceivePlanNode in project voltdb by VoltDB.

the class TestPlansSubQueries method testPartitionedCrossLevel.

public void testPartitionedCrossLevel() {
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    AbstractPlanNode nlpn;
    String sql, sqlNoSimplification, equivalentSql;
    sql = "SELECT T1.A, T1.C, P2.D FROM P2, (SELECT A, C FROM P1) T1 " + "where T1.A = P2.A ";
    sqlNoSimplification = "SELECT T1.A, T1.C, P2.D FROM P2, (SELECT DISTINCT A, C FROM P1 ) T1 " + "where T1.A = P2.A ";
    equivalentSql = "SELECT T1.A, T1.C, P2.D FROM P2, P1 T1 WHERE T1.A = P2.A";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopIndexPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopIndexPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "A", "C");
    checkFragmentCount("SELECT P2.A, P2.C FROM P2, (SELECT A, C FROM P1 GROUP BY A) T1 " + "where T1.A = P2.A and P2.A = 1", 1);
    checkFragmentCount("SELECT P2.A, P2.C FROM P2, (SELECT A, C FROM P1) T1 " + "where T1.A = P2.A and P2.A = 1", 1);
    checkFragmentCount("SELECT P2.A, P2.C FROM P2, (SELECT A, C, A + 1 FROM P1) T1 " + "where T1.A = P2.A and T1.A = 1", 1);
    checkFragmentCount("SELECT P2.A, P2.C FROM P2, (SELECT A, C FROM P1) T1 " + "where T1.A = P2.A and T1.A = 1", 1);
    checkFragmentCount("SELECT P2.A, P2.C FROM P2, (SELECT A, C, A + 1 FROM P1 where P1.A = 3) T1 " + "where T1.A = P2.A ", 1);
    checkFragmentCount("SELECT P2.A, P2.C FROM P2, (SELECT A, C FROM P1 where P1.A = 3) T1 " + "where T1.A = P2.A ", 1);
    // Distributed join
    checkFragmentCount("select D1, D2 " + "FROM (SELECT A, D + 1 D1 FROM P1 ) T1, (SELECT A, D + 2 D2 FROM P2 ) T2 " + "WHERE T1.A = T2.A", 2);
    checkFragmentCount("select D1, D2 " + "FROM (SELECT A, D D1 FROM P1 ) T1, (SELECT A, D D2 FROM P2 ) T2 " + "WHERE T1.A = T2.A", 2);
    checkFragmentCount("select D1, P2.D " + "FROM (SELECT A, D + 1 D1 FROM P1 WHERE A=1) T1, P2 " + "WHERE T1.A = P2.A AND P2.A = 1", 1);
    checkFragmentCount("select D1, P2.D " + "FROM (SELECT A, D D1 FROM P1 WHERE A=1) T1, P2 " + "WHERE T1.A = P2.A AND P2.A = 1", 1);
    checkFragmentCount("select T1.A, T1.C, T1.SD FROM " + "(SELECT A, C, SUM(D) as SD FROM P1 WHERE A > 3 GROUP BY A, C) T1, P2 WHERE T1.A = P2.A", 2);
    // (1) Multiple level subqueries (recursive) partition detecting
    checkFragmentCount("select * from p2, " + "(select * from (SELECT A, D + 1 D1 FROM P1) T1) T2 where p2.A = T2.A", 2);
    checkFragmentCount("select * from p2, " + "(select * from (SELECT A, D D1 FROM P1) T1) T2 where p2.A = T2.A", 2);
    checkFragmentCount("select * from p2, " + "(select * from (SELECT A, D + 1 D1 FROM P1 WHERE A=2) T1) T2 " + "where p2.A = T2.A ", 1);
    checkFragmentCount("select * from p2, " + "(select * from (SELECT A, D D1 FROM P1 WHERE A=2) T1) T2 " + "where p2.A = T2.A ", 1);
    checkFragmentCount("select * from p2, " + "(select * from (SELECT P1.A, P1.D FROM P1, P3 where P1.A = P3.A) T1) T2 " + "where p2.A = T2.A", 2);
    checkFragmentCount("select * from p2, " + "(select * from (SELECT P1.A, P1.D FROM P1, P3 where P1.A = P3.A) T1) T2 " + "where p2.A = T2.A and P2.A = 1", 1);
    // (2) Multiple subqueries on the same level partition detecting
    planNodes = compileToFragments("select D1, D2 FROM " + "(SELECT A, D + 1 D1 FROM P1 WHERE A=2) T1, " + "(SELECT A, D + 1 D2 FROM P2 WHERE A=2) T2");
    assertEquals(1, planNodes.size());
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A, D + 1 D1 FROM P1 WHERE A=2) T1, " + "(SELECT A, D + 1 D2 FROM P2) T2 where T2.A = 2", 1);
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A, D D1 FROM P1 WHERE A=2) T1, " + "(SELECT A, D D2 FROM P2) T2 where T2.A = 2", 1);
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A, D + 1 D1 FROM P1) T1, " + "(SELECT A, D + 1 D2 FROM P2 WHERE A=2) T2 where T1.A = 2", 1);
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A, D D1 FROM P1) T1, " + "(SELECT A, D D2 FROM P2 WHERE A=2) T2 where T1.A = 2", 1);
    // partitioned column renaming tests
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A A1, D + 1 D1 FROM P1) T1, " + "(SELECT A, D + 1 D2 FROM P2 WHERE A=2) T2 where T1.A1 = 2", 1);
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A A1, D D1 FROM P1) T1, " + "(SELECT A, D D2 FROM P2 WHERE A=2) T2 where T1.A1 = 2", 1);
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A, D + 1 D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D + 1 D2 FROM P2 ) T2 where T2.A2 = 2", 1);
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A, D D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D D2 FROM P2 ) T2 where T2.A2 = 2", 1);
    checkFragmentCount("select A1, A2, D1, D2 " + "FROM (SELECT A A1, D + 1 D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D + 1 D2 FROM P2) T2 where T2.A2=2", 1);
    checkFragmentCount("select A1, A2, D1, D2 " + "FROM (SELECT A A1, D D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D D2 FROM P2) T2 where T2.A2=2", 1);
    checkFragmentCount("select A1, A2, D1, D2 " + "FROM (SELECT A A1, D + 1 D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D + 1 D2 FROM P2) T2 where T2.A2=2", 1);
    checkFragmentCount("select A1, A2, D1, D2 " + "FROM (SELECT A A1, D D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D D2 FROM P2) T2 where T2.A2=2", 1);
    // Test with LIMIT
    failToCompile("select A1, A2, D1, D2 " + "FROM (SELECT A A1, D D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D D2 FROM P2 ORDER BY D LIMIT 3) T2 where T2.A2=2", joinErrorMsg);
}

Example 4 with ReceivePlanNode

use of org.voltdb.plannodes.ReceivePlanNode in project voltdb by VoltDB.

the class TestWindowedFunctions method validatePartitionedQuery.

private void validatePartitionedQuery(String query, boolean hasStatementOrderBy) {
    List<AbstractPlanNode> nodes = compileToFragments(query);
    assertEquals(2, nodes.size());
    AbstractPlanNode child = nodes.get(0);
    // Validate the coordinator fragment.
    assertTrue(child instanceof SendPlanNode);
    child = child.getChild(0);
    assertTrue(child instanceof ProjectionPlanNode);
    if (hasStatementOrderBy) {
        child = child.getChild(0);
        assertTrue(child instanceof OrderByPlanNode);
    }
    child = child.getChild(0);
    assertTrue(child instanceof WindowFunctionPlanNode);
    child = child.getChild(0);
    assertTrue(child instanceof OrderByPlanNode);
    child = child.getChild(0);
    assertTrue(child instanceof ReceivePlanNode);
    assertEquals(0, child.getChildCount());
    // Get the distributed fragment.
    child = nodes.get(1);
    assertTrue(child instanceof SendPlanNode);
    child = child.getChild(0);
    assertTrue(child instanceof SeqScanPlanNode);
    assertEquals(0, child.getChildCount());
}

Example 5 with ReceivePlanNode

use of org.voltdb.plannodes.ReceivePlanNode in project voltdb by VoltDB.

the class TestPlansSubQueries method testJoins.

public void testJoins() {
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    AbstractPlanNode nlpn;
    String sql, sqlNoSimplification, equivalentSql;
    // Left Outer join
    sql = "SELECT R1.A, R1.C FROM R1 LEFT JOIN (SELECT A, C FROM R2) T1 ON T1.C = R1.C ";
    sqlNoSimplification = "SELECT R1.A, R1.C FROM R1 LEFT JOIN (SELECT A, C FROM R2 LIMIT 10) T1 ON T1.C = R1.C ";
    equivalentSql = "SELECT R1.A, R1.C FROM R1 LEFT JOIN R2 T1 ON T1.C = R1.C ";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.LEFT, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "R1", "A", "C");
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "T1", "C");
    pn = pn.getChild(0);
    checkSeqScan(pn, "R2", "A", "C");
    checkSubquerySimplification(sql, equivalentSql);
    // Join with partitioned tables
    // Join on coordinator: LEFT OUTER JOIN, replicated table on left side
    sql = "SELECT R1.A, R1.C FROM R1 LEFT JOIN (SELECT A, C FROM P1) T1 ON T1.C = R1.C ";
    sqlNoSimplification = "SELECT R1.A, R1.C FROM R1 LEFT JOIN (SELECT DISTINCT A, C FROM P1) T1 ON T1.C = R1.C ";
    equivalentSql = "SELECT R1.A, R1.C FROM R1 LEFT JOIN P1 T1 ON T1.C = R1.C ";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.LEFT, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "R1", "A", "C");
    pn = nlpn.getChild(1);
    assertEquals(PlanNodeType.RECEIVE, pn.getPlanNodeType());
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "C");
    checkSubquerySimplification(sql, equivalentSql);
    // Group by inside of the subquery
    // whether it contains group by or not does not matter, because we check it by whether inner side is partitioned or not
    planNodes = compileToFragments("SELECT R1.A, R1.C FROM R1 LEFT JOIN (SELECT A, count(*) C FROM P1 GROUP BY A) T1 ON T1.C = R1.C ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.LEFT, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "R1", "A", "C");
    pn = nlpn.getChild(1);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "C");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "A", "C");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    // Using index scan for group by only: use serial aggregate instead hash aggregate
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.AGGREGATE));
    // LEFT partition table
    planNodes = compileToFragments("SELECT T1.CC FROM P1 LEFT JOIN (SELECT A, count(*) CC FROM P2 GROUP BY A) T1 ON T1.A = P1.A ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.LEFT, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "T1");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P2");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    // Using index scan for group by only: use serial aggregate instead hash aggregate
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.AGGREGATE));
    // Right outer join
    planNodes = compileToFragments("SELECT R1.A, R1.C FROM R1 RIGHT JOIN (SELECT A, count(*) C FROM P1 GROUP BY A) T1 ON T1.C = R1.C ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.LEFT, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "R1", "A", "C");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "C");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "A", "C");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    // Using index scan for group by only: use serial aggregate instead hash aggregate
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.AGGREGATE));
    // RIGHT partition table
    planNodes = compileToFragments("SELECT T1.CC FROM P1 RIGHT JOIN (SELECT A, count(*) CC FROM P2 GROUP BY A) T1 ON T1.A = P1.A ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopIndexPlanNode);
    assertEquals(JoinType.LEFT, ((NestLoopIndexPlanNode) nlpn).getJoinType());
    pn = nlpn.getInlinePlanNode(PlanNodeType.INDEXSCAN);
    checkPrimaryKeyIndexScan(pn, "P1");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P2");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    // Using index scan for group by only: use serial aggregate instead hash aggregate
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.AGGREGATE));
    // Join locally: inner join case for subselects
    sql = "SELECT R1.A, R1.C FROM R1 INNER JOIN (SELECT A, C FROM P1) T1 ON T1.C = R1.C ";
    sqlNoSimplification = "SELECT R1.A, R1.C FROM R1 INNER JOIN (SELECT DISTINCT A, C FROM P1) T1 ON T1.C = R1.C ";
    equivalentSql = "SELECT R1.A, R1.C FROM R1 INNER JOIN P1 T1 ON T1.C = R1.C ";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "R1", "A", "C");
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "T1", "C");
    checkSubquerySimplification(sql, equivalentSql);
    // Two sub-queries. One is partitioned and the other one is replicated
    sql = "select A, AC FROM (SELECT A FROM R1) T1, (SELECT C AC FROM P1) T2 WHERE T1.A = T2.AC ";
    sqlNoSimplification = "select A, AC FROM (SELECT A FROM R1 LIMIT 10) T1, (SELECT DISTINCT A AC FROM P1) T2 WHERE T1.A = T2.AC ";
    equivalentSql = "select T1.A, T2.C AC FROM R1 T1, P1 T2 WHERE T1.A = T2.C ";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "A");
    pn = pn.getChild(0);
    checkSeqScan(pn, "R1", "A");
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "T2", "AC");
    checkSubquerySimplification(sql, equivalentSql);
    // This is a single fragment plan because planner can detect "A = 3".
    // Join locally
    sql = "select A1, A2 FROM (SELECT A A1 FROM R1) T1, (SELECT A A2 FROM P1 where A = 3) T2 WHERE T1.A1 = T2.A2 ";
    sqlNoSimplification = "select A2, A1 FROM (SELECT DISTINCT A A1 FROM R1) T1, (SELECT DISTINCT A A2 FROM P1 where A = 3) T2 WHERE T1.A1 = T2.A2 ";
    equivalentSql = "select T1.A A1, T2.A A2 FROM R1 T1 join P1 T2 on T2.A = 3 and T1.A = T2.A";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "A1");
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "T2", "A2");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "A");
    assertEquals(2, ((IndexScanPlanNode) pn).getInlinePlanNodes().size());
    assertNotNull(((IndexScanPlanNode) pn).getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(((IndexScanPlanNode) pn).getInlinePlanNode(PlanNodeType.AGGREGATE));
    checkSubquerySimplification(sql, equivalentSql);
    // More single partition detection
    planNodes = compileToFragments("select C FROM (SELECT P1.C FROM P1, P2 " + "WHERE P1.A = P2.A AND P1.A = 3) T1 ");
    assertEquals(1, planNodes.size());
    planNodes = compileToFragments("select T1.C FROM (SELECT P1.C FROM P1, P2 " + "WHERE P1.A = P2.A AND P1.A = 3) T1, R1 where T1.C > R1.C ");
    assertEquals(1, planNodes.size());
    planNodes = compileToFragments("select T1.C FROM (SELECT P1.C FROM P1, P2 " + "WHERE P1.A = P2.A AND P1.A = 3) T1, (select C FROM R1) T2 where T1.C > T2.C ");
    assertEquals(1, planNodes.size());
}