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

Example 26 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 27 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));
}

Example 28 with AggregatePlanNode

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

the class InlineAggregation method inlineAggregationApply.

AbstractPlanNode inlineAggregationApply(AbstractPlanNode plan) {
    // check for an aggregation of the right form
    if (!(plan instanceof AggregatePlanNode)) {
        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;
}

Example 29 with AggregatePlanNode

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

the class TestPlansInExistsSubQueries method verifyAggregateSubquery.

private void verifyAggregateSubquery(AbstractExpression exists, int columnCount, int groupByCount, boolean hasHaving) {
    assertNotNull(exists);
    assertEquals(ExpressionType.OPERATOR_EXISTS, exists.getExpressionType());
    AbstractSubqueryExpression se = (AbstractSubqueryExpression) exists.getLeft();
    AbstractPlanNode sn = se.getSubqueryNode();
    assertTrue(sn instanceof AbstractScanPlanNode);
    AbstractPlanNode inline = sn.getInlinePlanNode(PlanNodeType.AGGREGATE);
    assertNotNull(inline);
    assertEquals(columnCount, inline.getOutputSchema().size());
    AggregatePlanNode agg = (AggregatePlanNode) inline;
    assertEquals(groupByCount, agg.getGroupByExpressions().size());
    assertEquals(hasHaving, agg.getPostPredicate() != null);
}

Example 30 with AggregatePlanNode

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

the class TestPlansInExistsSubQueries method testHavingInSubquery.

public void testHavingInSubquery() {
    String sql;
    AbstractPlanNode pn;
    AggregatePlanNode aggNode;
    List<AbstractExpression> args;
    AbstractExpression pred;
    AbstractSubqueryExpression sqe;
    AbstractExpression postExpr;
    AbstractExpression re;
    AbstractExpression le;
    // filter on agg of expression involving grand-parent tve
    sql = "select a from r1 where exists " + "(select 1 from r2 where exists  " + " (select 1 from r3 group by c having min(a) > r1.d)) ";
    pn = compile(sql);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    pred = ((SeqScanPlanNode) pn).getPredicate();
    // child
    assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
    sqe = (AbstractSubqueryExpression) pred.getLeft();
    //* enable to debug */ System.out.println(se.explain(""));
    args = sqe.getArgs();
    assertEquals(1, args.size());
    assertEquals(1, sqe.getParameterIdxList().size());
    assertEquals("D", ((TupleValueExpression) args.get(0)).getColumnName());
    pn = sqe.getSubqueryNode();
    assertTrue(pn instanceof SeqScanPlanNode);
    pred = ((SeqScanPlanNode) pn).getPredicate();
    // grand child
    assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
    sqe = (AbstractSubqueryExpression) pred.getLeft();
    pn = sqe.getSubqueryNode();
    pn = pn.getChild(0).getChild(0);
    aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
    assertNotNull(aggNode);
    postExpr = aggNode.getPostPredicate();
    assertNotNull(postExpr);
    assertEquals(ExpressionType.COMPARE_GREATERTHAN, postExpr.getExpressionType());
    re = postExpr.getRight();
    assertEquals(ExpressionType.VALUE_PARAMETER, re.getExpressionType());
    assertEquals(new Integer(0), ((ParameterValueExpression) re).getParameterIndex());
    // filter on agg of expression involving parent tve
    sql = "select a from r1 where c in " + " (select max(c) from r2 group by e having min(a) > r1.d)";
    pn = compile(sql);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    pred = ((SeqScanPlanNode) pn).getPredicate();
    assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
    sqe = (AbstractSubqueryExpression) pred.getLeft();
    args = sqe.getArgs();
    assertEquals(2, args.size());
    assertEquals(2, sqe.getParameterIdxList().size());
    assertEquals("D", ((TupleValueExpression) args.get(0)).getColumnName());
    assertEquals("C", ((TupleValueExpression) args.get(1)).getColumnName());
    pn = sqe.getSubqueryNode();
    pn = pn.getChild(0);
    assertTrue(pn instanceof LimitPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
    assertNotNull(aggNode);
    assertEquals(3, aggNode.getOutputSchema().size());
    postExpr = aggNode.getPostPredicate();
    assertEquals(ExpressionType.CONJUNCTION_AND, postExpr.getExpressionType());
    le = postExpr.getLeft();
    assertEquals(ExpressionType.COMPARE_GREATERTHAN, le.getExpressionType());
    assertEquals(new Integer(0), ((ParameterValueExpression) le.getRight()).getParameterIndex());
    re = postExpr.getRight();
    assertEquals(ExpressionType.COMPARE_EQUAL, re.getExpressionType());
    assertEquals(new Integer(1), ((ParameterValueExpression) re.getLeft()).getParameterIndex());
    // filter on agg of expression involving user parameter ('?')
    sql = "select a from r1 where c in " + " (select max(c) from r2 group by e having min(a) > ?) ";
    pn = compile(sql);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    pred = ((SeqScanPlanNode) pn).getPredicate();
    assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
    sqe = (AbstractSubqueryExpression) pred.getLeft();
    assertEquals(1, sqe.getParameterIdxList().size());
    assertEquals(new Integer(1), sqe.getParameterIdxList().get(0));
    pn = sqe.getSubqueryNode();
    pn = pn.getChild(0).getChild(0);
    assertEquals(PlanNodeType.SEQSCAN, pn.getPlanNodeType());
    aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
    assertNotNull(aggNode);
    postExpr = aggNode.getPostPredicate();
    assertEquals(ExpressionType.CONJUNCTION_AND, postExpr.getExpressionType());
    // User PVE
    le = postExpr.getLeft();
    assertEquals(ExpressionType.COMPARE_GREATERTHAN, le.getExpressionType());
    assertEquals(ExpressionType.VALUE_PARAMETER, le.getRight().getExpressionType());
    assertEquals(new Integer(0), ((ParameterValueExpression) le.getRight()).getParameterIndex());
    // Parent PVE
    re = postExpr.getRight();
    assertEquals(ExpressionType.COMPARE_EQUAL, re.getExpressionType());
    assertEquals(ExpressionType.VALUE_PARAMETER, re.getLeft().getExpressionType());
    assertEquals(new Integer(1), ((ParameterValueExpression) re.getLeft()).getParameterIndex());
    // filter on agg of local tve
    sql = "select a from r1 where c in " + " (select max(c) from r2 group by e having min(a) > 0)";
    pn = compile(sql);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    AbstractExpression p = ((SeqScanPlanNode) pn).getPredicate();
    assertEquals(ExpressionType.OPERATOR_EXISTS, p.getExpressionType());
    AbstractExpression subquery = p.getLeft();
    pn = ((AbstractSubqueryExpression) subquery).getSubqueryNode();
    pn = pn.getChild(0);
    assertTrue(pn instanceof LimitPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
    assertNotNull(aggNode);
    assertEquals(3, aggNode.getOutputSchema().size());
    postExpr = aggNode.getPostPredicate();
    assertEquals(ExpressionType.CONJUNCTION_AND, postExpr.getExpressionType());
    failToCompile("select max(c) from r1 group by a " + " having count(*) = (select c from r2 where r2.c = r1.a)", HavingErrorMsg);
/**
         * Uncomment these tests when ENG-8306 is finished
         */
//        // parent correlated TVE in the aggregate expression.
//        sql = "select max(c) from r1 group by a " +
//                " having count(*) = (select c from r2 where r2.c = r1.a)";
//        pn = compile(sql);
//        pn = pn.getChild(0);
//        assertTrue(pn instanceof ProjectionPlanNode);
//        pn = pn.getChild(0);
//        assertTrue(pn instanceof SeqScanPlanNode);
//        aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
//        assertNotNull(aggNode);
//        assertNotNull(aggNode instanceof HashAggregatePlanNode);
//        assertEquals(3, aggNode.getOutputSchema().size()); // group by key, max, count
//
//        postExpr = aggNode.getPostPredicate();
//        assertEquals(ExpressionType.COMPARE_EQUAL, postExpr.getExpressionType());
//        assertTrue(postExpr.getLeft() instanceof TupleValueExpression);
//        assertTrue(postExpr.getRight() instanceof SelectSubqueryExpression);
}