Search in sources :

Example 16 with ProjectionPlanNode

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);
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SendPlanNode(org.voltdb.plannodes.SendPlanNode) MergeReceivePlanNode(org.voltdb.plannodes.MergeReceivePlanNode) ReceivePlanNode(org.voltdb.plannodes.ReceivePlanNode) ProjectionPlanNode(org.voltdb.plannodes.ProjectionPlanNode) NestLoopIndexPlanNode(org.voltdb.plannodes.NestLoopIndexPlanNode)

Example 17 with ProjectionPlanNode

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");
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SeqScanPlanNode(org.voltdb.plannodes.SeqScanPlanNode) OrderByPlanNode(org.voltdb.plannodes.OrderByPlanNode) ProjectionPlanNode(org.voltdb.plannodes.ProjectionPlanNode)

Example 18 with ProjectionPlanNode

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);
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) ProjectionPlanNode(org.voltdb.plannodes.ProjectionPlanNode)

Example 19 with ProjectionPlanNode

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());
    }
}
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 20 with ProjectionPlanNode

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);
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) TupleValueExpression(org.voltdb.expressions.TupleValueExpression) AbstractExpression(org.voltdb.expressions.AbstractExpression) OrderByPlanNode(org.voltdb.plannodes.OrderByPlanNode) IndexScanPlanNode(org.voltdb.plannodes.IndexScanPlanNode) ConstantValueExpression(org.voltdb.expressions.ConstantValueExpression) NestLoopIndexPlanNode(org.voltdb.plannodes.NestLoopIndexPlanNode) ProjectionPlanNode(org.voltdb.plannodes.ProjectionPlanNode)

Aggregations

ProjectionPlanNode (org.voltdb.plannodes.ProjectionPlanNode)51 AbstractPlanNode (org.voltdb.plannodes.AbstractPlanNode)48 OrderByPlanNode (org.voltdb.plannodes.OrderByPlanNode)25 HashAggregatePlanNode (org.voltdb.plannodes.HashAggregatePlanNode)17 SendPlanNode (org.voltdb.plannodes.SendPlanNode)16 AbstractScanPlanNode (org.voltdb.plannodes.AbstractScanPlanNode)14 AggregatePlanNode (org.voltdb.plannodes.AggregatePlanNode)14 MergeReceivePlanNode (org.voltdb.plannodes.MergeReceivePlanNode)13 NestLoopPlanNode (org.voltdb.plannodes.NestLoopPlanNode)13 ReceivePlanNode (org.voltdb.plannodes.ReceivePlanNode)13 AbstractExpression (org.voltdb.expressions.AbstractExpression)10 NodeSchema (org.voltdb.plannodes.NodeSchema)9 SeqScanPlanNode (org.voltdb.plannodes.SeqScanPlanNode)9 IndexScanPlanNode (org.voltdb.plannodes.IndexScanPlanNode)8 SchemaColumn (org.voltdb.plannodes.SchemaColumn)7 TupleValueExpression (org.voltdb.expressions.TupleValueExpression)5 AbstractReceivePlanNode (org.voltdb.plannodes.AbstractReceivePlanNode)5 LimitPlanNode (org.voltdb.plannodes.LimitPlanNode)4 NestLoopIndexPlanNode (org.voltdb.plannodes.NestLoopIndexPlanNode)4 Table (org.voltdb.catalog.Table)3