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

Example 16 with NestLoopPlanNode

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

Example 17 with NestLoopPlanNode

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

the class TestWindowedFunctions method validateWindowedFunctionPlan.

/**
     * Validate that each similar windowed query in testRank produces a similar
     * plan, with the expected minor variation to its ORDER BY node.
     * @param windowedQuery a variant of a test query of a known basic format
     * @param nSorts the expected number of sort criteria that should have been
     *        extracted from the variant query's PARTITION BY and ORDER BY.
     * @param descSortIndex the position among the sort criteria of the original
     *        ORDER BY column, always distinguishable by its "DESC" direction.
     **/
private void validateWindowedFunctionPlan(String windowedQuery, int nSorts, int descSortIndex, int numPartitionExprs, ExpressionType winOpType) {
    // Sometimes we get multi-fragment nodes when we
    // expect single fragment nodes.  Keeping all the fragments
    // helps to diagnose the problem.
    List<AbstractPlanNode> nodes = compileToFragments(windowedQuery);
    assertEquals(1, nodes.size());
    AbstractPlanNode node = nodes.get(0);
    // The plan should look like:
    // SendNode -> ProjectionPlanNode -> PartitionByPlanNode -> OrderByPlanNode -> SeqScanNode
    // We also do some sanity checking on the PartitionPlan node.
    // First dissect the plan.
    assertTrue(node instanceof SendPlanNode);
    AbstractPlanNode projPlanNode = node.getChild(0);
    assertTrue(projPlanNode instanceof ProjectionPlanNode);
    AbstractPlanNode windowFuncPlanNode = projPlanNode.getChild(0);
    assertTrue(windowFuncPlanNode instanceof WindowFunctionPlanNode);
    AbstractPlanNode abstractOrderByNode = windowFuncPlanNode.getChild(0);
    assertTrue(abstractOrderByNode instanceof OrderByPlanNode);
    OrderByPlanNode orderByNode = (OrderByPlanNode) abstractOrderByNode;
    NodeSchema input_schema = orderByNode.getOutputSchema();
    assertNotNull(input_schema);
    AbstractPlanNode seqScanNode = orderByNode.getChild(0);
    assertTrue(seqScanNode instanceof SeqScanPlanNode || seqScanNode instanceof NestLoopPlanNode);
    WindowFunctionPlanNode wfPlanNode = (WindowFunctionPlanNode) windowFuncPlanNode;
    NodeSchema schema = wfPlanNode.getOutputSchema();
    //
    // Check that the window function plan node's output schema is correct.
    // Look at the first expression, to verify that it's the windowed expression.
    // Then check that the TVEs all make sense.
    //
    SchemaColumn column = schema.getColumns().get(0);
    assertEquals("ARANK", column.getColumnAlias());
    assertEquals(numPartitionExprs, wfPlanNode.getPartitionByExpressions().size());
    validateTVEs(input_schema, wfPlanNode, false);
    //
    // Check that the operation is what we expect.
    //
    assertTrue(wfPlanNode.getAggregateTypes().size() > 0);
    assertEquals(winOpType, wfPlanNode.getAggregateTypes().get(0));
    //
    for (List<AbstractExpression> exprs : wfPlanNode.getAggregateExpressions()) {
        if (exprs != null) {
            for (AbstractExpression expr : exprs) {
                assertNotNull(expr.getValueType());
            }
        }
    }
    //
    // Check that the order by node has the right number of expressions.
    // and that they have the correct order.
    //
    assertEquals(nSorts, orderByNode.getSortExpressions().size());
    int sortIndex = 0;
    for (SortDirectionType direction : orderByNode.getSortDirections()) {
        SortDirectionType expected = (sortIndex == descSortIndex) ? SortDirectionType.DESC : SortDirectionType.ASC;
        assertEquals(expected, direction);
        ++sortIndex;
    }
}

Example 18 with NestLoopPlanNode

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

the class TestWindowedFunctions method validateQueryWithSubquery.

/**
     * Validate that each similar windowed query in testRankWithSubqueries
     * produces a similar plan
     * @param windowedQuery a variant of a test query of a known basic format
     **/
private void validateQueryWithSubquery(String windowedQuery, boolean waiveAliasMatch) {
    AbstractPlanNode node = compile(windowedQuery);
    // Dissect the plan.
    assertTrue(node instanceof SendPlanNode);
    AbstractPlanNode projectionPlanNode = node.getChild(0);
    assertTrue(projectionPlanNode instanceof ProjectionPlanNode);
    AbstractPlanNode partitionByPlanNode = projectionPlanNode.getChild(0);
    assertTrue(partitionByPlanNode instanceof WindowFunctionPlanNode);
    AbstractPlanNode orderByPlanNode = partitionByPlanNode.getChild(0);
    assertTrue(orderByPlanNode instanceof OrderByPlanNode);
    NodeSchema input_schema = orderByPlanNode.getOutputSchema();
    AbstractPlanNode scanNode = orderByPlanNode.getChild(0);
    assertTrue(scanNode instanceof NestLoopPlanNode);
    NodeSchema schema = partitionByPlanNode.getOutputSchema();
    SchemaColumn column = schema.getColumns().get(0);
    assertEquals("ARANK", column.getColumnAlias());
    validateTVEs(input_schema, (WindowFunctionPlanNode) partitionByPlanNode, waiveAliasMatch);
}

Example 19 with NestLoopPlanNode

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

the class TestSelfJoins method testOuterSelfJoin.

public void testOuterSelfJoin() {
    // A.C = B.C Inner-Outer join Expr stays at the NLJ as Join predicate
    // A.A > 1 Outer Join Expr stays at the the NLJ as pre-join predicate
    // B.A < 0 Inner Join Expr is pushed down to the inner SeqScan node
    AbstractPlanNode pn = compile("select * FROM R1 A LEFT JOIN R1 B ON A.C = B.C AND A.A > 1 AND B.A < 0");
    pn = pn.getChild(0).getChild(0);
    assertTrue(pn instanceof NestLoopPlanNode);
    NestLoopPlanNode nl = (NestLoopPlanNode) pn;
    assertNotNull(nl.getPreJoinPredicate());
    AbstractExpression p = nl.getPreJoinPredicate();
    assertEquals(ExpressionType.COMPARE_GREATERTHAN, p.getExpressionType());
    assertNotNull(nl.getJoinPredicate());
    p = nl.getJoinPredicate();
    assertEquals(ExpressionType.COMPARE_EQUAL, p.getExpressionType());
    assertNull(nl.getWherePredicate());
    assertEquals(2, nl.getChildCount());
    SeqScanPlanNode c = (SeqScanPlanNode) nl.getChild(0);
    assertNull(c.getPredicate());
    c = (SeqScanPlanNode) nl.getChild(1);
    assertNotNull(c.getPredicate());
    p = c.getPredicate();
    assertEquals(ExpressionType.COMPARE_LESSTHAN, p.getExpressionType());
}

Example 20 with NestLoopPlanNode

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

the class TestPlansJoin method testDisplayColumnFromUsingCondition.

public void testDisplayColumnFromUsingCondition() {
    String query;
    List<AbstractPlanNode> lpn;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    AbstractExpression predicate;
    SeqScanPlanNode seqScan;
    SchemaColumn sc0;
    List<SchemaColumn> selectColumns;
    query = "SELECT max(A) FROM R1 JOIN R2 USING(A)";
    pn = compileToTopDownTree(query, 1, PlanNodeType.SEND, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    selectColumns = pn.getOutputSchema().getColumns();
    for (SchemaColumn sc : selectColumns) {
        AbstractExpression e = sc.getExpression();
        assertTrue(e instanceof TupleValueExpression);
        TupleValueExpression tve = (TupleValueExpression) e;
        assertNotSame(-1, tve.getColumnIndex());
    }
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.NESTLOOP);
    assertNotNull(AggregatePlanNode.getInlineAggregationNode(node));
    query = "SELECT distinct(A) FROM R1 JOIN R2 USING(A)";
    pn = compileToTopDownTree(query, 1, PlanNodeType.SEND, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    selectColumns = pn.getOutputSchema().getColumns();
    for (SchemaColumn sc : selectColumns) {
        AbstractExpression e = sc.getExpression();
        assertTrue(e instanceof TupleValueExpression);
        TupleValueExpression tve = (TupleValueExpression) e;
        assertNotSame(-1, tve.getColumnIndex());
    }
    query = "SELECT A FROM R1 JOIN R2 USING(A) ORDER BY A";
    pn = compileToTopDownTree(query, 1, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.ORDERBY, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    selectColumns = pn.getOutputSchema().getColumns();
    for (SchemaColumn sc : selectColumns) {
        AbstractExpression e = sc.getExpression();
        assertTrue(e instanceof TupleValueExpression);
        TupleValueExpression tve = (TupleValueExpression) e;
        assertNotSame(-1, tve.getColumnIndex());
    }
    query = "SELECT * FROM P1 LABEL JOIN R2 USING(A) " + "WHERE A > 0 AND R2.C >= 5";
    lpn = compileToFragments(query);
    assertProjectingCoordinator(lpn);
    pn = lpn.get(1);
    assertTopDownTree(pn, PlanNodeType.SEND, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(lpn.get(1), PlanNodeType.SEND, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_GREATERTHANOREQUALTO, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertNull(seqScan.getPredicate());
    query = "SELECT * FROM P1 LABEL LEFT JOIN R2 USING(A) WHERE A > 0";
    lpn = compileToFragments(query);
    node = followAssertedLeftChain(lpn.get(1), PlanNodeType.SEND, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertTrue(JoinType.LEFT == nlj.getJoinType());
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    query = "SELECT A FROM R2 LABEL RIGHT JOIN P1 AP1 USING(A) WHERE A > 0";
    lpn = compileToFragments(query);
    assertProjectingCoordinator(lpn);
    pn = lpn.get(0);
    selectColumns = pn.getOutputSchema().getColumns();
    assertEquals(1, selectColumns.size());
    sc0 = selectColumns.get(0);
    assertEquals("AP1", sc0.getTableAlias());
    assertEquals("P1", sc0.getTableName());
    pn = lpn.get(1);
    assertTopDownTree(pn, PlanNodeType.SEND, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(lpn.get(1), PlanNodeType.SEND, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(JoinType.LEFT, nlj.getJoinType());
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    selectColumns = seqScan.getOutputSchema().getColumns();
    assertEquals(1, selectColumns.size());
    sc0 = selectColumns.get(0);
    assertEquals("AP1", sc0.getTableAlias());
    assertEquals("P1", sc0.getTableName());
}