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

Example 21 with ProjectionPlanNode

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

the class TestWindowedFunctions method validatePartitionedQuery.

private void validatePartitionedQuery(String query, boolean hasStatementOrderBy) {
    List<AbstractPlanNode> nodes = compileToFragments(query);
    assertEquals(2, nodes.size());
    AbstractPlanNode child = nodes.get(0);
    // Validate the coordinator fragment.
    assertTrue(child instanceof SendPlanNode);
    child = child.getChild(0);
    assertTrue(child instanceof ProjectionPlanNode);
    if (hasStatementOrderBy) {
        child = child.getChild(0);
        assertTrue(child instanceof OrderByPlanNode);
    }
    child = child.getChild(0);
    assertTrue(child instanceof WindowFunctionPlanNode);
    child = child.getChild(0);
    assertTrue(child instanceof OrderByPlanNode);
    child = child.getChild(0);
    assertTrue(child instanceof ReceivePlanNode);
    assertEquals(0, child.getChildCount());
    // Get the distributed fragment.
    child = nodes.get(1);
    assertTrue(child instanceof SendPlanNode);
    child = child.getChild(0);
    assertTrue(child instanceof SeqScanPlanNode);
    assertEquals(0, child.getChildCount());
}

Example 22 with ProjectionPlanNode

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

the class TestPlansSubQueries method testUnions.

public void testUnions() {
    AbstractPlanNode pn;
    pn = compile("select A, C FROM (SELECT A, C FROM R1 UNION SELECT A, C FROM R2 UNION SELECT A, C FROM R3) T1 order by A ");
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof OrderByPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "A", "C");
    AbstractPlanNode upn = pn.getChild(0);
    assertTrue(upn instanceof UnionPlanNode);
    pn = upn.getChild(0);
    checkSeqScan(pn, "R1", "A", "C");
    pn = upn.getChild(1);
    checkSeqScan(pn, "R2", "A", "C");
    pn = upn.getChild(2);
    checkSeqScan(pn, "R3", "A", "C");
    String message = "This query is not plannable.  It has a subquery which needs cross-partition access.";
    failToCompile("select * FROM " + "(SELECT A, COUNT(*) FROM P1 GROUP BY A " + "UNION " + "SELECT A, COUNT(*) FROM R2 GROUP BY A) T1 , P2 where T1.A = P2.A ", message);
}

Example 23 with ProjectionPlanNode

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

the class TestPlansSubQueries method testJoins.

public void testJoins() {
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    AbstractPlanNode nlpn;
    String sql, sqlNoSimplification, equivalentSql;
    // Left Outer join
    sql = "SELECT R1.A, R1.C FROM R1 LEFT JOIN (SELECT A, C FROM R2) T1 ON T1.C = R1.C ";
    sqlNoSimplification = "SELECT R1.A, R1.C FROM R1 LEFT JOIN (SELECT A, C FROM R2 LIMIT 10) T1 ON T1.C = R1.C ";
    equivalentSql = "SELECT R1.A, R1.C FROM R1 LEFT JOIN R2 T1 ON T1.C = R1.C ";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.LEFT, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "R1", "A", "C");
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "T1", "C");
    pn = pn.getChild(0);
    checkSeqScan(pn, "R2", "A", "C");
    checkSubquerySimplification(sql, equivalentSql);
    // Join with partitioned tables
    // Join on coordinator: LEFT OUTER JOIN, replicated table on left side
    sql = "SELECT R1.A, R1.C FROM R1 LEFT JOIN (SELECT A, C FROM P1) T1 ON T1.C = R1.C ";
    sqlNoSimplification = "SELECT R1.A, R1.C FROM R1 LEFT JOIN (SELECT DISTINCT A, C FROM P1) T1 ON T1.C = R1.C ";
    equivalentSql = "SELECT R1.A, R1.C FROM R1 LEFT JOIN P1 T1 ON T1.C = R1.C ";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.LEFT, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "R1", "A", "C");
    pn = nlpn.getChild(1);
    assertEquals(PlanNodeType.RECEIVE, pn.getPlanNodeType());
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "C");
    checkSubquerySimplification(sql, equivalentSql);
    // Group by inside of the subquery
    // whether it contains group by or not does not matter, because we check it by whether inner side is partitioned or not
    planNodes = compileToFragments("SELECT R1.A, R1.C FROM R1 LEFT JOIN (SELECT A, count(*) C FROM P1 GROUP BY A) T1 ON 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);
    assertEquals(JoinType.LEFT, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "R1", "A", "C");
    pn = nlpn.getChild(1);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "C");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "A", "C");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    // Using index scan for group by only: use serial aggregate instead hash aggregate
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.AGGREGATE));
    // LEFT partition table
    planNodes = compileToFragments("SELECT T1.CC FROM P1 LEFT JOIN (SELECT A, count(*) CC FROM P2 GROUP BY A) T1 ON T1.A = P1.A ");
    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);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.LEFT, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "T1");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P2");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    // Using index scan for group by only: use serial aggregate instead hash aggregate
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.AGGREGATE));
    // Right outer join
    planNodes = compileToFragments("SELECT R1.A, R1.C FROM R1 RIGHT JOIN (SELECT A, count(*) C FROM P1 GROUP BY A) T1 ON T1.C = R1.C ");
    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);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.LEFT, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "R1", "A", "C");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "C");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "A", "C");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    // Using index scan for group by only: use serial aggregate instead hash aggregate
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.AGGREGATE));
    // RIGHT partition table
    planNodes = compileToFragments("SELECT T1.CC FROM P1 RIGHT JOIN (SELECT A, count(*) CC FROM P2 GROUP BY A) T1 ON T1.A = P1.A ");
    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);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopIndexPlanNode);
    assertEquals(JoinType.LEFT, ((NestLoopIndexPlanNode) nlpn).getJoinType());
    pn = nlpn.getInlinePlanNode(PlanNodeType.INDEXSCAN);
    checkPrimaryKeyIndexScan(pn, "P1");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P2");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    // Using index scan for group by only: use serial aggregate instead hash aggregate
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.AGGREGATE));
    // Join locally: inner join case for subselects
    sql = "SELECT R1.A, R1.C FROM R1 INNER JOIN (SELECT A, C FROM P1) T1 ON T1.C = R1.C ";
    sqlNoSimplification = "SELECT R1.A, R1.C FROM R1 INNER JOIN (SELECT DISTINCT A, C FROM P1) T1 ON T1.C = R1.C ";
    equivalentSql = "SELECT R1.A, R1.C FROM R1 INNER JOIN P1 T1 ON T1.C = R1.C ";
    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);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "R1", "A", "C");
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "T1", "C");
    checkSubquerySimplification(sql, equivalentSql);
    // Two sub-queries. One is partitioned and the other one is replicated
    sql = "select A, AC FROM (SELECT A FROM R1) T1, (SELECT C AC FROM P1) T2 WHERE T1.A = T2.AC ";
    sqlNoSimplification = "select A, AC FROM (SELECT A FROM R1 LIMIT 10) T1, (SELECT DISTINCT A AC FROM P1) T2 WHERE T1.A = T2.AC ";
    equivalentSql = "select T1.A, T2.C AC FROM R1 T1, P1 T2 WHERE T1.A = T2.C ";
    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);
    assertTrue(pn instanceof SendPlanNode);
    nlpn = pn.getChild(0);
    assertTrue(nlpn instanceof NestLoopPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "A");
    pn = pn.getChild(0);
    checkSeqScan(pn, "R1", "A");
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "T2", "AC");
    checkSubquerySimplification(sql, equivalentSql);
    // This is a single fragment plan because planner can detect "A = 3".
    // Join locally
    sql = "select A1, A2 FROM (SELECT A A1 FROM R1) T1, (SELECT A A2 FROM P1 where A = 3) T2 WHERE T1.A1 = T2.A2 ";
    sqlNoSimplification = "select A2, A1 FROM (SELECT DISTINCT A A1 FROM R1) T1, (SELECT DISTINCT A A2 FROM P1 where A = 3) T2 WHERE T1.A1 = T2.A2 ";
    equivalentSql = "select T1.A A1, T2.A A2 FROM R1 T1 join P1 T2 on T2.A = 3 and T1.A = T2.A";
    planNodes = compileToFragments(sqlNoSimplification);
    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);
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "A1");
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "T2", "A2");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "A");
    assertEquals(2, ((IndexScanPlanNode) pn).getInlinePlanNodes().size());
    assertNotNull(((IndexScanPlanNode) pn).getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(((IndexScanPlanNode) pn).getInlinePlanNode(PlanNodeType.AGGREGATE));
    checkSubquerySimplification(sql, equivalentSql);
    // More single partition detection
    planNodes = compileToFragments("select C FROM (SELECT P1.C FROM P1, P2 " + "WHERE P1.A = P2.A AND P1.A = 3) T1 ");
    assertEquals(1, planNodes.size());
    planNodes = compileToFragments("select T1.C FROM (SELECT P1.C FROM P1, P2 " + "WHERE P1.A = P2.A AND P1.A = 3) T1, R1 where T1.C > R1.C ");
    assertEquals(1, planNodes.size());
    planNodes = compileToFragments("select T1.C FROM (SELECT P1.C FROM P1, P2 " + "WHERE P1.A = P2.A AND P1.A = 3) T1, (select C FROM R1) T2 where T1.C > T2.C ");
    assertEquals(1, planNodes.size());
}

Example 24 with ProjectionPlanNode

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

the class TestPlansSubQueries method testPartitionedGroupByWithoutAggregate.

public void testPartitionedGroupByWithoutAggregate() {
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    // group by non-partition column, no pushed down
    planNodes = compileToFragments("SELECT * FROM (SELECT C FROM P1 GROUP BY C) T1");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    checkSeqScan(pn, "T1");
    pn = pn.getChild(0);
    assertTrue(pn instanceof HashAggregatePlanNode);
    pn = planNodes.get(1).getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    // count(*), no pushed down
    planNodes = compileToFragments("SELECT count(*) FROM (SELECT c FROM P1 GROUP BY c) T1");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof TableCountPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof HashAggregatePlanNode);
    pn = planNodes.get(1).getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    // group by partition column, pushed down
    planNodes = compileToFragments("SELECT * FROM (SELECT A FROM P1 GROUP BY A) T1");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    assertTrue(pn.getChild(0) instanceof ReceivePlanNode);
    pn = planNodes.get(1).getChild(0);
    checkSeqScan(pn, "T1");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
    planNodes = compileToFragments("SELECT count(*) FROM (SELECT A FROM P1 GROUP BY A) T1");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertTrue(pn.getChild(0) instanceof ReceivePlanNode);
    pn = planNodes.get(1).getChild(0);
    assertTrue(pn instanceof TableCountPlanNode);
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1");
}

Example 25 with ProjectionPlanNode

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

the class TestPlansSubQueries method checkReplicatedTwo.

private void checkReplicatedTwo(String sql, int nljCount, int nlijCount) {
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    planNodes = compileToFragments(sql);
    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);
    assertTrue(pn instanceof SendPlanNode);
    checkJoinNode(pn, PlanNodeType.NESTLOOP, nljCount);
    checkJoinNode(pn, PlanNodeType.NESTLOOPINDEX, nlijCount);
}