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

Example 36 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));
}

Example 37 with SeqScanPlanNode

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

the class SubPlanAssembler method getScanAccessPlanForTable.

/**
     * Get a sequential scan access plan for a table. For multi-site plans/tables,
     * scans at all partitions.
     *
     * @param table The table to scan.
     * @param path The access path to access the data in the table (index/scan/etc).
     * @return A scan plan node
     */
private static AbstractScanPlanNode getScanAccessPlanForTable(StmtTableScan tableScan, AccessPath path) {
    // build the scan node
    SeqScanPlanNode scanNode = new SeqScanPlanNode(tableScan);
    // build the predicate
    scanNode.setPredicate(path.otherExprs);
    return scanNode;
}

Example 38 with SeqScanPlanNode

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

the class ScanDeterminizer method recursivelyApply.

private static AbstractPlanNode recursivelyApply(AbstractPlanNode plan) {
    assert (plan != null);
    // depth first:
    //     Find Sequential Scan node.
    //     Replace with any unique tree index scan if possible.
    ArrayList<AbstractPlanNode> children = new ArrayList<AbstractPlanNode>();
    for (int i = 0; i < plan.getChildCount(); i++) {
        children.add(plan.getChild(i));
    }
    for (AbstractPlanNode child : children) {
        // TODO this will break when children feed multiple parents
        AbstractPlanNode newChild = recursivelyApply(child);
        // Do a graft into the (parent) plan only if a replacement for a child was found.
        if (newChild == child) {
            continue;
        }
        boolean replaced = plan.replaceChild(child, newChild);
        assert (replaced);
    }
    // skip the meat if this isn't a scan node
    if (!(plan instanceof SeqScanPlanNode)) {
        return plan;
    }
    SeqScanPlanNode scanNode = (SeqScanPlanNode) plan;
    if (scanNode.isSubQuery()) {
        // This is a sub-query and can't have indexes
        return plan;
    }
    // got here? we're got ourselves a sequential scan over a real table
    assert (scanNode.getChildCount() == 0);
    StmtTableScan tableScan = scanNode.getTableScan();
    assert (tableScan != null);
    Index indexToScan = null;
    // does anything for performance at all.
    for (Index index : tableScan.getIndexes()) {
        // skip non-unique indexes
        if (index.getUnique() == false) {
            continue;
        } else // skip hash indexes
        if (index.getType() != IndexType.BALANCED_TREE.getValue()) {
            continue;
        } else // skip partial indexes
        if (!index.getPredicatejson().isEmpty()) {
            continue;
        } else if (indexToScan == null || CatalogUtil.getCatalogIndexSize(indexToScan) > CatalogUtil.getCatalogIndexSize(index)) {
            indexToScan = index;
        }
    }
    if (indexToScan == null) {
        return plan;
    }
    // make an index node from the scan node
    IndexScanPlanNode indexScanNode = new IndexScanPlanNode(scanNode, null, indexToScan, SortDirectionType.ASC);
    indexScanNode.setForDeterminismOnly();
    return indexScanNode;
}

Example 39 with SeqScanPlanNode

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

the class TestPlansInExistsSubQueries method testHavingInSubquery.

public void testHavingInSubquery() {
    String sql;
    AbstractPlanNode pn;
    AggregatePlanNode aggNode;
    List<AbstractExpression> args;
    AbstractExpression pred;
    AbstractSubqueryExpression sqe;
    AbstractExpression postExpr;
    AbstractExpression re;
    AbstractExpression le;
    // filter on agg of expression involving grand-parent tve
    sql = "select a from r1 where exists " + "(select 1 from r2 where exists  " + " (select 1 from r3 group by c having min(a) > r1.d)) ";
    pn = compile(sql);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    pred = ((SeqScanPlanNode) pn).getPredicate();
    // child
    assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
    sqe = (AbstractSubqueryExpression) pred.getLeft();
    //* enable to debug */ System.out.println(se.explain(""));
    args = sqe.getArgs();
    assertEquals(1, args.size());
    assertEquals(1, sqe.getParameterIdxList().size());
    assertEquals("D", ((TupleValueExpression) args.get(0)).getColumnName());
    pn = sqe.getSubqueryNode();
    assertTrue(pn instanceof SeqScanPlanNode);
    pred = ((SeqScanPlanNode) pn).getPredicate();
    // grand child
    assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
    sqe = (AbstractSubqueryExpression) pred.getLeft();
    pn = sqe.getSubqueryNode();
    pn = pn.getChild(0).getChild(0);
    aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
    assertNotNull(aggNode);
    postExpr = aggNode.getPostPredicate();
    assertNotNull(postExpr);
    assertEquals(ExpressionType.COMPARE_GREATERTHAN, postExpr.getExpressionType());
    re = postExpr.getRight();
    assertEquals(ExpressionType.VALUE_PARAMETER, re.getExpressionType());
    assertEquals(new Integer(0), ((ParameterValueExpression) re).getParameterIndex());
    // filter on agg of expression involving parent tve
    sql = "select a from r1 where c in " + " (select max(c) from r2 group by e having min(a) > r1.d)";
    pn = compile(sql);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    pred = ((SeqScanPlanNode) pn).getPredicate();
    assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
    sqe = (AbstractSubqueryExpression) pred.getLeft();
    args = sqe.getArgs();
    assertEquals(2, args.size());
    assertEquals(2, sqe.getParameterIdxList().size());
    assertEquals("D", ((TupleValueExpression) args.get(0)).getColumnName());
    assertEquals("C", ((TupleValueExpression) args.get(1)).getColumnName());
    pn = sqe.getSubqueryNode();
    pn = pn.getChild(0);
    assertTrue(pn instanceof LimitPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
    assertNotNull(aggNode);
    assertEquals(3, aggNode.getOutputSchema().size());
    postExpr = aggNode.getPostPredicate();
    assertEquals(ExpressionType.CONJUNCTION_AND, postExpr.getExpressionType());
    le = postExpr.getLeft();
    assertEquals(ExpressionType.COMPARE_GREATERTHAN, le.getExpressionType());
    assertEquals(new Integer(0), ((ParameterValueExpression) le.getRight()).getParameterIndex());
    re = postExpr.getRight();
    assertEquals(ExpressionType.COMPARE_EQUAL, re.getExpressionType());
    assertEquals(new Integer(1), ((ParameterValueExpression) re.getLeft()).getParameterIndex());
    // filter on agg of expression involving user parameter ('?')
    sql = "select a from r1 where c in " + " (select max(c) from r2 group by e having min(a) > ?) ";
    pn = compile(sql);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    pred = ((SeqScanPlanNode) pn).getPredicate();
    assertEquals(ExpressionType.OPERATOR_EXISTS, pred.getExpressionType());
    sqe = (AbstractSubqueryExpression) pred.getLeft();
    assertEquals(1, sqe.getParameterIdxList().size());
    assertEquals(new Integer(1), sqe.getParameterIdxList().get(0));
    pn = sqe.getSubqueryNode();
    pn = pn.getChild(0).getChild(0);
    assertEquals(PlanNodeType.SEQSCAN, pn.getPlanNodeType());
    aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
    assertNotNull(aggNode);
    postExpr = aggNode.getPostPredicate();
    assertEquals(ExpressionType.CONJUNCTION_AND, postExpr.getExpressionType());
    // User PVE
    le = postExpr.getLeft();
    assertEquals(ExpressionType.COMPARE_GREATERTHAN, le.getExpressionType());
    assertEquals(ExpressionType.VALUE_PARAMETER, le.getRight().getExpressionType());
    assertEquals(new Integer(0), ((ParameterValueExpression) le.getRight()).getParameterIndex());
    // Parent PVE
    re = postExpr.getRight();
    assertEquals(ExpressionType.COMPARE_EQUAL, re.getExpressionType());
    assertEquals(ExpressionType.VALUE_PARAMETER, re.getLeft().getExpressionType());
    assertEquals(new Integer(1), ((ParameterValueExpression) re.getLeft()).getParameterIndex());
    // filter on agg of local tve
    sql = "select a from r1 where c in " + " (select max(c) from r2 group by e having min(a) > 0)";
    pn = compile(sql);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    AbstractExpression p = ((SeqScanPlanNode) pn).getPredicate();
    assertEquals(ExpressionType.OPERATOR_EXISTS, p.getExpressionType());
    AbstractExpression subquery = p.getLeft();
    pn = ((AbstractSubqueryExpression) subquery).getSubqueryNode();
    pn = pn.getChild(0);
    assertTrue(pn instanceof LimitPlanNode);
    pn = pn.getChild(0);
    assertTrue(pn instanceof SeqScanPlanNode);
    aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
    assertNotNull(aggNode);
    assertEquals(3, aggNode.getOutputSchema().size());
    postExpr = aggNode.getPostPredicate();
    assertEquals(ExpressionType.CONJUNCTION_AND, postExpr.getExpressionType());
    failToCompile("select max(c) from r1 group by a " + " having count(*) = (select c from r2 where r2.c = r1.a)", HavingErrorMsg);
/**
         * Uncomment these tests when ENG-8306 is finished
         */
//        // parent correlated TVE in the aggregate expression.
//        sql = "select max(c) from r1 group by a " +
//                " having count(*) = (select c from r2 where r2.c = r1.a)";
//        pn = compile(sql);
//        pn = pn.getChild(0);
//        assertTrue(pn instanceof ProjectionPlanNode);
//        pn = pn.getChild(0);
//        assertTrue(pn instanceof SeqScanPlanNode);
//        aggNode = AggregatePlanNode.getInlineAggregationNode(pn);
//        assertNotNull(aggNode);
//        assertNotNull(aggNode instanceof HashAggregatePlanNode);
//        assertEquals(3, aggNode.getOutputSchema().size()); // group by key, max, count
//
//        postExpr = aggNode.getPostPredicate();
//        assertEquals(ExpressionType.COMPARE_EQUAL, postExpr.getExpressionType());
//        assertTrue(postExpr.getLeft() instanceof TupleValueExpression);
//        assertTrue(postExpr.getRight() instanceof SelectSubqueryExpression);
}

Example 40 with SeqScanPlanNode

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

the class TestPlansJoin method perJoinOpTestIndexJoinConditions.

private void perJoinOpTestIndexJoinConditions(JoinOp joinOp) {
    String query;
    AbstractPlanNode pn;
    AbstractPlanNode node;
    NestLoopPlanNode nlj;
    NestLoopIndexPlanNode nlij;
    IndexScanPlanNode indexScan;
    AbstractExpression predicate;
    SeqScanPlanNode seqScan;
    query = "SELECT * FROM R3, R2 WHERE R3.A" + joinOp + "R2.A AND R3.C > 0 AND R2.C >= 5";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertNull(nlij.getJoinPredicate());
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHANOREQUALTO, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    query = "SELECT * FROM R3 JOIN R2 ON R3.A" + joinOp + "R2.A WHERE R3.C > 0 AND R2.C >= 5";
    pn = compileToTopDownTree(query, 4, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertNull(nlij.getJoinPredicate());
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHANOREQUALTO, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    query = "SELECT * FROM R3 JOIN R2 USING(A) WHERE R3.C > 0 AND R2.C >= 5";
    pn = compileToTopDownTree(query, 3, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX, PlanNodeType.SEQSCAN);
    node = followAssertedLeftChain(pn, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOPINDEX);
    nlij = (NestLoopIndexPlanNode) node;
    assertNull(nlij.getJoinPredicate());
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHANOREQUALTO, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    query = "SELECT * FROM R3 JOIN R2 ON R3.A" + joinOp + " R2.A JOIN R1 ON R2.A" + joinOp + "R1.A WHERE R3.C > 0 AND R2.C >= 5";
    pn = compileToTopDownTree(query, 7, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.NESTLOOPINDEX, 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());
    nlij = (NestLoopIndexPlanNode) nlj.getChild(0);
    indexScan = nlij.getInlineIndexScan();
    assertEquals(IndexLookupType.EQ, indexScan.getLookupType());
    predicate = indexScan.getEndExpression();
    assertExprTopDownTree(predicate, joinOp.toOperator(), ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = indexScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    seqScan = (SeqScanPlanNode) nlij.getChild(0);
    predicate = seqScan.getPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHANOREQUALTO, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
}