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

Example 36 with AggregatePlanNode

use of org.voltdb.plannodes.AggregatePlanNode 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 37 with AggregatePlanNode

use of org.voltdb.plannodes.AggregatePlanNode 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 38 with AggregatePlanNode

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

the class TestPlansSubQueries method testTableAggSubquery.

public void testTableAggSubquery() {
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    AbstractPlanNode nlpn;
    planNodes = compileToFragments("SELECT * FROM (SELECT sum(C) AS SC FROM P1) T1");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    checkSeqScan(pn, "T1", "SC");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    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);
    checkSeqScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.AGGREGATE));
    failToCompile("SELECT * FROM (SELECT sum(C) AS SC FROM P1) T1, P2 " + "where P2.A = T1.SC", joinErrorMsg);
    failToCompile("SELECT * FROM (SELECT count(A) as A FROM P1) T1, P2 " + "where P2.A = T1.A", joinErrorMsg);
    // Special non-push-down-able join case where the join must follow the
    // agg which must follow the send/receive.
    planNodes = compileToFragments("SELECT * FROM (SELECT sum(C) AS SC FROM P1) T1, R1 " + "where R1.A = T1.SC");
    assertEquals(2, planNodes.size());
    //* enable to debug */ System.out.println(planNodes.get(0).toExplainPlanString());
    //* enable to debug */ System.out.println(planNodes.get(1).toExplainPlanString());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "R1");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    assertTrue(pn instanceof AggregatePlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1).getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.AGGREGATE));
}