Search in sources :

Example 1 with NestLoopPlanNode

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

the class TestPlansSubQueries method testPartitionedLimitOffset.

public void testPartitionedLimitOffset() {
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    AbstractPlanNode nlpn;
    // Top aggregation node on coordinator
    planNodes = compileToFragments("SELECT -8, T1.NUM " + "FROM SR4 T0, (select RATIO, NUM, DESC from SP4 order by DESC, NUM, RATIO limit 1 offset 1) 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 ProjectionPlanNode);
    pn = pn.getChild(0);
    // inline limit with order by
    assertTrue(pn instanceof MergeReceivePlanNode);
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.LIMIT));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.ORDERBY));
    pn = planNodes.get(1).getChild(0);
    // inline limit with order by
    assertTrue(pn instanceof OrderByPlanNode);
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.LIMIT));
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "SP4");
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C FROM P1 LIMIT 3) T1 " + "where T1.A = 1 ");
    assertEquals(2, planNodes.size());
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) OrderByPlanNode(org.voltdb.plannodes.OrderByPlanNode) SendPlanNode(org.voltdb.plannodes.SendPlanNode) NestLoopPlanNode(org.voltdb.plannodes.NestLoopPlanNode) MergeReceivePlanNode(org.voltdb.plannodes.MergeReceivePlanNode) ProjectionPlanNode(org.voltdb.plannodes.ProjectionPlanNode)

Example 2 with NestLoopPlanNode

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

the class TestSelfJoins method testSelfJoin.

public void testSelfJoin() {
    AbstractPlanNode pn = compile("select * FROM R1 A JOIN R1 B ON A.C = B.C WHERE B.A > 0 AND A.C < 3");
    pn = pn.getChild(0).getChild(0);
    assertTrue(pn instanceof NestLoopPlanNode);
    assertEquals(4, pn.getOutputSchema().getColumns().size());
    assertEquals(2, pn.getChildCount());
    AbstractPlanNode c = pn.getChild(0);
    assertTrue(c instanceof SeqScanPlanNode);
    SeqScanPlanNode ss = (SeqScanPlanNode) c;
    assertEquals("R1", ss.getTargetTableName());
    assertEquals("A", ss.getTargetTableAlias());
    assertEquals(ExpressionType.COMPARE_LESSTHAN, ss.getPredicate().getExpressionType());
    c = pn.getChild(1);
    assertTrue(c instanceof SeqScanPlanNode);
    ss = (SeqScanPlanNode) c;
    assertEquals("R1", ss.getTargetTableName());
    assertEquals("B", ss.getTargetTableAlias());
    assertEquals(ExpressionType.COMPARE_GREATERTHAN, ss.getPredicate().getExpressionType());
    pn = compile("select * FROM R1 JOIN R1 B ON R1.C = B.C");
    pn = pn.getChild(0).getChild(0);
    assertTrue(pn instanceof NestLoopPlanNode);
    assertEquals(4, pn.getOutputSchema().getColumns().size());
    assertEquals(2, pn.getChildCount());
    c = pn.getChild(0);
    assertTrue(c instanceof SeqScanPlanNode);
    ss = (SeqScanPlanNode) c;
    assertEquals("R1", ss.getTargetTableName());
    assertEquals("R1", ss.getTargetTableAlias());
    c = pn.getChild(1);
    assertTrue(c instanceof SeqScanPlanNode);
    ss = (SeqScanPlanNode) c;
    assertEquals("R1", ss.getTargetTableName());
    assertEquals("B", ss.getTargetTableAlias());
    pn = compile("select A.A, A.C, B.A, B.C FROM R1 A JOIN R1 B ON A.C = B.C");
    pn = pn.getChild(0).getChild(0);
    assertTrue(pn instanceof NestLoopPlanNode);
    assertEquals(4, pn.getOutputSchema().getColumns().size());
    pn = compile("select A,B.C  FROM R1 A JOIN R2 B USING(A)");
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    NodeSchema ns = pn.getOutputSchema();
    for (SchemaColumn sc : ns.getColumns()) {
        AbstractExpression e = sc.getExpression();
        assertTrue(e instanceof TupleValueExpression);
        TupleValueExpression tve = (TupleValueExpression) e;
        assertNotSame(-1, tve.getColumnIndex());
    }
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SeqScanPlanNode(org.voltdb.plannodes.SeqScanPlanNode) TupleValueExpression(org.voltdb.expressions.TupleValueExpression) AbstractExpression(org.voltdb.expressions.AbstractExpression) SchemaColumn(org.voltdb.plannodes.SchemaColumn) NestLoopPlanNode(org.voltdb.plannodes.NestLoopPlanNode) NodeSchema(org.voltdb.plannodes.NodeSchema) ProjectionPlanNode(org.voltdb.plannodes.ProjectionPlanNode)

Example 3 with NestLoopPlanNode

use of org.voltdb.plannodes.NestLoopPlanNode 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());
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SendPlanNode(org.voltdb.plannodes.SendPlanNode) IndexScanPlanNode(org.voltdb.plannodes.IndexScanPlanNode) 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 4 with NestLoopPlanNode

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

the class TestPlansSubQueries method testPartitionedSameLevel.

public void testPartitionedSameLevel() {
    // force it to be single partitioned.
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    String sql, sqlNoSimplification, equivalentSql;
    //
    // Single partition detection : single table
    //
    sql = "select A FROM (SELECT A FROM P1 WHERE A = 3) T1 ";
    sqlNoSimplification = "select A FROM (SELECT A FROM P1 WHERE A = 3 LIMIT 1) T1 ";
    equivalentSql = "SELECT A FROM P1 T1 WHERE A = 3";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "A");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "A");
    assertNotNull(((IndexScanPlanNode) pn).getInlinePlanNode(PlanNodeType.PROJECTION));
    checkSubquerySimplification(sql, equivalentSql);
    sql = "select A, C FROM (SELECT A, C FROM P1 WHERE A = 3) T1 ";
    sqlNoSimplification = "select A, C FROM (SELECT A, C FROM P1 WHERE A = 3 LIMIT 1) T1 ";
    equivalentSql = "SELECT A, C FROM P1 T1 WHERE A = 3";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(1, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "A", "C");
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "A", "C");
    assertNotNull(((IndexScanPlanNode) pn).getInlinePlanNode(PlanNodeType.PROJECTION));
    checkSubquerySimplification(sql, equivalentSql);
    // Single partition query without selecting partition column from sub-query
    planNodes = compileToFragments("select C FROM (SELECT A, C FROM P1 WHERE A = 3 LIMIT 1) T1 ");
    assertEquals(1, planNodes.size());
    planNodes = compileToFragments("select C FROM (SELECT C FROM P1 WHERE A = 3 LIMIT 1) T1 ");
    assertEquals(1, planNodes.size());
    //
    // AdHoc multiple partitioned sub-select queries.
    //
    sql = "select A1, C FROM (SELECT A A1, C FROM P1) T1  ";
    sqlNoSimplification = "select A1, C FROM (SELECT DISTINCT A A1, C FROM P1) T1 ";
    equivalentSql = "SELECT A A1, C FROM P1 T1";
    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).getChild(0);
    checkSeqScan(pn, "T1", "A1", "C");
    checkSubquerySimplification(sql, equivalentSql);
    sql = "select A1 FROM (SELECT A A1, C FROM P1 WHERE A > 3) T1 ";
    sqlNoSimplification = "select A1 FROM (SELECT A A1, C FROM P1 WHERE A > 3 LIMIT 10) T1 ";
    equivalentSql = "SELECT A A1 FROM P1 T1 WHERE A > 3";
    planNodes = compileToFragments(sqlNoSimplification);
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    checkSeqScan(pn, "T1", "A1");
    checkSubquerySimplification(sql, equivalentSql);
    //
    // Group by
    //
    planNodes = compileToFragments("select C, SD FROM " + "(SELECT C, SUM(D) as SD FROM P1 GROUP BY C) T1 ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    checkSeqScan(pn, "T1", "C", "SD");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    assertTrue(pn instanceof HashAggregatePlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "C", "SD");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    // rename group by column
    planNodes = compileToFragments("select X, SD FROM " + "(SELECT C AS X, SUM(D) as SD FROM P1 GROUP BY C) T1 ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    checkSeqScan(pn, "T1", "X", "SD");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    assertTrue(pn instanceof HashAggregatePlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "C", "SD");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    AbstractPlanNode nlpn;
    //
    // Partitioned Joined tests
    //
    failToCompile("select * FROM " + "(SELECT C, SUM(D) as SD FROM P1 GROUP BY C) T1, P2 where T1.C = P2.A ", joinErrorMsg);
    planNodes = compileToFragments("select T1.C, T1.SD FROM " + "(SELECT C, SUM(D) as SD FROM P1 GROUP BY C) T1, R1 Where 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);
    pn = nlpn.getChild(1);
    checkSeqScan(pn, "R1");
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "C", "SD");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = pn.getChild(0);
    assertTrue(pn instanceof HashAggregatePlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof ReceivePlanNode);
    pn = planNodes.get(1);
    assertTrue(pn instanceof SendPlanNode);
    pn = pn.getChild(0);
    checkPrimaryKeyIndexScan(pn, "P1", "C", "SD");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    // Group by Partitioned column
    planNodes = compileToFragments("select C, SD FROM " + "(SELECT A, C, SUM(D) as SD FROM P1 WHERE A > 3 GROUP BY A, C) T1 ");
    assertEquals(2, planNodes.size());
    planNodes = compileToFragments("select C, SD FROM " + "(SELECT A, C, SUM(D) as SD FROM P1 WHERE A = 3 GROUP BY A, C) T1 ");
    assertEquals(1, planNodes.size());
    planNodes = compileToFragments("select T1.C, T1.SD FROM " + "(SELECT A, C, SUM(D) as SD FROM P1 WHERE A = 3 GROUP BY A, C) T1, R1 WHERE T1.C = R1.C ");
    assertEquals(1, planNodes.size());
    //
    // Limit
    //
    planNodes = compileToFragments("select C FROM (SELECT C FROM P1 WHERE A > 3 ORDER BY C LIMIT 5) T1 ");
    assertEquals(2, planNodes.size());
    planNodes = compileToFragments("select T1.C FROM (SELECT C FROM P1 WHERE A > 3 ORDER BY C LIMIT 5) T1, " + "R1 WHERE T1.C > R1.C ");
    assertEquals(2, planNodes.size());
    planNodes = compileToFragments("select C FROM (SELECT A, C FROM P1 WHERE A = 3 ORDER BY C LIMIT 5) T1 ");
    assertEquals(1, planNodes.size());
    // Without selecting partition column from sub-query
    planNodes = compileToFragments(("select C FROM (SELECT C FROM P1 WHERE A = 3 ORDER BY C LIMIT 5) T1 "));
    assertEquals(1, planNodes.size());
    planNodes = compileToFragments("select T1.C FROM (SELECT A, C FROM P1 WHERE A = 3 ORDER BY C LIMIT 5) T1, " + "R1 WHERE T1.C > R1.C ");
    assertEquals(1, planNodes.size());
    // Without selecting partition column from sub-query
    planNodes = compileToFragments("select T1.C FROM (SELECT C FROM P1 WHERE A = 3 ORDER BY C LIMIT 5) T1, " + "R1 WHERE T1.C > R1.C ");
    assertEquals(1, planNodes.size());
    //
    // Group by & LIMIT 5
    //
    planNodes = compileToFragments("select C, SD FROM " + "(SELECT C, SUM(D) as SD FROM P1 GROUP BY C ORDER BY C LIMIT 5) T1 ");
    assertEquals(2, planNodes.size());
    // Without selecting partition column from sub-query
    planNodes = compileToFragments("select C, SD FROM " + "(SELECT C, SUM(D) as SD FROM P1 WHERE A = 3 GROUP BY C ORDER BY C LIMIT 5) T1 ");
    assertEquals(1, planNodes.size());
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SendPlanNode(org.voltdb.plannodes.SendPlanNode) MergeReceivePlanNode(org.voltdb.plannodes.MergeReceivePlanNode) ReceivePlanNode(org.voltdb.plannodes.ReceivePlanNode) HashAggregatePlanNode(org.voltdb.plannodes.HashAggregatePlanNode) NestLoopPlanNode(org.voltdb.plannodes.NestLoopPlanNode) ProjectionPlanNode(org.voltdb.plannodes.ProjectionPlanNode)

Example 5 with NestLoopPlanNode

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

the class TestUnion method testSelfUnion.

public void testSelfUnion() {
    AbstractPlanNode pn = compile("select B from T2 UNION select B from T2");
    assertTrue(pn.getChild(0) instanceof UnionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn.getChildCount() == 2);
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    assertTrue(pn.getChild(1) instanceof SeqScanPlanNode);
    // The same table/alias is repeated twice in the union but in the different selects
    pn = compile("select A1.B from T2 A1, T2 A2 WHERE A1.B = A2.B UNION select B from T2 A1");
    assertTrue(pn.getChild(0) instanceof UnionPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn.getChildCount() == 2);
    assertTrue(pn.getChild(0) instanceof ProjectionPlanNode);
    assertTrue(pn.getChild(0).getChild(0) instanceof NestLoopPlanNode);
    assertTrue(pn.getChild(1) instanceof SeqScanPlanNode);
    // BOTH sides are single-partitioned  for the same partition
    pn = compile("select F from T1 WHERE T1.A = 2 UNION select F from T1 WHERE T1.A = 2");
    assertTrue(pn.getChild(0) instanceof UnionPlanNode);
    // If BOTH sides are single-partitioned, but for different partitions,
    // it would theoretically be possible to satisfy
    // the query with a 2-fragment plan IFF the coordinator fragment could be forced to
    // execute on one of the designated single partitions.
    // At this point, coordinator designation is only supported for single-fragment plans.
    failToCompile("select DESC from T1 WHERE A = 1 UNION select DESC from T1 WHERE A = 2");
    // If both sides are multi-partitioned, there is no facility for pushing down the
    // union processing below the send/receive, so each child of the union requires
    // its own send/receive so the plan ends up as an unsupported 3-fragment plan.
    failToCompile("select DESC from T1 UNION select DESC from T1");
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SeqScanPlanNode(org.voltdb.plannodes.SeqScanPlanNode) UnionPlanNode(org.voltdb.plannodes.UnionPlanNode) NestLoopPlanNode(org.voltdb.plannodes.NestLoopPlanNode) ProjectionPlanNode(org.voltdb.plannodes.ProjectionPlanNode)

Aggregations

NestLoopPlanNode (org.voltdb.plannodes.NestLoopPlanNode)38 AbstractPlanNode (org.voltdb.plannodes.AbstractPlanNode)37 AbstractExpression (org.voltdb.expressions.AbstractExpression)23 SeqScanPlanNode (org.voltdb.plannodes.SeqScanPlanNode)19 ProjectionPlanNode (org.voltdb.plannodes.ProjectionPlanNode)13 IndexScanPlanNode (org.voltdb.plannodes.IndexScanPlanNode)11 NestLoopIndexPlanNode (org.voltdb.plannodes.NestLoopIndexPlanNode)11 SendPlanNode (org.voltdb.plannodes.SendPlanNode)8 MergeReceivePlanNode (org.voltdb.plannodes.MergeReceivePlanNode)6 OrderByPlanNode (org.voltdb.plannodes.OrderByPlanNode)5 ReceivePlanNode (org.voltdb.plannodes.ReceivePlanNode)5 SchemaColumn (org.voltdb.plannodes.SchemaColumn)5 HashAggregatePlanNode (org.voltdb.plannodes.HashAggregatePlanNode)4 AggregatePlanNode (org.voltdb.plannodes.AggregatePlanNode)3 NodeSchema (org.voltdb.plannodes.NodeSchema)3 TupleValueExpression (org.voltdb.expressions.TupleValueExpression)2 AbstractJoinPlanNode (org.voltdb.plannodes.AbstractJoinPlanNode)2 AbstractReceivePlanNode (org.voltdb.plannodes.AbstractReceivePlanNode)2 WindowFunctionPlanNode (org.voltdb.plannodes.WindowFunctionPlanNode)2 ArrayList (java.util.ArrayList)1