Examples with OrderBy org.teiid.query.sql.lang.OrderBy used on opensource projects

Example 1 with OrderBy

use of org.teiid.query.sql.lang.OrderBy in project teiid by teiid.

the class LanguageBridgeFactory method translate.

public org.teiid.language.OrderBy translate(OrderBy orderBy, boolean set) {
    if (orderBy == null) {
        return null;
    }
    List<OrderByItem> items = orderBy.getOrderByItems();
    List<SortSpecification> translatedItems = new ArrayList<SortSpecification>();
    for (int i = 0; i < items.size(); i++) {
        Expression symbol = items.get(i).getSymbol();
        Ordering direction = items.get(i).isAscending() ? Ordering.ASC : Ordering.DESC;
        SortSpecification orderByItem = null;
        if (!set && (items.get(i).isUnrelated() || symbol instanceof ElementSymbol)) {
            orderByItem = new SortSpecification(direction, translate(symbol));
        } else {
            orderByItem = new SortSpecification(direction, new ColumnReference(null, Symbol.getShortName(((Symbol) symbol).getOutputName()), null, symbol.getType()));
        }
        orderByItem.setNullOrdering(items.get(i).getNullOrdering());
        translatedItems.add(orderByItem);
    }
    return new org.teiid.language.OrderBy(translatedItems);
}

Example 2 with OrderBy

use of org.teiid.query.sql.lang.OrderBy in project teiid by teiid.

the class TestGroupingNode method test1.

// ################################## ACTUAL TESTS ################################
@Test
public void test1() throws Exception {
    BufferManager mgr = BufferManagerFactory.getStandaloneBufferManager();
    // Set up
    GroupingNode node = new GroupingNode(1);
    List outputElements = new ArrayList();
    // $NON-NLS-1$
    ElementSymbol col1 = new ElementSymbol("col1");
    col1.setType(Integer.class);
    // $NON-NLS-1$
    ElementSymbol col2 = new ElementSymbol("col2");
    col2.setType(Integer.class);
    outputElements.add(col1);
    // $NON-NLS-1$ //$NON-NLS-2$
    outputElements.add(new AggregateSymbol("COUNT", false, null));
    // $NON-NLS-1$ //$NON-NLS-2$
    outputElements.add(new AggregateSymbol("COUNT", false, col2));
    // $NON-NLS-1$ //$NON-NLS-2$
    outputElements.add(new AggregateSymbol("COUNT", true, col2));
    // $NON-NLS-1$ //$NON-NLS-2$
    outputElements.add(new AggregateSymbol("SUM", false, col2));
    // $NON-NLS-1$ //$NON-NLS-2$
    outputElements.add(new AggregateSymbol("SUM", true, col2));
    // $NON-NLS-1$ //$NON-NLS-2$
    outputElements.add(new AggregateSymbol("AVG", false, col2));
    // $NON-NLS-1$ //$NON-NLS-2$
    outputElements.add(new AggregateSymbol("AVG", true, col2));
    // $NON-NLS-1$ //$NON-NLS-2$
    outputElements.add(new AggregateSymbol("MIN", false, col2));
    // $NON-NLS-1$ //$NON-NLS-2$
    outputElements.add(new AggregateSymbol("MIN", true, col2));
    // $NON-NLS-1$ //$NON-NLS-2$
    outputElements.add(new AggregateSymbol("MAX", false, col2));
    // $NON-NLS-1$ //$NON-NLS-2$
    outputElements.add(new AggregateSymbol("MAX", true, col2));
    node.setElements(outputElements);
    List groupingElements = new ArrayList();
    groupingElements.add(col1);
    node.setOrderBy(new OrderBy(groupingElements).getOrderByItems());
    // $NON-NLS-1$ //$NON-NLS-2$
    CommandContext context = new CommandContext("pid", "test", null, null, 1);
    List[] expected = new List[] { Arrays.asList(new Object[] { null, new Integer(2), new Integer(1), new Integer(1), new Long(3), new Long(3), new BigDecimal(3.0), new BigDecimal(3.0), new Integer(3), new Integer(3), new Integer(3), new Integer(3) }), Arrays.asList(new Object[] { new Integer(0), new Integer(1), new Integer(1), new Integer(1), new Long(4), new Long(4), new BigDecimal(4.0), new BigDecimal(4.0), new Integer(4), new Integer(4), new Integer(4), new Integer(4) }), Arrays.asList(new Object[] { new Integer(1), new Integer(1), new Integer(1), new Integer(1), new Long(2), new Long(2), new BigDecimal(2.0), new BigDecimal(2.0), new Integer(2), new Integer(2), new Integer(2), new Integer(2) }), Arrays.asList(new Object[] { new Integer(2), new Integer(4), new Integer(4), new Integer(2), new Long(5), new Long(3), new BigDecimal(1.25), new BigDecimal(1.5), new Integer(1), new Integer(1), new Integer(2), new Integer(2) }), Arrays.asList(new Object[] { new Integer(3), new Integer(1), new Integer(1), new Integer(1), new Long(0), new Long(0), new BigDecimal(0.0), new BigDecimal(0.0), new Integer(0), new Integer(0), new Integer(0), new Integer(0) }), Arrays.asList(new Object[] { new Integer(4), new Integer(3), new Integer(2), new Integer(2), new Long(5), new Long(5), new BigDecimal(2.5), new BigDecimal(2.5), new Integer(2), new Integer(2), new Integer(3), new Integer(3) }), Arrays.asList(new Object[] { new Integer(5), new Integer(1), new Integer(1), new Integer(1), new Long(3), new Long(3), new BigDecimal(3.0), new BigDecimal(3.0), new Integer(3), new Integer(3), new Integer(3), new Integer(3) }), Arrays.asList(new Object[] { new Integer(6), new Integer(2), new Integer(2), new Integer(2), new Long(7), new Long(7), new BigDecimal(3.5), new BigDecimal(3.5), new Integer(3), new Integer(3), new Integer(4), new Integer(4) }) };
    helpProcess(mgr, node, context, expected, null);
    // ensure that the distinct input type is correct
    AggregateFunction[][] functions = node.getFunctions();
    AggregateFunction countDist = functions[5][0];
    SortingFilter dup = (SortingFilter) countDist;
    assertEquals(DataTypeManager.DefaultDataClasses.INTEGER, dup.getElements().get(0).getType());
}

Example 3 with OrderBy

use of org.teiid.query.sql.lang.OrderBy in project teiid by teiid.

the class TestGroupingNode method getExampleGroupingNode.

private GroupingNode getExampleGroupingNode() {
    GroupingNode node = new GroupingNode(1);
    List outputElements = new ArrayList();
    // $NON-NLS-1$
    ElementSymbol col1 = new ElementSymbol("col1");
    col1.setType(Integer.class);
    // $NON-NLS-1$
    ElementSymbol col2 = new ElementSymbol("col2");
    col2.setType(Integer.class);
    outputElements.add(col1);
    // $NON-NLS-1$ //$NON-NLS-2$
    outputElements.add(new AggregateSymbol("COUNT", true, col2));
    node.setElements(outputElements);
    List groupingElements = new ArrayList();
    groupingElements.add(col1);
    node.setOrderBy(new OrderBy(groupingElements).getOrderByItems());
    return node;
}

Example 4 with OrderBy

use of org.teiid.query.sql.lang.OrderBy in project teiid by teiid.

the class RuleMergeVirtual method doMerge.

static PlanNode doMerge(PlanNode frame, PlanNode root, boolean beforeDecomposeJoin, QueryMetadataInterface metadata, CapabilitiesFinder capFinder) throws QueryPlannerException, QueryMetadataException, TeiidComponentException {
    if (frame.hasBooleanProperty(Info.NO_UNNEST)) {
        return root;
    }
    GroupSymbol virtualGroup = frame.getGroups().iterator().next();
    // check to see if frame represents a proc relational query.
    if (virtualGroup.isProcedure()) {
        return root;
    }
    List<PlanNode> sources = NodeEditor.findAllNodes(frame.getFirstChild(), NodeConstants.Types.SOURCE, NodeConstants.Types.SOURCE);
    SymbolMap references = (SymbolMap) frame.getProperty(NodeConstants.Info.CORRELATED_REFERENCES);
    if (references != null) {
        if (!sources.isEmpty()) {
            // correlated nested table commands should not be merged
            return root;
        }
        // this is ok only if all of the references go above the correlating join
        // currently this check is simplistic - just look at the parent join more nested scenarios won't work
        PlanNode parentJoin = NodeEditor.findParent(frame, NodeConstants.Types.JOIN, NodeConstants.Types.SOURCE | NodeConstants.Types.GROUP);
        if (parentJoin != null && !parentJoin.getGroups().containsAll(GroupsUsedByElementsVisitor.getGroups(references.getValues()))) {
            return root;
        }
    }
    PlanNode parentProject = NodeEditor.findParent(frame, NodeConstants.Types.PROJECT);
    // Check whether the upper frame is a SELECT INTO
    if (parentProject.getProperty(NodeConstants.Info.INTO_GROUP) != null) {
        return root;
    }
    if (!FrameUtil.canConvertAccessPatterns(frame)) {
        return root;
    }
    PlanNode projectNode = frame.getFirstChild();
    // Check if lower frame has only a stored procedure execution - this cannot be merged to parent frame
    if (FrameUtil.isProcedure(projectNode)) {
        return root;
    }
    SymbolMap symbolMap = (SymbolMap) frame.getProperty(NodeConstants.Info.SYMBOL_MAP);
    PlanNode sortNode = NodeEditor.findParent(parentProject, NodeConstants.Types.SORT, NodeConstants.Types.SOURCE);
    if (sortNode != null && sortNode.hasBooleanProperty(NodeConstants.Info.UNRELATED_SORT)) {
        OrderBy sortOrder = (OrderBy) sortNode.getProperty(NodeConstants.Info.SORT_ORDER);
        boolean unrelated = false;
        for (OrderByItem item : sortOrder.getOrderByItems()) {
            if (!item.isUnrelated()) {
                continue;
            }
            Collection<ElementSymbol> elements = ElementCollectorVisitor.getElements(item.getSymbol(), true);
            for (ElementSymbol elementSymbol : elements) {
                if (virtualGroup.equals(elementSymbol.getGroupSymbol())) {
                    unrelated = true;
                }
            }
        }
        // the lower frame cannot contain DUP_REMOVE, GROUP, UNION if unrelated
        if (unrelated && NodeEditor.findNodePreOrder(frame, NodeConstants.Types.DUP_REMOVE, NodeConstants.Types.PROJECT) != null || NodeEditor.findNodePreOrder(frame, NodeConstants.Types.SET_OP, NodeConstants.Types.SOURCE) != null || NodeEditor.findNodePreOrder(frame, NodeConstants.Types.GROUP, NodeConstants.Types.SOURCE) != null) {
            return root;
        }
    }
    PlanNode parentJoin = NodeEditor.findParent(frame, NodeConstants.Types.JOIN, NodeConstants.Types.SOURCE | NodeConstants.Types.GROUP);
    // 3. if the frame has no sources
    if (projectNode.getType() != NodeConstants.Types.PROJECT || NodeEditor.findNodePreOrder(frame.getFirstChild(), NodeConstants.Types.GROUP, NodeConstants.Types.SOURCE | NodeConstants.Types.JOIN) != null || sources.isEmpty()) {
        PlanNode parentSource = NodeEditor.findParent(parentProject, NodeConstants.Types.SOURCE);
        if (beforeDecomposeJoin && parentSource != null && parentSource.hasProperty(Info.PARTITION_INFO) && !NodeEditor.findAllNodes(frame.getFirstChild(), NodeConstants.Types.SET_OP, NodeConstants.Types.SOURCE).isEmpty()) {
            // don't bother to merge until after
            return root;
        }
        root = checkForSimpleProjection(frame, root, parentProject, metadata, capFinder);
        if (frame.getParent() == null || !sources.isEmpty() || projectNode.getType() != NodeConstants.Types.PROJECT || parentJoin == null) {
            // only consider no sources when the frame is simple and there is a parent join
            return root;
        }
        if (sources.isEmpty() && parentJoin != null) {
            JoinType jt = (JoinType) parentJoin.getProperty(Info.JOIN_TYPE);
            if (jt.isOuter()) {
                // cannot remove if the no source side is an outer side, or if it can change the meaning of the plan
                return root;
            }
            PlanNode joinToTest = parentJoin;
            while (joinToTest != null) {
                if (FrameUtil.findJoinSourceNode(joinToTest.getFirstChild()).getGroups().contains(virtualGroup)) {
                    // scan all sources under the other side as there could be a join structure
                    for (PlanNode node : NodeEditor.findAllNodes(joinToTest.getLastChild(), NodeConstants.Types.SOURCE, NodeConstants.Types.SOURCE)) {
                        SymbolMap map = (SymbolMap) node.getProperty(NodeConstants.Info.CORRELATED_REFERENCES);
                        if (map != null && GroupsUsedByElementsVisitor.getGroups(map.getValues()).contains(virtualGroup)) {
                            // TODO: we don't have the logic yet to then replace the correlated references
                            return root;
                        }
                    }
                }
                joinToTest = NodeEditor.findParent(joinToTest, NodeConstants.Types.JOIN, NodeConstants.Types.SOURCE | NodeConstants.Types.GROUP);
            }
        }
    }
    if (!checkJoinCriteria(frame.getFirstChild(), virtualGroup, parentJoin)) {
        return root;
    }
    // we don't have to check for null dependent with no source without criteria since there must be a row
    if (!checkProjectedSymbols(projectNode, virtualGroup, parentJoin, metadata, sources, !sources.isEmpty() || frame.getParent() != parentJoin, parentProject)) {
        // TODO: propagate constants if just inhibited by subquery/non-deterministic expressions
        return root;
    }
    // Otherwise merge should work
    // Convert parent frame before merge
    Set<GroupSymbol> groups = Collections.emptySet();
    if (!sources.isEmpty()) {
        groups = FrameUtil.findJoinSourceNode(projectNode).getGroups();
    } else if (references != null) {
        // convert from correlated form to regular references
        RuleMergeCriteria.ReferenceReplacementVisitor rrv = new RuleMergeCriteria.ReferenceReplacementVisitor(references);
        for (Map.Entry<ElementSymbol, Expression> entry : symbolMap.asUpdatableMap().entrySet()) {
            if (entry.getValue() instanceof Reference) {
                Expression ex = rrv.replaceExpression(entry.getValue());
                entry.setValue(ex);
            } else {
                PreOrPostOrderNavigator.doVisit(entry.getValue(), rrv, PreOrPostOrderNavigator.PRE_ORDER);
            }
        }
    }
    FrameUtil.convertFrame(frame, virtualGroup, groups, symbolMap.asMap(), metadata);
    PlanNode parentBottom = frame.getParent();
    prepareFrame(frame);
    if (sources.isEmpty() && parentJoin != null) {
        // special handling for no sources
        PlanNode parent = frame;
        List<PlanNode> criteriaNodes = new ArrayList<PlanNode>();
        while (parent.getParent() != parentJoin) {
            parent = parent.getParent();
            if (!parent.hasBooleanProperty(Info.IS_PHANTOM)) {
                criteriaNodes.add(parent);
            }
        }
        PlanNode parentNode = parentJoin.getParent();
        parentJoin.removeChild(parent);
        PlanNode other = parentJoin.getFirstChild();
        NodeEditor.removeChildNode(parentNode, parentJoin);
        JoinType jt = (JoinType) parentJoin.getProperty(Info.JOIN_TYPE);
        if (!jt.isOuter()) {
            // if we are not an outer join then the join/parent criteria is effectively
            // applied to the other side
            List<Criteria> joinCriteria = (List<Criteria>) parentJoin.getProperty(Info.JOIN_CRITERIA);
            if (joinCriteria != null) {
                for (Criteria crit : joinCriteria) {
                    PlanNode critNode = RelationalPlanner.createSelectNode(crit, false);
                    criteriaNodes.add(critNode);
                }
            }
            if (!criteriaNodes.isEmpty()) {
                for (PlanNode selectNode : criteriaNodes) {
                    selectNode.removeAllChildren();
                    selectNode.removeFromParent();
                    other.addAsParent(selectNode);
                }
            }
        }
    } else {
        // Remove top 2 nodes (SOURCE, PROJECT) of virtual group - they're no longer needed
        NodeEditor.removeChildNode(parentBottom, frame);
        NodeEditor.removeChildNode(parentBottom, projectNode);
    }
    return root;
}

Example 5 with OrderBy

use of org.teiid.query.sql.lang.OrderBy in project teiid by teiid.

the class RulePlanSorts method optimizeSorts.

private PlanNode optimizeSorts(boolean parentBlocking, PlanNode node, PlanNode root, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, AnalysisRecord record, CommandContext context) throws QueryMetadataException, TeiidComponentException, QueryPlannerException {
    node = NodeEditor.findNodePreOrder(node, NodeConstants.Types.SORT | NodeConstants.Types.DUP_REMOVE | NodeConstants.Types.GROUP | NodeConstants.Types.JOIN | NodeConstants.Types.SET_OP, NodeConstants.Types.ACCESS);
    if (node == null) {
        return root;
    }
    switch(node.getType()) {
        case NodeConstants.Types.SORT:
            parentBlocking = true;
            if (node.hasBooleanProperty(NodeConstants.Info.IS_DUP_REMOVAL)) {
                break;
            }
            if (mergeSortWithDupRemoval(node)) {
                node.setProperty(NodeConstants.Info.IS_DUP_REMOVAL, true);
            } else {
                root = checkForProjectOptimization(node, root, metadata, capFinder, record, context);
                if (NodeEditor.findParent(node, NodeConstants.Types.ACCESS) != null) {
                    return root;
                }
            }
            OrderBy orderBy = (OrderBy) node.getProperty(NodeConstants.Info.SORT_ORDER);
            List<Expression> orderColumns = orderBy.getSortKeys();
            List<Expression> sortExpressions = new ArrayList<Expression>(orderColumns.size());
            PlanNode possibleSort = NodeEditor.findNodePreOrder(node, NodeConstants.Types.GROUP, NodeConstants.Types.SOURCE | NodeConstants.Types.ACCESS);
            if (possibleSort != null && !possibleSort.hasBooleanProperty(Info.ROLLUP)) {
                boolean otherExpression = false;
                SymbolMap groupMap = (SymbolMap) possibleSort.getProperty(Info.SYMBOL_MAP);
                for (Expression singleElementSymbol : orderColumns) {
                    Expression ex = SymbolMap.getExpression(singleElementSymbol);
                    if (ex instanceof ElementSymbol) {
                        sortExpressions.add(groupMap.getMappedExpression((ElementSymbol) ex));
                    } else {
                        otherExpression = true;
                        break;
                    }
                }
                List<Expression> exprs = (List<Expression>) possibleSort.getProperty(Info.GROUP_COLS);
                if (!otherExpression && exprs != null && exprs.containsAll(sortExpressions)) {
                    exprs.removeAll(sortExpressions);
                    exprs.addAll(0, sortExpressions);
                    if (node.getParent() == null) {
                        root = node.getFirstChild();
                        root.removeFromParent();
                        Object cols = node.getProperty(NodeConstants.Info.OUTPUT_COLS);
                        root.setProperty(NodeConstants.Info.OUTPUT_COLS, cols);
                        if (root.getType() == NodeConstants.Types.PROJECT) {
                            root.setProperty(NodeConstants.Info.PROJECT_COLS, cols);
                        }
                        node = root;
                    } else {
                        PlanNode nextNode = node.getFirstChild();
                        NodeEditor.removeChildNode(node.getParent(), node);
                        node = nextNode;
                    }
                    possibleSort.setProperty(Info.SORT_ORDER, orderBy);
                }
            }
            break;
        case NodeConstants.Types.DUP_REMOVE:
            if (parentBlocking) {
                node.setType(NodeConstants.Types.SORT);
                node.setProperty(NodeConstants.Info.IS_DUP_REMOVAL, true);
            }
            break;
        case NodeConstants.Types.GROUP:
            if (!node.hasCollectionProperty(NodeConstants.Info.GROUP_COLS)) {
                break;
            }
            SymbolMap map = (SymbolMap) node.getProperty(Info.SYMBOL_MAP);
            boolean cardinalityDependent = false;
            boolean canOptimize = true;
            for (Expression ex : map.asMap().values()) {
                if (ex instanceof AggregateSymbol) {
                    AggregateSymbol agg = (AggregateSymbol) ex;
                    if (agg.isCardinalityDependent()) {
                        cardinalityDependent = true;
                        break;
                    }
                } else if (!(ex instanceof ElementSymbol)) {
                    // there is an expression in the grouping columns
                    canOptimize = false;
                    break;
                }
            }
            if (canOptimize && mergeSortWithDupRemovalAcrossSource(node)) {
                node.setProperty(NodeConstants.Info.IS_DUP_REMOVAL, true);
                if (cardinalityDependent) {
                    PlanNode source = NodeEditor.findNodePreOrder(node, NodeConstants.Types.SOURCE);
                    List<Expression> sourceOutput = (List<Expression>) source.getProperty(Info.OUTPUT_COLS);
                    PlanNode child = node.getFirstChild();
                    while (child != source) {
                        child.setProperty(Info.OUTPUT_COLS, sourceOutput);
                        child = child.getFirstChild();
                    }
                }
            }
            // TODO: check the join interesting order
            parentBlocking = true;
            break;
        case NodeConstants.Types.JOIN:
            if (node.getProperty(NodeConstants.Info.JOIN_STRATEGY) == JoinStrategyType.NESTED_LOOP || node.getProperty(NodeConstants.Info.JOIN_STRATEGY) == JoinStrategyType.NESTED_TABLE) {
                break;
            }
            /*
			 *  Look under the left and the right sources for a dup removal operation
			 *  join
			 *   [project]
			 *     source
			 *       dup remove | union not all
			 */
            parentBlocking = true;
            PlanNode toTest = node.getFirstChild();
            if (mergeSortWithDupRemovalAcrossSource(toTest)) {
                node.setProperty(NodeConstants.Info.SORT_LEFT, SortOption.SORT_DISTINCT);
                if (node.getProperty(NodeConstants.Info.SORT_RIGHT) != SortOption.SORT) {
                    node.setProperty(NodeConstants.Info.JOIN_STRATEGY, JoinStrategyType.MERGE);
                }
            }
            toTest = node.getLastChild();
            if (mergeSortWithDupRemovalAcrossSource(toTest)) {
                node.setProperty(NodeConstants.Info.SORT_RIGHT, SortOption.SORT_DISTINCT);
                if (node.getProperty(NodeConstants.Info.SORT_LEFT) != SortOption.SORT) {
                    node.setProperty(NodeConstants.Info.JOIN_STRATEGY, JoinStrategyType.MERGE);
                }
            }
            break;
        case NodeConstants.Types.SET_OP:
            // assumes the use of the merge algorithm
            if (node.getProperty(NodeConstants.Info.SET_OPERATION) != SetQuery.Operation.UNION) {
                parentBlocking = true;
            } else if (!node.hasBooleanProperty(NodeConstants.Info.USE_ALL) && !parentBlocking) {
                // do the incremental dup removal for lower latency
                node.setProperty(NodeConstants.Info.IS_DUP_REMOVAL, true);
            }
            break;
    }
    for (PlanNode child : node.getChildren()) {
        root = optimizeSorts(parentBlocking, child, root, metadata, capFinder, record, context);
    }
    return root;
}