Examples with SelectSubqueryExpression org.voltdb.expressions.SelectSubqueryExpression used on opensource projects

Example 1 with SelectSubqueryExpression

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

the class AbstractParsedStmt method optimizeExistsExpression.

/**
     * Simplify the EXISTS expression:
     *  1. EXISTS ( table-agg-without-having-groupby) => TRUE
     *  2. Replace the display columns with a single dummy column "1" and GROUP BY expressions
     *  3. Drop DISTINCT expression
     *  4. Add LIMIT 1
     *  5. Remove ORDER BY expressions if HAVING expression is not present
     *
     * @param existsExpr
     * @return optimized exists expression
     */
private AbstractExpression optimizeExistsExpression(AbstractExpression existsExpr) {
    assert (ExpressionType.OPERATOR_EXISTS == existsExpr.getExpressionType());
    assert (existsExpr.getLeft() != null);
    if (existsExpr.getLeft() instanceof SelectSubqueryExpression) {
        SelectSubqueryExpression subqueryExpr = (SelectSubqueryExpression) existsExpr.getLeft();
        AbstractParsedStmt subquery = subqueryExpr.getSubqueryStmt();
        if (subquery instanceof ParsedSelectStmt) {
            ParsedSelectStmt selectSubquery = (ParsedSelectStmt) subquery;
            return selectSubquery.simplifyExistsSubqueryStmt(existsExpr);
        }
    }
    return existsExpr;
}

Example 2 with SelectSubqueryExpression

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

the class AbstractParsedStmt method parseSubqueryExpression.

/**
     * Parse an expression subquery
     */
private SelectSubqueryExpression parseSubqueryExpression(VoltXMLElement exprNode) {
    assert (exprNode.children.size() == 1);
    VoltXMLElement subqueryElmt = exprNode.children.get(0);
    AbstractParsedStmt subqueryStmt = parseSubquery(subqueryElmt);
    // add table to the query cache
    String withoutAlias = null;
    StmtSubqueryScan stmtSubqueryScan = addSubqueryToStmtCache(subqueryStmt, withoutAlias);
    // Set to the default SELECT_SUBQUERY. May be overridden depending on the context
    return new SelectSubqueryExpression(ExpressionType.SELECT_SUBQUERY, stmtSubqueryScan);
}

Example 3 with SelectSubqueryExpression

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

the class ParsedUnionStmt method breakUpSetOpSubquery.

/**
     * Break up UNION/INTERSECT (ALL) set ops into individual selects that are part
     * of the IN/EXISTS subquery into multiple expressions for each set op child
     * combined by the conjunction AND/OR expression.
     * col IN ( queryA UNION queryB ) - > col IN (queryA) OR col IN (queryB)
     * col IN ( queryA INTERSECTS queryB ) - > col IN (queryA) AND col IN (queryB)
     * The EXCEPT set op is LEFT as is
     * Also the ALL qualifier is dropped because IN/EXISTS expressions only
     * need just one tuple in the results set
     *
     * @param subqueryExpr - IN/EXISTS expression with a possible SET OP subquery
     * @return simplified expression
     */
protected static AbstractExpression breakUpSetOpSubquery(AbstractExpression expr) {
    assert (expr != null);
    SelectSubqueryExpression subqueryExpr = null;
    if (expr.getExpressionType() == ExpressionType.COMPARE_EQUAL && expr.getRight() instanceof SelectSubqueryExpression) {
        subqueryExpr = (SelectSubqueryExpression) expr.getRight();
    } else if (expr.getExpressionType() == ExpressionType.OPERATOR_EXISTS && expr.getLeft() instanceof SelectSubqueryExpression) {
        subqueryExpr = (SelectSubqueryExpression) expr.getLeft();
    }
    if (subqueryExpr == null) {
        return expr;
    }
    AbstractParsedStmt subquery = subqueryExpr.getSubqueryStmt();
    if (!(subquery instanceof ParsedUnionStmt)) {
        return expr;
    }
    ParsedUnionStmt setOpStmt = (ParsedUnionStmt) subquery;
    if (UnionType.EXCEPT == setOpStmt.m_unionType || UnionType.EXCEPT_ALL == setOpStmt.m_unionType) {
        setOpStmt.m_unionType = UnionType.EXCEPT;
        return expr;
    }
    if (UnionType.UNION_ALL == setOpStmt.m_unionType) {
        setOpStmt.m_unionType = UnionType.UNION;
    } else if (UnionType.INTERSECT_ALL == setOpStmt.m_unionType) {
        setOpStmt.m_unionType = UnionType.INTERSECT;
    }
    ExpressionType conjuctionType = (setOpStmt.m_unionType == UnionType.UNION) ? ExpressionType.CONJUNCTION_OR : ExpressionType.CONJUNCTION_AND;
    AbstractExpression retval = null;
    AbstractParsedStmt parentStmt = subquery.m_parentStmt;
    // It's a subquery which means it must have a parent
    assert (parentStmt != null);
    for (AbstractParsedStmt child : setOpStmt.m_children) {
        // add table to the query cache
        String withoutAlias = null;
        StmtSubqueryScan tableCache = parentStmt.addSubqueryToStmtCache(child, withoutAlias);
        AbstractExpression childSubqueryExpr = new SelectSubqueryExpression(subqueryExpr.getExpressionType(), tableCache);
        AbstractExpression newExpr = null;
        try {
            newExpr = expr.getExpressionType().getExpressionClass().newInstance();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage(), e);
        }
        newExpr.setExpressionType(expr.getExpressionType());
        if (ExpressionType.COMPARE_EQUAL == expr.getExpressionType()) {
            newExpr.setLeft(expr.getLeft().clone());
            newExpr.setRight(childSubqueryExpr);
            assert (newExpr instanceof ComparisonExpression);
            ((ComparisonExpression) newExpr).setQuantifier(((ComparisonExpression) expr).getQuantifier());
        } else {
            newExpr.setLeft(childSubqueryExpr);
        }
        // Recurse
        newExpr = ParsedUnionStmt.breakUpSetOpSubquery(newExpr);
        if (retval == null) {
            retval = newExpr;
        } else {
            retval = new ConjunctionExpression(conjuctionType, retval, newExpr);
        }
    }
    return retval;
}

Example 4 with SelectSubqueryExpression

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

the class AbstractParsedStmt method optimizeInExpressions.

/**
     * Perform various optimizations for IN/EXISTS subqueries if possible
     *
     * @param expr to optimize
     * @return optimized expression
     */
private AbstractExpression optimizeInExpressions(AbstractExpression expr) {
    ExpressionType exprType = expr.getExpressionType();
    if (ExpressionType.CONJUNCTION_AND == exprType || ExpressionType.CONJUNCTION_OR == exprType) {
        AbstractExpression optimizedLeft = optimizeInExpressions(expr.getLeft());
        expr.setLeft(optimizedLeft);
        AbstractExpression optimizedRight = optimizeInExpressions(expr.getRight());
        expr.setRight(optimizedRight);
        return expr;
    }
    if (ExpressionType.COMPARE_EQUAL != exprType) {
        return expr;
    }
    assert (expr instanceof ComparisonExpression);
    if (((ComparisonExpression) expr).getQuantifier() != QuantifierType.ANY) {
        return expr;
    }
    /*
         * Verify that an IN expression can be safely converted to an EXISTS one
         * IN (SELECT" forms e.g. "(A, B) IN (SELECT X, Y, FROM ...) =>
         * EXISTS (SELECT 42 FROM ... AND|WHERE|HAVING A=X AND|WHERE|HAVING B=Y)
         */
    AbstractExpression inColumns = expr.getLeft();
    if (inColumns instanceof SelectSubqueryExpression) {
        // (expression must return a single row at most)
        return expr;
    }
    // The right hand operand of the equality operation must be a SELECT statement
    AbstractExpression rightExpr = expr.getRight();
    if (!(rightExpr instanceof SelectSubqueryExpression)) {
        return expr;
    }
    SelectSubqueryExpression subqueryExpr = (SelectSubqueryExpression) rightExpr;
    AbstractParsedStmt subquery = subqueryExpr.getSubqueryStmt();
    if (!(subquery instanceof ParsedSelectStmt)) {
        return expr;
    }
    ParsedSelectStmt selectStmt = (ParsedSelectStmt) subquery;
    //      seems to require 1 match that has exactly 10-14 rows (matching or not) with lesser or equal values of Y.
    if (selectStmt.hasLimitOrOffset()) {
        return expr;
    }
    ParsedSelectStmt.rewriteInSubqueryAsExists(selectStmt, inColumns);
    subqueryExpr.resolveCorrelations();
    AbstractExpression existsExpr = new OperatorExpression();
    existsExpr.setExpressionType(ExpressionType.OPERATOR_EXISTS);
    existsExpr.setLeft(subqueryExpr);
    return optimizeExistsExpression(existsExpr);
}

Example 5 with SelectSubqueryExpression

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

the class PlanAssembler method getBestCostPlan.

CompiledPlan getBestCostPlan(AbstractParsedStmt parsedStmt) {
    // parse any subqueries that the statement contains
    List<StmtSubqueryScan> subqueryNodes = parsedStmt.getSubqueryScans();
    ParsedResultAccumulator fromSubqueryResult = null;
    if (!subqueryNodes.isEmpty()) {
        fromSubqueryResult = getBestCostPlanForFromSubQueries(subqueryNodes);
        if (fromSubqueryResult == null) {
            // There was at least one sub-query and we should have a compiled plan for it
            return null;
        }
    }
    // Get the best plans for the expression subqueries ( IN/EXISTS (SELECT...) )
    Set<AbstractExpression> subqueryExprs = parsedStmt.findSubquerySubexpressions();
    if (!subqueryExprs.isEmpty()) {
        // guards against IN/EXISTS/Scalar subqueries
        if (!m_partitioning.wasSpecifiedAsSingle()) {
            // levels of correlated subqueries.
            for (AbstractExpression e : subqueryExprs) {
                assert (e instanceof SelectSubqueryExpression);
                SelectSubqueryExpression subExpr = (SelectSubqueryExpression) e;
                if (!subExpr.getSubqueryScan().getIsReplicated()) {
                    m_recentErrorMsg = IN_EXISTS_SCALAR_ERROR_MESSAGE;
                    return null;
                }
            }
        }
        if (!getBestCostPlanForExpressionSubQueries(subqueryExprs)) {
            // There was at least one sub-query and we should have a compiled plan for it
            return null;
        }
    }
    // set up the plan assembler for this statement
    setupForNewPlans(parsedStmt);
    // get ready to find the plan with minimal cost
    CompiledPlan rawplan = null;
    // loop over all possible plans
    while (true) {
        rawplan = getNextPlan();
        // stop this while loop when no more plans are generated
        if (rawplan == null) {
            break;
        }
        // Update the best cost plan so far
        m_planSelector.considerCandidatePlan(rawplan, parsedStmt);
    }
    CompiledPlan retval = m_planSelector.m_bestPlan;
    if (retval == null) {
        return null;
    }
    if (fromSubqueryResult != null) {
        // Calculate the combined state of determinism for the parent and child statements
        boolean orderIsDeterministic = retval.isOrderDeterministic();
        String contentDeterminismDetail = fromSubqueryResult.m_isContentDeterministic;
        if (orderIsDeterministic && !fromSubqueryResult.m_orderIsDeterministic) {
            //TODO: this reliance on the vague isOrderDeterministicInSpiteOfUnorderedSubqueries test
            // is subject to false negatives for determinism. It misses the subtlety of parent
            // queries that surgically add orderings for specific "key" columns of a subquery result
            // or a subquery-based join for an effectively deterministic result.
            // The first step towards repairing this would involve detecting deterministic and
            // non-deterministic subquery results IN CONTEXT where they are scanned in the parent
            // query, so that the parent query can ensure that ALL the columns from a
            // non-deterministic subquery are later sorted.
            // The next step would be to extend the model for "subquery scans"
            // to identify dependencies / uniqueness constraints in subquery results
            // that can be exploited to impose determinism with fewer parent order by columns
            // -- like just the keys.
            orderIsDeterministic = parsedStmt.isOrderDeterministicInSpiteOfUnorderedSubqueries();
        }
        boolean hasLimitOrOffset = fromSubqueryResult.m_hasLimitOrOffset || retval.hasLimitOrOffset();
        retval.statementGuaranteesDeterminism(hasLimitOrOffset, orderIsDeterministic, contentDeterminismDetail);
        // Need to re-attach the sub-queries plans to the best parent plan. The same best plan for each
        // sub-query is reused with all parent candidate plans and needs to be reconnected with
        // the final best parent plan
        retval.rootPlanGraph = connectChildrenBestPlans(retval.rootPlanGraph);
    }
    /*
         * Find out if the query is inherently content deterministic and
         * remember it.
         */
    String contentDeterminismMessage = parsedStmt.getContentDeterminismMessage();
    if (contentDeterminismMessage != null) {
        retval.setNondeterminismDetail(contentDeterminismMessage);
    }
    failIfNonDeterministicDml(parsedStmt, retval);
    if (m_partitioning != null) {
        retval.setStatementPartitioning(m_partitioning);
    }
    return retval;
}