use of org.voltdb.plannodes.ProjectionPlanNode 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);
}
use of org.voltdb.plannodes.ProjectionPlanNode 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");
}
use of org.voltdb.plannodes.ProjectionPlanNode in project voltdb by VoltDB.
the class TestPlansSubQueries method testEdgeCases.
public void testEdgeCases() {
AbstractPlanNode pn;
String[] outputSchema;
String sql, sqlNoSimplification, equivalentSql;
sql = "select T1.A, T2.A FROM (SELECT A FROM R1) T1, (SELECT A FROM R2)T2 ";
sqlNoSimplification = "select T1.A, T2.A FROM (SELECT A FROM R1 LIMIT 1) T1, (SELECT A A FROM R2 LIMIT 1)T2 ";
equivalentSql = "select T1.A, T2.A FROM R1 T1, R2 T2";
outputSchema = new String[] { "T1.A", "T2.A" };
checkEdgeCases(sqlNoSimplification, outputSchema, "T1", "A", "T2", "A", "R1", "A", "R2", "A");
checkSubquerySimplification(sql, equivalentSql);
// Quick tests of some past spectacular planner failures that sqlcoverage uncovered.
sql = "SELECT 1, * FROM (select * from R1) T1, R2 T2 WHERE T2.A < 3737632230784348203";
sqlNoSimplification = "SELECT 1, * FROM (select * from R1 LIMIT 5) T1, R2 T2 WHERE T2.A < 3737632230784348203";
equivalentSql = "SELECT 1, * FROM R1 T1, R2 T2 WHERE T2.A < 3737632230784348203";
pn = compile(sqlNoSimplification);
assertTrue(pn.getChild(0) instanceof ProjectionPlanNode);
checkSubquerySimplification(sql, equivalentSql);
sql = "SELECT 2, * FROM (select * from R1) T1, R2 T2 WHERE CASE WHEN T2.A > 44 THEN T2.C END < 44 + 10";
sqlNoSimplification = "SELECT 2, * FROM (select * from R1 LIMIT 5) T1, R2 T2 WHERE CASE WHEN T2.A > 44 THEN T2.C END < 44 + 10";
equivalentSql = "SELECT 2, * FROM R1 T1, R2 T2 WHERE CASE WHEN T2.A > 44 THEN T2.C END < 44 + 10";
pn = compile(sqlNoSimplification);
assertTrue(pn.getChild(0) instanceof ProjectionPlanNode);
checkSubquerySimplification(sql, equivalentSql);
sql = "SELECT -8, T2.C FROM (select * from R1) T1, R1 T2 WHERE (T2.C + 5 ) > 44";
sqlNoSimplification = "SELECT -8, T2.C FROM (select * from R1 LIMIT 5) T1, R1 T2 WHERE (T2.C + 5 ) > 44";
equivalentSql = "SELECT -8, T2.C FROM R1 T1, R1 T2 WHERE (T2.C + 5 ) > 44";
pn = compile(sqlNoSimplification);
//* enable to debug */ System.out.println(pn.toExplainPlanString());
assertTrue(pn.getChild(0) instanceof ProjectionPlanNode);
checkSubquerySimplification(sql, equivalentSql);
}
use of org.voltdb.plannodes.ProjectionPlanNode 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.ProjectionPlanNode in project voltdb by VoltDB.
the class TestSelfJoins method testIndexedSelfJoin.
public void testIndexedSelfJoin() {
AbstractPlanNode.enableVerboseExplainForDebugging();
IndexScanPlanNode c;
AbstractPlanNode apn;
AbstractPlanNode pn;
NestLoopIndexPlanNode nlij;
List<AbstractExpression> searchKeys;
// SELF JOIN using two different indexes on the same table
// sometimes with a surviving sort ordering that supports GROUP BY and/or ORDER BY.
apn = compile("select * FROM R2 A, R2 B WHERE A.A = B.A AND B.C > 1 ORDER BY B.C");
//* for debug */ System.out.println(apn.toExplainPlanString());
// Some day, the wasteful projection node will not be here to skip.
pn = apn.getChild(0).getChild(0);
assertTrue(pn instanceof NestLoopIndexPlanNode);
nlij = (NestLoopIndexPlanNode) pn;
assertNull(nlij.getPreJoinPredicate());
assertNull(nlij.getJoinPredicate());
assertNull(nlij.getWherePredicate());
assertEquals(1, nlij.getChildCount());
c = (IndexScanPlanNode) nlij.getChild(0);
assertNull(c.getPredicate());
assertEquals(IndexLookupType.GT, c.getLookupType());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof ConstantValueExpression);
c = (IndexScanPlanNode) nlij.getInlinePlanNode(PlanNodeType.INDEXSCAN);
assertEquals(IndexLookupType.GTE, c.getLookupType());
assertNull(c.getPredicate());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof TupleValueExpression);
apn = compile("select * FROM R2 A, R2 B WHERE A.A = B.A AND B.C > 1 ORDER BY B.A, B.C");
//* for debug */ System.out.println(apn.toExplainPlanString());
// Some day, the wasteful projection node will not be here to skip.
pn = apn.getChild(0).getChild(0);
assertTrue(pn instanceof OrderByPlanNode);
pn = pn.getChild(0);
assertTrue(pn instanceof NestLoopIndexPlanNode);
nlij = (NestLoopIndexPlanNode) pn;
assertNull(nlij.getPreJoinPredicate());
assertNull(nlij.getJoinPredicate());
assertNull(nlij.getWherePredicate());
assertEquals(1, nlij.getChildCount());
c = (IndexScanPlanNode) nlij.getChild(0);
assertNull(c.getPredicate());
assertEquals(IndexLookupType.GT, c.getLookupType());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof ConstantValueExpression);
c = (IndexScanPlanNode) nlij.getInlinePlanNode(PlanNodeType.INDEXSCAN);
assertEquals(IndexLookupType.GTE, c.getLookupType());
assertNull(c.getPredicate());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof TupleValueExpression);
apn = compile("select * FROM R2 A, R2 B WHERE A.A = B.A AND B.A > 1 ORDER BY B.A, B.C");
//* for debug */ System.out.println(apn.toExplainPlanString());
// Some day, the wasteful projection node will not be here to skip.
pn = apn.getChild(0).getChild(0);
assertTrue(pn instanceof NestLoopIndexPlanNode);
nlij = (NestLoopIndexPlanNode) pn;
assertNull(nlij.getPreJoinPredicate());
assertNull(nlij.getJoinPredicate());
assertNull(nlij.getWherePredicate());
assertEquals(1, nlij.getChildCount());
assertTrue(nlij.getChild(0) instanceof IndexScanPlanNode);
c = (IndexScanPlanNode) nlij.getChild(0);
assertEquals(IndexLookupType.GT, c.getLookupType());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof ConstantValueExpression);
c = (IndexScanPlanNode) nlij.getInlinePlanNode(PlanNodeType.INDEXSCAN);
assertEquals(IndexLookupType.GTE, c.getLookupType());
assertNull(c.getPredicate());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof TupleValueExpression);
apn = compile("select B.C, MAX(A.C) FROM R2 A, R2 B WHERE A.A = B.A AND B.C > 1 GROUP BY B.C ORDER BY B.C");
//* for debug */ System.out.println(apn.toExplainPlanString());
// Some day, the wasteful projection node will not be here to skip.
pn = apn.getChild(0);
assertNotNull(AggregatePlanNode.getInlineAggregationNode(pn));
assertTrue(pn instanceof NestLoopIndexPlanNode);
nlij = (NestLoopIndexPlanNode) pn;
assertNull(nlij.getPreJoinPredicate());
assertNull(nlij.getJoinPredicate());
assertNull(nlij.getWherePredicate());
assertEquals(1, nlij.getChildCount());
c = (IndexScanPlanNode) nlij.getChild(0);
assertNull(c.getPredicate());
assertEquals(IndexLookupType.GT, c.getLookupType());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof ConstantValueExpression);
c = (IndexScanPlanNode) nlij.getInlinePlanNode(PlanNodeType.INDEXSCAN);
assertEquals(IndexLookupType.GTE, c.getLookupType());
assertNull(c.getPredicate());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof TupleValueExpression);
apn = compile("select B.C, B.A FROM R2 A, R2 B WHERE A.A = B.A AND B.C > 1 GROUP BY B.A, B.C ORDER BY B.A, B.C");
//* for debug */ System.out.println(apn.toExplainPlanString());
// Some day, the wasteful projection node will not be here to skip.
pn = apn.getChild(0).getChild(0);
assertTrue(pn instanceof OrderByPlanNode);
pn = pn.getChild(0);
assertNotNull(AggregatePlanNode.getInlineAggregationNode(pn));
assertTrue(pn instanceof NestLoopIndexPlanNode);
nlij = (NestLoopIndexPlanNode) pn;
assertNull(nlij.getPreJoinPredicate());
assertNull(nlij.getJoinPredicate());
assertNull(nlij.getWherePredicate());
assertEquals(1, nlij.getChildCount());
c = (IndexScanPlanNode) nlij.getChild(0);
assertNull(c.getPredicate());
assertEquals(IndexLookupType.GT, c.getLookupType());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof ConstantValueExpression);
c = (IndexScanPlanNode) nlij.getInlinePlanNode(PlanNodeType.INDEXSCAN);
assertEquals(IndexLookupType.GTE, c.getLookupType());
assertNull(c.getPredicate());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof TupleValueExpression);
apn = compile("select B.C, B.A FROM R2 A, R2 B WHERE A.A = B.A AND B.A > 1 GROUP BY B.A, B.C ORDER BY B.A, B.C");
//* for debug */ System.out.println(apn.toExplainPlanString());
pn = apn.getChild(0);
assertNotNull(AggregatePlanNode.getInlineAggregationNode(pn));
assertTrue(pn instanceof NestLoopIndexPlanNode);
nlij = (NestLoopIndexPlanNode) pn;
assertNull(nlij.getPreJoinPredicate());
assertNull(nlij.getJoinPredicate());
assertNull(nlij.getWherePredicate());
assertEquals(1, nlij.getChildCount());
assertTrue(nlij.getChild(0) instanceof IndexScanPlanNode);
c = (IndexScanPlanNode) nlij.getChild(0);
assertEquals(IndexLookupType.GT, c.getLookupType());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof ConstantValueExpression);
c = (IndexScanPlanNode) nlij.getInlinePlanNode(PlanNodeType.INDEXSCAN);
assertEquals(IndexLookupType.GTE, c.getLookupType());
assertNull(c.getPredicate());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof TupleValueExpression);
// Here's a case that can't be optimized because it purposely uses the "wrong" column
// in the GROUP BY and ORDER BY.
apn = compile("select B.C FROM R2 A, R2 B WHERE B.A = A.A AND B.C > 1 GROUP BY A.A, B.C ORDER BY A.A, B.C");
//* for debug */ System.out.println(apn.toExplainPlanString());
// Complex ORDER BY case: GROUP BY column that is not in the display column list
pn = apn.getChild(0);
assertTrue(pn instanceof ProjectionPlanNode);
pn = pn.getChild(0);
assertTrue(pn instanceof OrderByPlanNode);
pn = pn.getChild(0);
assertNotNull(AggregatePlanNode.getInlineAggregationNode(pn));
assertTrue(pn instanceof NestLoopIndexPlanNode);
nlij = (NestLoopIndexPlanNode) pn;
assertNull(nlij.getPreJoinPredicate());
assertNull(nlij.getJoinPredicate());
assertNull(nlij.getWherePredicate());
assertEquals(1, nlij.getChildCount());
c = (IndexScanPlanNode) nlij.getChild(0);
assertNull(c.getPredicate());
assertEquals(IndexLookupType.GT, c.getLookupType());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof ConstantValueExpression);
c = (IndexScanPlanNode) nlij.getInlinePlanNode(PlanNodeType.INDEXSCAN);
assertEquals(IndexLookupType.GTE, c.getLookupType());
assertNull(c.getPredicate());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof TupleValueExpression);
// Here's a case that can't be optimized because it purposely uses the "wrong" column
// in the GROUP BY and ORDER BY.
apn = compile("select B.C FROM R2 A, R2 B WHERE B.A = A.A AND B.C > 1 GROUP BY A.A, B.C ORDER BY B.C");
//* for debug */ System.out.println(apn.toExplainPlanString());
// Project-first case: GROUP BY column that is not in the order by or the display column list
pn = apn.getChild(0);
assertTrue(pn instanceof OrderByPlanNode);
pn = pn.getChild(0);
//TODO: This represents a missed optimization.
// The projection could have been inlined.
assertTrue(pn instanceof ProjectionPlanNode);
pn = pn.getChild(0);
assertNotNull(AggregatePlanNode.getInlineAggregationNode(pn));
assertTrue(pn instanceof NestLoopIndexPlanNode);
nlij = (NestLoopIndexPlanNode) pn;
assertNull(nlij.getPreJoinPredicate());
assertNull(nlij.getJoinPredicate());
assertNull(nlij.getWherePredicate());
assertEquals(1, nlij.getChildCount());
c = (IndexScanPlanNode) nlij.getChild(0);
assertNull(c.getPredicate());
assertEquals(IndexLookupType.GT, c.getLookupType());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof ConstantValueExpression);
c = (IndexScanPlanNode) nlij.getInlinePlanNode(PlanNodeType.INDEXSCAN);
assertEquals(IndexLookupType.GTE, c.getLookupType());
assertNull(c.getPredicate());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof TupleValueExpression);
// This variant shows that the GROUP BY can be a permutation of the sort order
// without messing up the optimization
apn = compile("select B.C, B.A FROM R2 A, R2 B WHERE A.A = B.A AND B.A > 1 GROUP BY B.C, B.A ORDER BY B.A, B.C");
//* for debug */ System.out.println(apn.toExplainPlanString());
// Some day, the wasteful projection node will not be here to skip.
pn = apn.getChild(0);
assertNotNull(AggregatePlanNode.getInlineAggregationNode(pn));
assertTrue(pn instanceof NestLoopIndexPlanNode);
nlij = (NestLoopIndexPlanNode) pn;
assertNull(nlij.getPreJoinPredicate());
assertNull(nlij.getJoinPredicate());
assertNull(nlij.getWherePredicate());
assertEquals(1, nlij.getChildCount());
assertTrue(nlij.getChild(0) instanceof IndexScanPlanNode);
c = (IndexScanPlanNode) nlij.getChild(0);
assertEquals(IndexLookupType.GT, c.getLookupType());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof ConstantValueExpression);
c = (IndexScanPlanNode) nlij.getInlinePlanNode(PlanNodeType.INDEXSCAN);
assertEquals(IndexLookupType.GTE, c.getLookupType());
assertNull(c.getPredicate());
searchKeys = c.getSearchKeyExpressions();
assertEquals(1, searchKeys.size());
assertTrue(searchKeys.get(0) instanceof TupleValueExpression);
}
Aggregations