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

Example 11 with OrderByPlanNode

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

the class PlanAssembler method getNextSelectPlan.

private CompiledPlan getNextSelectPlan() {
    assert (m_subAssembler != null);
    // A matview reaggregation template plan may have been initialized
    // with a post-predicate expression moved from the statement's
    // join tree prior to any subquery planning.
    // Since normally subquery planning is driven from the join tree,
    // any subqueries that are moved out of the join tree would need
    // to be planned separately.
    // This planning would need to be done prior to calling
    // m_subAssembler.nextPlan()
    // because it can have query partitioning implications.
    // Under the current query limitations, the partitioning implications
    // are very simple -- subqueries are not allowed in multipartition
    // queries against partitioned data, so detection of a subquery in
    // the same query as a matview reaggregation can just return an error,
    // without any need for subquery planning here.
    HashAggregatePlanNode reAggNode = null;
    HashAggregatePlanNode mvReAggTemplate = m_parsedSelect.m_mvFixInfo.getReAggregationPlanNode();
    if (mvReAggTemplate != null) {
        reAggNode = new HashAggregatePlanNode(mvReAggTemplate);
        AbstractExpression postPredicate = reAggNode.getPostPredicate();
        if (postPredicate != null && postPredicate.hasSubquerySubexpression()) {
            // For now, this is just a special case violation of the limitation on
            // use of subquery expressions in MP queries on partitioned data.
            // That special case was going undetected when we didn't flag it here.
            m_recentErrorMsg = IN_EXISTS_SCALAR_ERROR_MESSAGE;
            return null;
        }
    // // Something more along these lines would have to be enabled
    // // to allow expression subqueries to be used in multi-partition
    // // matview queries.
    // if (!getBestCostPlanForExpressionSubQueries(subqueryExprs)) {
    //     // There was at least one sub-query and we should have a compiled plan for it
    //    return null;
    // }
    }
    AbstractPlanNode subSelectRoot = m_subAssembler.nextPlan();
    if (subSelectRoot == null) {
        m_recentErrorMsg = m_subAssembler.m_recentErrorMsg;
        return null;
    }
    AbstractPlanNode root = subSelectRoot;
    boolean mvFixNeedsProjection = false;
    /*
         * If the access plan for the table in the join order was for a
         * distributed table scan there must be a send/receive pair at the top
         * EXCEPT for the special outer join case in which a replicated table
         * was on the OUTER side of an outer join across from the (joined) scan
         * of the partitioned table(s) (all of them) in the query. In that case,
         * the one required send/receive pair is already in the plan below the
         * inner side of a NestLoop join.
         */
    if (m_partitioning.requiresTwoFragments()) {
        boolean mvFixInfoCoordinatorNeeded = true;
        boolean mvFixInfoEdgeCaseOuterJoin = false;
        ArrayList<AbstractPlanNode> receivers = root.findAllNodesOfClass(AbstractReceivePlanNode.class);
        if (receivers.size() == 1) {
            // Edge cases: left outer join with replicated table.
            if (m_parsedSelect.m_mvFixInfo.needed()) {
                mvFixInfoCoordinatorNeeded = false;
                AbstractPlanNode receiveNode = receivers.get(0);
                if (receiveNode.getParent(0) instanceof NestLoopPlanNode) {
                    if (subSelectRoot.hasInlinedIndexScanOfTable(m_parsedSelect.m_mvFixInfo.getMVTableName())) {
                        return getNextSelectPlan();
                    }
                    List<AbstractPlanNode> nljs = receiveNode.findAllNodesOfType(PlanNodeType.NESTLOOP);
                    List<AbstractPlanNode> nlijs = receiveNode.findAllNodesOfType(PlanNodeType.NESTLOOPINDEX);
                    // This is like a single table case.
                    if (nljs.size() + nlijs.size() == 0) {
                        mvFixInfoEdgeCaseOuterJoin = true;
                    }
                    root = handleMVBasedMultiPartQuery(reAggNode, root, mvFixInfoEdgeCaseOuterJoin);
                }
            }
        } else {
            if (receivers.size() > 0) {
                throw new PlanningErrorException("This special case join between an outer replicated table and " + "an inner partitioned table is too complex and is not supported.");
            }
            root = SubPlanAssembler.addSendReceivePair(root);
            // Root is a receive node here.
            assert (root instanceof ReceivePlanNode);
            if (m_parsedSelect.mayNeedAvgPushdown()) {
                m_parsedSelect.switchOptimalSuiteForAvgPushdown();
            }
            if (m_parsedSelect.m_tableList.size() > 1 && m_parsedSelect.m_mvFixInfo.needed() && subSelectRoot.hasInlinedIndexScanOfTable(m_parsedSelect.m_mvFixInfo.getMVTableName())) {
                // So, in-lined index scan of Nested loop index join can not be possible.
                return getNextSelectPlan();
            }
        }
        root = handleAggregationOperators(root);
        // Process the re-aggregate plan node and insert it into the plan.
        if (m_parsedSelect.m_mvFixInfo.needed() && mvFixInfoCoordinatorNeeded) {
            AbstractPlanNode tmpRoot = root;
            root = handleMVBasedMultiPartQuery(reAggNode, root, mvFixInfoEdgeCaseOuterJoin);
            if (root != tmpRoot) {
                mvFixNeedsProjection = true;
            }
        }
    } else {
        /*
             * There is no receive node and root is a single partition plan.
             */
        // If there is no receive plan node and no distributed plan has been generated,
        // the fix set for MV is not needed.
        m_parsedSelect.m_mvFixInfo.setNeeded(false);
        root = handleAggregationOperators(root);
    }
    // add a PartitionByPlanNode here.
    if (m_parsedSelect.hasWindowFunctionExpression()) {
        root = handleWindowedOperators(root);
    }
    if (m_parsedSelect.hasOrderByColumns()) {
        root = handleOrderBy(m_parsedSelect, root);
        if (m_parsedSelect.isComplexOrderBy() && root instanceof OrderByPlanNode) {
            AbstractPlanNode child = root.getChild(0);
            AbstractPlanNode grandChild = child.getChild(0);
            // swap the ORDER BY and complex aggregate Projection node
            if (child instanceof ProjectionPlanNode) {
                root.unlinkChild(child);
                child.unlinkChild(grandChild);
                child.addAndLinkChild(root);
                root.addAndLinkChild(grandChild);
                // update the new root
                root = child;
            } else if (m_parsedSelect.hasDistinctWithGroupBy() && child.getPlanNodeType() == PlanNodeType.HASHAGGREGATE && grandChild.getPlanNodeType() == PlanNodeType.PROJECTION) {
                AbstractPlanNode grandGrandChild = grandChild.getChild(0);
                child.clearParents();
                root.clearChildren();
                grandGrandChild.clearParents();
                grandChild.clearChildren();
                grandChild.addAndLinkChild(root);
                root.addAndLinkChild(grandGrandChild);
                root = child;
            }
        }
    }
    // node.
    if (mvFixNeedsProjection || needProjectionNode(root)) {
        root = addProjection(root);
    }
    if (m_parsedSelect.hasLimitOrOffset()) {
        root = handleSelectLimitOperator(root);
    }
    CompiledPlan plan = new CompiledPlan();
    plan.rootPlanGraph = root;
    plan.setReadOnly(true);
    boolean orderIsDeterministic = m_parsedSelect.isOrderDeterministic();
    boolean hasLimitOrOffset = m_parsedSelect.hasLimitOrOffset();
    String contentDeterminismMessage = m_parsedSelect.getContentDeterminismMessage();
    plan.statementGuaranteesDeterminism(hasLimitOrOffset, orderIsDeterministic, contentDeterminismMessage);
    // Apply the micro-optimization:
    // LIMIT push down, Table count / Counting Index, Optimized Min/Max
    MicroOptimizationRunner.applyAll(plan, m_parsedSelect);
    return plan;
}

Example 12 with OrderByPlanNode

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

the class PlanAssembler method handleWindowedOperators.

/**
     * Create nodes for windowed operations.
     *
     * @param root
     * @return
     */
private AbstractPlanNode handleWindowedOperators(AbstractPlanNode root) {
    // Get the windowed expression.  We need to set its output
    // schema from the display list.
    WindowFunctionExpression winExpr = m_parsedSelect.getWindowFunctionExpressions().get(0);
    assert (winExpr != null);
    // This will set the output schema to contain the
    // windowed schema column only.  In generateOutputSchema
    // we will add the input columns.
    WindowFunctionPlanNode pnode = new WindowFunctionPlanNode();
    pnode.setWindowFunctionExpression(winExpr);
    // We always need an order by plan node, even if the sort
    // is optimized away by an index.  This may be turned
    // into an inline order by in a MergeReceivePlanNode.
    IndexUseForOrderBy scanNode = findScanNodeForWindowFunction(root);
    AbstractPlanNode cnode = null;
    int winfunc = (scanNode == null) ? SubPlanAssembler.NO_INDEX_USE : scanNode.getWindowFunctionUsesIndex();
    // statement level order by ordering.
    if ((SubPlanAssembler.STATEMENT_LEVEL_ORDER_BY_INDEX == winfunc) || (SubPlanAssembler.NO_INDEX_USE == winfunc)) {
        // No index.  Calculate the expression order here and stuff it into
        // the order by node.  Note that if we support more than one window
        // function this would be the case when scanNode.getWindowFunctionUsesIndex()
        // returns a window function number which is different from the number
        // of winExpr.
        List<AbstractExpression> partitionByExpressions = winExpr.getPartitionByExpressions();
        // If the order by expression list contains a partition by expression then
        // we won't have to sort by it twice.  We sort by the partition by expressions
        // first, and we don't care what order we sort by them.  So, find the
        // sort direction in the order by list and use that in the partition by
        // list, and then mark that it was deleted in the order by
        // list.
        //
        // We choose to make this dontsort rather than dosort because the
        // Java default value for boolean is false, and we want to sort by
        // default.
        boolean[] dontsort = new boolean[winExpr.getOrderbySize()];
        List<AbstractExpression> orderByExpressions = winExpr.getOrderByExpressions();
        List<SortDirectionType> orderByDirections = winExpr.getOrderByDirections();
        OrderByPlanNode onode = new OrderByPlanNode();
        for (int idx = 0; idx < winExpr.getPartitionbySize(); ++idx) {
            SortDirectionType pdir = SortDirectionType.ASC;
            AbstractExpression partitionByExpression = partitionByExpressions.get(idx);
            int sidx = winExpr.getSortIndexOfOrderByExpression(partitionByExpression);
            if (0 <= sidx) {
                pdir = orderByDirections.get(sidx);
                dontsort[sidx] = true;
            }
            onode.addSort(partitionByExpression, pdir);
        }
        for (int idx = 0; idx < winExpr.getOrderbySize(); ++idx) {
            if (!dontsort[idx]) {
                AbstractExpression orderByExpr = orderByExpressions.get(idx);
                SortDirectionType orderByDir = orderByDirections.get(idx);
                onode.addSort(orderByExpr, orderByDir);
            }
        }
        onode.addAndLinkChild(root);
        cnode = onode;
    } else {
        assert (scanNode != null);
        // inline order by node of a MergeReceive node.
        assert (0 == scanNode.getWindowFunctionUsesIndex());
        if (m_partitioning.requiresTwoFragments()) {
            OrderByPlanNode onode = new OrderByPlanNode();
            SortDirectionType dir = scanNode.getSortOrderFromIndexScan();
            assert (dir != SortDirectionType.INVALID);
            // This was created when the index was determined.
            // We cached it in the scan node.
            List<AbstractExpression> orderExprs = scanNode.getFinalExpressionOrderFromIndexScan();
            assert (orderExprs != null);
            for (AbstractExpression ae : orderExprs) {
                onode.addSort(ae, dir);
            }
            // Link in the OrderByNode.
            onode.addAndLinkChild(root);
            cnode = onode;
        } else {
            // Don't create and link in the order by node.
            cnode = root;
        }
    }
    pnode.addAndLinkChild(cnode);
    return pnode;
}

Example 13 with OrderByPlanNode

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

the class PlanAssembler method isValidAggregateNodeForLimitPushdown.

private static boolean isValidAggregateNodeForLimitPushdown(AbstractPlanNode aggregateNode, List<ParsedColInfo> orderBys, boolean orderByCoversAllGroupBy) {
    if (aggregateNode instanceof AggregatePlanNode == false) {
        return false;
    }
    if (aggregateNode.getParentCount() == 0) {
        return false;
    }
    // Limitation: can only push past coordinating aggregation nodes
    if (!((AggregatePlanNode) aggregateNode).m_isCoordinatingAggregator) {
        return false;
    }
    AbstractPlanNode parent = aggregateNode.getParent(0);
    AbstractPlanNode orderByNode = null;
    if (parent instanceof OrderByPlanNode) {
        orderByNode = parent;
    } else if (parent instanceof ProjectionPlanNode && parent.getParentCount() > 0 && parent.getParent(0) instanceof OrderByPlanNode) {
        // Xin really wants inline project with aggregation
        orderByNode = parent.getParent(0);
    }
    if (orderByNode == null) {
        // the limit should not be pushed down.
        return false;
    }
    if ((!orderByCoversAllGroupBy) || isOrderByAggregationValue(orderBys)) {
        return false;
    }
    return true;
}

Example 14 with OrderByPlanNode

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

the class TestPlansGroupBy method checkMVReaggregateFeature.

// topNode, reAggNode
private void checkMVReaggregateFeature(List<AbstractPlanNode> pns, boolean needFix, int numGroupByOfTopAggNode, int numAggsOfTopAggNode, int numGroupByOfReaggNode, int numAggsOfReaggNode, boolean aggPushdown, boolean aggInline) {
    assertEquals(2, pns.size());
    AbstractPlanNode p = pns.get(0);
    assertTrue(p instanceof SendPlanNode);
    p = p.getChild(0);
    if (p instanceof ProjectionPlanNode) {
        p = p.getChild(0);
    }
    if (p instanceof LimitPlanNode) {
        // No limit pushed down.
        p = p.getChild(0);
    }
    if (p instanceof OrderByPlanNode) {
        p = p.getChild(0);
    }
    HashAggregatePlanNode reAggNode = null;
    List<AbstractPlanNode> nodes = p.findAllNodesOfClass(AbstractReceivePlanNode.class);
    assertEquals(1, nodes.size());
    AbstractPlanNode receiveNode = nodes.get(0);
    // Indicates that there is no top aggregation node.
    if (numGroupByOfTopAggNode == -1) {
        if (needFix) {
            p = receiveNode.getParent(0);
            assertTrue(p instanceof HashAggregatePlanNode);
            reAggNode = (HashAggregatePlanNode) p;
            assertEquals(numGroupByOfReaggNode, reAggNode.getGroupByExpressionsSize());
            assertEquals(numAggsOfReaggNode, reAggNode.getAggregateTypesSize());
            p = p.getChild(0);
        }
        assertTrue(p instanceof ReceivePlanNode);
        p = pns.get(1);
        assertTrue(p instanceof SendPlanNode);
        p = p.getChild(0);
        assertTrue(p instanceof AbstractScanPlanNode);
        return;
    }
    if (p instanceof ProjectionPlanNode) {
        p = p.getChild(0);
    }
    //
    // Hash top aggregate node
    //
    AggregatePlanNode topAggNode = null;
    if (p instanceof AbstractJoinPlanNode) {
        // Inline aggregation with join
        topAggNode = AggregatePlanNode.getInlineAggregationNode(p);
    } else {
        assertTrue(p instanceof AggregatePlanNode);
        topAggNode = (AggregatePlanNode) p;
        p = p.getChild(0);
    }
    assertEquals(numGroupByOfTopAggNode, topAggNode.getGroupByExpressionsSize());
    assertEquals(numAggsOfTopAggNode, topAggNode.getAggregateTypesSize());
    if (needFix) {
        p = receiveNode.getParent(0);
        assertTrue(p instanceof HashAggregatePlanNode);
        reAggNode = (HashAggregatePlanNode) p;
        assertEquals(numGroupByOfReaggNode, reAggNode.getGroupByExpressionsSize());
        assertEquals(numAggsOfReaggNode, reAggNode.getAggregateTypesSize());
        p = p.getChild(0);
    }
    assertTrue(p instanceof ReceivePlanNode);
    // Test the second part
    p = pns.get(1);
    assertTrue(p instanceof SendPlanNode);
    p = p.getChild(0);
    if (aggPushdown) {
        assertTrue(!needFix);
        if (aggInline) {
            assertNotNull(AggregatePlanNode.getInlineAggregationNode(p));
        } else {
            assertTrue(p instanceof AggregatePlanNode);
            p = p.getChild(0);
        }
    }
    if (needFix) {
        assertTrue(p instanceof AbstractScanPlanNode);
    } else {
        assertTrue(p instanceof AbstractScanPlanNode || p instanceof AbstractJoinPlanNode);
    }
}

Example 15 with OrderByPlanNode

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

the class TestPlansJoin method testUsingColumns.

public void testUsingColumns() {
    String query;
    AbstractPlanNode pn;
    OrderByPlanNode orderBy;
    NestLoopPlanNode nlj;
    AggregatePlanNode aggr;
    List<SchemaColumn> selectColumns;
    SchemaColumn col;
    AbstractExpression colExp;
    AbstractExpression predicate;
    // Test USING column
    query = "SELECT MAX(R1.A), C FROM R1 FULL JOIN R2 USING (C) " + "WHERE C > 0 GROUP BY C ORDER BY C";
    pn = compileToTopDownTree(query, 2, PlanNodeType.SEND, PlanNodeType.ORDERBY, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    // ORDER BY column
    orderBy = (OrderByPlanNode) pn.getChild(0);
    List<AbstractExpression> s = orderBy.getSortExpressions();
    assertEquals(1, s.size());
    assertEquals(ExpressionType.VALUE_TUPLE, s.get(0).getExpressionType());
    // WHERE
    nlj = (NestLoopPlanNode) orderBy.getChild(0);
    assertNull(nlj.getPreJoinPredicate());
    predicate = nlj.getJoinPredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_EQUAL, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    predicate = nlj.getWherePredicate();
    assertExprTopDownTree(predicate, ExpressionType.COMPARE_GREATERTHAN, ExpressionType.OPERATOR_CASE_WHEN, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE, ExpressionType.OPERATOR_ALTERNATIVE, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_CONSTANT);
    // GROUP BY
    aggr = (AggregatePlanNode) nlj.getInlinePlanNode(PlanNodeType.HASHAGGREGATE);
    assertNotNull(aggr);
    List<AbstractExpression> g = aggr.getGroupByExpressions();
    assertEquals(1, g.size());
    assertExprTopDownTree(g.get(0), ExpressionType.OPERATOR_CASE_WHEN, ExpressionType.OPERATOR_IS_NULL, ExpressionType.VALUE_TUPLE, ExpressionType.OPERATOR_ALTERNATIVE, ExpressionType.VALUE_TUPLE, ExpressionType.VALUE_TUPLE);
    // Test three table full join
    query = "SELECT C FROM R1 FULL JOIN R2 USING (C) FULL JOIN R3 USING (C)";
    pn = compileToTopDownTree(query, 1, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    selectColumns = pn.getOutputSchema().getColumns();
    col = selectColumns.get(0);
    assertEquals("C", col.getColumnAlias());
    colExp = col.getExpression();
    assertEquals(ExpressionType.VALUE_TUPLE, colExp.getExpressionType());
    // Test three table INNER join. USING C column should be resolved
    query = "SELECT C FROM R1 JOIN R2 USING (C) JOIN R3 USING (C)";
    pn = compileToTopDownTree(query, 1, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    selectColumns = pn.getOutputSchema().getColumns();
    assertEquals(1, selectColumns.size());
    col = selectColumns.get(0);
    assertEquals("C", col.getColumnAlias());
    colExp = col.getExpression();
    assertEquals(ExpressionType.VALUE_TUPLE, colExp.getExpressionType());
    // Test two table LEFT join. USING C column should be resolved
    query = "SELECT C FROM R1 LEFT JOIN R2 USING (C)";
    pn = compileToTopDownTree(query, 1, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    selectColumns = pn.getOutputSchema().getColumns();
    assertEquals(1, selectColumns.size());
    col = selectColumns.get(0);
    assertEquals("C", col.getColumnAlias());
    colExp = col.getExpression();
    assertEquals(ExpressionType.VALUE_TUPLE, colExp.getExpressionType());
    // Test two table RIGHT join. USING C column should be resolved
    query = "SELECT C FROM R1 RIGHT JOIN R2 USING (C)";
    pn = compileToTopDownTree(query, 1, PlanNodeType.SEND, PlanNodeType.PROJECTION, PlanNodeType.NESTLOOP, PlanNodeType.SEQSCAN, PlanNodeType.SEQSCAN);
    selectColumns = pn.getOutputSchema().getColumns();
    assertEquals(1, selectColumns.size());
    col = selectColumns.get(0);
    assertEquals("C", col.getColumnAlias());
    colExp = col.getExpression();
    assertEquals(ExpressionType.VALUE_TUPLE, colExp.getExpressionType());
}