Author: shawkins Date: 2011-08-30 15:30:48 -0400 (Tue, 30 Aug 2011) New Revision: 3437 Modified: trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java trunk/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CapabilitiesUtil.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java trunk/engine/src/main/java/org/teiid/query/processor/relational/GroupingNode.java trunk/engine/src/main/java/org/teiid/query/processor/relational/WindowFunctionProjectNode.java trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java trunk/engine/src/main/java/org/teiid/query/sql/symbol/AggregateSymbol.java trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java trunk/engine/src/main/resources/org/teiid/query/i18n.properties trunk/engine/src/test/java/org/teiid/query/processor/TestWindowFunctions.java trunk/engine/src/test/java/org/teiid/query/validator/TestValidator.java Log: TEIID-1735 adding support for non-ordered distinct aggs Modified: trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java =================================================================== --- trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -887,6 +887,14 @@ } /** + * @return true if distinct aggregates can be windowed function. + * @since 7.6 + */ + public boolean supportsWindowDistinctAggregates() { + return supportsElementaryOlapOperations(); + } + + /** * @return true if array_agg is supported * @since 7.5 */ Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/postgresql/PostgreSQLExecutionFactory.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -506,6 +506,11 @@ } @Override + public boolean supportsWindowDistinctAggregates() { + return false; + } + + @Override public boolean supportsSimilarTo() { return true; } Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -233,6 +233,11 @@ } @Override + public boolean supportsWindowDistinctAggregates() { + return false; + } + + @Override public boolean supportsWindowOrderByWithAggregates() { return false; } Modified: trunk/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml =================================================================== --- trunk/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml 2011-08-30 19:30:48 UTC (rev 3437) @@ -1331,7 +1331,7 @@ <para>WindowOrderByWithAggregates</para> </entry> <entry> - <para/> + <para>ElementaryOlapOperations</para> </entry> <entry> <para>Translator supports windowed aggregates with a window order by clause.</para> @@ -1339,10 +1339,21 @@ </row> <row> <entry> + <para>WindowDistinctAggregates</para> + </entry> + <entry> + <para>ElementaryOlapOperations, AggregatesDistinct</para> + </entry> + <entry> + <para>Translator supports windowed distinct aggregates.</para> + </entry> + </row> + <row> + <entry> <para>AdvancedOlapOperations</para> </entry> <entry> - <para/> + <para>ElementaryOlapOperations</para> </entry> <entry> <para>Translator supports aggregate conditions.</para> 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 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2011-08-30 19:30:48 UTC (rev 3437) @@ -326,7 +326,7 @@ </para> </listitem> <listitem> - <para>Windowed aggregates may not use DISTINCT. + <para>Windowed aggregates may not use DISTINCT if the window specification is ordered. </para> </listitem> </itemizedlist> 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 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -25,7 +25,10 @@ import java.util.Iterator; import java.util.List; +import org.teiid.logging.LogConstants; +import org.teiid.logging.LogManager; import org.teiid.metadata.FunctionMethod; +import org.teiid.query.QueryPlugin; import org.teiid.query.optimizer.capabilities.BasicSourceCapabilities; import org.teiid.query.optimizer.capabilities.SourceCapabilities; import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability; @@ -52,14 +55,14 @@ tgtCaps.setCapabilitySupport(Capability.QUERY_SELECT_DISTINCT, srcCaps.supportsSelectDistinct()); tgtCaps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, srcCaps.supportsAliasedTable()); tgtCaps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, srcCaps.supportsInnerJoins()); - tgtCaps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, srcCaps.supportsSelfJoins()); + setSupports(connectorID, tgtCaps, Capability.QUERY_FROM_JOIN_SELFJOIN, srcCaps.supportsSelfJoins(), Capability.QUERY_FROM_GROUP_ALIAS); tgtCaps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, srcCaps.supportsOuterJoins()); tgtCaps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER_FULL, srcCaps.supportsFullOuterJoins()); - tgtCaps.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, srcCaps.supportsInlineViews()); + setSupports(connectorID, tgtCaps, Capability.QUERY_FROM_INLINE_VIEWS, srcCaps.supportsInlineViews(), Capability.QUERY_FROM_GROUP_ALIAS); tgtCaps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, srcCaps.supportsCompareCriteriaEquals()); tgtCaps.setCapabilitySupport(Capability.CRITERIA_COMPARE_ORDERED, srcCaps.supportsCompareCriteriaOrdered()); tgtCaps.setCapabilitySupport(Capability.CRITERIA_LIKE, srcCaps.supportsLikeCriteria()); - tgtCaps.setCapabilitySupport(Capability.CRITERIA_LIKE_ESCAPE, srcCaps.supportsLikeCriteriaEscapeCharacter()); + setSupports(connectorID, tgtCaps, Capability.CRITERIA_LIKE_ESCAPE, srcCaps.supportsLikeCriteriaEscapeCharacter(), Capability.CRITERIA_LIKE); tgtCaps.setCapabilitySupport(Capability.CRITERIA_IN, srcCaps.supportsInCriteria()); tgtCaps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, srcCaps.supportsInCriteriaSubquery()); tgtCaps.setCapabilitySupport(Capability.CRITERIA_ISNULL, srcCaps.supportsIsNullCriteria()); @@ -99,12 +102,13 @@ tgtCaps.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING, srcCaps.supportsOrderByNullOrdering()); tgtCaps.setCapabilitySupport(Capability.INSERT_WITH_ITERATOR, srcCaps.supportsInsertWithIterator()); tgtCaps.setCapabilitySupport(Capability.COMMON_TABLE_EXPRESSIONS, srcCaps.supportsCommonTableExpressions()); - tgtCaps.setCapabilitySupport(Capability.ADVANCED_OLAP, srcCaps.supportsAdvancedOlapOperations()); tgtCaps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, srcCaps.supportsElementaryOlapOperations()); - tgtCaps.setCapabilitySupport(Capability.WINDOW_FUNCTION_ORDER_BY_AGGREGATES, srcCaps.supportsWindowOrderByWithAggregates()); + setSupports(connectorID, tgtCaps, Capability.ADVANCED_OLAP, srcCaps.supportsAdvancedOlapOperations(), Capability.ELEMENTARY_OLAP); + setSupports(connectorID, tgtCaps, Capability.WINDOW_FUNCTION_ORDER_BY_AGGREGATES, srcCaps.supportsWindowOrderByWithAggregates(), Capability.ELEMENTARY_OLAP); tgtCaps.setCapabilitySupport(Capability.QUERY_AGGREGATES_ARRAY, srcCaps.supportsArrayAgg()); tgtCaps.setCapabilitySupport(Capability.CRITERIA_SIMILAR, srcCaps.supportsSimilarTo()); tgtCaps.setCapabilitySupport(Capability.CRITERIA_LIKE_REGEX, srcCaps.supportsLikeRegex()); + setSupports(connectorID, tgtCaps, Capability.WINDOW_FUNCTION_DISTINCT_AGGREGATES, srcCaps.supportsWindowDistinctAggregates(), Capability.ELEMENTARY_OLAP, Capability.QUERY_AGGREGATES_DISTINCT); List<String> functions = srcCaps.getSupportedFunctions(); if(functions != null && functions.size() > 0) { @@ -130,4 +134,17 @@ return tgtCaps; } + private static void setSupports(Object connectorID, BasicSourceCapabilities tgtCaps, Capability cap, boolean supports, Capability... required) { + if (!supports) { + return; + } + for (Capability capability : required) { + if (!tgtCaps.supportsCapability(capability)) { + LogManager.logWarning(LogConstants.CTX_CONNECTOR, QueryPlugin.Util.getString("support_required", cap, capability, connectorID)); //$NON-NLS-1$ + supports = false; + } + } + tgtCaps.setCapabilitySupport(cap, supports); + } + } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -25,6 +25,7 @@ public interface SourceCapabilities { public enum Capability { + /** * Support indicates connector can accept queries with SELECT DISTINCT */ @@ -38,7 +39,7 @@ * * @since 3.1 SP2 */ - QUERY_FROM_GROUP_ALIAS, + QUERY_FROM_GROUP_ALIAS("TableAlias"), //$NON-NLS-1$ /** * Max number of groups appearing in a from clause */ @@ -64,7 +65,7 @@ * * @since 3.1 SP2 */ - QUERY_FROM_JOIN_SELFJOIN, + QUERY_FROM_JOIN_SELFJOIN("SelfJoins"), //$NON-NLS-1$ /** * Support indicates connector can accept right or left outer joins * @@ -76,7 +77,7 @@ * * @since 4.1 */ - QUERY_FROM_INLINE_VIEWS, + QUERY_FROM_INLINE_VIEWS("InlineViews"), //$NON-NLS-1$ /** * Support indicates connector can accept full outer joins * @@ -101,13 +102,13 @@ * * @since 3.1 SP2 */ - CRITERIA_LIKE, + CRITERIA_LIKE("LikeCriteria"), //$NON-NLS-1$ /** * Support indicates connector accepts criteria of form (element LIKE constant ESCAPE char) - CURRENTLY NOT USED * * @since 3.1 SP2 */ - CRITERIA_LIKE_ESCAPE, + CRITERIA_LIKE_ESCAPE("LikeCriteriaEscapeCharacter"), //$NON-NLS-1$ /** * Support indicates connector accepts criteria of form (element IN set) * @@ -226,7 +227,7 @@ * * @since 3.1 SP2 */ - QUERY_AGGREGATES_DISTINCT, + QUERY_AGGREGATES_DISTINCT("AggregatesDistinct"), /** * Support indicates connector can accept scalar subqueries in the SELECT, WHERE, and HAVING clauses * @@ -321,12 +322,29 @@ INSERT_WITH_ITERATOR, COMMON_TABLE_EXPRESSIONS, MAX_DEPENDENT_PREDICATES, - ADVANCED_OLAP, + ADVANCED_OLAP("AdvancedOLAP"), //$NON-NLS-1$ QUERY_AGGREGATES_ARRAY, - ELEMENTARY_OLAP, - WINDOW_FUNCTION_ORDER_BY_AGGREGATES, + ELEMENTARY_OLAP("ElementaryOLAP"), //$NON-NLS-1$ + WINDOW_FUNCTION_ORDER_BY_AGGREGATES("WindowOrderByAggregates"), //$NON-NLS-1$ CRITERIA_SIMILAR, - CRITERIA_LIKE_REGEX + CRITERIA_LIKE_REGEX, + WINDOW_FUNCTION_DISTINCT_AGGREGATES("WindowDistinctAggregates"); //$NON-NLS-1$ + + + private final String toString; + + Capability(String toString) { + this.toString = toString; + } + + Capability() { + this.toString = name(); + } + + public String toString() { + return toString; + } + } public enum Scope { Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CapabilitiesUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CapabilitiesUtil.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CapabilitiesUtil.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -30,6 +30,7 @@ import org.teiid.api.exception.query.QueryMetadataException; import org.teiid.core.TeiidComponentException; import org.teiid.core.types.DataTypeManager; +import org.teiid.language.SortSpecification.NullOrdering; import org.teiid.metadata.Schema; import org.teiid.query.function.FunctionLibrary; import org.teiid.query.metadata.QueryMetadataInterface; @@ -38,6 +39,7 @@ import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability; import org.teiid.query.sql.LanguageObject; import org.teiid.query.sql.lang.JoinType; +import org.teiid.query.sql.lang.OrderByItem; import org.teiid.query.sql.lang.SetQuery.Operation; import org.teiid.query.sql.symbol.AggregateSymbol; import org.teiid.query.sql.symbol.Constant; @@ -462,4 +464,35 @@ } return true; } + + static boolean supportsNullOrdering(QueryMetadataInterface metadata, + CapabilitiesFinder capFinder, Object modelID, OrderByItem symbol) + throws QueryMetadataException, TeiidComponentException { + boolean supportsNullOrdering = CapabilitiesUtil.supports(Capability.QUERY_ORDERBY_NULL_ORDERING, modelID, metadata, capFinder); + NullOrder defaultNullOrder = CapabilitiesUtil.getDefaultNullOrder(modelID, metadata, capFinder); + if (symbol.getNullOrdering() != null) { + if (!supportsNullOrdering) { + if (symbol.getNullOrdering() == NullOrdering.FIRST) { + if (defaultNullOrder != NullOrder.FIRST && !(symbol.isAscending() && defaultNullOrder == NullOrder.LOW) + && !(!symbol.isAscending() && defaultNullOrder == NullOrder.HIGH)) { + return false; + } + } else if (defaultNullOrder != NullOrder.LAST && !(symbol.isAscending() && defaultNullOrder == NullOrder.HIGH) + && !(!symbol.isAscending() && defaultNullOrder == NullOrder.LOW)) { + return false; + } + symbol.setNullOrdering(null); + } + } else if (supportsNullOrdering && defaultNullOrder != NullOrder.LOW) { + //try to match the expected default of low + if (symbol.isAscending()) { + if (defaultNullOrder != NullOrder.FIRST) { + symbol.setNullOrdering(NullOrdering.FIRST); + } + } else if (defaultNullOrder != NullOrder.LAST) { + symbol.setNullOrdering(NullOrdering.LAST); + } + } + return true; + } } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -51,6 +51,7 @@ import org.teiid.query.sql.lang.IsNullCriteria; import org.teiid.query.sql.lang.MatchCriteria; import org.teiid.query.sql.lang.NotCriteria; +import org.teiid.query.sql.lang.OrderByItem; import org.teiid.query.sql.lang.Query; import org.teiid.query.sql.lang.QueryCommand; import org.teiid.query.sql.lang.SetCriteria; @@ -176,8 +177,36 @@ markInvalid(windowFunction, "Window function order by with aggregate not supported by source"); //$NON-NLS-1$ return; } + if (!this.caps.supportsCapability(Capability.WINDOW_FUNCTION_DISTINCT_AGGREGATES) + && windowFunction.getFunction().isDistinct()) { + markInvalid(windowFunction, "Window function distinct aggregate not supported by source"); //$NON-NLS-1$ + return; + } + try { + if (!CapabilitiesUtil.checkElementsAreSearchable(windowFunction.getWindowSpecification().getPartition(), metadata, SupportConstants.Element.SEARCHABLE_COMPARE)) { + markInvalid(windowFunction, "not all source columns support search type"); //$NON-NLS-1$ + } + } catch(QueryMetadataException e) { + handleException(new TeiidComponentException(e)); + } catch(TeiidComponentException e) { + handleException(e); + } } + @Override + public void visit(OrderByItem obj) { + try { + checkElementsAreSearchable(obj.getSymbol(), SupportConstants.Element.SEARCHABLE_COMPARE); + if (!CapabilitiesUtil.supportsNullOrdering(this.metadata, this.capFinder, this.modelID, obj)) { + markInvalid(obj, "Desired null ordering is not supported by source"); //$NON-NLS-1$ + } + } catch(QueryMetadataException e) { + handleException(new TeiidComponentException(e)); + } catch(TeiidComponentException e) { + handleException(e); + } + } + public void visit(CaseExpression obj) { if(! this.caps.supportsCapability(Capability.QUERY_CASE)) { markInvalid(obj, "CaseExpression pushdown not supported by source"); //$NON-NLS-1$ Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -32,7 +32,6 @@ import org.teiid.api.exception.query.QueryMetadataException; import org.teiid.api.exception.query.QueryPlannerException; import org.teiid.core.TeiidComponentException; -import org.teiid.language.SortSpecification.NullOrdering; import org.teiid.query.analysis.AnalysisRecord; import org.teiid.query.metadata.QueryMetadataInterface; import org.teiid.query.metadata.SupportConstants; @@ -58,7 +57,6 @@ import org.teiid.query.sql.symbol.SingleElementSymbol; import org.teiid.query.sql.util.SymbolMap; import org.teiid.query.util.CommandContext; -import org.teiid.translator.ExecutionFactory.NullOrder; import org.teiid.translator.ExecutionFactory.SupportedJoinCriteria; @@ -356,30 +354,8 @@ if(! canPushSymbol(symbol.getSymbol(), true, modelID, metadata, capFinder, record)) { return false; } - boolean supportsNullOrdering = CapabilitiesUtil.supports(Capability.QUERY_ORDERBY_NULL_ORDERING, modelID, metadata, capFinder); - NullOrder defaultNullOrder = CapabilitiesUtil.getDefaultNullOrder(modelID, metadata, capFinder); - if (symbol.getNullOrdering() != null) { - if (!supportsNullOrdering) { - if (symbol.getNullOrdering() == NullOrdering.FIRST) { - if (defaultNullOrder != NullOrder.FIRST && !(symbol.isAscending() && defaultNullOrder == NullOrder.LOW) - && !(!symbol.isAscending() && defaultNullOrder == NullOrder.HIGH)) { - return false; - } - } else if (defaultNullOrder != NullOrder.LAST && !(symbol.isAscending() && defaultNullOrder == NullOrder.HIGH) - && !(!symbol.isAscending() && defaultNullOrder == NullOrder.LOW)) { - return false; - } - symbol.setNullOrdering(null); - } - } else if (supportsNullOrdering && defaultNullOrder != NullOrder.LOW) { - //try to match the expected default of low - if (symbol.isAscending()) { - if (defaultNullOrder != NullOrder.FIRST) { - symbol.setNullOrdering(NullOrdering.FIRST); - } - } else if (defaultNullOrder != NullOrder.LAST) { - symbol.setNullOrdering(NullOrdering.LAST); - } + if (!CapabilitiesUtil.supportsNullOrdering(metadata, capFinder, modelID, symbol)) { + return false; } } Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/GroupingNode.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/GroupingNode.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/GroupingNode.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -39,7 +39,6 @@ import org.teiid.common.buffer.TupleSource; import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidProcessingException; -import org.teiid.language.SQLConstants.NonReserved; import org.teiid.language.SortSpecification.NullOrdering; import org.teiid.query.eval.Evaluator; import org.teiid.query.function.aggregate.AggregateFunction; @@ -235,12 +234,8 @@ functions[i] = new StatsFunction(function); } - if(aggSymbol.isDistinct() && !function.equals(NonReserved.MIN) && !function.equals(NonReserved.MAX)) { - SortingFilter filter = new SortingFilter(functions[i], getBufferManager(), getConnectionID(), true); - ElementSymbol element = new ElementSymbol("val"); //$NON-NLS-1$ - element.setType(inputType); - filter.setElements(Arrays.asList(element)); - functions[i] = filter; + if(aggSymbol.isDistinct()) { + functions[i] = handleDistinct(functions[i], inputType, getBufferManager(), getConnectionID()); } else if (aggSymbol.getOrderBy() != null) { //handle the xmlagg case int[] orderIndecies = new int[aggSymbol.getOrderBy().getOrderByItems().size()]; List<OrderByItem> orderByItems = new ArrayList<OrderByItem>(orderIndecies.length); @@ -277,6 +272,14 @@ } } + static SortingFilter handleDistinct(AggregateFunction af, Class<?> inputType, BufferManager bm, String cid) { + SortingFilter filter = new SortingFilter(af, bm, cid, true); + ElementSymbol element = new ElementSymbol("val"); //$NON-NLS-1$ + element.setType(inputType); + filter.setElements(Arrays.asList(element)); + return filter; + } + private int collectExpression(Expression ex) { int index = this.collectedExpressions.indexOf(ex); if(index == -1) { Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/WindowFunctionProjectNode.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/WindowFunctionProjectNode.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/WindowFunctionProjectNode.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -462,6 +462,10 @@ af = new StatsFunction(function); } + if(aggSymbol.isDistinct()) { + af = GroupingNode.handleDistinct(af, inputType, getBufferManager(), getConnectionID()); + } + af.setExpressionIndex(wfi.expressionIndex); af.setConditionIndex(wfi.conditionIndex); af.initialize(outputType, inputType); Modified: trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -2256,9 +2256,13 @@ expression.setAggregateFunction(Type.MAX); } } - if ((expression.getAggregateFunction() == Type.MAX || expression.getAggregateFunction() == Type.MIN) - && rewriteAggs && expression.getExpression() != null && EvaluatableVisitor.willBecomeConstant(expression.getExpression())) { - return expression.getExpression(); + if ((expression.getAggregateFunction() == Type.MAX || expression.getAggregateFunction() == Type.MIN)) { + if (expression.isDistinct()) { + expression.setDistinct(false); + } + if (rewriteAggs && expression.getExpression() != null && EvaluatableVisitor.willBecomeConstant(expression.getExpression())) { + return expression.getExpression(); + } } if (expression.getExpression() != null && expression.getCondition() != null && !expression.respectsNulls()) { Expression cond = expression.getCondition(); Modified: trunk/engine/src/main/java/org/teiid/query/sql/symbol/AggregateSymbol.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/symbol/AggregateSymbol.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/engine/src/main/java/org/teiid/query/sql/symbol/AggregateSymbol.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -156,6 +156,10 @@ public boolean isDistinct() { return this.distinct; } + + public void setDistinct(boolean distinct) { + this.distinct = distinct; + } /** * Get the type of the symbol, which depends on the aggregate function and the Modified: trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -1234,7 +1234,7 @@ break; } validateNoSubqueriesOrOuterReferences(windowFunction); - if (windowFunction.getFunction().getOrderBy() != null || windowFunction.getFunction().isDistinct()) { + if (windowFunction.getFunction().getOrderBy() != null || (windowFunction.getFunction().isDistinct() && windowFunction.getWindowSpecification().getOrderBy() != null)) { handleValidationError(QueryPlugin.Util.getString("ERR.015.012.0042", new Object[] {windowFunction.getFunction(), windowFunction}), windowFunction); //$NON-NLS-1$ } if (windowFunction.getWindowSpecification().getPartition() != null) { Modified: trunk/engine/src/main/resources/org/teiid/query/i18n.properties =================================================================== --- trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2011-08-30 19:30:48 UTC (rev 3437) @@ -210,7 +210,7 @@ ERR.015.012.0037 = {0} cannot be used outside of aggregate functions since they are not present in a GROUP BY clause. ERR.015.012.0039 = Nested aggregate expressions are not allowed: {0} ERR.015.012.0041 = The aggregate function {0} cannot be used with non-numeric expressions: {1} -ERR.015.012.0042 = A windowed aggregate function {0} cannot use its own order by clause or be distinct: {1} +ERR.015.012.0042 = A windowed aggregate function {0} cannot use its own order by clause or be distinct with an OVER ORDER BY: {1} AggregateValidationVisitor.non_comparable = The aggregate function {0} cannot be used with non-comparable expressions: {1} AggregateValidationVisitor.non_xml = The XMLAGG aggregate function {0} requires an expression of type XML: {1} AggregateValidationVisitor.non_boolean=The boolean aggregate functions ANY, SOME, EVERY require a boolean expression. @@ -938,4 +938,6 @@ DdlPlan.event_not_exists={0} does not have an INSTEAD OF trigger defined for {1}. DdlPlan.event_already_exists={0} already has an INSTEAD OF trigger defined for {1}. -error_refresh=error occurred during refreshing the materialized view entries for view {0} \ No newline at end of file +error_refresh=error occurred during refreshing the materialized view entries for view {0} + +support_required=Without required support property {0}, pushdown will not be enabled for {1} on translator {2}. \ No newline at end of file Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestWindowFunctions.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestWindowFunctions.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestWindowFunctions.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -34,6 +34,9 @@ import org.teiid.query.optimizer.capabilities.BasicSourceCapabilities; import org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder; import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability; +import org.teiid.query.processor.relational.AccessNode; +import org.teiid.query.processor.relational.ProjectNode; +import org.teiid.query.processor.relational.WindowFunctionProjectNode; import org.teiid.query.unittest.RealMetadataFactory; @SuppressWarnings({"nls", "unchecked"}) @@ -91,6 +94,49 @@ }); } + @Test public void testWindowFunctionPushdown2() throws Exception { + BasicSourceCapabilities caps = getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true); + caps.setCapabilitySupport(Capability.WINDOW_FUNCTION_ORDER_BY_AGGREGATES, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true); + ProcessorPlan plan = TestOptimizer.helpPlan("select max(e1) over (order by e1 nulls first) as y from pm1.g1", //$NON-NLS-1$ + RealMetadataFactory.example1Cached(), null, new DefaultCapabilitiesFinder(caps), + new String[] { + "SELECT g_0.e1 FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + + checkNodeTypes(plan, new int[] {1, 1, 1}, new Class<?>[] {AccessNode.class, WindowFunctionProjectNode.class, ProjectNode.class}); + + caps.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING, true); + plan = TestOptimizer.helpPlan("select max(e1) over (order by e1 nulls first) as y from pm1.g1", //$NON-NLS-1$ + RealMetadataFactory.example1Cached(), null, new DefaultCapabilitiesFinder(caps), + new String[] { + "SELECT MAX(g_0.e1) OVER (ORDER BY g_0.e1 NULLS FIRST) FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + + checkNodeTypes(plan, FULL_PUSHDOWN); + } + + @Test public void testWindowFunctionPushdown3() throws Exception { + BasicSourceCapabilities caps = getTypicalCapabilities(); + caps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_COUNT, true); + caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_DISTINCT, true); + ProcessorPlan plan = TestOptimizer.helpPlan("select count(distinct e1) over (partition by e2) as y from pm1.g1", //$NON-NLS-1$ + RealMetadataFactory.example1Cached(), null, new DefaultCapabilitiesFinder(caps), + new String[] { + "SELECT g_0.e1, g_0.e2 FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + + checkNodeTypes(plan, new int[] {1, 1, 1}, new Class<?>[] {AccessNode.class, WindowFunctionProjectNode.class, ProjectNode.class}); + + caps.setCapabilitySupport(Capability.WINDOW_FUNCTION_DISTINCT_AGGREGATES, true); + plan = TestOptimizer.helpPlan("select count(distinct e1) over (partition by e2) as y from pm1.g1", //$NON-NLS-1$ + RealMetadataFactory.example1Cached(), null, new DefaultCapabilitiesFinder(caps), + new String[] { + "SELECT COUNT(DISTINCT g_0.e1) OVER (PARTITION BY g_0.e2) FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + + checkNodeTypes(plan, FULL_PUSHDOWN); + } + + @Test public void testRanking() throws Exception { String sql = "select e1, row_number() over (order by e1), rank() over (order by e1), dense_rank() over (order by e1 nulls last) from pm1.g1"; @@ -236,4 +282,24 @@ helpProcess(plan, dataManager, expected); } + @Test public void testPartitionedDistinctCount() throws Exception { + String sql = "select e1, e3, count(distinct e1) over (partition by e3) as r from pm1.g1 order by e1, e3"; + + List<?>[] expected = new List[] { + Arrays.asList(null, Boolean.FALSE, 2), + Arrays.asList("a", Boolean.FALSE, 2), + Arrays.asList("a", Boolean.FALSE, 2), + Arrays.asList("a", Boolean.TRUE, 2), + Arrays.asList("b", Boolean.FALSE, 2), + Arrays.asList("c", Boolean.TRUE, 2), + }; + + FakeDataManager dataManager = new FakeDataManager(); + sampleData1(dataManager); + ProcessorPlan plan = helpGetPlan(sql, RealMetadataFactory.example1Cached()); + + helpProcess(plan, dataManager, expected); + } + + } Modified: trunk/engine/src/test/java/org/teiid/query/validator/TestValidator.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/validator/TestValidator.java 2011-08-30 18:11:33 UTC (rev 3436) +++ trunk/engine/src/test/java/org/teiid/query/validator/TestValidator.java 2011-08-30 19:30:48 UTC (rev 3437) @@ -1949,4 +1949,8 @@ helpValidate("select count(*) over () as y", new String[] {"COUNT(*) OVER ()"}, RealMetadataFactory.example1Cached()); } + @Test public void testWindowFunctionOrderedDistinct() { + helpValidate("select count(distinct e1) over (order by e2) as y from pm1.g1", new String[] {"COUNT(DISTINCT e1) OVER (ORDER BY e2)"}, RealMetadataFactory.example1Cached()); + } + }