Search in sources :

Example 6 with AbstractSubqueryExpression

use of org.voltdb.expressions.AbstractSubqueryExpression in project voltdb by VoltDB.

the class TestPlansScalarSubQueries method testSelectCorrelatedScalar.

public void testSelectCorrelatedScalar() {
    AbstractPlanNode pn = compile("select r2.c, (select d from r1 where r1.c = r2.c ) scalar from r2");
    pn = pn.getChild(0);
    assertTrue(pn instanceof AbstractScanPlanNode);
    AbstractPlanNode proj = pn.getInlinePlanNode(PlanNodeType.PROJECTION);
    NodeSchema schema = proj.getOutputSchema();
    assertEquals(2, schema.size());
    SchemaColumn col = schema.getColumns().get(1);
    assertTrue(col != null);
    assertEquals("SCALAR", col.getColumnName());
    AbstractExpression colExpr = col.getExpression();
    assertEquals(ExpressionType.VALUE_SCALAR, colExpr.getExpressionType());
    assertTrue(colExpr.getLeft() instanceof AbstractSubqueryExpression);
    AbstractSubqueryExpression subqueryExpr = (AbstractSubqueryExpression) colExpr.getLeft();
    List<Integer> params = subqueryExpr.getParameterIdxList();
    assertEquals(1, params.size());
    assertEquals(new Integer(0), params.get(0));
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) AbstractScanPlanNode(org.voltdb.plannodes.AbstractScanPlanNode) AbstractExpression(org.voltdb.expressions.AbstractExpression) AbstractSubqueryExpression(org.voltdb.expressions.AbstractSubqueryExpression) SchemaColumn(org.voltdb.plannodes.SchemaColumn) NodeSchema(org.voltdb.plannodes.NodeSchema)

Example 7 with AbstractSubqueryExpression

use of org.voltdb.expressions.AbstractSubqueryExpression in project voltdb by VoltDB.

the class SubPlanAssembler method getIndexAccessPlanForTable.

/**
     * Get an index scan access plan for a table.
     *
     * @param tableAliasIndex The table to get data from.
     * @param path The access path to access the data in the table (index/scan/etc).
     * @return An index scan plan node OR,
               in one edge case, an NLIJ of a MaterializedScan and an index scan plan node.
     */
private static AbstractPlanNode getIndexAccessPlanForTable(StmtTableScan tableScan, AccessPath path) {
    // now assume this will be an index scan and get the relevant index
    Index index = path.index;
    IndexScanPlanNode scanNode = new IndexScanPlanNode(tableScan, index);
    AbstractPlanNode resultNode = scanNode;
    // set sortDirection here because it might be used for IN list
    scanNode.setSortDirection(path.sortDirection);
    // the one element of indexExprs.
    for (AbstractExpression expr : path.indexExprs) {
        if (path.lookupType == IndexLookupType.GEO_CONTAINS) {
            scanNode.addSearchKeyExpression(expr);
            scanNode.addCompareNotDistinctFlag(false);
            continue;
        }
        AbstractExpression exprRightChild = expr.getRight();
        assert (exprRightChild != null);
        if (expr.getExpressionType() == ExpressionType.COMPARE_IN) {
            // Replace this method's result with an injected NLIJ.
            resultNode = injectIndexedJoinWithMaterializedScan(exprRightChild, scanNode);
            // Extract a TVE from the LHS MaterializedScan for use by the IndexScan in its new role.
            MaterializedScanPlanNode matscan = (MaterializedScanPlanNode) resultNode.getChild(0);
            AbstractExpression elemExpr = matscan.getOutputExpression();
            assert (elemExpr != null);
            // Replace the IN LIST condition in the end expression referencing all the list elements
            // with a more efficient equality filter referencing the TVE for each element in turn.
            replaceInListFilterWithEqualityFilter(path.endExprs, exprRightChild, elemExpr);
            // Set up the similar VectorValue --> TVE replacement of the search key expression.
            exprRightChild = elemExpr;
        }
        if (exprRightChild instanceof AbstractSubqueryExpression) {
            // DEAD CODE with the guards on index: ENG-8203
            assert (false);
        }
        scanNode.addSearchKeyExpression(exprRightChild);
        // If the index expression is an "IS NOT DISTINCT FROM" comparison, let the NULL values go through. (ENG-11096)
        scanNode.addCompareNotDistinctFlag(expr.getExpressionType() == ExpressionType.COMPARE_NOTDISTINCT);
    }
    // create the IndexScanNode with all its metadata
    scanNode.setLookupType(path.lookupType);
    scanNode.setBindings(path.bindings);
    scanNode.setEndExpression(ExpressionUtil.combinePredicates(path.endExprs));
    scanNode.setPredicate(path.otherExprs);
    // Propagate the sorting information
    // into the scan node from the access path.
    // The initial expression is needed to control a (short?) forward scan to adjust the start of a reverse
    // iteration after it had to initially settle for starting at "greater than a prefix key".
    scanNode.setInitialExpression(ExpressionUtil.combinePredicates(path.initialExpr));
    scanNode.setSkipNullPredicate();
    scanNode.setEliminatedPostFilters(path.eliminatedPostExprs);
    if (scanNode instanceof IndexSortablePlanNode) {
        IndexUseForOrderBy indexUse = ((IndexSortablePlanNode) scanNode).indexUse();
        indexUse.setWindowFunctionUsesIndex(path.m_windowFunctionUsesIndex);
        indexUse.setSortOrderFromIndexScan(path.sortDirection);
        indexUse.setWindowFunctionIsCompatibleWithOrderBy(path.m_stmtOrderByIsCompatible);
        indexUse.setFinalExpressionOrderFromIndexScan(path.m_finalExpressionOrder);
    }
    return resultNode;
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) IndexSortablePlanNode(org.voltdb.plannodes.IndexSortablePlanNode) AbstractExpression(org.voltdb.expressions.AbstractExpression) IndexScanPlanNode(org.voltdb.plannodes.IndexScanPlanNode) MaterializedScanPlanNode(org.voltdb.plannodes.MaterializedScanPlanNode) AbstractSubqueryExpression(org.voltdb.expressions.AbstractSubqueryExpression) Index(org.voltdb.catalog.Index) IndexUseForOrderBy(org.voltdb.plannodes.IndexUseForOrderBy)

Example 8 with AbstractSubqueryExpression

use of org.voltdb.expressions.AbstractSubqueryExpression in project voltdb by VoltDB.

the class TestPlansInExistsSubQueries method verifyAggregateSubquery.

private void verifyAggregateSubquery(AbstractExpression exists, int columnCount, int groupByCount, boolean hasHaving) {
    assertNotNull(exists);
    assertEquals(ExpressionType.OPERATOR_EXISTS, exists.getExpressionType());
    AbstractSubqueryExpression se = (AbstractSubqueryExpression) exists.getLeft();
    AbstractPlanNode sn = se.getSubqueryNode();
    assertTrue(sn instanceof AbstractScanPlanNode);
    AbstractPlanNode inline = sn.getInlinePlanNode(PlanNodeType.AGGREGATE);
    assertNotNull(inline);
    assertEquals(columnCount, inline.getOutputSchema().size());
    AggregatePlanNode agg = (AggregatePlanNode) inline;
    assertEquals(groupByCount, agg.getGroupByExpressions().size());
    assertEquals(hasHaving, agg.getPostPredicate() != null);
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) AbstractScanPlanNode(org.voltdb.plannodes.AbstractScanPlanNode) AggregatePlanNode(org.voltdb.plannodes.AggregatePlanNode) AbstractSubqueryExpression(org.voltdb.expressions.AbstractSubqueryExpression)

Example 9 with AbstractSubqueryExpression

use of org.voltdb.expressions.AbstractSubqueryExpression 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);
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) SeqScanPlanNode(org.voltdb.plannodes.SeqScanPlanNode) AbstractExpression(org.voltdb.expressions.AbstractExpression) AggregatePlanNode(org.voltdb.plannodes.AggregatePlanNode) AbstractSubqueryExpression(org.voltdb.expressions.AbstractSubqueryExpression) LimitPlanNode(org.voltdb.plannodes.LimitPlanNode)

Example 10 with AbstractSubqueryExpression

use of org.voltdb.expressions.AbstractSubqueryExpression in project voltdb by VoltDB.

the class TestPlansInExistsSubQueries method testNotExists.

public void testNotExists() {
    AbstractPlanNode pn = compile("select r2.c from r2 where not exists (select c from r1 where r1.c = r2.c)");
    pn = pn.getChild(0);
    assertTrue(pn instanceof AbstractScanPlanNode);
    AbstractScanPlanNode nps = (AbstractScanPlanNode) pn;
    AbstractExpression e = nps.getPredicate();
    assertEquals(ExpressionType.OPERATOR_NOT, e.getExpressionType());
    AbstractExpression le = e.getLeft();
    assertEquals(ExpressionType.OPERATOR_EXISTS, le.getExpressionType());
    AbstractSubqueryExpression subExpr = (AbstractSubqueryExpression) le.getLeft();
    assertEquals(1, subExpr.getArgs().size());
    assertEquals(1, subExpr.getParameterIdxList().size());
    assertEquals(Integer.valueOf(0), subExpr.getParameterIdxList().get(0));
}
Also used : AbstractPlanNode(org.voltdb.plannodes.AbstractPlanNode) AbstractScanPlanNode(org.voltdb.plannodes.AbstractScanPlanNode) AbstractExpression(org.voltdb.expressions.AbstractExpression) AbstractSubqueryExpression(org.voltdb.expressions.AbstractSubqueryExpression)

Aggregations

AbstractSubqueryExpression (org.voltdb.expressions.AbstractSubqueryExpression)18 AbstractExpression (org.voltdb.expressions.AbstractExpression)16 AbstractPlanNode (org.voltdb.plannodes.AbstractPlanNode)10 AbstractScanPlanNode (org.voltdb.plannodes.AbstractScanPlanNode)7 SeqScanPlanNode (org.voltdb.plannodes.SeqScanPlanNode)4 AggregatePlanNode (org.voltdb.plannodes.AggregatePlanNode)3 LimitPlanNode (org.voltdb.plannodes.LimitPlanNode)2 NodeSchema (org.voltdb.plannodes.NodeSchema)2 SchemaColumn (org.voltdb.plannodes.SchemaColumn)2 ArrayList (java.util.ArrayList)1 JSONObject (org.json_voltpatches.JSONObject)1 Index (org.voltdb.catalog.Index)1 TupleValueExpression (org.voltdb.expressions.TupleValueExpression)1 IndexScanPlanNode (org.voltdb.plannodes.IndexScanPlanNode)1 IndexSortablePlanNode (org.voltdb.plannodes.IndexSortablePlanNode)1 IndexUseForOrderBy (org.voltdb.plannodes.IndexUseForOrderBy)1 MaterializedScanPlanNode (org.voltdb.plannodes.MaterializedScanPlanNode)1