Author: shawkins Date: 2009-07-14 09:58:11 -0400 (Tue, 14 Jul 2009) New Revision: 1124 Modified: trunk/client-jdbc/src/main/java/com/metamatrix/jdbc/MMDatabaseMetaData.java trunk/connector-api/src/main/java/org/teiid/connector/api/ConnectorCapabilities.java trunk/connector-api/src/main/java/org/teiid/connector/basic/BasicConnectorCapabilities.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCCapabilities.java trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml trunk/engine/src/main/java/com/metamatrix/query/optimizer/capabilities/SourceCapabilities.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/GenerateCanonical.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/PlanToProcessConverter.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/plantree/NodeConstants.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleAssignOutputElements.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleMergeVirtual.java trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRaiseAccess.java trunk/engine/src/main/java/com/metamatrix/query/processor/relational/ProjectNode.java trunk/engine/src/main/java/com/metamatrix/query/processor/relational/SortNode.java trunk/engine/src/main/java/com/metamatrix/query/resolver/command/SetQueryResolver.java trunk/engine/src/main/java/com/metamatrix/query/resolver/command/SimpleQueryResolver.java trunk/engine/src/main/java/com/metamatrix/query/resolver/util/ResolverUtil.java trunk/engine/src/main/java/com/metamatrix/query/rewriter/QueryRewriter.java trunk/engine/src/main/java/com/metamatrix/query/sql/lang/OrderBy.java trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java trunk/engine/src/test/java/com/metamatrix/query/processor/TestProcessor.java trunk/engine/src/test/java/com/metamatrix/query/resolver/TestResolver.java Log: TEIID-514 adding the ability to specify order by columns unrelated to the select cause. Modified: trunk/client-jdbc/src/main/java/com/metamatrix/jdbc/MMDatabaseMetaData.java =================================================================== --- trunk/client-jdbc/src/main/java/com/metamatrix/jdbc/MMDatabaseMetaData.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/client-jdbc/src/main/java/com/metamatrix/jdbc/MMDatabaseMetaData.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -2856,7 +2856,7 @@ * @throws SQLException, should never occur. */ public boolean supportsOrderByUnrelated() throws SQLException { - return false; + return true; } /** Modified: trunk/connector-api/src/main/java/org/teiid/connector/api/ConnectorCapabilities.java =================================================================== --- trunk/connector-api/src/main/java/org/teiid/connector/api/ConnectorCapabilities.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/connector-api/src/main/java/org/teiid/connector/api/ConnectorCapabilities.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -205,6 +205,13 @@ boolean supportsOrderBy(); /** + * Support indicates connector accepts ORDER BY clause with columns not from the select + * @since 6.2 + * @return + */ + boolean supportsOrderByUnrelated(); + + /** * Whether the source supports an explicit GROUP BY clause * @since 6.1 */ Modified: trunk/connector-api/src/main/java/org/teiid/connector/basic/BasicConnectorCapabilities.java =================================================================== --- trunk/connector-api/src/main/java/org/teiid/connector/basic/BasicConnectorCapabilities.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/connector-api/src/main/java/org/teiid/connector/basic/BasicConnectorCapabilities.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -372,4 +372,9 @@ return false; } + @Override + public boolean supportsOrderByUnrelated() { + return false; + } + } Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCCapabilities.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCCapabilities.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCCapabilities.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -211,6 +211,11 @@ public boolean supportsOrderBy() { return true; } + + @Override + public boolean supportsOrderByUnrelated() { + return true; + } /* * @see com.metamatrix.data.ConnectorCapabilities#supportsOuterJoins() Modified: trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2009-07-14 13:58:11 UTC (rev 1124) @@ -791,16 +791,32 @@ <para>Syntax Rules: </para> <listitem> - <para>Column references in the order by must appear in the select clause.</para> + <para>Sort columns may be specified positionally by a 1 based + integer or string literal, by SELECT clause alias name, or by a column + reference.</para> </listitem> <listitem> - <para>The order by columns must be of a comparable type.</para> + <para>Column references may appear in the SELECT clause as the + expression for an aliased column or may reference columns from tables + in the FROM clause. + If the column reference is not in the SELECT clause the query must not + be a set operation, specify SELECT DISTINCT, or contain a GROUP BY + clause.</para> </listitem> <listitem> - <para>If an order by is used in an inline view or view definition without a limit clause, it will be removed by the Teiid optimizer. - </para> + <para>The ORDER BY columns must be of a comparable type.</para> </listitem> + <listitem> + <para>If an ORDER BY is used in an inline view or view + definition without a limit clause, it will be removed by the Teiid + optimizer.</para> + </listitem> </itemizedlist> + <warning> + <para>The use of positional ordering is no longer supported by the + ANSI SQL standard and is a deprecated feature in Teiid. It is + preferable to use alias names in the order by clause.</para> + </warning> </sect2> <sect2 id="limit_clause"> <title>LIMIT Clause</title> Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/capabilities/SourceCapabilities.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/capabilities/SourceCapabilities.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/capabilities/SourceCapabilities.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -162,6 +162,8 @@ * @since 3.1 SP2 */ QUERY_ORDERBY, + + QUERY_ORDERBY_UNRELATED, /** * Composite support for group by and having - not * used by the connector layer Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/GenerateCanonical.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/GenerateCanonical.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/GenerateCanonical.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -33,6 +33,7 @@ import com.metamatrix.query.optimizer.relational.plantree.NodeConstants; import com.metamatrix.query.optimizer.relational.plantree.NodeFactory; import com.metamatrix.query.optimizer.relational.plantree.PlanNode; +import com.metamatrix.query.optimizer.relational.plantree.NodeConstants.Info; import com.metamatrix.query.processor.relational.JoinNode.JoinStrategyType; import com.metamatrix.query.sql.lang.Command; import com.metamatrix.query.sql.lang.Criteria; @@ -445,7 +446,9 @@ sortNode.setProperty(NodeConstants.Info.SORT_ORDER, orderBy.getVariables()); sortNode.setProperty(NodeConstants.Info.ORDER_TYPES, orderBy.getTypes()); - + if (orderBy.hasUnrelated()) { + sortNode.setProperty(Info.UNRELATED_SORT, true); + } sortNode.addGroups(GroupsUsedByElementsVisitor.getGroups(orderBy)); GenerateCanonical.attachLast(sortNode, plan); Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/PlanToProcessConverter.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/PlanToProcessConverter.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/PlanToProcessConverter.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -346,6 +346,10 @@ PlanNode child = node.getLastChild(); if (node.getParent().getType() != NodeConstants.Types.PROJECT || node.getParent().getProperty(NodeConstants.Info.INTO_GROUP) == null) { + if (child.getType() == NodeConstants.Types.PROJECT) { + //update the project cols based upon the original output + child.setProperty(NodeConstants.Info.PROJECT_COLS, child.getProperty(NodeConstants.Info.OUTPUT_COLS)); + } child.setProperty(NodeConstants.Info.OUTPUT_COLS, node.getProperty(NodeConstants.Info.OUTPUT_COLS)); } } Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/plantree/NodeConstants.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/plantree/NodeConstants.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/plantree/NodeConstants.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -110,6 +110,7 @@ // Sort node properties ORDER_TYPES, // List <Boolean> SORT_ORDER, // List <SingleElementSymbol> + UNRELATED_SORT, // Boolean IS_DUP_REMOVAL, // Boolean // Source node properties Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleAssignOutputElements.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleAssignOutputElements.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleAssignOutputElements.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -146,7 +146,16 @@ case NodeConstants.Types.TUPLE_LIMIT: case NodeConstants.Types.DUP_REMOVE: case NodeConstants.Types.SORT: - //for nodes that are above a project or an access node, there's no special work needed + if (root.hasBooleanProperty(NodeConstants.Info.UNRELATED_SORT)) { + //add missing sort columns + List<SingleElementSymbol> elements = (List<SingleElementSymbol>) root.getProperty(NodeConstants.Info.SORT_ORDER); + outputElements = new ArrayList<SingleElementSymbol>(outputElements); + for (SingleElementSymbol singleElementSymbol : elements) { + if (!outputElements.contains(singleElementSymbol)) { + outputElements.add(singleElementSymbol); + } + } + } assignOutputElements(root.getLastChild(), outputElements, metadata, capFinder, rules, analysisRecord, context); break; case NodeConstants.Types.SOURCE: { Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleMergeVirtual.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleMergeVirtual.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleMergeVirtual.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -102,6 +102,16 @@ if (FrameUtil.isProcedure(projectNode)) { return root; } + + PlanNode sortNode = NodeEditor.findParent(parentProject, NodeConstants.Types.SORT, NodeConstants.Types.SOURCE); + + if (sortNode != null && sortNode.hasBooleanProperty(NodeConstants.Info.UNRELATED_SORT)) { + // the lower group cannot contain DUP_REMOVE, GROUP, UNION + // or a projected expression for an unrelated sort column (until SQL 2003 sorts are supported). + + // for now though the simplification is to just not remove the inline view. + return root; + } //try to remove the virtual layer if we are only doing a simple projection in the following cases: // 1. if the frame root is something other than a project (SET_OP, SORT, LIMIT, etc.) Modified: trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRaiseAccess.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRaiseAccess.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/optimizer/relational/rules/RuleRaiseAccess.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -282,7 +282,19 @@ } // If model supports the support constant parameter, then move access node - return CapabilitiesUtil.supportsOrderBy(modelID, metadata, capFinder); + if (!CapabilitiesUtil.supportsOrderBy(modelID, metadata, capFinder)) { + return false; + } + + /* If we have an unrelated sort it cannot be pushed down if it's not supported by the source + * + * TODO: we should be able to work this + */ + if (parentNode.hasBooleanProperty(NodeConstants.Info.UNRELATED_SORT) && !CapabilitiesUtil.supports(Capability.QUERY_ORDERBY_UNRELATED, modelID, metadata, capFinder)) { + return false; + } + + return true; } /** Modified: trunk/engine/src/main/java/com/metamatrix/query/processor/relational/ProjectNode.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/processor/relational/ProjectNode.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/processor/relational/ProjectNode.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -66,7 +66,6 @@ public void reset() { super.reset(); - needsProject = true; currentBatch = null; currentRow = 0; Modified: trunk/engine/src/main/java/com/metamatrix/query/processor/relational/SortNode.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/processor/relational/SortNode.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/processor/relational/SortNode.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -130,7 +130,7 @@ this.phase = SORT; } - private void sortPhase() throws BlockedException, MetaMatrixComponentException, MetaMatrixProcessingException { + private void sortPhase() throws BlockedException, MetaMatrixComponentException { this.outputID = this.sortUtility.sort(); this.phase = OUTPUT; } @@ -154,10 +154,12 @@ this.outputBeginRow += outputBatch.getRowCount(); + outputBatch = removeUnrelatedColumns(outputBatch); + if(rowCount != -1 && outputBeginRow > rowCount) { outputBatch.setTerminationFlag(true); } - + return outputBatch; } catch(MemoryNotAvailableException e) { throw BlockedOnMemoryException.INSTANCE; @@ -166,6 +168,18 @@ } } + private TupleBatch removeUnrelatedColumns(TupleBatch outputBatch) { + int extraColumns = this.getChildren()[0].getElements().size() - this.getElements().size(); + + if (extraColumns > 0) { + for (List tuple : outputBatch.getAllTuples()) { + addBatchRow(tuple.subList(0, this.getElements().size())); + } + outputBatch = pullBatch(); + } + return outputBatch; + } + public void close() throws MetaMatrixComponentException { if (!isClosed()) { super.close(); Modified: trunk/engine/src/main/java/com/metamatrix/query/resolver/command/SetQueryResolver.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/resolver/command/SetQueryResolver.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/resolver/command/SetQueryResolver.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -92,7 +92,7 @@ if(setQuery.getOrderBy() != null) { List validGroups = Collections.EMPTY_LIST; //order by elements must use the short name of the projected symbols - ResolverUtil.resolveOrderBy(setQuery.getOrderBy(), validGroups, setQuery.getProjectedSymbols(), metadata); + ResolverUtil.resolveOrderBy(setQuery.getOrderBy(), validGroups, setQuery.getProjectedSymbols(), metadata, false); } setProjectedTypes(setQuery, firstProjectTypes); Modified: trunk/engine/src/main/java/com/metamatrix/query/resolver/command/SimpleQueryResolver.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/resolver/command/SimpleQueryResolver.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/resolver/command/SimpleQueryResolver.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -612,7 +612,7 @@ public void visit(OrderBy obj) { try { - ResolverUtil.resolveOrderBy(obj, new ArrayList(currentGroups), query.getSelect().getProjectedSymbols(), metadata); + ResolverUtil.resolveOrderBy(obj, new ArrayList(currentGroups), query.getSelect().getProjectedSymbols(), metadata, query.getGroupBy() == null && !query.getSelect().isDistinct()); } catch(QueryResolverException e) { throw new MetaMatrixRuntimeException(e); } catch(MetaMatrixComponentException e) { Modified: trunk/engine/src/main/java/com/metamatrix/query/resolver/util/ResolverUtil.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/resolver/util/ResolverUtil.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/resolver/util/ResolverUtil.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -282,21 +282,37 @@ * Attempt to resolve the order by * throws QueryResolverException if the symbol is not of SingleElementSymbol type * @param orderBy - * @param fromClauseGroups groups of the FROM clause of the query (for + * @param fromClauseGroups groups of the FROM clause of the query (for * resolving ambiguous unqualified element names), or empty List if a Set Query * Order By is being resolved - * @param knownElements resolved elements from SELECT clause, which are only + * @param knownElements resolved elements from SELECT clause, which are only * ones allowed to be in ORDER BY - * @param metadata QueryMetadataInterface + * @param metadata QueryMetadataInterface + * @param isSimpleQuery */ - public static void resolveOrderBy(OrderBy orderBy, List fromClauseGroups, List knownElements, QueryMetadataInterface metadata) + public static void resolveOrderBy(OrderBy orderBy, List fromClauseGroups, List knownElements, QueryMetadataInterface metadata, boolean isSimpleQuery) throws QueryResolverException, QueryMetadataException, MetaMatrixComponentException { orderBy.setInPlanForm(false); // Cached state, if needed - String[] knownShortNames = null; + String[] knownShortNames = new String[knownElements.size()]; + for(int i=0; i<knownElements.size(); i++) { + SingleElementSymbol knownSymbol = (SingleElementSymbol) knownElements.get(i); + if (knownSymbol instanceof ExpressionSymbol) { + continue; + } + + String name = knownSymbol.getShortName(); + //special check for uuid element symbols + if (knownSymbol instanceof ElementSymbol && knownSymbol.getShortName().equalsIgnoreCase(knownSymbol.getName())) { + name = metadata.getShortElementName(metadata.getFullName((((ElementSymbol)knownSymbol).getMetadataID()))); + } + + knownShortNames[i] = name; + } + // Collect all elements from order by List elements = orderBy.getVariables(); Iterator elementIter = elements.iterator(); @@ -314,25 +330,6 @@ throw new QueryResolverException(ErrorMessageKeys.RESOLVER_0043, QueryPlugin.Util.getString(ErrorMessageKeys.RESOLVER_0043, symbolName)); } - // construct the array of short names of the SELECT columns (once only) - if(knownShortNames == null) { - knownShortNames = new String[knownElements.size()]; - - for(int i=0; i<knownElements.size(); i++) { - SingleElementSymbol knownSymbol = (SingleElementSymbol) knownElements.get(i); - if (knownSymbol instanceof ExpressionSymbol) { - continue; - } - - String name = knownSymbol.getShortName(); - //special check for uuid element symbols - if (knownSymbol instanceof ElementSymbol && knownSymbol.getShortName().equalsIgnoreCase(knownSymbol.getName())) { - name = metadata.getShortElementName(metadata.getFullName((((ElementSymbol)knownSymbol).getMetadataID()))); - } - - knownShortNames[i] = name; - } - } // walk the SELECT col short names, looking for a match on the current ORDER BY 'short name' for(int i=0; i<knownShortNames.length; i++) { if( shortName.equalsIgnoreCase( knownShortNames[i] )) { @@ -387,11 +384,18 @@ matchedSymbol = findMatchingElementByID(symbol, knownElements); } - - TempMetadataID tempMetadataID = new TempMetadataID(symbol.getName(), matchedSymbol.getType()); - tempMetadataID.setPosition(knownElements.indexOf(matchedSymbol)); - symbol.setMetadataID(tempMetadataID); - symbol.setType(matchedSymbol.getType()); + + if (matchedSymbol == null) { + if (!isSimpleQuery) { + throw new QueryResolverException(ErrorMessageKeys.RESOLVER_0043, QueryPlugin.Util.getString(ErrorMessageKeys.RESOLVER_0043, symbol.getName())); + } + orderBy.setUnrelated(true); + } else { + TempMetadataID tempMetadataID = new TempMetadataID(symbol.getName(), matchedSymbol.getType()); + tempMetadataID.setPosition(knownElements.indexOf(matchedSymbol)); + symbol.setMetadataID(tempMetadataID); + symbol.setType(matchedSymbol.getType()); + } } } @@ -429,7 +433,7 @@ } } - throw new QueryResolverException(ErrorMessageKeys.RESOLVER_0043, QueryPlugin.Util.getString(ErrorMessageKeys.RESOLVER_0043, symbol.getName())); + return null; } /** Modified: trunk/engine/src/main/java/com/metamatrix/query/rewriter/QueryRewriter.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/rewriter/QueryRewriter.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/rewriter/QueryRewriter.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -853,14 +853,21 @@ } OrderBy newOrderBy = new OrderBy(); + newOrderBy.setUnrelated(orderBy.hasUnrelated()); HashSet<Expression> previousExpressions = new HashSet<Expression>(); for (int i = 0; i < orderBy.getVariableCount(); i++) { SingleElementSymbol querySymbol = orderBy.getVariable(i); if (!orderBy.isInPlanForm()) { //get the order by item from the select clause, the variable must be an element symbol - int index = ((TempMetadataID)((ElementSymbol)querySymbol).getMetadataID()).getPosition(); - querySymbol = (SingleElementSymbol)((SingleElementSymbol)projectedSymbols.get(index)).clone(); + //however we have a hack to determine the position... + Object id = ((ElementSymbol)querySymbol).getMetadataID(); + if (id instanceof TempMetadataID) { + int index = ((TempMetadataID)((ElementSymbol)querySymbol).getMetadataID()).getPosition(); + if (index != -1) { + querySymbol = (SingleElementSymbol)((SingleElementSymbol)projectedSymbols.get(index)).clone(); + } + } // else not a projected symbol } Expression expr = SymbolMap.getExpression(querySymbol); if (!previousExpressions.add(expr)) { Modified: trunk/engine/src/main/java/com/metamatrix/query/sql/lang/OrderBy.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/sql/lang/OrderBy.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/com/metamatrix/query/sql/lang/OrderBy.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -54,6 +54,7 @@ private List sortOrder; private List orderTypes; private boolean inPlanForm = true; + private boolean hasUnrelated; /** * Constructs a default instance of this class. @@ -192,6 +193,7 @@ } OrderBy result = new OrderBy(copySymbols, getTypes()); result.setInPlanForm(this.inPlanForm); + result.setUnrelated(this.hasUnrelated); return result; } @@ -244,5 +246,13 @@ public void setInPlanForm(boolean inPlanForm) { this.inPlanForm = inPlanForm; } + + public boolean hasUnrelated() { + return hasUnrelated; + } + + public void setUnrelated(boolean hasUnrelated) { + this.hasUnrelated = hasUnrelated; + } } Modified: trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -93,6 +93,7 @@ tgtCaps.setCapabilitySupport(Capability.QUERY_GROUP_BY, srcCaps.supportsGroupBy()); tgtCaps.setCapabilitySupport(Capability.QUERY_HAVING, srcCaps.supportsHaving()); tgtCaps.setCapabilitySupport(Capability.INSERT_WITH_QUERYEXPRESSION, srcCaps.supportsInsertWithQueryExpression()); + tgtCaps.setCapabilitySupport(Capability.QUERY_ORDERBY_UNRELATED, srcCaps.supportsOrderByUnrelated()); List functions = srcCaps.getSupportedFunctions(); if(functions != null && functions.size() > 0) { Modified: trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/test/java/com/metamatrix/query/optimizer/TestOptimizer.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -144,6 +144,7 @@ caps.setCapabilitySupport(Capability.CRITERIA_OR, true); caps.setCapabilitySupport(Capability.CRITERIA_NOT, true); caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true); + caps.setCapabilitySupport(Capability.QUERY_ORDERBY_UNRELATED, true); // set typical max set size caps.setSourceProperty(Capability.MAX_IN_CRITERIA_SIZE, new Integer(1000)); Modified: trunk/engine/src/test/java/com/metamatrix/query/processor/TestProcessor.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/processor/TestProcessor.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/test/java/com/metamatrix/query/processor/TestProcessor.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -7683,6 +7683,32 @@ helpProcess(plan, dataManager, expected); } + @Test public void testOrderByOutsideOfSelect() { + // Create query + String sql = "SELECT e1 FROM (select e1, e2 || e3 as e2 from pm1.g2) x order by e2"; //$NON-NLS-1$ + + //a, a, null, c, b, a + // Create expected results + List[] expected = new List[] { + Arrays.asList("a"), + Arrays.asList("a"), + Arrays.asList(new String[] {null}), + Arrays.asList("c"), + Arrays.asList("b"), + Arrays.asList("a"), + }; + // Construct data manager with data + FakeDataManager dataManager = new FakeDataManager(); + sampleData1(dataManager); + + // Plan query + ProcessorPlan plan = helpGetPlan(helpParse(sql), FakeMetadataFactory.example1Cached(), TestOptimizer.getGenericFinder()); + + // Run query + helpProcess(plan, dataManager, expected); + } + + private static final boolean DEBUG = false; } Modified: trunk/engine/src/test/java/com/metamatrix/query/resolver/TestResolver.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/resolver/TestResolver.java 2009-07-13 22:00:25 UTC (rev 1123) +++ trunk/engine/src/test/java/com/metamatrix/query/resolver/TestResolver.java 2009-07-14 13:58:11 UTC (rev 1124) @@ -4698,5 +4698,17 @@ String sql = "SELECT * FROM pm1.g1 WHERE e3 = CASE WHEN e2 BETWEEN 3 AND 5 THEN e2 ELSE -1 END"; //$NON-NLS-1$ helpResolve(sql); } + + public void testOrderByUnrelated() { + helpResolve("SELECT pm1.g1.e1, e2 as x, e3 as y FROM pm1.g1 ORDER BY e4"); //$NON-NLS-1$ + } + public void testOrderByUnrelated1() { + helpResolveException("SELECT distinct pm1.g1.e1, e2 as x, e3 as y FROM pm1.g1 ORDER BY e4"); //$NON-NLS-1$ + } + + public void testOrderByUnrelated2() { + helpResolveException("SELECT max(e2) FROM pm1.g1 group by e1 ORDER BY e4"); //$NON-NLS-1$ + } + } \ No newline at end of file