Search in sources :

Example 11 with SeqScanPlanNode

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

the class PlanAssembler method switchToIndexScanForGroupBy.

/**
     * For a seqscan feeding a GROUP BY, consider substituting an IndexScan
     * that pre-sorts by the GROUP BY keys.
     * If a candidate is already an indexscan,
     * simply calculate GROUP BY column coverage
     *
     * @param candidate
     * @param gbInfo
     * @return true when planner can switch to index scan
     *         from a sequential scan, and when the index scan
     *         has no parent plan node or the candidate is already
     *         an indexscan and covers all or some GROUP BY columns
     */
private boolean switchToIndexScanForGroupBy(AbstractPlanNode candidate, IndexGroupByInfo gbInfo) {
    if (!m_parsedSelect.isGrouped()) {
        return false;
    }
    if (candidate instanceof IndexScanPlanNode) {
        calculateIndexGroupByInfo((IndexScanPlanNode) candidate, gbInfo);
        if (gbInfo.m_coveredGroupByColumns != null && !gbInfo.m_coveredGroupByColumns.isEmpty()) {
            // The candidate index does cover all or some
            // of the GROUP BY columns and can be serialized
            gbInfo.m_indexAccess = candidate;
            return true;
        }
        return false;
    }
    AbstractPlanNode sourceSeqScan = findSeqScanCandidateForGroupBy(candidate);
    if (sourceSeqScan == null) {
        return false;
    }
    assert (sourceSeqScan instanceof SeqScanPlanNode);
    AbstractPlanNode parent = null;
    if (sourceSeqScan.getParentCount() > 0) {
        parent = sourceSeqScan.getParent(0);
    }
    AbstractPlanNode indexAccess = indexAccessForGroupByExprs((SeqScanPlanNode) sourceSeqScan, gbInfo);
    if (indexAccess.getPlanNodeType() != PlanNodeType.INDEXSCAN) {
        // does not find proper index to replace sequential scan
        return false;
    }
    gbInfo.m_indexAccess = indexAccess;
    if (parent != null) {
        // have a parent and would like to replace
        // the sequential scan with an index scan
        indexAccess.clearParents();
        // For two children join node, index 0 is its outer side
        parent.replaceChild(0, indexAccess);
        return false;
    }
    // parent is null and switched to index scan from sequential scan
    return true;
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SeqScanPlanNode(org.voltdb.plannodes.SeqScanPlanNode) IndexScanPlanNode(org.voltdb.plannodes.IndexScanPlanNode)

Example 12 with SeqScanPlanNode

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

the class TestPlansGroupBy method testInlineSerialAgg_noGroupBy_special.

// AVG is optimized with SUM / COUNT, generating extra projection node
// In future, inline projection for aggregation.
public void testInlineSerialAgg_noGroupBy_special() {
    AbstractPlanNode p;
    List<AbstractPlanNode> pns;
    pns = compileToFragments("SELECT AVG(A1) from T1");
    //* enable to debug */ printExplainPlan(pns);
    p = pns.get(0).getChild(0);
    assertTrue(p instanceof ProjectionPlanNode);
    assertTrue(p.getChild(0) instanceof AggregatePlanNode);
    assertTrue(p.getChild(0).getChild(0) instanceof ReceivePlanNode);
    p = pns.get(1).getChild(0);
    assertTrue(p instanceof SeqScanPlanNode);
    assertNotNull(p.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertNotNull(p.getInlinePlanNode(PlanNodeType.AGGREGATE));
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SeqScanPlanNode(org.voltdb.plannodes.SeqScanPlanNode) HashAggregatePlanNode(org.voltdb.plannodes.HashAggregatePlanNode) AggregatePlanNode(org.voltdb.plannodes.AggregatePlanNode) ReceivePlanNode(org.voltdb.plannodes.ReceivePlanNode) AbstractReceivePlanNode(org.voltdb.plannodes.AbstractReceivePlanNode) ProjectionPlanNode(org.voltdb.plannodes.ProjectionPlanNode)

Example 13 with SeqScanPlanNode

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

the class TestPlansJoin method perJoinOpTestScanJoinConditions.

private void perJoinOpTestScanJoinConditions(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    SeqScanPlanNode seqScan;
    AbstractExpression predicate;
    boolean theOpIsOnTheLeft;
    query = "SELECT * FROM R1, R2 WHERE R1.A" + joinOp + "R2.A AND R1.C > 0";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) node.getChild(0);
    assertEquals("R1", seqScan.getTargetTableName());
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    query = "SELECT * FROM R1, R2 WHERE R1.A" + joinOp + "R2.A AND R1.C > R2.C";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    theOpIsOnTheLeft = (predicate != null) && (predicate.getLeft() != null) && predicate.getLeft().getExpressionType() == joinOp.toOperator();
    assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, (theOpIsOnTheLeft ? joinOp.toOperator() : ExpressionType.COMPARE_LESSTHAN), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, (theOpIsOnTheLeft ? ExpressionType.COMPARE_LESSTHAN : joinOp.toOperator()), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertNull(seqScan.getPredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertNull(seqScan.getPredicate());
    query = "SELECT * FROM R1 JOIN R2 ON R1.A" + joinOp + "R2.A WHERE R1.C > 0";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertEquals("R1", seqScan.getTargetTableName());
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertEquals("R2", seqScan.getTargetTableName());
    assertNull(seqScan.getPredicate());
    query = "SELECT * FROM R1 JOIN R2 ON R1.A" + joinOp + "R2.A WHERE R1.C > R2.C";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    theOpIsOnTheLeft = (predicate != null) && (predicate.getLeft() != null) && predicate.getLeft().getExpressionType() == joinOp.toOperator();
    assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, (theOpIsOnTheLeft ? joinOp.toOperator() : ExpressionType.COMPARE_LESSTHAN), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, (theOpIsOnTheLeft ? ExpressionType.COMPARE_LESSTHAN : joinOp.toOperator()), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertNull(seqScan.getPredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertNull(seqScan.getPredicate());
    query = "SELECT * FROM R1, R2, R3 WHERE R1.A" + joinOp + "R2.A AND R1.C" + joinOp + "R3.C AND R1.A > 0";
    pn = compileToTopDownTree(query, 7, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    // Validate trivial child 1 before child 0 to free up local variable nlj.
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertEquals("R3", seqScan.getTargetTableName());
    assertNull(seqScan.getPredicate());
    nlj = (NestLoopPlanNode) nlj.getChild(0);
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertEquals("R1", seqScan.getTargetTableName());
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertEquals("R2", seqScan.getTargetTableName());
    assertNull(seqScan.getPredicate());
    query = "SELECT * FROM R1 JOIN R2 ON R1.A" + joinOp + "R2.A AND R1.C" + joinOp + "R2.C WHERE R1.A > 0";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertEquals("R1", seqScan.getTargetTableName());
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertEquals("R2", seqScan.getTargetTableName());
    assertNull(seqScan.getPredicate());
    query = "SELECT A, C FROM R1 JOIN R2 USING (A, C)";
    pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    query = "SELECT A, C FROM R1 JOIN R2 USING (A, C) WHERE A > 0";
    pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, 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);
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertNull(seqScan.getPredicate());
    query = "SELECT * FROM R1 JOIN R2 ON R1.A" + joinOp + "R2.A JOIN R3 ON R1.C" + joinOp + "R3.C WHERE R1.A > 0";
    pn = compileToTopDownTree(query, 7, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    // Validate trivial child 1 before child 0 to free up local variable nlj.
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertEquals("R3", seqScan.getTargetTableName());
    assertNull(seqScan.getPredicate());
    nlj = (NestLoopPlanNode) nlj.getChild(0);
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertEquals("R1", seqScan.getTargetTableName());
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertEquals("R2", seqScan.getTargetTableName());
    assertNull(seqScan.getPredicate());
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SeqScanPlanNode(org.voltdb.plannodes.SeqScanPlanNode) AbstractExpression(org.voltdb.expressions.AbstractExpression) NestLoopPlanNode(org.voltdb.plannodes.NestLoopPlanNode)

Example 14 with SeqScanPlanNode

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

the class TestPlansJoin method perJoinOpTestBasicOuterJoin.

private void perJoinOpTestBasicOuterJoin(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    SeqScanPlanNode seqScan;
    AbstractExpression predicate;
    // SELECT * with ON clause should return all columns from all tables
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(JoinType.LEFT, nlj.getJoinType());
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertEquals("R1", seqScan.getTargetTableName());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertEquals("R2", seqScan.getTargetTableName());
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C AND R1.A = 5";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(JoinType.LEFT, nlj.getJoinType());
    predicate = nlj.getPreJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertEquals("R1", seqScan.getTargetTableName());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertEquals("R2", seqScan.getTargetTableName());
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SeqScanPlanNode(org.voltdb.plannodes.SeqScanPlanNode) AbstractExpression(org.voltdb.expressions.AbstractExpression) NestLoopPlanNode(org.voltdb.plannodes.NestLoopPlanNode)

Example 15 with SeqScanPlanNode

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

the class TestPlansJoin method perJoinOpTestOuterJoinSimplification.

private void perJoinOpTestOuterJoinSimplification(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    SeqScanPlanNode seqScan;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    NestLoopIndexPlanNode nlij;
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE R2.C IS NOT NULL";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE R2.C > 0";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 RIGHT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE R1.C > 0";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.C" + joinOp + "R3.C WHERE R3.A > 0";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.INDEXSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.C" + joinOp + "R3.A WHERE R3.A > 0";
    if (joinOp == JoinOp.EQUAL) {
        // weaken test for now
        pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
        node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
        nlij = (NestLoopIndexPlanNode) node;
        assertEquals(nlij.getJoinType(), JoinType.INNER);
    }
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) < 10";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 RIGHT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) < 10";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) < 10";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    query = "SELECT * FROM R1 RIGHT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) < 10";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) < 10 AND R1.C = 3";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R2.C) <  10 OR R2.C IS NOT NULL";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) <  10 AND R1.C > 3";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.C" + joinOp + "R2.C WHERE ABS(R1.C) <  10 OR R2.C IS NOT NULL";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    // Test with seqscan with different filers.
    query = "SELECT R2.A, R1.* FROM R1 LEFT OUTER JOIN R2 ON R2.A" + joinOp + "R1.A WHERE R2.A > 3";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    //* enable for debug */ System.out.println(pn.toExplainPlanString());
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.INNER);
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(nlj.getWherePredicate());
    query = "SELECT R2.A, R1.* FROM R1 LEFT OUTER JOIN R2 ON R2.A" + joinOp + "R1.A WHERE R2.A IS NULL";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    assertEquals(nlj.getJoinType(), JoinType.LEFT);
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = nlj.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertNull(seqScan.getPredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertNull(seqScan.getPredicate());
    if (joinOp != JoinOp.EQUAL) {
        // weaken test for now
        return;
    }
    query = "SELECT b.A, a.* FROM R1 a LEFT OUTER JOIN R4 b ON b.A" + joinOp + "a.A AND b.C " + joinOp + " a.C AND a.D " + joinOp + " b.D WHERE b.A IS NULL";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    //* enable for debug */ System.out.println(pn.toExplainPlanString());
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertEquals(nlij.getJoinType(), JoinType.LEFT);
    assertNull(nlij.getPreJoinPredicate());
    assertNull(nlij.getJoinPredicate());
    predicate = nlij.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, ExpressionType.CONJUNCTION_AND, ExpressionType.CONJUNCTION_AND, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(indexScan.getPredicate());
    query = "SELECT b.A, a.* FROM R1 a LEFT OUTER JOIN R4 b ON b.A" + joinOp + "a.A AND b.C " + joinOp + " a.C AND a.D " + joinOp + " b.D WHERE b.B + b.A IS NULL";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertEquals(nlij.getJoinType(), JoinType.LEFT);
    assertNull(nlij.getPreJoinPredicate());
    assertNull(nlij.getJoinPredicate());
    predicate = nlij.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.OPERATOR_PLUS, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    query = "SELECT a.* FROM R1 a LEFT OUTER JOIN R5 b ON b.A" + joinOp + "a.A WHERE b.A IS NULL";
    pn = compileToTopDownTree(query, 3, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertEquals(nlij.getJoinType(), JoinType.LEFT);
    assertNull(nlij.getPreJoinPredicate());
    assertNull(nlij.getJoinPredicate());
    predicate = nlij.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE);
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SeqScanPlanNode(org.voltdb.plannodes.SeqScanPlanNode) AbstractExpression(org.voltdb.expressions.AbstractExpression) IndexScanPlanNode(org.voltdb.plannodes.IndexScanPlanNode) NestLoopPlanNode(org.voltdb.plannodes.NestLoopPlanNode) NestLoopIndexPlanNode(org.voltdb.plannodes.NestLoopIndexPlanNode)

Aggregations

SeqScanPlanNode (org.voltdb.plannodes.SeqScanPlanNode)49 AbstractPlanNode (org.voltdb.plannodes.AbstractPlanNode)44 AbstractExpression (org.voltdb.expressions.AbstractExpression)26 NestLoopPlanNode (org.voltdb.plannodes.NestLoopPlanNode)19 IndexScanPlanNode (org.voltdb.plannodes.IndexScanPlanNode)14 ProjectionPlanNode (org.voltdb.plannodes.ProjectionPlanNode)9 NestLoopIndexPlanNode (org.voltdb.plannodes.NestLoopIndexPlanNode)8 AggregatePlanNode (org.voltdb.plannodes.AggregatePlanNode)6 AbstractScanPlanNode (org.voltdb.plannodes.AbstractScanPlanNode)5 OrderByPlanNode (org.voltdb.plannodes.OrderByPlanNode)5 AbstractSubqueryExpression (org.voltdb.expressions.AbstractSubqueryExpression)4 TupleValueExpression (org.voltdb.expressions.TupleValueExpression)4 ReceivePlanNode (org.voltdb.plannodes.ReceivePlanNode)4 SchemaColumn (org.voltdb.plannodes.SchemaColumn)4 ArrayList (java.util.ArrayList)3 Index (org.voltdb.catalog.Index)3 ComparisonExpression (org.voltdb.expressions.ComparisonExpression)3 StmtTableScan (org.voltdb.planner.parseinfo.StmtTableScan)3 LimitPlanNode (org.voltdb.plannodes.LimitPlanNode)3 NodeSchema (org.voltdb.plannodes.NodeSchema)3