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

Example 46 with SeqScanPlanNode

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

the class TestPlansSubQueries method testDistinct.

/**
     * MANY of these DISTINCT use cases could be supported, some quite easily.
     * The cases that we can not support are those that require a join on
     * partition key AFTER a global distinct operation.
     * Other cases where the DISTINCT can be executed locally -- because it
     * contains the partition key are the most trivial.
     * TODO: make the planner smarter to plan these kind of sub-queries.
     */
public void testDistinct() {
    AbstractPlanNode pn;
    List<AbstractPlanNode> planNodes;
    planNodes = compileToFragments("SELECT * FROM (SELECT A, C, SUM(distinct D) FROM P2 GROUP BY A, C) T1, R1 where T1.A = R1.A ");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0).getChild(0);
    assertFalse(pn.toExplainPlanString().contains("DISTINCT"));
    pn = planNodes.get(1).getChild(0);
    // this join can be pushed down.
    //* enable to debug */ System.out.println(pn.toExplainPlanString());
    assertTrue(pn.toExplainPlanString().contains("LOOP INNER JOIN"));
    pn = pn.getChild(0);
    // This is a trivial subquery result scan.
    assertTrue(pn instanceof SeqScanPlanNode);
    pn = pn.getChild(0);
    // This is the subquery plan.
    checkPrimaryKeyIndexScan(pn, "P2");
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.PROJECTION));
    assertTrue(pn.toExplainPlanString().contains("SUM DISTINCT(P2.D"));
    // verify the optimized plan without sub-query like the one above
    planNodes = compileToFragments("SELECT P2.A, P2.C, SUM(distinct P2.D) FROM P2, R1 WHERE P2.A = R1.A GROUP BY P2.A, P2.C");
    assertEquals(2, planNodes.size());
    pn = planNodes.get(0);
    assertTrue(pn instanceof SendPlanNode);
    assertTrue(pn.getChild(0) instanceof ReceivePlanNode);
    pn = planNodes.get(1).getChild(0);
    assertTrue(pn instanceof NestLoopIndexPlanNode);
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.HASHAGGREGATE));
    assertNotNull(pn.getInlinePlanNode(PlanNodeType.INDEXSCAN));
    assertTrue(pn.getInlinePlanNode(PlanNodeType.INDEXSCAN).toExplainPlanString().contains("INDEX SCAN of \"P2\" using its primary key index"));
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    assertTrue(pn.getChild(0).toExplainPlanString().contains("SEQUENTIAL SCAN of \"R1\""));
    // T
    planNodes = compileToFragments("SELECT * FROM (SELECT DISTINCT A FROM P1) T1, P2 where T1.A = P2.A");
    assertEquals(2, planNodes.size());
    //* enable to debug */ System.out.println(planNodes.get(1).toExplainPlanString());
    assertFalse(planNodes.get(0).toExplainPlanString().contains("AGGREGATION"));
    assertFalse(planNodes.get(0).toExplainPlanString().contains("DISTINCT"));
    assertFalse(planNodes.get(0).toExplainPlanString().contains("JOIN"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("AGGREGATION"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("INDEX INNER JOIN"));
    // Distinct with GROUP BY
    // TODO: group by partition column cases can be supported
    String errorMessage = "This query is not plannable.  It has a subquery which needs cross-partition access.";
    failToCompile("SELECT * FROM (SELECT DISTINCT A, C FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A", errorMessage);
    failToCompile("SELECT * FROM (SELECT DISTINCT A FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A", errorMessage);
    planNodes = compileToFragments("SELECT * " + "FROM   (   SELECT T0.A, R1.C " + "           FROM   R1, " + "                  (   SELECT DISTINCT P1.A " + "                      FROM P1, R2 " + "                      WHERE P1.A = R2.A) " + "                  T0 " + "           WHERE  R1.A = T0.A ) " + "       T1, " + "       P2 " + "WHERE T1.A = P2.A");
    assertEquals(2, planNodes.size());
    //* enable to debug */ System.out.println(planNodes.get(1).toExplainPlanString());
    assertFalse(planNodes.get(0).toExplainPlanString().contains("AGGREGATION"));
    assertFalse(planNodes.get(0).toExplainPlanString().contains("DISTINCT"));
    assertFalse(planNodes.get(0).toExplainPlanString().contains("JOIN"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("AGGREGATION"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("INDEX INNER JOIN"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("LOOP INNER JOIN"));
    // Distinct without GROUP BY
    String sql1, sql2;
    sql1 = "SELECT * FROM (SELECT DISTINCT A, C FROM P1) T1, P2 where T1.A = P2.A";
    sql2 = "SELECT * FROM (SELECT A, C FROM P1 GROUP BY A, C) T1, P2 where T1.A = P2.A";
    checkQueriesPlansAreTheSame(sql1, sql2);
    sql1 = "SELECT * FROM (SELECT T0.A, R1.C FROM R1, " + "                (SELECT Distinct P1.A, P1.C FROM P1,R2 where P1.A = R2.A) T0 where R1.A = T0.A ) T1, " + "              P2 " + "where T1.A = P2.A";
    sql2 = "SELECT * FROM (SELECT T0.A, R1.C FROM R1, " + "                (SELECT P1.A, P1.C FROM P1,R2 where P1.A = R2.A group by P1.A, P1.C) T0 where R1.A = T0.A ) T1, " + "              P2 " + "where T1.A = P2.A";
    checkQueriesPlansAreTheSame(sql1, sql2);
    planNodes = compileToFragments("SELECT * FROM (SELECT DISTINCT T0.A FROM R1, " + "                (SELECT P1.A, P1.C FROM P1,R2 where P1.A = R2.A) T0 where R1.A = T0.A ) T1, " + "              P2 " + "where T1.A = P2.A");
    assertEquals(2, planNodes.size());
    //* enable to debug */ System.out.println(planNodes.get(1).toExplainPlanString());
    assertFalse(planNodes.get(0).toExplainPlanString().contains("AGGREGATION"));
    assertFalse(planNodes.get(0).toExplainPlanString().contains("DISTINCT"));
    assertFalse(planNodes.get(0).toExplainPlanString().contains("JOIN"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("AGGREGATION"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("INDEX INNER JOIN"));
    assertTrue(planNodes.get(1).toExplainPlanString().contains("LOOP INNER JOIN"));
    failToCompile("SELECT * FROM (SELECT DISTINCT A FROM P1 GROUP BY A, C) T1, P2 " + "where T1.A = P2.A");
    sql1 = "SELECT * FROM (SELECT DISTINCT T0.A, R1.C FROM R1, " + "                (SELECT P1.A, P1.C FROM P1,R2 where P1.A = R2.A) T0 where R1.A = T0.A ) T1, " + "              P2 " + "where T1.A = P2.A";
    sql2 = "SELECT * FROM (SELECT T0.A, R1.C FROM R1, " + "                (SELECT P1.A, P1.C FROM P1,R2 where P1.A = R2.A) T0 where R1.A = T0.A GROUP BY T0.A, R1.C) T1, " + "              P2 " + "where T1.A = P2.A";
    checkQueriesPlansAreTheSame(sql1, sql2);
}

Example 47 with SeqScanPlanNode

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

the class TestPlansSubQueries method checkSeqScan.

private void checkSeqScan(AbstractPlanNode scanNode, String tableAlias, String... columns) {
    assertEquals(PlanNodeType.SEQSCAN, scanNode.getPlanNodeType());
    SeqScanPlanNode snode = (SeqScanPlanNode) scanNode;
    if (tableAlias != null) {
        assertEquals(tableAlias, snode.getTargetTableAlias());
    }
    checkOutputSchema(snode, columns);
}

Example 48 with SeqScanPlanNode

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

the class TestPlansJoin method perJoinOpTestTransitiveValueEquivalenceConditions.

private void perJoinOpTestTransitiveValueEquivalenceConditions(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    SeqScanPlanNode seqScan;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    boolean theConstantIsOnTheLeft;
    // R1.A" + joinOp + "R2.A AND R2.A = 1 => R1.A = 1 AND R2.A = 1
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.A" + joinOp + "R2.A AND R2.A = 1 ";
    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;
    predicate = nlj.getPreJoinPredicate();
    theConstantIsOnTheLeft = (predicate != null) && (predicate.getLeft() != null) && (predicate.getLeft().getExpressionType() == ExpressionType.VALUE_CONSTANT);
    if (theConstantIsOnTheLeft) {
        assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_CONSTANT, ExpressionType.VALUE_TUPLE);
    } else {
        assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    }
    assertNull(nlj.getJoinPredicate());
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertNull(seqScan.getPredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    // Same test but now R2 is outer table R1.A " +
    // joinOp + "R2.A AND R2.A = 1 => R1.A = 1 AND R2.A = 1
    query = "SELECT * FROM R2 LEFT JOIN R1 ON R1.A" + joinOp + "R2.A AND R2.A = 1 ";
    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;
    predicate = nlj.getPreJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    assertNull(nlj.getJoinPredicate());
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertNull(seqScan.getPredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    // R1.A" + joinOp + "R2.A AND R2.C = 1 => R1.A " +
    // joinOp + "R2.A AND R2.C = 1
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.A" + joinOp + "R2.A AND R2.C = 1 ";
    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;
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    // R1.A" + joinOp + "R2.A AND ABS(R2.C) = 1 => R1.A " +
    // joinOp + "R2.A AND ABS(R2.C) = 1
    query = "SELECT * FROM R1 LEFT JOIN R2 ON R1.A" + joinOp + "R2.A AND ABS(R2.C) = 1 ";
    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;
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    // R1.A" + joinOp + "R3.A - NLIJ
    query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.A" + joinOp + "R3.A";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, // PlanNodeType.NESTLOOPINDEX,
    null);
    // PlanNodeType.SEQSCAN); weakened for now
    if (joinOp == JoinOp.EQUAL) {
        // weaken test for now
        node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    }
    // R1.A" + joinOp + "R3.A AND R1.A = 4 =>  R3.A = 4 AND R1.A = 4  -- NLJ/IndexScan
    query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.A" + joinOp + "R3.A AND R1.A = 4";
    pn = compileToTopDownTree(query, 5, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, // weakened for now
    null);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP);
    nlj = (NestLoopPlanNode) node;
    predicate = nlj.getPreJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    assertNull(nlj.getJoinPredicate());
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertEquals("R1", seqScan.getTargetTableName());
    assertNull(seqScan.getPredicate());
    if (joinOp == JoinOp.EQUAL) {
        // weakened for now
        indexScan = (IndexScanPlanNode) nlj.getChild(1);
        assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
        predicate = indexScan.getEndExpression();
        assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
        assertNull(indexScan.getPredicate());
        assertEquals("R3", indexScan.getTargetTableName());
    }
    // R1.A" + joinOp + "R3.A AND R3.A = 4 =>  R3.A = 4 AND R1.A = 4  -- NLJ/IndexScan
    query = "SELECT * FROM R1 LEFT JOIN R3 ON R1.A" + joinOp + "R3.A AND R3.A = 4";
    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;
    predicate = nlj.getPreJoinPredicate();
    theConstantIsOnTheLeft = (predicate != null) && (predicate.getLeft() != null) && (predicate.getLeft().getExpressionType() == ExpressionType.VALUE_CONSTANT);
    if (theConstantIsOnTheLeft) {
        assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_CONSTANT, ExpressionType.VALUE_TUPLE);
    } else {
        assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    }
    assertNull(nlj.getJoinPredicate());
    assertNull(nlj.getWherePredicate());
    seqScan = (SeqScanPlanNode) nlj.getChild(0);
    assertEquals("R1", seqScan.getTargetTableName());
    assertNull(seqScan.getPredicate());
    //        predicate = seqScan.getPredicate();
    //        assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL,
    //                ExpressionType.VALUE_TUPLE,
    //                ExpressionType.VALUE_CONSTANT);
    indexScan = (IndexScanPlanNode) nlj.getChild(1);
    assertEquals("R3", indexScan.getTargetTableName());
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    assertNull(indexScan.getPredicate());
}

Example 49 with SeqScanPlanNode

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

the class TestPlansSubQueries method checkPredicateConjunction.

private void checkPredicateConjunction(AbstractPlanNode pn, int nTerms) {
    AbstractExpression expr = ((SeqScanPlanNode) pn).getPredicate();
    assertTrue(expr instanceof ConjunctionExpression);
    assertEquals(nTerms, countTerms(expr));
}