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

Example 1 with NestLoopIndexPlanNode

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

the class SubPlanAssembler method injectIndexedJoinWithMaterializedScan.

// Generate a plan for an IN-LIST-driven index scan
private static AbstractPlanNode injectIndexedJoinWithMaterializedScan(AbstractExpression listElements, IndexScanPlanNode scanNode) {
    MaterializedScanPlanNode matScan = new MaterializedScanPlanNode();
    assert (listElements instanceof VectorValueExpression || listElements instanceof ParameterValueExpression);
    matScan.setRowData(listElements);
    matScan.setSortDirection(scanNode.getSortDirection());
    NestLoopIndexPlanNode nlijNode = new NestLoopIndexPlanNode();
    nlijNode.setJoinType(JoinType.INNER);
    nlijNode.addInlinePlanNode(scanNode);
    nlijNode.addAndLinkChild(matScan);
    // resolve the sort direction
    nlijNode.resolveSortDirection();
    return nlijNode;
}

Example 2 with NestLoopIndexPlanNode

use of org.voltdb.plannodes.NestLoopIndexPlanNode 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 3 with NestLoopIndexPlanNode

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

Example 4 with NestLoopIndexPlanNode

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

Example 5 with NestLoopIndexPlanNode

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

the class TestIndexSelection method testEng3850ComplexIndexablePlan.

// This tests recognition of a complex expression value
// -- an addition -- used as an indexable join key's search key value.
// Some time ago, this would throw a casting error in the planner.
public void testEng3850ComplexIndexablePlan() {
    AbstractPlanNode pn = compile("select id from a, t where a.id < (t.a + ?);");
    pn = pn.getChild(0);
    pn = pn.getChild(0);
    //*enable to debug*/System.out.println("DEBUG: " + pn.toExplainPlanString());
    assertTrue(pn instanceof NestLoopIndexPlanNode);
    IndexScanPlanNode indexScan = ((NestLoopIndexPlanNode) pn).getInlineIndexScan();
    assertEquals(IndexLookupType.LT, indexScan.getLookupType());
    assertEquals(HSQLInterface.AUTO_GEN_NAMED_CONSTRAINT_IDX + "ID", indexScan.getTargetIndexName());
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    SeqScanPlanNode sspn = (SeqScanPlanNode) pn;
    //*enable to debug*/System.out.println("DEBUG: " + pn.toJSONString());
    assertEquals("T", sspn.getTargetTableName());
}