Search in sources :

Example 16 with NestLoopIndexPlanNode

use of org.voltdb.plannodes.NestLoopIndexPlanNode 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");
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) AggregatePlanNode(org.voltdb.plannodes.AggregatePlanNode) HashAggregatePlanNode(org.voltdb.plannodes.HashAggregatePlanNode) SendPlanNode(org.voltdb.plannodes.SendPlanNode) MergeReceivePlanNode(org.voltdb.plannodes.MergeReceivePlanNode) ReceivePlanNode(org.voltdb.plannodes.ReceivePlanNode) NestLoopPlanNode(org.voltdb.plannodes.NestLoopPlanNode) ProjectionPlanNode(org.voltdb.plannodes.ProjectionPlanNode) NestLoopIndexPlanNode(org.voltdb.plannodes.NestLoopIndexPlanNode)

Example 17 with NestLoopIndexPlanNode

use of org.voltdb.plannodes.NestLoopIndexPlanNode 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);
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SeqScanPlanNode(org.voltdb.plannodes.SeqScanPlanNode) SendPlanNode(org.voltdb.plannodes.SendPlanNode) MergeReceivePlanNode(org.voltdb.plannodes.MergeReceivePlanNode) ReceivePlanNode(org.voltdb.plannodes.ReceivePlanNode) NestLoopIndexPlanNode(org.voltdb.plannodes.NestLoopIndexPlanNode)

Example 18 with NestLoopIndexPlanNode

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

the class TestPlansJoin method perJoinOpTestIndexJoinConditions.

private void perJoinOpTestIndexJoinConditions(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    NestLoopIndexPlanNode nlij;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    SeqScanPlanNode seqScan;
    query = "SELECT * FROM R3, R2 WHERE R3.A" + joinOp + "R2.A AND R3.C > 0 AND R2.C >= 5";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertNull(nlij.getJoinPredicate());
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHANOREQUALTO, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    query = "SELECT * FROM R3 JOIN R2 ON R3.A" + joinOp + "R2.A WHERE R3.C > 0 AND R2.C >= 5";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertNull(nlij.getJoinPredicate());
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHANOREQUALTO, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    query = "SELECT * FROM R3 JOIN R2 USING(A) WHERE R3.C > 0 AND R2.C >= 5";
    pn = compileToTopDownTree(query, 3, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertNull(nlij.getJoinPredicate());
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHANOREQUALTO, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    query = "SELECT * FROM R3 JOIN R2 ON R3.A" + joinOp + " R2.A JOIN R1 ON R2.A" + joinOp + "R1.A WHERE R3.C > 0 AND R2.C >= 5";
    pn = compileToTopDownTree(query, 7, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    nlij = (NestLoopIndexPlanNode) nlj.getChild(0);
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHANOREQUALTO, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SeqScanPlanNode(org.voltdb.plannodes.SeqScanPlanNode) AbstractExpression(org.voltdb.expressions.AbstractExpression) IndexScanPlanNode(org.voltdb.plannodes.IndexScanPlanNode) NestLoopPlanNode(org.voltdb.plannodes.NestLoopPlanNode) NestLoopIndexPlanNode(org.voltdb.plannodes.NestLoopIndexPlanNode)

Example 19 with NestLoopIndexPlanNode

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

the class TestIndexSelection method checkDualIndexedJoin.

private void checkDualIndexedJoin(AbstractPlanNode pn, String leftIndexName, String rightIndexName, int nJoinKeys) {
    IndexScanPlanNode ispn;
    // Skip the Send and Projection plan nodes.
    pn = pn.getChild(0).getChild(0);
    assertTrue(pn instanceof NestLoopIndexPlanNode);
    ispn = ((NestLoopIndexPlanNode) pn).getInlineIndexScan();
    assertEquals(leftIndexName, ispn.getTargetIndexName());
    pn = pn.getChild(0);
    assertTrue(pn instanceof IndexScanPlanNode);
    ispn = (IndexScanPlanNode) pn;
    assertEquals(rightIndexName, ispn.getTargetIndexName());
    assertEquals(nJoinKeys, ispn.getSearchKeyExpressions().size());
}
Also used : IndexScanPlanNode(org.voltdb.plannodes.IndexScanPlanNode) NestLoopIndexPlanNode(org.voltdb.plannodes.NestLoopIndexPlanNode)

Aggregations

NestLoopIndexPlanNode (org.voltdb.plannodes.NestLoopIndexPlanNode)19 AbstractPlanNode (org.voltdb.plannodes.AbstractPlanNode)16 IndexScanPlanNode (org.voltdb.plannodes.IndexScanPlanNode)15 AbstractExpression (org.voltdb.expressions.AbstractExpression)11 NestLoopPlanNode (org.voltdb.plannodes.NestLoopPlanNode)11 SeqScanPlanNode (org.voltdb.plannodes.SeqScanPlanNode)8 MergeReceivePlanNode (org.voltdb.plannodes.MergeReceivePlanNode)4 ProjectionPlanNode (org.voltdb.plannodes.ProjectionPlanNode)4 ReceivePlanNode (org.voltdb.plannodes.ReceivePlanNode)4 SendPlanNode (org.voltdb.plannodes.SendPlanNode)4 MaterializedScanPlanNode (org.voltdb.plannodes.MaterializedScanPlanNode)2 ArrayList (java.util.ArrayList)1 ConstantValueExpression (org.voltdb.expressions.ConstantValueExpression)1 ParameterValueExpression (org.voltdb.expressions.ParameterValueExpression)1 TupleValueExpression (org.voltdb.expressions.TupleValueExpression)1 VectorValueExpression (org.voltdb.expressions.VectorValueExpression)1 BranchNode (org.voltdb.planner.parseinfo.BranchNode)1 JoinNode (org.voltdb.planner.parseinfo.JoinNode)1 AbstractJoinPlanNode (org.voltdb.plannodes.AbstractJoinPlanNode)1 AbstractReceivePlanNode (org.voltdb.plannodes.AbstractReceivePlanNode)1