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

Example 16 with ReceivePlanNode

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

the class TestPlansSubQueries method testPartitionedSameLevel.

public void testPartitionedSameLevel() {
    // force it to be single partitioned.
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    String sql, sqlNoSimplification, equivalentSql;
    //
    // Single partition detection : single table
    //
    sql = "select A FROM (SELECT A FROM P1 WHERE A = 3) T1 ";
    sqlNoSimplification = "select A FROM (SELECT A FROM P1 WHERE A = 3 LIMIT 1) T1 ";
    equivalentSql = "SELECT A FROM P1 T1 WHERE A = 3";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "A");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "A");
    assertNotNull(((IndexScanPlanNode) pn).getInlinePlanNode(PlanNodeType.PROJECTION));
    checkSubquerySimplification(sql, equivalentSql);
    sql = "select A, C FROM (SELECT A, C FROM P1 WHERE A = 3) T1 ";
    sqlNoSimplification = "select A, C FROM (SELECT A, C FROM P1 WHERE A = 3 LIMIT 1) T1 ";
    equivalentSql = "SELECT A, C FROM P1 T1 WHERE A = 3";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "A", "C");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "A", "C");
    assertNotNull(((IndexScanPlanNode) pn).getInlinePlanNode(PlanNodeType.PROJECTION));
    checkSubquerySimplification(sql, equivalentSql);
    // Single partition query without selecting partition column from sub-query
    planNodes = compileToFragments("select C FROM (SELECT A, C FROM P1 WHERE A = 3 LIMIT 1) T1 ");
    assertEquals(1, planNodes.size());
    planNodes = compileToFragments("select C FROM (SELECT C FROM P1 WHERE A = 3 LIMIT 1) T1 ");
    assertEquals(1, planNodes.size());
    //
    // AdHoc multiple partitioned sub-select queries.
    //
    sql = "select A1, C FROM (SELECT A A1, C FROM P1) T1  ";
    sqlNoSimplification = "select A1, C FROM (SELECT DISTINCT A A1, C FROM P1) T1 ";
    equivalentSql = "SELECT A A1, C FROM P1 T1";
    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).getChild(0);
    checkSeqScan(pn, "T1", "A1", "C");
    checkSubquerySimplification(sql, equivalentSql);
    sql = "select A1 FROM (SELECT A A1, C FROM P1 WHERE A > 3) T1 ";
    sqlNoSimplification = "select A1 FROM (SELECT A A1, C FROM P1 WHERE A > 3 LIMIT 10) T1 ";
    equivalentSql = "SELECT A A1 FROM P1 T1 WHERE A > 3";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    checkSeqScan(pn, "T1", "A1");
    checkSubquerySimplification(sql, equivalentSql);
    //
    // Group by
    //
    planNodes = compileToFragments("select C, SD FROM " + "(SELECT C, SUM(D) as SD FROM P1 GROUP BY C) T1 ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    checkSeqScan(pn, "T1", "C", "SD");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    assertTrue(pn instanceof HashAggregatePlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "C", "SD");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    // rename group by column
    planNodes = compileToFragments("select X, SD FROM " + "(SELECT C AS X, SUM(D) as SD FROM P1 GROUP BY C) T1 ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    checkSeqScan(pn, "T1", "X", "SD");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    assertTrue(pn instanceof HashAggregatePlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "C", "SD");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    AbstractPlanNode nlpn;
    //
    // Partitioned Joined tests
    //
    failToCompile("select * FROM " + "(SELECT C, SUM(D) as SD FROM P1 GROUP BY C) T1, P2 where T1.C = P2.A ", joinErrorMsg);
    planNodes = compileToFragments("select T1.C, T1.SD FROM " + "(SELECT C, SUM(D) as SD FROM P1 GROUP BY C) T1, R1 Where 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);
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "R1");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "C", "SD");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    assertTrue(pn instanceof HashAggregatePlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "C", "SD");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    // Group by Partitioned column
    planNodes = compileToFragments("select C, SD FROM " + "(SELECT A, C, SUM(D) as SD FROM P1 WHERE A > 3 GROUP BY A, C) T1 ");
    assertEquals(2, planNodes.size());
    planNodes = compileToFragments("select C, SD FROM " + "(SELECT A, C, SUM(D) as SD FROM P1 WHERE A = 3 GROUP BY A, C) T1 ");
    assertEquals(1, planNodes.size());
    planNodes = compileToFragments("select T1.C, T1.SD FROM " + "(SELECT A, C, SUM(D) as SD FROM P1 WHERE A = 3 GROUP BY A, C) T1, R1 WHERE T1.C = R1.C ");
    assertEquals(1, planNodes.size());
    //
    // Limit
    //
    planNodes = compileToFragments("select C FROM (SELECT C FROM P1 WHERE A > 3 ORDER BY C LIMIT 5) T1 ");
    assertEquals(2, planNodes.size());
    planNodes = compileToFragments("select T1.C FROM (SELECT C FROM P1 WHERE A > 3 ORDER BY C LIMIT 5) T1, " + "R1 WHERE T1.C > R1.C ");
    assertEquals(2, planNodes.size());
    planNodes = compileToFragments("select C FROM (SELECT A, C FROM P1 WHERE A = 3 ORDER BY C LIMIT 5) T1 ");
    assertEquals(1, planNodes.size());
    // Without selecting partition column from sub-query
    planNodes = compileToFragments(("select C FROM (SELECT C FROM P1 WHERE A = 3 ORDER BY C LIMIT 5) T1 "));
    assertEquals(1, planNodes.size());
    planNodes = compileToFragments("select T1.C FROM (SELECT A, C FROM P1 WHERE A = 3 ORDER BY C LIMIT 5) T1, " + "R1 WHERE T1.C > R1.C ");
    assertEquals(1, planNodes.size());
    // Without selecting partition column from sub-query
    planNodes = compileToFragments("select T1.C FROM (SELECT C FROM P1 WHERE A = 3 ORDER BY C LIMIT 5) T1, " + "R1 WHERE T1.C > R1.C ");
    assertEquals(1, planNodes.size());
    //
    // Group by & LIMIT 5
    //
    planNodes = compileToFragments("select C, SD FROM " + "(SELECT C, SUM(D) as SD FROM P1 GROUP BY C ORDER BY C LIMIT 5) T1 ");
    assertEquals(2, planNodes.size());
    // Without selecting partition column from sub-query
    planNodes = compileToFragments("select C, SD FROM " + "(SELECT C, SUM(D) as SD FROM P1 WHERE A = 3 GROUP BY C ORDER BY C LIMIT 5) T1 ");
    assertEquals(1, planNodes.size());
}

Example 17 with ReceivePlanNode

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

the class PlanAssembler method pushDownAggregate.

/**
     * Push the given aggregate if the plan is distributed, then add the
     * coordinator node on top of the send/receive pair. If the plan
     * is not distributed, or coordNode is not provided, the distNode
     * is added at the top of the plan.
     *
     * Note: this works in part because the push-down node is also an acceptable
     * top level node if the plan is not distributed. This wouldn't be true
     * if we started pushing down something like (sum, count) to calculate
     * a distributed average.  (We already do something like this for
     * APPROX_COUNT_DISTINCT, which must be split into two different functions
     * for the pushed-down case.)
     *
     * @param root
     *            The root node
     * @param distNode
     *            The node to push down
     * @param coordNode [may be null]
     *            The top node to put on top of the send/receive pair after
     *            push-down. If this is null, no push-down will be performed.
     * @return The new root node.
     */
private static AbstractPlanNode pushDownAggregate(AbstractPlanNode root, AggregatePlanNode distNode, AggregatePlanNode coordNode, ParsedSelectStmt selectStmt) {
    AggregatePlanNode rootAggNode;
    // to be pushed down past the receive as well.
    if (coordNode != null) {
        coordNode.m_isCoordinatingAggregator = true;
    }
    /*
         * Push this node down to partition if it's distributed. First remove
         * the send/receive pair, add the node, then put the send/receive pair
         * back on top of the node, followed by another top node at the
         * coordinator.
         */
    if (coordNode != null && root instanceof ReceivePlanNode) {
        AbstractPlanNode accessPlanTemp = root;
        root = accessPlanTemp.getChild(0).getChild(0);
        root.clearParents();
        accessPlanTemp.getChild(0).clearChildren();
        distNode.addAndLinkChild(root);
        if (selectStmt.hasPartitionColumnInGroupby()) {
            // Set post predicate for final distributed Aggregation node
            distNode.setPostPredicate(selectStmt.getHavingPredicate());
            // a very edge ORDER BY case.
            if (selectStmt.isComplexOrderBy()) {
                // Put the send/receive pair back into place
                accessPlanTemp.getChild(0).addAndLinkChild(distNode);
                root = processComplexAggProjectionNode(selectStmt, accessPlanTemp);
                return root;
            }
            root = processComplexAggProjectionNode(selectStmt, distNode);
            // Put the send/receive pair back into place
            accessPlanTemp.getChild(0).addAndLinkChild(root);
            return accessPlanTemp;
        }
        // Without including partition column in GROUP BY clause,
        // there has to be a top GROUP BY plan node on coordinator.
        //
        // Now that we're certain the aggregate will be pushed down
        // (no turning back now!), fix any APPROX_COUNT_DISTINCT aggregates.
        fixDistributedApproxCountDistinct(distNode, coordNode);
        // Put the send/receive pair back into place
        accessPlanTemp.getChild(0).addAndLinkChild(distNode);
        // Add the top node
        coordNode.addAndLinkChild(accessPlanTemp);
        rootAggNode = coordNode;
    } else {
        distNode.addAndLinkChild(root);
        rootAggNode = distNode;
    }
    // Set post predicate for final Aggregation node.
    rootAggNode.setPostPredicate(selectStmt.getHavingPredicate());
    root = processComplexAggProjectionNode(selectStmt, rootAggNode);
    return root;
}

Example 18 with ReceivePlanNode

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

the class PlanAssembler method handleMVBasedMultiPartQuery.

private AbstractPlanNode handleMVBasedMultiPartQuery(HashAggregatePlanNode reAggNode, AbstractPlanNode root, boolean edgeCaseOuterJoin) {
    MaterializedViewFixInfo mvFixInfo = m_parsedSelect.m_mvFixInfo;
    AbstractPlanNode receiveNode = root;
    AbstractPlanNode reAggParent = null;
    // re-aggregation plan node.
    if (root instanceof AbstractReceivePlanNode) {
        root = reAggNode;
    } else {
        List<AbstractPlanNode> recList = root.findAllNodesOfClass(AbstractReceivePlanNode.class);
        assert (recList.size() == 1);
        receiveNode = recList.get(0);
        reAggParent = receiveNode.getParent(0);
        boolean result = reAggParent.replaceChild(receiveNode, reAggNode);
        assert (result);
    }
    reAggNode.addAndLinkChild(receiveNode);
    reAggNode.m_isCoordinatingAggregator = true;
    assert (receiveNode instanceof ReceivePlanNode);
    AbstractPlanNode sendNode = receiveNode.getChild(0);
    assert (sendNode instanceof SendPlanNode);
    AbstractPlanNode sendNodeChild = sendNode.getChild(0);
    HashAggregatePlanNode reAggNodeForReplace = null;
    if (m_parsedSelect.m_tableList.size() > 1 && !edgeCaseOuterJoin) {
        reAggNodeForReplace = reAggNode;
    }
    boolean find = mvFixInfo.processScanNodeWithReAggNode(sendNode, reAggNodeForReplace);
    assert (find);
    // receive node with materialized view scan node.
    if (m_parsedSelect.m_tableList.size() > 1 && !edgeCaseOuterJoin) {
        AbstractPlanNode joinNode = sendNodeChild;
        // No agg, limit pushed down at this point.
        assert (joinNode instanceof AbstractJoinPlanNode);
        // Fix the node after Re-aggregation node.
        joinNode.clearParents();
        assert (mvFixInfo.m_scanNode != null);
        mvFixInfo.m_scanNode.clearParents();
        // replace joinNode with MV scan node on each partition.
        sendNode.clearChildren();
        sendNode.addAndLinkChild(mvFixInfo.m_scanNode);
        // its parent will be the parent of the new join node. Update the root node.
        if (reAggParent != null) {
            reAggParent.replaceChild(reAggNode, joinNode);
            root = reAggParent;
        } else {
            root = joinNode;
        }
    }
    return root;
}

Example 19 with ReceivePlanNode

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

the class TestPlansSubQueries method testPartitionedGroupBy.

public void testPartitionedGroupBy() {
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    AbstractPlanNode nlpn;
    // (1) Single partition query, filter on outer query.
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 GROUP BY A, C) T1 " + "where T1.A = 1 ");
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "A", "C");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    // Because it group by the partition column, we can drop the group by column on coordinator
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    // (2) Single partition query, filter in inner sub-query.
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 WHERE A = 1 GROUP BY A, C) T1");
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "A", "C");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    // (3) Sub-query with replicated table group by
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM R1 GROUP BY A, C) T1, P1 " + "where T1.A = P1.A ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.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.getInlinePlanNode(PlanNodeType.INDEXSCAN);
    checkPrimaryKeyIndexScan(pn, "P1");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkSeqScan(pn, "R1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    // Top aggregation node on coordinator
    planNodes = compileToFragments("SELECT -8, T1.NUM FROM SR4 T0, " + "(select max(RATIO) RATIO, sum(NUM) NUM, DESC from SP4 group by DESC) T1 " + "WHERE (T1.NUM + 5 ) > 44");
    assertEquals(2, 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);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(1);
    checkPrimaryKeyIndexScan(pn, "SR4");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "NUM");
    pn = pn.getChild(0);
    assertTrue(pn instanceof AggregatePlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    checkPrimaryKeyIndexScan(pn, "SP4");
    //
    // (4) Sub-query with partitioned table group by
    //
    // optimize the group by case to join on distributed node.
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.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.getInlinePlanNode(PlanNodeType.INDEXSCAN);
    checkPrimaryKeyIndexScan(pn, "P2");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    // Add aggregate inside of subquery
    planNodes = compileToFragments("SELECT * FROM (SELECT A, COUNT(*) CT FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.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.getInlinePlanNode(PlanNodeType.INDEXSCAN);
    checkPrimaryKeyIndexScan(pn, "P2");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    // Add distinct option to aggregate inside of subquery
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C, SUM(distinct D) FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.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.getInlinePlanNode(PlanNodeType.INDEXSCAN);
    checkPrimaryKeyIndexScan(pn, "P2");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    // single partition filter inside subquery
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 WHERE A = 3 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A ");
    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 NestLoopIndexPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopIndexPlanNode) nlpn).getJoinType());
    pn = nlpn.getInlinePlanNode(PlanNodeType.INDEXSCAN);
    checkPrimaryKeyIndexScan(pn, "P2");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    // single partition filter outside subquery
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A and P2.A = 3");
    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);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    pn = nlpn.getChild(1);
    checkPrimaryKeyIndexScan(pn, "P2");
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A and T1.A = 3");
    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);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    pn = nlpn.getChild(1);
    checkPrimaryKeyIndexScan(pn, "P2");
    // Group by C, A instead of A, C
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 GROUP BY C, A) T1, P2 " + "where T1.A = P2.A and T1.A = 3");
    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);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    pn = nlpn.getChild(1);
    checkPrimaryKeyIndexScan(pn, "P2");
}

Example 20 with ReceivePlanNode

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

the class TestPlansSubQueries method testDistinct.

/**
     * MANY of these DISTINCT use cases could be supported, some quite easily.
     * The cases that we can not support are those that require a join on
     * partition key AFTER a global distinct operation.
     * Other cases where the DISTINCT can be executed locally -- because it
     * contains the partition key are the most trivial.
     * TODO: make the planner smarter to plan these kind of sub-queries.
     */
public void testDistinct() {
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C, SUM(distinct D) FROM P2 GROUP BY A, C) T1, R1 where T1.A = R1.A ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertFalse(pn.toExplainPlanString().contains("DISTINCT"));
    pn = planNodes.get(1).getChild(0);
    // this join can be pushed down.
    //* enable to debug */ System.out.println(pn.toExplainPlanString());
    assertTrue(pn.toExplainPlanString().contains("LOOP INNER JOIN"));
    pn = pn.getChild(0);
    // This is a trivial subquery result scan.
    assertTrue(pn instanceof SeqScanPlanNode);
    pn = pn.getChild(0);
    // This is the subquery plan.
    checkPrimaryKeyIndexScan(pn, "P2");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertTrue(pn.toExplainPlanString().contains("SUM DISTINCT(P2.D"));
    // verify the optimized plan without sub-query like the one above
    planNodes = compileToFragments("SELECT P2.A, P2.C, SUM(distinct P2.D) FROM P2, R1 WHERE P2.A = R1.A GROUP BY P2.A, P2.C");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    assertTrue(pn.getChild(0) instanceof ReceivePlanNode);
    pn = planNodes.get(1).getChild(0);
    assertTrue(pn instanceof NestLoopIndexPlanNode);
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.INDEXSCAN));
    assertTrue(pn.getInlinePlanNode(PlanNodeType.INDEXSCAN).toExplainPlanString().contains("INDEX SCAN of \"P2\" using its primary key index"));
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    assertTrue(pn.getChild(0).toExplainPlanString().contains("SEQUENTIAL SCAN of \"R1\""));
    // T
    planNodes = compileToFragments("SELECT * FROM (SELECT DISTINCT A FROM P1) T1, P2 where T1.A = P2.A");
    assertEquals(2, planNodes.size());
    //* enable to debug */ System.out.println(planNodes.get(1).toExplainPlanString());
    assertFalse(planNodes.get(0).toExplainPlanString().contains("AGGREGATION"));
    assertFalse(planNodes.get(0).toExplainPlanString().contains("DISTINCT"));
    assertFalse(planNodes.get(0).toExplainPlanString().contains("JOIN"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("AGGREGATION"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("INDEX INNER JOIN"));
    // Distinct with GROUP BY
    // TODO: group by partition column cases can be supported
    String errorMessage = "This query is not plannable.  It has a subquery which needs cross-partition access.";
    failToCompile("SELECT * FROM (SELECT DISTINCT A, C FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A", errorMessage);
    failToCompile("SELECT * FROM (SELECT DISTINCT A FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A", errorMessage);
    planNodes = compileToFragments("SELECT * " + "FROM   (   SELECT T0.A, R1.C " + "           FROM   R1, " + "                  (   SELECT DISTINCT P1.A " + "                      FROM P1, R2 " + "                      WHERE P1.A = R2.A) " + "                  T0 " + "           WHERE  R1.A = T0.A ) " + "       T1, " + "       P2 " + "WHERE T1.A = P2.A");
    assertEquals(2, planNodes.size());
    //* enable to debug */ System.out.println(planNodes.get(1).toExplainPlanString());
    assertFalse(planNodes.get(0).toExplainPlanString().contains("AGGREGATION"));
    assertFalse(planNodes.get(0).toExplainPlanString().contains("DISTINCT"));
    assertFalse(planNodes.get(0).toExplainPlanString().contains("JOIN"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("AGGREGATION"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("INDEX INNER JOIN"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("LOOP INNER JOIN"));
    // Distinct without GROUP BY
    String sql1, sql2;
    sql1 = "SELECT * FROM (SELECT DISTINCT A, C FROM P1) T1, P2 where T1.A = P2.A";
    sql2 = "SELECT * FROM (SELECT A, C FROM P1 GROUP BY A, C) T1, P2 where T1.A = P2.A";
    checkQueriesPlansAreTheSame(sql1, sql2);
    sql1 = "SELECT * FROM (SELECT T0.A, R1.C FROM R1, " + "                (SELECT Distinct P1.A, P1.C FROM P1,R2 where P1.A = R2.A) T0 where R1.A = T0.A ) T1, " + "              P2 " + "where T1.A = P2.A";
    sql2 = "SELECT * FROM (SELECT T0.A, R1.C FROM R1, " + "                (SELECT P1.A, P1.C FROM P1,R2 where P1.A = R2.A group by P1.A, P1.C) T0 where R1.A = T0.A ) T1, " + "              P2 " + "where T1.A = P2.A";
    checkQueriesPlansAreTheSame(sql1, sql2);
    planNodes = compileToFragments("SELECT * FROM (SELECT DISTINCT T0.A FROM R1, " + "                (SELECT P1.A, P1.C FROM P1,R2 where P1.A = R2.A) T0 where R1.A = T0.A ) T1, " + "              P2 " + "where T1.A = P2.A");
    assertEquals(2, planNodes.size());
    //* enable to debug */ System.out.println(planNodes.get(1).toExplainPlanString());
    assertFalse(planNodes.get(0).toExplainPlanString().contains("AGGREGATION"));
    assertFalse(planNodes.get(0).toExplainPlanString().contains("DISTINCT"));
    assertFalse(planNodes.get(0).toExplainPlanString().contains("JOIN"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("AGGREGATION"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("INDEX INNER JOIN"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("LOOP INNER JOIN"));
    failToCompile("SELECT * FROM (SELECT DISTINCT A FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A");
    sql1 = "SELECT * FROM (SELECT DISTINCT T0.A, R1.C FROM R1, " + "                (SELECT P1.A, P1.C FROM P1,R2 where P1.A = R2.A) T0 where R1.A = T0.A ) T1, " + "              P2 " + "where T1.A = P2.A";
    sql2 = "SELECT * FROM (SELECT T0.A, R1.C FROM R1, " + "                (SELECT P1.A, P1.C FROM P1,R2 where P1.A = R2.A) T0 where R1.A = T0.A GROUP BY T0.A, R1.C) T1, " + "              P2 " + "where T1.A = P2.A";
    checkQueriesPlansAreTheSame(sql1, sql2);
}