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

Example 16 with SeqScanPlanNode

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

the class TestPlansJoin method perJoinOpTestBasicIndexOuterJoin.

private void perJoinOpTestBasicIndexOuterJoin(JoinOp joinOp) {
    // R3 is indexed but it's the outer table and the join expression
    // must stay at the NLJ so the index can't be used
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopIndexPlanNode nlij;
    NestLoopPlanNode nlj;
    SeqScanPlanNode seqScan;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    query = "SELECT * FROM R3 LEFT JOIN R2 ON R3.A" + joinOp + "R2.C";
    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(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("R3", seqScan.getTargetTableName());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertEquals("R2", seqScan.getTargetTableName());
    // R3 is indexed but it's the outer table so index can't be used
    query = "SELECT * FROM R2 RIGHT JOIN R3 ON R3.A" + joinOp + "R2.C";
    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(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("R3", seqScan.getTargetTableName());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertEquals("R2", seqScan.getTargetTableName());
    if (joinOp != JoinOp.EQUAL) {
        // weaken test for now
        return;
    }
    query = "SELECT * FROM R2 LEFT JOIN R3 ON R2.C" + joinOp + "R3.A";
    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(JoinType.LEFT, nlij.getJoinType());
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    assertEquals("R2", seqScan.getTargetTableName());
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    assertNull(indexScan.getPredicate());
    assertEquals("R3", indexScan.getTargetTableName());
}

Example 17 with SeqScanPlanNode

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

the class TestPlansJoin method perJoinOpTestRightOuterJoin.

private void perJoinOpTestRightOuterJoin(JoinOp joinOp) {
    String query;
    List<AbstractPlanNode> lpn;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    SeqScanPlanNode seqScan;
    AbstractExpression predicate;
    // SELECT * FROM R1 RIGHT JOIN R2 ON R1.C " +
    // joinOp + "R2.C => SELECT * FROM R2 LEFT JOIN R1 ON R1.C" + joinOp + "R2.C
    query = "SELECT * FROM R1 RIGHT 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("R2", seqScan.getTargetTableName());
    seqScan = (SeqScanPlanNode) nlj.getChild(1);
    assertEquals("R1", seqScan.getTargetTableName());
    // Same but with distributed table
    query = "SELECT * FROM P1 RIGHT JOIN R2 ON P1.C" + joinOp + "R2.C";
    lpn = compileToFragments(query);
    assertReplicatedLeftJoinCoordinator(lpn, "R2");
    pn = lpn.get(1);
    assertTopDownTree(pn, PlanNodeType.SEND, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.SEQSCAN);
    seqScan = (SeqScanPlanNode) node;
    assertEquals("P1", seqScan.getTargetTableName());
}

Example 18 with SeqScanPlanNode

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

the class TestPlansSubQueries method testParameters.

public void testParameters() {
    AbstractPlanNode pn = compile("select A1 FROM (SELECT A A1 FROM R1 WHERE A > ? LIMIT 10) TEMP WHERE A1 < ?");
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    AbstractExpression p = ((SeqScanPlanNode) pn).getPredicate();
    assertTrue(p != null);
    assertTrue(p instanceof ComparisonExpression);
    AbstractExpression cp = p.getLeft();
    assertTrue(cp instanceof TupleValueExpression);
    cp = p.getRight();
    assertTrue(cp instanceof ParameterValueExpression);
    assertEquals(1, ((ParameterValueExpression) cp).getParameterIndex().intValue());
    assertTrue(pn.getChildCount() == 1);
    assertTrue(pn.getChild(0) instanceof SeqScanPlanNode);
    SeqScanPlanNode sc = (SeqScanPlanNode) pn.getChild(0);
    assertTrue(sc.getPredicate() != null);
    p = sc.getPredicate();
    assertTrue(p instanceof ComparisonExpression);
    cp = p.getRight();
    assertTrue(cp instanceof ParameterValueExpression);
    assertEquals(0, ((ParameterValueExpression) cp).getParameterIndex().intValue());
}

Example 19 with SeqScanPlanNode

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

Example 20 with SeqScanPlanNode

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