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());
}
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());
}
}
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());
}
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());
}
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");
}
Aggregations