Author: shawkins Date: 2012-10-05 08:36:44 -0400 (Fri, 05 Oct 2012) New Revision: 4515 Modified: trunk/build/kits/jboss-as7/docs/teiid/teiid-releasenotes.html trunk/engine/src/main/java/org/teiid/query/metadata/TempMetadataAdapter.java trunk/engine/src/main/java/org/teiid/query/optimizer/QueryOptimizer.java trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/BasicSourceCapabilities.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/RuleCollapseSource.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java trunk/engine/src/main/java/org/teiid/query/util/Options.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java trunk/engine/src/test/java/org/teiid/query/processor/TestOrderByProcessing.java trunk/engine/src/test/java/org/teiid/query/processor/TestWindowFunctions.java trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/BaseQueryTest.java trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java Log: TEIID-2245 limiting when nulls first/last is pushed down Modified: trunk/build/kits/jboss-as7/docs/teiid/teiid-releasenotes.html =================================================================== --- trunk/build/kits/jboss-as7/docs/teiid/teiid-releasenotes.html 2012-10-04 16:49:23 UTC (rev 4514) +++ trunk/build/kits/jboss-as7/docs/teiid/teiid-releasenotes.html 2012-10-05 12:36:44 UTC (rev 4515) @@ -62,7 +62,8 @@ <li>TEIID-2226 All statements that return result sets that are executed as command statements in a procedure are validated against the expected resultset columns of the procedure. If the statement is not intended to be returnable, WITHOUT RETURN can be added to the end of the statement. <li>TEIID-2235 The MetadataRepository.setNext was removed. Also if an instance of a DefaultMetadataRepository is used, it will only affect metadata already loaded in the repository chain. - <li>TEIID-2237 teiid_ is a reserved DDL namespace prefix and the MetadataFactory class no longer throws TranslatorExceptions, instead the unchecked MetadataException is thrown. + <li>TEIID-2237 teiid_ is a reserved DDL namespace prefix and the MetadataFactory class no longer throws TranslatorExceptions, instead the unchecked MetadataException is thrown. + <li>TEIID-2243 by default Teiid will not pushdown the default null sort order of nulls low when no null sort order is specified. Set the system property org.teiid.pushdownDefaultNullOrder to true mimic the 8.1 and older release behavior. </ul> <h4>from 8.0</h4> Modified: trunk/engine/src/main/java/org/teiid/query/metadata/TempMetadataAdapter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/metadata/TempMetadataAdapter.java 2012-10-04 16:49:23 UTC (rev 4514) +++ trunk/engine/src/main/java/org/teiid/query/metadata/TempMetadataAdapter.java 2012-10-05 12:36:44 UTC (rev 4515) @@ -92,15 +92,11 @@ return this.actualMetadata.getSessionMetadata(); } - public QueryMetadataInterface getDesignTimeMetadata() { - if (isSession()) { - return this.actualMetadata.getDesignTimeMetadata(); - } - return super.getDesignTimeMetadata(); - } - @Override protected QueryMetadataInterface createDesignTimeMetadata() { + if (isSession()) { + return new TempMetadataAdapter(this.actualMetadata.getDesignTimeMetadata(), new TempMetadataStore()); + } return new TempMetadataAdapter(this.actualMetadata.getDesignTimeMetadata(), tempStore, materializationTables, queryNodes); } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/QueryOptimizer.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/QueryOptimizer.java 2012-10-04 16:49:23 UTC (rev 4514) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/QueryOptimizer.java 2012-10-05 12:36:44 UTC (rev 4515) @@ -82,13 +82,14 @@ boolean debug = analysisRecord.recordDebug(); - TempMetadataStore tempMetadata = command.getTemporaryMetadata(); - if (tempMetadata != null) { - metadata = new TempMetadataAdapter(metadata, tempMetadata); - } else if (!(metadata instanceof TempMetadataAdapter)) { + if (!(metadata instanceof TempMetadataAdapter)) { metadata = new TempMetadataAdapter(metadata, new TempMetadataStore()); } - + + if (context.getMetadata() == null) { + context.setMetadata(metadata); + } + // Create an ID generator that can be used for all plans to generate unique data node IDs if(idGenerator == null) { idGenerator = new IDGenerator(); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/BasicSourceCapabilities.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/BasicSourceCapabilities.java 2012-10-04 16:49:23 UTC (rev 4514) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/capabilities/BasicSourceCapabilities.java 2012-10-05 12:36:44 UTC (rev 4515) @@ -29,6 +29,7 @@ import org.teiid.translator.ExecutionFactory; import org.teiid.translator.ExecutionFactory.Format; +import org.teiid.translator.ExecutionFactory.NullOrder; /** */ @@ -45,6 +46,8 @@ * Construct a basic capabilities object. */ public BasicSourceCapabilities() { + //for test compatibility we'll assume low, this does get replaced by a proper value at runtime + setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.LOW); } public boolean supportsCapability(Capability capability) { 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 2012-10-04 16:49:23 UTC (rev 4514) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CapabilitiesUtil.java 2012-10-05 12:36:44 UTC (rev 4515) @@ -469,28 +469,16 @@ 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)) { + if (symbol.getNullOrdering() != null && !supportsNullOrdering) { + if (symbol.getNullOrdering() == NullOrdering.FIRST) { + if (defaultNullOrder != NullOrder.FIRST && !(symbol.isAscending() && defaultNullOrder == NullOrder.LOW) + && !(!symbol.isAscending() && defaultNullOrder == NullOrder.HIGH)) { 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); - } + } else if (defaultNullOrder != NullOrder.LAST && !(symbol.isAscending() && defaultNullOrder == NullOrder.HIGH) + && !(!symbol.isAscending() && defaultNullOrder == NullOrder.LOW)) { + return false; + } } return true; } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleCollapseSource.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleCollapseSource.java 2012-10-04 16:49:23 UTC (rev 4514) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleCollapseSource.java 2012-10-05 12:36:44 UTC (rev 4515) @@ -38,6 +38,7 @@ import org.teiid.core.TeiidException; import org.teiid.core.TeiidRuntimeException; import org.teiid.core.types.DataTypeManager; +import org.teiid.language.SortSpecification.NullOrdering; import org.teiid.query.QueryPlugin; import org.teiid.query.analysis.AnalysisRecord; import org.teiid.query.metadata.QueryMetadataInterface; @@ -71,6 +72,7 @@ import org.teiid.query.sql.visitor.ExpressionMappingVisitor; import org.teiid.query.sql.visitor.ValueIteratorProviderCollectorVisitor; import org.teiid.query.util.CommandContext; +import org.teiid.translator.ExecutionFactory.NullOrder; public final class RuleCollapseSource implements OptimizerRule { @@ -96,7 +98,7 @@ commandRoot = NodeEditor.findNodePreOrder(accessNode, NodeConstants.Types.SOURCE).getFirstChild(); } plan = removeUnnecessaryInlineView(plan, commandRoot); - QueryCommand queryCommand = createQuery(metadata, capFinder, accessNode, commandRoot); + QueryCommand queryCommand = createQuery(context, capFinder, accessNode, commandRoot); addDistinct(metadata, capFinder, accessNode, queryCommand); command = queryCommand; if (intoGroup != null) { @@ -193,8 +195,10 @@ return root; } - private QueryCommand createQuery(QueryMetadataInterface metadata, CapabilitiesFinder capFinder, PlanNode accessRoot, PlanNode node) throws QueryMetadataException, TeiidComponentException, QueryPlannerException { + private QueryCommand createQuery(CommandContext context, CapabilitiesFinder capFinder, PlanNode accessRoot, PlanNode node) throws QueryMetadataException, TeiidComponentException, QueryPlannerException { + QueryMetadataInterface metadata = context.getMetadata(); PlanNode setOpNode = NodeEditor.findNodePreOrder(node, NodeConstants.Types.SET_OP, NodeConstants.Types.SOURCE); + Object modelID = RuleRaiseAccess.getModelIDFromAccess(accessRoot, metadata); if (setOpNode != null) { Operation setOp = (Operation)setOpNode.getProperty(NodeConstants.Info.SET_OPERATION); SetQuery unionCommand = new SetQuery(setOp); @@ -202,7 +206,7 @@ unionCommand.setAll(unionAll); PlanNode sort = NodeEditor.findNodePreOrder(node, NodeConstants.Types.SORT, NodeConstants.Types.SET_OP); if (sort != null) { - processOrderBy(sort, unionCommand); + processOrderBy(sort, unionCommand, modelID, context, capFinder); } PlanNode limit = NodeEditor.findNodePreOrder(node, NodeConstants.Types.TUPLE_LIMIT, NodeConstants.Types.SET_OP); if (limit != null) { @@ -210,7 +214,7 @@ } int count = 0; for (PlanNode child : setOpNode.getChildren()) { - QueryCommand command = createQuery(metadata, capFinder, accessRoot, child); + QueryCommand command = createQuery(context, capFinder, accessRoot, child); if (count == 0) { unionCommand.setLeftQuery(command); } else if (count == 1) { @@ -229,11 +233,10 @@ select.addSymbols(columns); query.setSelect(select); query.setFrom(new From()); - buildQuery(accessRoot, node, query, metadata, capFinder); + buildQuery(accessRoot, node, query, context, capFinder); if (query.getCriteria() instanceof CompoundCriteria) { query.setCriteria(QueryRewriter.optimizeCriteria((CompoundCriteria)query.getCriteria(), metadata)); } - Object modelID = RuleRaiseAccess.getModelIDFromAccess(accessRoot, metadata); if (!CapabilitiesUtil.useAnsiJoin(modelID, metadata, capFinder)) { simplifyFromClause(query); } @@ -297,8 +300,8 @@ return null; } - void buildQuery(PlanNode accessRoot, PlanNode node, Query query, QueryMetadataInterface metadata, CapabilitiesFinder capFinder) throws QueryMetadataException, TeiidComponentException, QueryPlannerException { - + void buildQuery(PlanNode accessRoot, PlanNode node, Query query, CommandContext context, CapabilitiesFinder capFinder) throws QueryMetadataException, TeiidComponentException, QueryPlannerException { + QueryMetadataInterface metadata = context.getMetadata(); //visit source and join nodes as they appear switch(node.getType()) { case NodeConstants.Types.JOIN: @@ -324,12 +327,12 @@ * if the join is a left outer join, criteria from the right side will be added to the on clause */ Criteria savedCriteria = null; - buildQuery(accessRoot, left, query, metadata, capFinder); + buildQuery(accessRoot, left, query, context, capFinder); if (joinType == JoinType.JOIN_LEFT_OUTER) { savedCriteria = query.getCriteria(); query.setCriteria(null); } - buildQuery(accessRoot, right, query, metadata, capFinder); + buildQuery(accessRoot, right, query, context, capFinder); if (joinType == JoinType.JOIN_LEFT_OUTER) { moveWhereClauseIntoOnClause(query, crits); query.setCriteria(savedCriteria); @@ -360,7 +363,7 @@ { if (Boolean.TRUE.equals(node.getProperty(NodeConstants.Info.INLINE_VIEW))) { PlanNode child = node.getFirstChild(); - QueryCommand newQuery = createQuery(metadata, capFinder, accessRoot, child); + QueryCommand newQuery = createQuery(context, capFinder, accessRoot, child); //ensure that the group is consistent GroupSymbol symbol = node.getGroups().iterator().next(); @@ -374,7 +377,7 @@ } for (PlanNode childNode : node.getChildren()) { - buildQuery(accessRoot, childNode, query, metadata, capFinder); + buildQuery(accessRoot, childNode, query, context, capFinder); } switch(node.getType()) { @@ -391,7 +394,7 @@ } case NodeConstants.Types.SORT: { - processOrderBy(node, query); + processOrderBy(node, query, RuleRaiseAccess.getModelIDFromAccess(accessRoot, metadata), context, capFinder); break; } case NodeConstants.Types.DUP_REMOVE: @@ -486,7 +489,7 @@ query.setCriteria(null); } - private void processOrderBy(PlanNode node, QueryCommand query) { + private void processOrderBy(PlanNode node, QueryCommand query, Object modelID, CommandContext context, CapabilitiesFinder capFinder) throws QueryMetadataException, TeiidComponentException { OrderBy orderBy = (OrderBy)node.getProperty(NodeConstants.Info.SORT_ORDER); query.setOrderBy(orderBy); if (query instanceof Query) { @@ -496,6 +499,24 @@ } QueryRewriter.rewriteOrderBy(query, orderBy, query.getProjectedSymbols(), new LinkedList<OrderByItem>()); } + boolean supportsNullOrdering = CapabilitiesUtil.supports(Capability.QUERY_ORDERBY_NULL_ORDERING, modelID, context.getMetadata(), capFinder); + NullOrder defaultNullOrder = CapabilitiesUtil.getDefaultNullOrder(modelID, context.getMetadata(), capFinder); + for (OrderByItem item : orderBy.getOrderByItems()) { + if (item.getNullOrdering() != null) { + if (!supportsNullOrdering) { + item.setNullOrdering(null); + } + } else if (supportsNullOrdering && defaultNullOrder != NullOrder.LOW && context.getOptions().isPushdownDefaultNullOrder()) { + //try to match the expected default of low + if (item.isAscending()) { + if (defaultNullOrder != NullOrder.FIRST) { + item.setNullOrdering(NullOrdering.FIRST); + } + } else if (defaultNullOrder != NullOrder.LAST) { + item.setNullOrdering(NullOrdering.LAST); + } + } + } } /** Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java 2012-10-04 16:49:23 UTC (rev 4514) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java 2012-10-05 12:36:44 UTC (rev 4515) @@ -34,9 +34,11 @@ 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.optimizer.capabilities.CapabilitiesFinder; +import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability; import org.teiid.query.optimizer.relational.OptimizerRule; import org.teiid.query.optimizer.relational.RuleStack; import org.teiid.query.optimizer.relational.plantree.NodeConstants; @@ -48,15 +50,19 @@ import org.teiid.query.processor.relational.JoinNode.JoinStrategyType; import org.teiid.query.processor.relational.MergeJoinStrategy.SortOption; import org.teiid.query.sql.lang.CompareCriteria; +import org.teiid.query.sql.lang.CompoundCriteria; import org.teiid.query.sql.lang.Criteria; +import org.teiid.query.sql.lang.IsNullCriteria; import org.teiid.query.sql.lang.JoinType; import org.teiid.query.sql.lang.OrderBy; +import org.teiid.query.sql.lang.OrderByItem; import org.teiid.query.sql.symbol.ElementSymbol; import org.teiid.query.sql.symbol.Expression; import org.teiid.query.sql.symbol.GroupSymbol; import org.teiid.query.sql.util.SymbolMap; import org.teiid.query.sql.visitor.GroupsUsedByElementsVisitor; import org.teiid.query.util.CommandContext; +import org.teiid.translator.ExecutionFactory.NullOrder; /** @@ -249,12 +255,40 @@ joinNode.setProperty(joinNode.getFirstChild() == childNode ? NodeConstants.Info.IS_LEFT_DISTINCT : NodeConstants.Info.IS_RIGHT_DISTINCT, true); } if (attemptPush && RuleRaiseAccess.canRaiseOverSort(sourceNode, metadata, capFinder, sortNode, null, false)) { - sourceNode.getFirstChild().addAsParent(sortNode); - - if (needsCorrection) { - correctOutputElements(joinNode, outputSymbols, sortNode); + boolean push = true; + Object model = RuleRaiseAccess.getModelIDFromAccess(sourceNode, metadata); + NullOrder order = CapabilitiesUtil.getDefaultNullOrder(model, metadata, capFinder); + if (order != NullOrder.LOW && order != NullOrder.FIRST) { + //TODO: check the metadata and query structure to see if nulls are possible + JoinType joinType = (JoinType) joinNode.getProperty(NodeConstants.Info.JOIN_TYPE); + if (joinType == JoinType.JOIN_INNER || joinType == JoinType.JOIN_SEMI || ((joinType == JoinType.JOIN_LEFT_OUTER || joinType == JoinType.JOIN_ANTI_SEMI) && joinNode.getLastChild() == childNode)) { + //just filter nulls on the teiid side + CompoundCriteria crit = new CompoundCriteria(); + for (Expression expression : orderSymbols) { + IsNullCriteria inc = new IsNullCriteria((Expression) expression.clone()); + inc.setNegated(true); + crit.addCriteria(inc); + } + PlanNode critNode = NodeFactory.getNewNode(NodeConstants.Types.SELECT); + critNode.setProperty(NodeConstants.Info.OUTPUT_COLS, new ArrayList<Expression>(outputSymbols)); + critNode.setProperty(NodeConstants.Info.SELECT_CRITERIA, crit); + sourceNode.addAsParent(critNode); + } else if (CapabilitiesUtil.supports(Capability.QUERY_ORDERBY_NULL_ORDERING, model, metadata, capFinder)) { + for (OrderByItem item : ((OrderBy)sortNode.getProperty(NodeConstants.Info.SORT_ORDER)).getOrderByItems()) { + item.setNullOrdering(NullOrdering.FIRST); + } + } else { + push = false; + } } - return true; + if (push) { + sourceNode.getFirstChild().addAsParent(sortNode); + + if (needsCorrection) { + correctOutputElements(joinNode, outputSymbols, sortNode); + } + return true; + } } } else if (sourceNode.getType() == NodeConstants.Types.GROUP) { sourceNode.addAsParent(sortNode); @@ -271,7 +305,7 @@ if (needsCorrection) { PlanNode projectNode = NodeFactory.getNewNode(NodeConstants.Types.PROJECT); - projectNode.setProperty(NodeConstants.Info.PROJECT_COLS, new ArrayList(outputSymbols)); + projectNode.setProperty(NodeConstants.Info.PROJECT_COLS, new ArrayList<Expression>(outputSymbols)); childNode.addAsParent(projectNode); correctOutputElements(joinNode, outputSymbols, projectNode); } @@ -279,18 +313,19 @@ } private static PlanNode createSortNode(List<Expression> orderSymbols, - Collection outputElements) { + Collection<Expression> outputElements) { PlanNode sortNode = NodeFactory.getNewNode(NodeConstants.Types.SORT); - sortNode.setProperty(NodeConstants.Info.SORT_ORDER, new OrderBy(orderSymbols)); - sortNode.setProperty(NodeConstants.Info.OUTPUT_COLS, new ArrayList(outputElements)); + OrderBy order = new OrderBy(orderSymbols); + sortNode.setProperty(NodeConstants.Info.SORT_ORDER, order); + sortNode.setProperty(NodeConstants.Info.OUTPUT_COLS, new ArrayList<Expression>(outputElements)); return sortNode; } private static void correctOutputElements(PlanNode endNode, - Collection outputElements, + Collection<Expression> outputElements, PlanNode startNode) { while (startNode != endNode) { - startNode.setProperty(NodeConstants.Info.OUTPUT_COLS, new ArrayList(outputElements)); + startNode.setProperty(NodeConstants.Info.OUTPUT_COLS, new ArrayList<Expression>(outputElements)); startNode = startNode.getParent(); } } Modified: trunk/engine/src/main/java/org/teiid/query/util/Options.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/util/Options.java 2012-10-04 16:49:23 UTC (rev 4514) +++ trunk/engine/src/main/java/org/teiid/query/util/Options.java 2012-10-05 12:36:44 UTC (rev 4515) @@ -23,19 +23,18 @@ package org.teiid.query.util; import java.util.Properties; -import java.util.Set; /** * A holder for options */ public class Options { - public static final String UNNEST_DEFAULT = "org.teiid.subqueryUnnestDefault"; //$NON-NLS-1$ - public static final String LANGUAGES_DEFAULT = "org.teiid.languagesWithoutDataRoles"; //$NON-NLS-1$ + public static final String UNNEST_DEFAULT = "org.teiid.subqueryUnnestDefault"; //$NON-NLS-1$ + public static final String PUSHDOWN_DEFAULT_NULL_ORDER = "org.teiid.pushdownDefaultNullOrder"; //$NON-NLS-1$ private Properties properties; - private Set<String> languages; private boolean subqueryUnnestDefault; + private boolean pushdownDefaultNullOrder; public Properties getProperties() { return properties; @@ -45,14 +44,6 @@ this.properties = properties; } - public void setLanguages(Set<String> languages) { - this.languages = languages; - } - - public Set<String> getLanguages() { - return languages; - } - public boolean isSubqueryUnnestDefault() { return subqueryUnnestDefault; } @@ -65,5 +56,18 @@ this.subqueryUnnestDefault = s; return this; } + + public boolean isPushdownDefaultNullOrder() { + return pushdownDefaultNullOrder; + } + + public void setPushdownDefaultNullOrder(boolean virtualizeDefaultNullOrdering) { + this.pushdownDefaultNullOrder = virtualizeDefaultNullOrdering; + } + + public Options pushdownDefaultNullOrder(boolean p) { + this.pushdownDefaultNullOrder = p; + return this; + } } Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java 2012-10-04 16:49:23 UTC (rev 4514) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestJoinOptimization.java 2012-10-05 12:36:44 UTC (rev 4515) @@ -52,6 +52,7 @@ import org.teiid.query.sql.symbol.GroupSymbol; import org.teiid.query.unittest.RealMetadataFactory; import org.teiid.translator.SourceSystemFunctions; +import org.teiid.translator.ExecutionFactory.NullOrder; @SuppressWarnings("nls") public class TestJoinOptimization { @@ -1047,5 +1048,64 @@ ComparisonMode.EXACT_COMMAND_STRING ); } + + @Test public void testJoinNullHandling() throws Exception { + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities bqt1 = new BasicSourceCapabilities(); + bqt1.setCapabilitySupport(Capability.QUERY_ORDERBY, true); + bqt1.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.HIGH); + bqt1.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING, true); + + BasicSourceCapabilities bqt2 = new BasicSourceCapabilities(); + bqt2.setCapabilitySupport(Capability.QUERY_ORDERBY, true); + bqt2.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.HIGH); + + capFinder.addCapabilities("BQT1", bqt1); //$NON-NLS-1$ + capFinder.addCapabilities("BQT2", bqt2); //$NON-NLS-1$ + + String sql = "select bqt1.smalla.intkey, bqt2.smalla.intkey from bqt1.smalla full outer join bqt2.smalla on (bqt1.smalla.intkey = bqt2.smalla.intkey)"; //$NON-NLS-1$ + // can't push order by to bqt2 as there is no compensating action available. + ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), new String[] {"SELECT BQT2.SmallA.IntKey FROM BQT2.SmallA", "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA ORDER BY BQT1.SmallA.IntKey NULLS FIRST"}, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ + + TestOptimizer.checkNodeTypes(plan, new int[] { + 2, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // Join + 1, // MergeJoin + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + + sql = "select bqt1.smalla.intkey, bqt2.smalla.intkey from bqt1.smalla left outer join bqt2.smalla on (bqt1.smalla.intkey = bqt2.smalla.intkey)"; //$NON-NLS-1$ + + // can push order by to bqt2 by adding a null filter + plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), new String[] {"SELECT BQT2.SmallA.IntKey FROM BQT2.SmallA ORDER BY BQT2.SmallA.IntKey", "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA ORDER BY BQT1.SmallA.IntKey NULLS FIRST"}, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ + + TestOptimizer.checkNodeTypes(plan, new int[] { + 2, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // Join + 1, // MergeJoin + 0, // Null + 0, // PlanExecution + 1, // Project + 1, // Select + 0, // Sort + 0 // UnionAll + }); + } + } Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestOrderByProcessing.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestOrderByProcessing.java 2012-10-04 16:49:23 UTC (rev 4514) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestOrderByProcessing.java 2012-10-05 12:36:44 UTC (rev 4515) @@ -34,6 +34,8 @@ import org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder; import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability; import org.teiid.query.unittest.RealMetadataFactory; +import org.teiid.query.util.CommandContext; +import org.teiid.query.util.Options; import org.teiid.translator.ExecutionFactory.NullOrder; @SuppressWarnings({"nls", "unchecked"}) @@ -146,56 +148,58 @@ ProcessorPlan plan = TestOptimizer.helpPlan("select e1 from pm1.g1 order by e1 desc, e2 asc", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), null, capFinder, - new String[] { "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0 DESC NULLS LAST, g_0.e2 NULLS FIRST"}, //$NON-NLS-1$ + new String[] { "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0 DESC, g_0.e2"}, //$NON-NLS-1$ TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - - @Test public void testNullOrdering1() throws Exception { + + @Test public void testNullOrdering2() throws Exception { FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING, true); - caps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.LAST); + caps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.FIRST); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ - - ProcessorPlan plan = TestOptimizer.helpPlan("select e1 from pm1.g1 order by e1 desc, e2 asc", //$NON-NLS-1$ - RealMetadataFactory.example1Cached(), null, capFinder, - new String[] { "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0 DESC, g_0.e2 NULLS FIRST"}, //$NON-NLS-1$ - TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); - + QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); + CommandContext cc = new CommandContext(); + cc.setOptions(new Options().pushdownDefaultNullOrder(true)); + ProcessorPlan plan = TestOptimizer.getPlan(TestOptimizer.helpGetCommand("select e1 from pm1.g1 order by e1 desc, e2 asc NULLS LAST", metadata, null), metadata, capFinder, null, true, cc); + TestOptimizer.checkAtomicQueries(new String[] { "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0 DESC NULLS LAST, g_0.e2 NULLS LAST"}, plan); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } - @Test public void testNullOrdering2() throws Exception { + /** + * The engine will remove the null ordering if it's not needed + * @throws Exception + */ + @Test public void testNullOrdering3() throws Exception { FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); - caps.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING, true); + caps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.HIGH); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ - - ProcessorPlan plan = TestOptimizer.helpPlan("select e1 from pm1.g1 order by e1 desc NULLS FIRST, e2 asc NULLS LAST", //$NON-NLS-1$ - RealMetadataFactory.example1Cached(), null, capFinder, - new String[] { "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0 DESC NULLS FIRST, g_0.e2 NULLS LAST"}, //$NON-NLS-1$ - TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); - + QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); + CommandContext cc = new CommandContext(); + cc.setOptions(new Options().pushdownDefaultNullOrder(true)); + ProcessorPlan plan = TestOptimizer.getPlan(TestOptimizer.helpGetCommand("select e1 from pm1.g1 order by e1 desc, e2 asc NULLS LAST", metadata, null), metadata, capFinder, null, true, cc); + TestOptimizer.checkAtomicQueries(new String[] { "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0 DESC, g_0.e2"}, plan); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } /** - * The engine will remove the null ordering if it's not needed + * turns on virtualization * @throws Exception */ - @Test public void testNullOrdering3() throws Exception { + @Test public void testNullOrdering4() throws Exception { FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); - caps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.HIGH); + caps.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING, true); + caps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.UNKNOWN); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ - - ProcessorPlan plan = TestOptimizer.helpPlan("select e1 from pm1.g1 order by e1 desc, e2 asc NULLS LAST", //$NON-NLS-1$ - RealMetadataFactory.example1Cached(), null, capFinder, - new String[] { "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0 DESC, g_0.e2"}, //$NON-NLS-1$ - TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); - + QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); + CommandContext cc = new CommandContext(); + cc.setOptions(new Options().pushdownDefaultNullOrder(true)); + ProcessorPlan plan = TestOptimizer.getPlan(TestOptimizer.helpGetCommand("select e1 from pm1.g1 order by e1 desc, e2 asc", metadata, null), metadata, capFinder, null, true, cc); + TestOptimizer.checkAtomicQueries(new String[] { "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0 DESC NULLS LAST, g_0.e2 NULLS FIRST"}, plan); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); } Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestWindowFunctions.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestWindowFunctions.java 2012-10-04 16:49:23 UTC (rev 4514) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestWindowFunctions.java 2012-10-05 12:36:44 UTC (rev 4515) @@ -38,6 +38,7 @@ import org.teiid.query.processor.relational.ProjectNode; import org.teiid.query.processor.relational.WindowFunctionProjectNode; import org.teiid.query.unittest.RealMetadataFactory; +import org.teiid.translator.ExecutionFactory.NullOrder; @SuppressWarnings({"nls", "unchecked"}) public class TestWindowFunctions { @@ -99,6 +100,7 @@ caps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true); caps.setCapabilitySupport(Capability.WINDOW_FUNCTION_ORDER_BY_AGGREGATES, true); caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true); + caps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.UNKNOWN); 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[] { Modified: trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/BaseQueryTest.java =================================================================== --- trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/BaseQueryTest.java 2012-10-04 16:49:23 UTC (rev 4514) +++ trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/BaseQueryTest.java 2012-10-05 12:36:44 UTC (rev 4515) @@ -56,9 +56,6 @@ protected void doProcess(QueryMetadataInterface metadata, String sql, CapabilitiesFinder capFinder, ProcessorDataManager dataManager, List[] expectedResults, boolean debug) throws Exception { CommandContext context = createCommandContext(); - if (context.getMetadata() == null) { - context.setMetadata(metadata); - } Command command = TestOptimizer.helpGetCommand(sql, metadata, null); // plan Modified: trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java =================================================================== --- trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java 2012-10-04 16:49:23 UTC (rev 4514) +++ trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java 2012-10-05 12:36:44 UTC (rev 4515) @@ -63,7 +63,7 @@ Arrays.asList(new Object[] { new Double(3459808.0), new BigDecimal("405838.6989"), TimestampUtil.createDate(95, 2, 4), new Double(0.0) }), //$NON-NLS-1$ Arrays.asList(new Object[] { new Double(492164.0), new BigDecimal("390324.0610"), TimestampUtil.createDate(95, 1, 19), new Double(0.0) }) }; //$NON-NLS-1$ - dataMgr.addData("SELECT g_2.L_ORDERKEY AS c_0, SUM((g_2.L_EXTENDEDPRICE * (1 - g_2.L_DISCOUNT))) AS c_1, g_1.O_ORDERDATE AS c_2, g_1.O_SHIPPRIORITY AS c_3 FROM TPCR_Oracle_9i.CUSTOMER AS g_0, TPCR_Oracle_9i.ORDERS AS g_1, TPCR_Oracle_9i.LINEITEM AS g_2 WHERE (g_0.C_CUSTKEY = g_1.O_CUSTKEY) AND (g_2.L_ORDERKEY = g_1.O_ORDERKEY) AND (g_0.C_MKTSEGMENT = 'BUILDING') AND (g_1.O_ORDERDATE < {d'1995-03-15'}) AND (g_2.L_SHIPDATE > {ts'1995-03-15 00:00:00.0'}) GROUP BY g_2.L_ORDERKEY, g_1.O_ORDERDATE, g_1.O_SHIPPRIORITY ORDER BY c_1 DESC NULLS LAST, c_2 NULLS FIRST", //$NON-NLS-1$ + dataMgr.addData("SELECT g_2.L_ORDERKEY AS c_0, SUM((g_2.L_EXTENDEDPRICE * (1 - g_2.L_DISCOUNT))) AS c_1, g_1.O_ORDERDATE AS c_2, g_1.O_SHIPPRIORITY AS c_3 FROM TPCR_Oracle_9i.CUSTOMER AS g_0, TPCR_Oracle_9i.ORDERS AS g_1, TPCR_Oracle_9i.LINEITEM AS g_2 WHERE (g_0.C_CUSTKEY = g_1.O_CUSTKEY) AND (g_2.L_ORDERKEY = g_1.O_ORDERKEY) AND (g_0.C_MKTSEGMENT = 'BUILDING') AND (g_1.O_ORDERDATE < {d'1995-03-15'}) AND (g_2.L_SHIPDATE > {ts'1995-03-15 00:00:00.0'}) GROUP BY g_2.L_ORDERKEY, g_1.O_ORDERDATE, g_1.O_SHIPPRIORITY ORDER BY c_1 DESC, c_2", //$NON-NLS-1$ expected); doProcess(METADATA, @@ -191,7 +191,7 @@ ProcessorPlan plan = TestOptimizer.helpPlan("SELECT custsale.cntrycode, COUNT(*) AS numcust, SUM(c_acctbal) AS totacctbal FROM (SELECT left(C_PHONE, 2) AS cntrycode, CUSTOMER.C_ACCTBAL FROM CUSTOMER WHERE (left(C_PHONE, 2) IN ('13','31','23','29','30','18','17')) AND (CUSTOMER.C_ACCTBAL > (SELECT AVG(CUSTOMER.C_ACCTBAL) FROM CUSTOMER WHERE (CUSTOMER.C_ACCTBAL > 0.0) AND (left(C_PHONE, 2) IN ('13','31','23','29','30','18','17')))) AND (NOT (EXISTS (SELECT * FROM ORDERS WHERE O_CUSTKEY = C_CUSTKEY)))) AS custsale GROUP BY custsale.cntrycode ORDER BY custsale.cntrycode", //$NON-NLS-1$ METADATA, null, finder, - new String[] {"SELECT left(g_0.C_PHONE, 2) AS c_0, COUNT(*) AS c_1, SUM(g_0.C_ACCTBAL) AS c_2 FROM TPCR_Oracle_9i.CUSTOMER AS g_0 WHERE (left(g_0.C_PHONE, 2) IN ('13', '31', '23', '29', '30', '18', '17')) AND (g_0.C_ACCTBAL > (SELECT AVG(g_1.C_ACCTBAL) FROM TPCR_Oracle_9i.CUSTOMER AS g_1 WHERE (g_1.C_ACCTBAL > 0.0) AND (left(g_1.C_PHONE, 2) IN ('13', '31', '23', '29', '30', '18', '17')))) AND (NOT EXISTS (SELECT 1 FROM TPCR_Oracle_9i.ORDERS AS g_2 WHERE g_2.O_CUSTKEY = g_0.C_CUSTKEY)) GROUP BY left(g_0.C_PHONE, 2) ORDER BY c_0 NULLS FIRST"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + new String[] {"SELECT left(g_0.C_PHONE, 2) AS c_0, COUNT(*) AS c_1, SUM(g_0.C_ACCTBAL) AS c_2 FROM TPCR_Oracle_9i.CUSTOMER AS g_0 WHERE (left(g_0.C_PHONE, 2) IN ('13', '31', '23', '29', '30', '18', '17')) AND (g_0.C_ACCTBAL > (SELECT AVG(g_1.C_ACCTBAL) FROM TPCR_Oracle_9i.CUSTOMER AS g_1 WHERE (g_1.C_ACCTBAL > 0.0) AND (left(g_1.C_PHONE, 2) IN ('13', '31', '23', '29', '30', '18', '17')))) AND (NOT EXISTS (SELECT 1 FROM TPCR_Oracle_9i.ORDERS AS g_2 WHERE g_2.O_CUSTKEY = g_0.C_CUSTKEY)) GROUP BY left(g_0.C_PHONE, 2) ORDER BY c_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }