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

Example 96 with AbstractPlanNode

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

the class TestPlansScalarSubQueries method testWhereGreaterRow.

public void testWhereGreaterRow() {
    AbstractPlanNode pn = compile("select r5.c from r5 where (a,c) > (select r1.a, r1.c from r1);");
    pn = pn.getChild(0);
    assertTrue(pn instanceof AbstractScanPlanNode);
    AbstractExpression pred = ((AbstractScanPlanNode) pn).getPredicate();
    assertEquals(ExpressionType.COMPARE_GREATERTHAN, pred.getExpressionType());
    assertEquals(ExpressionType.ROW_SUBQUERY, pred.getLeft().getExpressionType());
    assertEquals(ExpressionType.SELECT_SUBQUERY, pred.getRight().getExpressionType());
}

Example 97 with AbstractPlanNode

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

the class TestPlansSubQueries method testParameters.

public void testParameters() {
    AbstractPlanNode pn = compile("select A1 FROM (SELECT A A1 FROM R1 WHERE A > ? LIMIT 10) TEMP WHERE A1 < ?");
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    AbstractExpression p = ((SeqScanPlanNode) pn).getPredicate();
    assertTrue(p != null);
    assertTrue(p instanceof ComparisonExpression);
    AbstractExpression cp = p.getLeft();
    assertTrue(cp instanceof TupleValueExpression);
    cp = p.getRight();
    assertTrue(cp instanceof ParameterValueExpression);
    assertEquals(1, ((ParameterValueExpression) cp).getParameterIndex().intValue());
    assertTrue(pn.getChildCount() == 1);
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    SeqScanPlanNode sc = (SeqScanPlanNode) pn.getChild(0);
    assertTrue(sc.getPredicate() != null);
    p = sc.getPredicate();
    assertTrue(p instanceof ComparisonExpression);
    cp = p.getRight();
    assertTrue(cp instanceof ParameterValueExpression);
    assertEquals(0, ((ParameterValueExpression) cp).getParameterIndex().intValue());
}

Example 98 with AbstractPlanNode

use of org.voltdb.plannodes.AbstractPlanNode 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());
}

Example 99 with AbstractPlanNode

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

the class TestPlansSubQueries method testPartitionedCrossLevel.

public void testPartitionedCrossLevel() {
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    AbstractPlanNode nlpn;
    String sql, sqlNoSimplification, equivalentSql;
    sql = "SELECT T1.A, T1.C, P2.D FROM P2, (SELECT A, C FROM P1) T1 " + "where T1.A = P2.A ";
    sqlNoSimplification = "SELECT T1.A, T1.C, P2.D FROM P2, (SELECT DISTINCT A, C FROM P1 ) T1 " + "where T1.A = P2.A ";
    equivalentSql = "SELECT T1.A, T1.C, P2.D FROM P2, P1 T1 WHERE T1.A = P2.A";
    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 NestLoopIndexPlanNode);
    assertEquals(JoinType.INNER, ((NestLoopIndexPlanNode) nlpn).getJoinType());
    pn = nlpn.getChild(0);
    checkSeqScan(pn, "T1", "A", "C");
    checkFragmentCount("SELECT P2.A, P2.C FROM P2, (SELECT A, C FROM P1 GROUP BY A) T1 " + "where T1.A = P2.A and P2.A = 1", 1);
    checkFragmentCount("SELECT P2.A, P2.C FROM P2, (SELECT A, C FROM P1) T1 " + "where T1.A = P2.A and P2.A = 1", 1);
    checkFragmentCount("SELECT P2.A, P2.C FROM P2, (SELECT A, C, A + 1 FROM P1) T1 " + "where T1.A = P2.A and T1.A = 1", 1);
    checkFragmentCount("SELECT P2.A, P2.C FROM P2, (SELECT A, C FROM P1) T1 " + "where T1.A = P2.A and T1.A = 1", 1);
    checkFragmentCount("SELECT P2.A, P2.C FROM P2, (SELECT A, C, A + 1 FROM P1 where P1.A = 3) T1 " + "where T1.A = P2.A ", 1);
    checkFragmentCount("SELECT P2.A, P2.C FROM P2, (SELECT A, C FROM P1 where P1.A = 3) T1 " + "where T1.A = P2.A ", 1);
    // Distributed join
    checkFragmentCount("select D1, D2 " + "FROM (SELECT A, D + 1 D1 FROM P1 ) T1, (SELECT A, D + 2 D2 FROM P2 ) T2 " + "WHERE T1.A = T2.A", 2);
    checkFragmentCount("select D1, D2 " + "FROM (SELECT A, D D1 FROM P1 ) T1, (SELECT A, D D2 FROM P2 ) T2 " + "WHERE T1.A = T2.A", 2);
    checkFragmentCount("select D1, P2.D " + "FROM (SELECT A, D + 1 D1 FROM P1 WHERE A=1) T1, P2 " + "WHERE T1.A = P2.A AND P2.A = 1", 1);
    checkFragmentCount("select D1, P2.D " + "FROM (SELECT A, D D1 FROM P1 WHERE A=1) T1, P2 " + "WHERE T1.A = P2.A AND P2.A = 1", 1);
    checkFragmentCount("select T1.A, T1.C, T1.SD FROM " + "(SELECT A, C, SUM(D) as SD FROM P1 WHERE A > 3 GROUP BY A, C) T1, P2 WHERE T1.A = P2.A", 2);
    // (1) Multiple level subqueries (recursive) partition detecting
    checkFragmentCount("select * from p2, " + "(select * from (SELECT A, D + 1 D1 FROM P1) T1) T2 where p2.A = T2.A", 2);
    checkFragmentCount("select * from p2, " + "(select * from (SELECT A, D D1 FROM P1) T1) T2 where p2.A = T2.A", 2);
    checkFragmentCount("select * from p2, " + "(select * from (SELECT A, D + 1 D1 FROM P1 WHERE A=2) T1) T2 " + "where p2.A = T2.A ", 1);
    checkFragmentCount("select * from p2, " + "(select * from (SELECT A, D D1 FROM P1 WHERE A=2) T1) T2 " + "where p2.A = T2.A ", 1);
    checkFragmentCount("select * from p2, " + "(select * from (SELECT P1.A, P1.D FROM P1, P3 where P1.A = P3.A) T1) T2 " + "where p2.A = T2.A", 2);
    checkFragmentCount("select * from p2, " + "(select * from (SELECT P1.A, P1.D FROM P1, P3 where P1.A = P3.A) T1) T2 " + "where p2.A = T2.A and P2.A = 1", 1);
    // (2) Multiple subqueries on the same level partition detecting
    planNodes = compileToFragments("select D1, D2 FROM " + "(SELECT A, D + 1 D1 FROM P1 WHERE A=2) T1, " + "(SELECT A, D + 1 D2 FROM P2 WHERE A=2) T2");
    assertEquals(1, planNodes.size());
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A, D + 1 D1 FROM P1 WHERE A=2) T1, " + "(SELECT A, D + 1 D2 FROM P2) T2 where T2.A = 2", 1);
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A, D D1 FROM P1 WHERE A=2) T1, " + "(SELECT A, D D2 FROM P2) T2 where T2.A = 2", 1);
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A, D + 1 D1 FROM P1) T1, " + "(SELECT A, D + 1 D2 FROM P2 WHERE A=2) T2 where T1.A = 2", 1);
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A, D D1 FROM P1) T1, " + "(SELECT A, D D2 FROM P2 WHERE A=2) T2 where T1.A = 2", 1);
    // partitioned column renaming tests
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A A1, D + 1 D1 FROM P1) T1, " + "(SELECT A, D + 1 D2 FROM P2 WHERE A=2) T2 where T1.A1 = 2", 1);
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A A1, D D1 FROM P1) T1, " + "(SELECT A, D D2 FROM P2 WHERE A=2) T2 where T1.A1 = 2", 1);
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A, D + 1 D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D + 1 D2 FROM P2 ) T2 where T2.A2 = 2", 1);
    checkFragmentCount("select D1, D2 FROM " + "(SELECT A, D D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D D2 FROM P2 ) T2 where T2.A2 = 2", 1);
    checkFragmentCount("select A1, A2, D1, D2 " + "FROM (SELECT A A1, D + 1 D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D + 1 D2 FROM P2) T2 where T2.A2=2", 1);
    checkFragmentCount("select A1, A2, D1, D2 " + "FROM (SELECT A A1, D D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D D2 FROM P2) T2 where T2.A2=2", 1);
    checkFragmentCount("select A1, A2, D1, D2 " + "FROM (SELECT A A1, D + 1 D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D + 1 D2 FROM P2) T2 where T2.A2=2", 1);
    checkFragmentCount("select A1, A2, D1, D2 " + "FROM (SELECT A A1, D D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D D2 FROM P2) T2 where T2.A2=2", 1);
    // Test with LIMIT
    failToCompile("select A1, A2, D1, D2 " + "FROM (SELECT A A1, D D1 FROM P1 WHERE A=2) T1, " + "(SELECT A A2, D D2 FROM P2 ORDER BY D LIMIT 3) T2 where T2.A2=2", joinErrorMsg);
}

Example 100 with AbstractPlanNode

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

the class TestPlansSubQueries method testReplicatedGroupbyLIMIT.

public void testReplicatedGroupbyLIMIT() {
    AbstractPlanNode pn;
    AbstractPlanNode aggNode;
    pn = compile("select A, C FROM (SELECT * FROM R1 WHERE A > 3 Limit 3) T1 ");
    pn = pn.getChild(0);
    checkSeqScan(pn, "T1", "A", "C");
    pn = pn.getChild(0);
    checkSeqScan(pn, "R1", "A", "C", "D");
    checkPredicateComparisonExpression(pn, "R1");
    assertEquals(((SeqScanPlanNode) pn).getInlinePlanNodes().size(), 2);
    assertNotNull(((SeqScanPlanNode) pn).getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(((SeqScanPlanNode) pn).getInlinePlanNode(PlanNodeType.LIMIT));
    // inline limit and projection node.
    pn = compile("select A, SUM(D) FROM (SELECT A, D FROM R1 WHERE A > 3 Limit 3 ) T1 Group by A");
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    aggNode = pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE);
    assertNotNull(aggNode);
    pn = pn.getChild(0);
    checkSeqScan(pn, "R1", "A", "D");
    checkPredicateComparisonExpression(pn, "R1");
    assertEquals(((SeqScanPlanNode) pn).getInlinePlanNodes().size(), 2);
    assertNotNull(((SeqScanPlanNode) pn).getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(((SeqScanPlanNode) pn).getInlinePlanNode(PlanNodeType.LIMIT));
    // add order by node, without inline limit and projection node.
    pn = compile("select A, SUM(D) FROM (SELECT A, D FROM R1 WHERE A > 3 ORDER BY D Limit 3 ) T1 Group by A");
    pn = pn.getChild(0);
    aggNode = pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE);
    assertNotNull(aggNode);
    checkSeqScan(pn, "T1");
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    // inline limit with order by
    assertTrue(pn instanceof OrderByPlanNode);
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.LIMIT));
    pn = pn.getChild(0);
    checkSeqScan(pn, "R1", "A", "D");
    checkPredicateComparisonExpression(pn, "R1");
    assertEquals(((SeqScanPlanNode) pn).getInlinePlanNodes().size(), 1);
    assertNotNull(((SeqScanPlanNode) pn).getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = compile("select A, SUM(D) FROM (SELECT A, D FROM R1 WHERE A > 3 ORDER BY D Limit 3 ) T1 Group by A HAVING SUM(D) < 3");
    pn = pn.getChild(0);
    if (pn instanceof ProjectionPlanNode) {
        pn = pn.getChild(0);
    }
    aggNode = pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE);
    assertNotNull(aggNode);
    assertNotNull(((HashAggregatePlanNode) aggNode).getPostPredicate());
    checkSeqScan(pn, "T1");
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    // inline limit with order by
    assertTrue(pn instanceof OrderByPlanNode);
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.LIMIT));
    pn = pn.getChild(0);
    checkSeqScan(pn, "R1", "A", "D");
    checkPredicateComparisonExpression(pn, "R1");
    assertEquals(((SeqScanPlanNode) pn).getInlinePlanNodes().size(), 1);
    assertNotNull(((SeqScanPlanNode) pn).getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = compile("select A, SUM(D)*COUNT(*) FROM (SELECT A, D FROM R1 WHERE A > 3 ORDER BY D Limit 3 ) T1 Group by A HAVING SUM(D) < 3");
    pn = pn.getChild(0);
    // complex aggregation
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    aggNode = pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE);
    assertNotNull(aggNode);
    assertNotNull(((HashAggregatePlanNode) aggNode).getPostPredicate());
    checkSeqScan(pn, "T1");
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    // inline limit with order by
    assertTrue(pn instanceof OrderByPlanNode);
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.LIMIT));
    pn = pn.getChild(0);
    checkSeqScan(pn, "R1", "A", "D");
    checkPredicateComparisonExpression(pn, "R1");
    assertEquals(((SeqScanPlanNode) pn).getInlinePlanNodes().size(), 1);
    assertNotNull(((SeqScanPlanNode) pn).getInlinePlanNode(PlanNodeType.PROJECTION));
    pn = compile("select A, SUM(D) FROM (SELECT A, D FROM R1 WHERE A > 3 ORDER BY D Limit 3 ) T1 Group by A HAVING AVG(D) < 3");
    pn = pn.getChild(0);
    // complex aggregation
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    aggNode = pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE);
    assertNotNull(aggNode);
    assertNotNull(((HashAggregatePlanNode) aggNode).getPostPredicate());
    checkSeqScan(pn, "T1");
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    // inline limit with order by
    assertTrue(pn instanceof OrderByPlanNode);
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.LIMIT));
    pn = pn.getChild(0);
    checkSeqScan(pn, "R1", "A", "D");
    checkPredicateComparisonExpression(pn, "R1");
    assertEquals(((SeqScanPlanNode) pn).getInlinePlanNodes().size(), 1);
    assertNotNull(((SeqScanPlanNode) pn).getInlinePlanNode(PlanNodeType.PROJECTION));
    // Aggregation inside of the from clause
    pn = compile("select A FROM (SELECT A, SUM(C) FROM R1 WHERE A > 3 GROUP BY A ORDER BY A Limit 3) T1 ");
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    checkSeqScan(pn, "T1", "A");
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    // inline limit with order by
    assertTrue(pn instanceof OrderByPlanNode);
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.LIMIT));
    pn = pn.getChild(0);
    aggNode = pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE);
    assertNotNull(aggNode);
    assertTrue(pn instanceof SeqScanPlanNode);
    checkSeqScan(pn, "R1");
    pn = compile("select SC, SUM(A) as SA FROM (SELECT A, SUM(C) as SC, MAX(D) as MD FROM R1 " + "WHERE A > 3 GROUP BY A ORDER BY A Limit 3) T1  " + "Group by SC");
    pn = pn.getChild(0);
    aggNode = pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE);
    assertNotNull(aggNode);
    assertTrue(pn instanceof SeqScanPlanNode);
    checkSeqScan(pn, "T1");
    pn = pn.getChild(0);
    assertTrue(pn instanceof ProjectionPlanNode);
    pn = pn.getChild(0);
    // inline limit with order by
    assertTrue(pn instanceof OrderByPlanNode);
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.LIMIT));
    pn = pn.getChild(0);
    assertTrue(pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE) != null);
    assertTrue(pn instanceof SeqScanPlanNode);
    checkSeqScan(pn, "R1");
}