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

Example 21 with IndexScanPlanNode

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

the class TestPlansJoin method testIndexJoinConditions.

public void testIndexJoinConditions() {
    String query;
    AbstractPlanNode pn;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    //TODO: These are not even join queries. They should
    // probably be moved to some other test class.
    query = "SELECT * FROM R3 WHERE R3.A = 0";
    pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.INDEXSCAN);
    indexScan = (IndexScanPlanNode) pn.getChild(0);
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    assertNull(indexScan.getPredicate());
    query = "SELECT * FROM R3 WHERE R3.A > 0 AND R3.A < 5 AND R3.C = 4";
    pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.INDEXSCAN);
    indexScan = (IndexScanPlanNode) pn.getChild(0);
    assertEquals(IndexLookupType.GT, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_LESSTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    for (JoinOp joinOp : JoinOp.JOIN_OPS) {
        if (joinOp != JoinOp.EQUAL) {
            // weaken test for now
            continue;
        }
        perJoinOpTestIndexJoinConditions(joinOp);
    }
}

Example 22 with IndexScanPlanNode

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

the class TestPlansJoin method testMultiColumnJoin.

public void testMultiColumnJoin() {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    NestLoopIndexPlanNode nlij;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    // Test multi column condition on non index columns
    query = "SELECT A, C FROM R2 JOIN R1 USING(A, C)";
    pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    query = "SELECT A, C FROM R3 JOIN R2 USING(A, C)";
    pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    // Test multi column condition on index columns
    query = "SELECT A FROM R2 JOIN R3 USING(A)";
    pn = compileToTopDownTree(query, 1, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(indexScan.getPredicate());
    for (JoinOp joinOp : JoinOp.JOIN_OPS) {
        perJoinOpTestMultiColumnJoin(joinOp);
    }
}

Example 23 with IndexScanPlanNode

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

the class TestPlansJoin method perJoinOpTestOuterJoinSimplification.

private void perJoinOpTestOuterJoinSimplification(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    SeqScanPlanNode seqScan;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    NestLoopIndexPlanNode nlij;
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE R2.C IS NOT NULL";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE R2.C > 0";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 RIGHT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE R1.C > 0";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.C" + joinOp + "R3.C WHERE R3.A > 0";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.INDEXSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.C" + joinOp + "R3.A WHERE R3.A > 0";
    if (joinOp == JoinOp.EQUAL) {
        // weaken test for now
        pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
        node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
        nlij = (NestLoopIndexPlanNode) node;
        assertEquals(nlij.getJoinType(), JoinType.INNER);
    }
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) < 10";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 RIGHT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) < 10";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) < 10";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    query = "SELECT * FROM R1 RIGHT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) < 10";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) < 10 AND R1.C = 3";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) <  10 OR R2.C IS NOT NULL";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) <  10 AND R1.C > 3";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) <  10 OR R2.C IS NOT NULL";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    // Test with seqscan with different filers.
    query = "SELECT R2.A, R1.* FROM R1 LEFT OUTER JOIN R2 ON R2.A" + joinOp + "R1.A WHERE R2.A > 3";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    //* enable for debug */ System.out.println(pn.toExplainPlanString());
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    query = "SELECT R2.A, R1.* FROM R1 LEFT OUTER JOIN R2 ON R2.A" + joinOp + "R1.A WHERE R2.A IS NULL";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = nlj.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertNull(seqScan.getPredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertNull(seqScan.getPredicate());
    if (joinOp != JoinOp.EQUAL) {
        // weaken test for now
        return;
    }
    query = "SELECT b.A, a.* FROM R1 a LEFT OUTER JOIN R4 b ON b.A" + joinOp + "a.A AND b.C " + joinOp + " a.C AND a.D " + joinOp + " b.D WHERE b.A IS NULL";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    //* enable for debug */ System.out.println(pn.toExplainPlanString());
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertEquals(nlij.getJoinType(), JoinType.LEFT);
    assertNull(nlij.getPreJoinPredicate());
    assertNull(nlij.getJoinPredicate());
    predicate = nlij.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(indexScan.getPredicate());
    query = "SELECT b.A, a.* FROM R1 a LEFT OUTER JOIN R4 b ON b.A" + joinOp + "a.A AND b.C " + joinOp + " a.C AND a.D " + joinOp + " b.D WHERE b.B + b.A IS NULL";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertEquals(nlij.getJoinType(), JoinType.LEFT);
    assertNull(nlij.getPreJoinPredicate());
    assertNull(nlij.getJoinPredicate());
    predicate = nlij.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.OPERATOR_PLUS, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    query = "SELECT a.* FROM R1 a LEFT OUTER JOIN R5 b ON b.A" + joinOp + "a.A WHERE b.A IS NULL";
    pn = compileToTopDownTree(query, 3, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertEquals(nlij.getJoinType(), JoinType.LEFT);
    assertNull(nlij.getPreJoinPredicate());
    assertNull(nlij.getJoinPredicate());
    predicate = nlij.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
}

Example 24 with IndexScanPlanNode

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

the class TestPlansJoin method perJoinOpTestJoinOrders.

private void perJoinOpTestJoinOrders(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    SeqScanPlanNode seqScan;
    NestLoopIndexPlanNode nlij;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    if (joinOp != JoinOp.EQUAL) {
        // weaken test for now
        return;
    }
    // Index Join (R3.A) still has a lower cost compare to a Loop Join
    // despite the R3.C = 0 equality filter on the inner node
    query = "SELECT * FROM R1 JOIN R3 ON R3.A" + joinOp + "R1.A WHERE R3.C = 0";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    seqScan = (SeqScanPlanNode) node;
    assertEquals("R1", seqScan.getTargetTableName());
    // R3.A is an INDEX. Both children are IndexScans. With everything being equal,
    // the Left table (L) has fewer filters and should be an inner node
    query = "SELECT L.A, R.A FROM R3 L JOIN R3 R ON L.A" + joinOp + "R.A WHERE R.A > 3 AND R.C  = 3 AND L.A > 2 ;";
    pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.INDEXSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.INDEXSCAN);
    indexScan = (IndexScanPlanNode) node;
    assertEquals("R", indexScan.getTargetTableAlias());
    // NLIJ with inline inner IndexScan over R2 using its partial index is a winner
    // over the NLJ with R2 on the outer side
    query = "SELECT * FROM R3 JOIN R2 ON R3.C" + joinOp + "R2.C WHERE R2.C > 100;";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(indexScan.getPredicate());
    assertEquals("PARTIAL_IND2", indexScan.getTargetIndexName());
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    assertEquals("R3", seqScan.getTargetTableName());
}

Example 25 with IndexScanPlanNode

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

the class TestPlansGroupBy method testAggregateOptimizationWithIndex.

/**
     * VoltDB has two optimizations to use the ordered output of an index scan to
     * avoid a (full) hash aggregation. In one case, this takes advantage of an
     * existing index scan already in the plan -- this case applies generally to
     * partial indexes (with WHERE clauses) and full indexes. In another case, the
     * index scan is introduced as a replacement for the sequential scan.
     * For simplicity, this case does not consider partial indexes -- it would have
     * to validate that the query conditions imply the predicate of the index.
     * This could be implemented some day.
     */
public void testAggregateOptimizationWithIndex() {
    AbstractPlanNode p;
    List<AbstractPlanNode> pns;
    pns = compileToFragments("SELECT A, count(B) from R2 where B > 2 group by A;");
    assertEquals(1, pns.size());
    p = pns.get(0).getChild(0);
    assertTrue(p instanceof IndexScanPlanNode);
    assertNotNull(p.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    assertTrue(p.toExplainPlanString().contains("primary key index"));
    // matching the partial index where clause
    pns = compileToFragments("SELECT A, count(B) from R2 where B > 3 group by A;");
    assertEquals(1, pns.size());
    p = pns.get(0).getChild(0);
    assertTrue(p instanceof IndexScanPlanNode);
    assertNotNull(p.getInlinePlanNode(PlanNodeType.AGGREGATE));
    assertTrue(p.toExplainPlanString().contains("PARTIAL_IDX_R2"));
    // using the partial index with serial aggregation
    pns = compileToFragments("SELECT A, count(B) from R2 where A > 5 and B > 3 group by A;");
    assertEquals(1, pns.size());
    p = pns.get(0).getChild(0);
    assertTrue(p instanceof IndexScanPlanNode);
    assertNotNull(p.getInlinePlanNode(PlanNodeType.AGGREGATE));
    assertTrue(p.toExplainPlanString().contains("PARTIAL_IDX_R2"));
    // order by will help pick up the partial index
    pns = compileToFragments("SELECT A, count(B) from R2 where B > 3 group by A order by A;");
    assertEquals(1, pns.size());
    //* enable to debug */ printExplainPlan(pns);
    p = pns.get(0).getChild(0);
    assertTrue(p instanceof IndexScanPlanNode);
    assertNotNull(p.getInlinePlanNode(PlanNodeType.AGGREGATE));
    assertTrue(p.toExplainPlanString().contains("PARTIAL_IDX_R2"));
    // using the partial index with partial aggregation
    pns = compileToFragments("SELECT C, A, MAX(B) FROM R2 WHERE A > 0 and B > 3 GROUP BY C, A");
    assertEquals(1, pns.size());
    p = pns.get(0).getChild(0);
    assertEquals(PlanNodeType.INDEXSCAN, p.getPlanNodeType());
    assertNotNull(p.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    assertTrue(p.toExplainPlanString().contains("PARTIAL_IDX_R2"));
    // Partition IndexScan with HASH aggregate is optimized to use Partial aggregate -
    // index (F_D1) covers part of the GROUP BY columns
    pns = compileToFragments("SELECT F_D1, F_VAL1, MAX(F_VAL2) FROM F WHERE F_D1 > 0 GROUP BY F_D1, F_VAL1 ORDER BY F_D1, MAX(F_VAL2)");
    assertEquals(2, pns.size());
    p = pns.get(1).getChild(0);
    assertEquals(PlanNodeType.INDEXSCAN, p.getPlanNodeType());
    assertNotNull(p.getInlinePlanNode(PlanNodeType.PARTIALAGGREGATE));
    assertTrue(p.toExplainPlanString().contains("COL_F_TREE1"));
    // IndexScan with HASH aggregate is optimized to use Serial aggregate -
    // index (F_VAL1, F_VAL2) covers all of the GROUP BY columns
    pns = compileToFragments("SELECT F_VAL1, F_VAL2, MAX(F_VAL3) FROM RF WHERE F_VAL1 > 0 GROUP BY F_VAL2, F_VAL1");
    assertEquals(1, pns.size());
    p = pns.get(0).getChild(0);
    assertEquals(PlanNodeType.INDEXSCAN, p.getPlanNodeType());
    assertNotNull(p.getInlinePlanNode(PlanNodeType.AGGREGATE));
    assertTrue(p.toExplainPlanString().contains("COL_RF_TREE2"));
    // IndexScan with HASH aggregate remains not optimized -
    // The first column index (F_VAL1, F_VAL2) is not part of the GROUP BY
    pns = compileToFragments("SELECT F_VAL2, MAX(F_VAL2) FROM RF WHERE F_VAL1 > 0 GROUP BY F_VAL2");
    assertEquals(1, pns.size());
    p = pns.get(0).getChild(0);
    assertEquals(PlanNodeType.INDEXSCAN, p.getPlanNodeType());
    assertNotNull(p.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    assertTrue(p.toExplainPlanString().contains("COL_RF_TREE2"));
    // Partition IndexScan with HASH aggregate remains unoptimized -
    // index (F_VAL1, F_VAL2) does not cover any of the GROUP BY columns
    pns = compileToFragments("SELECT MAX(F_VAL2) FROM F WHERE F_VAL1 > 0 GROUP BY F_D1");
    assertEquals(2, pns.size());
    p = pns.get(1).getChild(0);
    assertEquals(PlanNodeType.INDEXSCAN, p.getPlanNodeType());
    assertNotNull(p.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    assertTrue(p.toExplainPlanString().contains("COL_F_TREE2"));
    // IndexScan with HASH aggregate remains unoptimized - the index COL_RF_HASH is not scannable
    pns = compileToFragments("SELECT F_VAL3, MAX(F_VAL2) FROM RF WHERE F_VAL3 = 0 GROUP BY F_VAL3");
    assertEquals(1, pns.size());
    p = pns.get(0).getChild(0);
    assertEquals(PlanNodeType.INDEXSCAN, p.getPlanNodeType());
    assertNotNull(p.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    assertTrue(p.toExplainPlanString().contains("COL_RF_HASH"));
    // where clause not matching
    pns = compileToFragments("SELECT A, count(B) from R2 where B > 2 group by A order by A;");
    assertEquals(1, pns.size());
    p = pns.get(0).getChild(0);
    assertTrue(p instanceof ProjectionPlanNode);
    p = p.getChild(0);
    assertTrue(p instanceof OrderByPlanNode);
    p = p.getChild(0);
    assertTrue(p instanceof SeqScanPlanNode);
    assertNotNull(p.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
}