Author: shawkins Date: 2010-11-16 13:23:48 -0500 (Tue, 16 Nov 2010) New Revision: 2724 Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/AliasGenerator.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.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/NewCalculateCostUtil.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseJoinStrategy.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/RuleConstants.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlaceAccess.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java trunk/engine/src/main/java/org/teiid/query/processor/relational/SourceState.java trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java trunk/engine/src/main/java/org/teiid/query/sql/lang/ExistsCriteria.java trunk/engine/src/main/java/org/teiid/query/sql/lang/Query.java trunk/engine/src/main/java/org/teiid/query/sql/visitor/EvaluatableVisitor.java trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java trunk/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestRuleAccessPatternValidation.java trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java trunk/engine/src/test/java/org/teiid/query/rewriter/TestOrderByRewrite.java trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java Log: TEIID-211 initial checkin of subquery optimization focusing on where/having with static rewrite to inner join and cost based usage of semi-join Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/AliasGenerator.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/AliasGenerator.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/AliasGenerator.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -332,6 +332,9 @@ } public void visit(ExistsCriteria obj) { + if (obj.shouldEvaluate()) { + return; + } visitor.createChildNamingContext(false); visitNode(obj.getCommand()); visitor.removeChildNamingContext(); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -63,6 +63,7 @@ import org.teiid.query.optimizer.relational.rules.CriteriaCapabilityValidatorVisitor; import org.teiid.query.optimizer.relational.rules.RuleCollapseSource; import org.teiid.query.optimizer.relational.rules.RuleConstants; +import org.teiid.query.optimizer.relational.rules.RuleMergeCriteria; import org.teiid.query.parser.QueryParser; import org.teiid.query.processor.ProcessorPlan; import org.teiid.query.processor.relational.RelationalPlan; @@ -212,7 +213,7 @@ List<SingleElementSymbol> topCols = Util.deepClone(command.getProjectedSymbols(), SingleElementSymbol.class); // Build rule set based on hints - RuleStack rules = RelationalPlanner.buildRules(hints); + RuleStack rules = buildRules(); // Run rule-based optimizer plan = executeRules(rules, plan); @@ -281,17 +282,18 @@ localGroupSymbols = getGroupSymbols(node); } for (SubqueryContainer container : subqueryContainers) { - ArrayList<Reference> correlatedReferences = new ArrayList<Reference>(); - Command subCommand = container.getCommand(); + //a clone is needed here because the command could get modified during planning + Command subCommand = (Command)container.getCommand().clone(); + ArrayList<Reference> correlatedReferences = new ArrayList<Reference>(); + CorrelatedReferenceCollectorVisitor.collectReferences(subCommand, localGroupSymbols, correlatedReferences); ProcessorPlan procPlan = QueryOptimizer.optimizePlan(subCommand, metadata, idGenerator, capFinder, analysisRecord, context); - subCommand.setProcessorPlan(procPlan); - CorrelatedReferenceCollectorVisitor.collectReferences(subCommand, localGroupSymbols, correlatedReferences); + container.getCommand().setProcessorPlan(procPlan); if (!correlatedReferences.isEmpty()) { SymbolMap map = new SymbolMap(); for (Reference reference : correlatedReferences) { map.addMapping(reference.getExpression(), reference.getExpression()); } - subCommand.setCorrelatedReferences(map); + container.getCommand().setCorrelatedReferences(map); } } node.addGroups(GroupsUsedByElementsVisitor.getGroups(node.getCorrelatedReferenceElements())); @@ -363,12 +365,17 @@ return appliedHint; } - public static RuleStack buildRules(PlanHints hints) { + public RuleStack buildRules() { RuleStack rules = new RuleStack(); rules.push(RuleConstants.COLLAPSE_SOURCE); rules.push(RuleConstants.PLAN_SORTS); + + //TODO: update plan sorts to take advantage or semi-join ordering + if (hints.hasJoin || hints.hasCriteria) { + rules.push(new RuleMergeCriteria(idGenerator, capFinder, analysisRecord, context, metadata)); + } if(hints.hasJoin) { rules.push(RuleConstants.IMPLEMENT_JOIN_STRATEGY); @@ -381,9 +388,6 @@ if (hints.hasLimit) { rules.push(RuleConstants.PUSH_LIMIT); } - if (hints.hasJoin || hints.hasCriteria) { - rules.push(RuleConstants.MERGE_CRITERIA); - } if (hints.hasRelationalProc) { rules.push(RuleConstants.PLAN_PROCEDURES); } @@ -667,7 +671,7 @@ } // Attach grouping node on top - if(query.getGroupBy() != null || query.getHaving() != null || !AggregateSymbolCollectorVisitor.getAggregates(query.getSelect(), false).isEmpty()) { + if(query.hasAggregates()) { plan = attachGrouping(plan, query, hints); } 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 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CriteriaCapabilityValidatorVisitor.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -328,7 +328,11 @@ try { if (validateSubqueryPushdown(crit, modelID, metadata, capFinder, analysisRecord) == null) { - markInvalid(crit.getCommand(), "Subquery cannot be pushed down"); //$NON-NLS-1$ + if (crit.getCommand().getCorrelatedReferences() == null) { + crit.setShouldEvaluate(true); + } else { + markInvalid(crit.getCommand(), "Subquery cannot be pushed down"); //$NON-NLS-1$ + } } } catch (TeiidComponentException e) { handleException(e); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/NewCalculateCostUtil.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -774,7 +774,7 @@ * we are in the plan. * if a key column is used after a non 1-1 join or a union all, then it may be non-unique. */ - private static boolean usesKey(Collection<? extends SingleElementSymbol> allElements, QueryMetadataInterface metadata) + public static boolean usesKey(Collection<? extends SingleElementSymbol> allElements, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException { if(allElements == null || allElements.size() == 0) { @@ -784,10 +784,11 @@ // Sort elements into groups Map<GroupSymbol, List<Object>> groupMap = new HashMap<GroupSymbol, List<Object>>(); for (SingleElementSymbol ses : allElements) { - if (!(ses instanceof ElementSymbol)) { + Expression ex = SymbolMap.getExpression(ses); + if (!(ex instanceof ElementSymbol)) { continue; } - ElementSymbol element = (ElementSymbol)ses; + ElementSymbol element = (ElementSymbol)ex; GroupSymbol group = element.getGroupSymbol(); List<Object> elements = groupMap.get(group); if(elements == null) { Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseJoinStrategy.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseJoinStrategy.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleChooseJoinStrategy.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -102,7 +102,28 @@ List<Criteria> nonEquiJoinCriteria = new ArrayList<Criteria>(); - for (Criteria theCrit : crits) { + separateCriteria(leftGroups, rightGroups, leftExpressions, rightExpressions, crits, nonEquiJoinCriteria); + if (!leftExpressions.isEmpty()) { + joinNode.setProperty(NodeConstants.Info.LEFT_EXPRESSIONS, createExpressionSymbols(leftExpressions)); + joinNode.setProperty(NodeConstants.Info.RIGHT_EXPRESSIONS, createExpressionSymbols(rightExpressions)); + + //make use of the one side criteria + joinNode.setProperty(NodeConstants.Info.JOIN_STRATEGY, JoinStrategyType.MERGE); + joinNode.setProperty(NodeConstants.Info.NON_EQUI_JOIN_CRITERIA, nonEquiJoinCriteria); + } else if (nonEquiJoinCriteria.isEmpty()) { + joinNode.setProperty(NodeConstants.Info.JOIN_CRITERIA, nonEquiJoinCriteria); + if (joinNode.getProperty(NodeConstants.Info.JOIN_TYPE) == JoinType.JOIN_INNER) { + joinNode.setProperty(NodeConstants.Info.JOIN_TYPE, JoinType.JOIN_CROSS); + } + } + } + + static void separateCriteria(Collection<GroupSymbol> leftGroups, + Collection<GroupSymbol> rightGroups, + List<Expression> leftExpressions, + List<Expression> rightExpressions, List<Criteria> crits, + List<Criteria> nonEquiJoinCriteria) { + for (Criteria theCrit : crits) { Set<GroupSymbol> critGroups =GroupsUsedByElementsVisitor.getGroups(theCrit); if (leftGroups.containsAll(critGroups) || rightGroups.containsAll(critGroups)) { @@ -143,24 +164,11 @@ nonEquiJoinCriteria.add(theCrit); } } - if (!leftExpressions.isEmpty()) { - joinNode.setProperty(NodeConstants.Info.LEFT_EXPRESSIONS, createExpressionSymbols(leftExpressions)); - joinNode.setProperty(NodeConstants.Info.RIGHT_EXPRESSIONS, createExpressionSymbols(rightExpressions)); - - //make use of the one side criteria - joinNode.setProperty(NodeConstants.Info.JOIN_STRATEGY, JoinStrategyType.MERGE); - joinNode.setProperty(NodeConstants.Info.NON_EQUI_JOIN_CRITERIA, nonEquiJoinCriteria); - } else if (nonEquiJoinCriteria.isEmpty()) { - joinNode.setProperty(NodeConstants.Info.JOIN_CRITERIA, nonEquiJoinCriteria); - if (joinNode.getProperty(NodeConstants.Info.JOIN_TYPE) == JoinType.JOIN_INNER) { - joinNode.setProperty(NodeConstants.Info.JOIN_TYPE, JoinType.JOIN_CROSS); - } - } - } + } private static AtomicInteger EXPRESSION_INDEX = new AtomicInteger(0); - private static List<SingleElementSymbol> createExpressionSymbols(List<Expression> expressions) { + static List<SingleElementSymbol> createExpressionSymbols(List<Expression> expressions) { HashMap<Expression, ExpressionSymbol> uniqueExpressions = new HashMap<Expression, ExpressionSymbol>(); List<SingleElementSymbol> result = new ArrayList<SingleElementSymbol>(); for (Expression expression : expressions) { 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 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleCollapseSource.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -487,7 +487,7 @@ * @param clause Clause to check recursively * @return True if tree has outer joins, false otherwise */ - private boolean hasOuterJoins(FromClause clause) { + static boolean hasOuterJoins(FromClause clause) { if(clause instanceof UnaryFromClause || clause instanceof SubqueryFromClause) { return false; } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleConstants.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleConstants.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleConstants.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -43,7 +43,6 @@ public static final OptimizerRule VALIDATE_WHERE_ALL = new RuleValidateWhereAll(); public static final OptimizerRule REMOVE_OPTIONAL_JOINS = new RuleRemoveOptionalJoins(); public static final OptimizerRule PUSH_NON_JOIN_CRITERIA = new RulePushNonJoinCriteria(); - public static final OptimizerRule MERGE_CRITERIA = new RuleMergeCriteria(); public static final OptimizerRule RAISE_NULL = new RuleRaiseNull(); public static final OptimizerRule PLAN_JOINS = new RulePlanJoins(); public static final OptimizerRule IMPLEMENT_JOIN_STRATEGY = new RuleImplementJoinStrategy(); 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 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleImplementJoinStrategy.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -129,7 +129,7 @@ * @throws TeiidComponentException * @throws QueryMetadataException */ - private static boolean insertSort(PlanNode childNode, List<SingleElementSymbol> expressions, PlanNode jnode, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, + static boolean insertSort(PlanNode childNode, List<SingleElementSymbol> expressions, PlanNode jnode, QueryMetadataInterface metadata, CapabilitiesFinder capFinder, boolean attemptPush) throws QueryMetadataException, TeiidComponentException { Set<SingleElementSymbol> orderSymbols = new LinkedHashSet<SingleElementSymbol>(expressions); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -23,30 +23,115 @@ package org.teiid.query.optimizer.relational.rules; import java.util.ArrayList; +import java.util.Collection; +import java.util.HashSet; +import java.util.Iterator; +import java.util.LinkedList; import java.util.List; +import java.util.Set; +import org.teiid.api.exception.query.QueryMetadataException; import org.teiid.api.exception.query.QueryPlannerException; import org.teiid.core.TeiidComponentException; +import org.teiid.core.id.IDGenerator; import org.teiid.query.analysis.AnalysisRecord; import org.teiid.query.metadata.QueryMetadataInterface; +import org.teiid.query.metadata.SupportConstants; +import org.teiid.query.optimizer.QueryOptimizer; import org.teiid.query.optimizer.capabilities.CapabilitiesFinder; import org.teiid.query.optimizer.relational.OptimizerRule; import org.teiid.query.optimizer.relational.RuleStack; import org.teiid.query.optimizer.relational.plantree.NodeConstants; import org.teiid.query.optimizer.relational.plantree.NodeEditor; +import org.teiid.query.optimizer.relational.plantree.NodeFactory; import org.teiid.query.optimizer.relational.plantree.PlanNode; +import org.teiid.query.processor.relational.RelationalPlan; +import org.teiid.query.processor.relational.JoinNode.JoinStrategyType; +import org.teiid.query.processor.relational.MergeJoinStrategy.SortOption; import org.teiid.query.rewriter.QueryRewriter; +import org.teiid.query.sql.LanguageObject; +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.ExistsCriteria; +import org.teiid.query.sql.lang.FromClause; +import org.teiid.query.sql.lang.GroupBy; +import org.teiid.query.sql.lang.JoinType; +import org.teiid.query.sql.lang.NotCriteria; +import org.teiid.query.sql.lang.OrderBy; +import org.teiid.query.sql.lang.OrderByItem; +import org.teiid.query.sql.lang.Query; +import org.teiid.query.sql.lang.SubqueryCompareCriteria; +import org.teiid.query.sql.lang.SubquerySetCriteria; +import org.teiid.query.sql.navigator.DeepPostOrderNavigator; +import org.teiid.query.sql.symbol.AggregateSymbol; +import org.teiid.query.sql.symbol.Constant; +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.symbol.Reference; +import org.teiid.query.sql.symbol.ScalarSubquery; +import org.teiid.query.sql.symbol.SingleElementSymbol; +import org.teiid.query.sql.symbol.AggregateSymbol.Type; +import org.teiid.query.sql.util.SymbolMap; +import org.teiid.query.sql.visitor.AggregateSymbolCollectorVisitor; +import org.teiid.query.sql.visitor.ExpressionMappingVisitor; import org.teiid.query.sql.visitor.GroupsUsedByElementsVisitor; +import org.teiid.query.sql.visitor.ReferenceCollectorVisitor; import org.teiid.query.util.CommandContext; - -/** - * TODO: this rule should attempt to intelligently order the criteria - */ public final class RuleMergeCriteria implements OptimizerRule { + + /** + * Used to replace correlated references + */ + private final class ReferenceReplacementVisitor extends + ExpressionMappingVisitor { + private final SymbolMap refs; + private boolean replacedAny; + + private ReferenceReplacementVisitor(SymbolMap refs) { + super(null); + this.refs = refs; + } + + public Expression replaceExpression(Expression element) { + if (element instanceof Reference) { + Reference r = (Reference)element; + Expression ex = refs.getMappedExpression(r.getExpression()); + if (ex != null) { + replacedAny = true; + return ex; + } + } + return element; + } + + } + + public static class PlannedResult { + public List leftExpressions = new LinkedList(); + public List rightExpressions = new LinkedList(); + public Query query; + public boolean not; + public List<Criteria> nonEquiJoinCriteria = new LinkedList<Criteria>(); + public Criteria additionalCritieria; + } + private IDGenerator idGenerator; + private CapabilitiesFinder capFinder; + private AnalysisRecord analysisRecord; + private CommandContext context; + private QueryMetadataInterface metadata; + + public RuleMergeCriteria(IDGenerator idGenerator, CapabilitiesFinder capFinder, AnalysisRecord analysisRecord, CommandContext context, QueryMetadataInterface metadata) { + this.idGenerator = idGenerator; + this.capFinder = capFinder; + this.analysisRecord = analysisRecord; + this.context = context; + this.metadata = metadata; + } + /** * @see OptimizerRule#execute(PlanNode, QueryMetadataInterface, RuleStack) */ @@ -56,7 +141,7 @@ // Find strings of criteria and merge them, removing duplicates List<PlanNode> criteriaChains = new ArrayList<PlanNode>(); findCriteriaChains(plan, criteriaChains); - + // Merge chains for (PlanNode critNode : criteriaChains) { mergeChain(critNode, metadata); @@ -75,9 +160,18 @@ PlanNode recurseRoot = root; if(root.getType() == NodeConstants.Types.SELECT) { + // Walk to end of the chain and change recurse root while(recurseRoot.getType() == NodeConstants.Types.SELECT) { - recurseRoot = recurseRoot.getLastChild(); + // Look for opportunities to replace with a semi-join + recurseRoot = planSemiJoin(recurseRoot, root); + if (root.getChildCount() == 0) { + root = recurseRoot.getFirstChild(); + if (root.getType() != NodeConstants.Types.SELECT) { + root = root.getParent(); + } + } + recurseRoot = recurseRoot.getFirstChild(); } // Ignore trivial 1-node case @@ -95,13 +189,18 @@ } static void mergeChain(PlanNode chainRoot, QueryMetadataInterface metadata) { - // Remove all of chain except root, collect crit from each CompoundCriteria critParts = new CompoundCriteria(); + LinkedList<Criteria> subqueryCriteria = new LinkedList<Criteria>(); PlanNode current = chainRoot; boolean isDependentSet = false; while(current.getType() == NodeConstants.Types.SELECT) { - critParts.getCriteria().add(0, (Criteria)current.getProperty(NodeConstants.Info.SELECT_CRITERIA)); + if (!current.getCorrelatedReferenceElements().isEmpty()) { + //add at the end for delayed evaluation + subqueryCriteria.add(0, (Criteria)current.getProperty(NodeConstants.Info.SELECT_CRITERIA)); + } else { + critParts.getCriteria().add(0, (Criteria)current.getProperty(NodeConstants.Info.SELECT_CRITERIA)); + } isDependentSet |= current.hasBooleanProperty(NodeConstants.Info.IS_DEPENDENT_SET); @@ -113,9 +212,8 @@ if(last != chainRoot) { NodeEditor.removeChildNode(last.getParent(), last); } - } - + critParts.getCriteria().addAll(subqueryCriteria); Criteria combinedCrit = QueryRewriter.optimizeCriteria(critParts, metadata); if (isDependentSet) { @@ -132,6 +230,324 @@ chainRoot.addGroups(GroupsUsedByElementsVisitor.getGroups(chainRoot.getCorrelatedReferenceElements())); } + /** + * Look for: + * [NOT] EXISTS ( ) + * IN ( ) / SOME ( ) + * + * and replace with a semi join + * + * TODO: it would be good to have a hint to force + */ + private PlanNode planSemiJoin(PlanNode current, PlanNode root) throws QueryMetadataException, + TeiidComponentException { + float sourceCost = NewCalculateCostUtil.computeCostForTree(current, metadata); + if (sourceCost != NewCalculateCostUtil.UNKNOWN_VALUE + && sourceCost < RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY) { + //TODO: see if a dependent join applies + return current; + } + Criteria crit = (Criteria)current.getProperty(NodeConstants.Info.SELECT_CRITERIA); + + PlannedResult plannedResult = findSubquery(crit); + if (plannedResult.query == null) { + return current; + } + + RelationalPlan originalPlan = (RelationalPlan)plannedResult.query.getProcessorPlan(); + Number originalCardinality = originalPlan.getRootNode().getEstimateNodeCardinality(); + if (originalCardinality.floatValue() == NewCalculateCostUtil.UNKNOWN_VALUE) { + //if it's currently unknown, removing criteria won't make it any better + return current; + } + + Collection<GroupSymbol> leftGroups = FrameUtil.findJoinSourceNode(current).getGroups(); + + if (!planQuery(leftGroups, false, plannedResult)) { + return current; + } + + //add an order by, which hopefully will get pushed down + plannedResult.query.setOrderBy(new OrderBy(plannedResult.rightExpressions)); + for (OrderByItem item : plannedResult.query.getOrderBy().getOrderByItems()) { + int index = plannedResult.query.getProjectedSymbols().indexOf(item.getSymbol()); + item.setExpressionPosition(index); + } + + try { + //NOTE: we could tap into the relationalplanner at a lower level to get this in a plan node form, + //the major benefit would be to reuse the dependent join planning logic if possible. + RelationalPlan subPlan = (RelationalPlan)QueryOptimizer.optimizePlan(plannedResult.query, metadata, idGenerator, capFinder, analysisRecord, context); + Number planCardinality = subPlan.getRootNode().getEstimateNodeCardinality(); + + if (planCardinality.floatValue() == NewCalculateCostUtil.UNKNOWN_VALUE + || planCardinality.floatValue() > 10000000 + || (sourceCost != NewCalculateCostUtil.UNKNOWN_VALUE && sourceCost * originalCardinality.floatValue() < planCardinality.floatValue() / (100 * Math.log(Math.max(4, sourceCost))))) { + //bail-out if both are unknown or the new plan is too large + return current; + } + + //TODO: don't allow if too large + PlanNode semiJoin = NodeFactory.getNewNode(NodeConstants.Types.JOIN); + semiJoin.addGroups(current.getGroups()); + semiJoin.setProperty(NodeConstants.Info.JOIN_STRATEGY, JoinStrategyType.MERGE); + semiJoin.setProperty(NodeConstants.Info.JOIN_TYPE, plannedResult.not?JoinType.JOIN_ANTI_SEMI:JoinType.JOIN_SEMI); + semiJoin.setProperty(NodeConstants.Info.NON_EQUI_JOIN_CRITERIA, plannedResult.nonEquiJoinCriteria); + + semiJoin.setProperty(NodeConstants.Info.LEFT_EXPRESSIONS, plannedResult.leftExpressions); + semiJoin.setProperty(NodeConstants.Info.RIGHT_EXPRESSIONS, plannedResult.rightExpressions); + semiJoin.setProperty(NodeConstants.Info.SORT_RIGHT, SortOption.ALREADY_SORTED); + semiJoin.setProperty(NodeConstants.Info.OUTPUT_COLS, root.getProperty(NodeConstants.Info.OUTPUT_COLS)); + + List childOutput = (List)current.getFirstChild().getProperty(NodeConstants.Info.OUTPUT_COLS); + PlanNode toCorrect = root; + while (toCorrect != current) { + toCorrect.setProperty(NodeConstants.Info.OUTPUT_COLS, childOutput); + toCorrect = toCorrect.getFirstChild(); + } + + PlanNode node = NodeFactory.getNewNode(NodeConstants.Types.ACCESS); + node.setProperty(NodeConstants.Info.PROCESSOR_PLAN, subPlan); + node.setProperty(NodeConstants.Info.OUTPUT_COLS, plannedResult.query.getProjectedSymbols()); + node.setProperty(NodeConstants.Info.EST_CARDINALITY, planCardinality); + root.addAsParent(semiJoin); + semiJoin.addLastChild(node); + PlanNode result = current.getParent(); + NodeEditor.removeChildNode(result, current); + RuleImplementJoinStrategy.insertSort(semiJoin.getFirstChild(), (List<SingleElementSymbol>) plannedResult.leftExpressions, semiJoin, metadata, capFinder, true); + return result; + } catch (QueryPlannerException e) { + //can't be done - probably access patterns - what about dependent + return current; + } + } + + public PlannedResult findSubquery(Criteria crit) throws TeiidComponentException, QueryMetadataException { + PlannedResult result = new PlannedResult(); + if (crit instanceof NotCriteria) { + result.not = true; + crit = ((NotCriteria)crit).getCriteria(); + } + if (crit instanceof SubquerySetCriteria) { + //convert to the quantified form + SubquerySetCriteria ssc = (SubquerySetCriteria)crit; + result.not ^= ssc.isNegated(); + crit = new SubqueryCompareCriteria(ssc.getExpression(), ssc.getCommand(), SubqueryCompareCriteria.EQ, SubqueryCompareCriteria.SOME); + } else if (crit instanceof CompareCriteria) { + //convert to the quantified form + CompareCriteria cc = (CompareCriteria)crit; + if (cc.getRightExpression() instanceof ScalarSubquery) { + ScalarSubquery ss = (ScalarSubquery)cc.getRightExpression(); + if (ss.getCommand() instanceof Query) { + Query query = (Query)ss.getCommand(); + if (query.getGroupBy() == null && query.hasAggregates()) { + crit = new SubqueryCompareCriteria(cc.getLeftExpression(), ss.getCommand(), cc.getOperator(), SubqueryCompareCriteria.SOME); + } + } + } + } + if (crit instanceof SubqueryCompareCriteria) { + SubqueryCompareCriteria scc = (SubqueryCompareCriteria)crit; + + /*if (scc.getCommand().getCorrelatedReferences() == null) { + RelationalPlan originalPlan = (RelationalPlan)scc.getCommand().getProcessorPlan(); + Number originalCardinality = originalPlan.getRootNode().getEstimateNodeCardinality(); + if (originalCardinality.floatValue() != NewCalculateCostUtil.UNKNOWN_VALUE + && originalCardinality.floatValue() < this.context.getProcessorBatchSize()) { + //this is small enough that it will effectively be a hash join + return current; + } + }*/ + + if (scc.getPredicateQuantifier() != SubqueryCompareCriteria.SOME + //TODO: could add an inline view if not a query + || !(scc.getCommand() instanceof Query)) { + return result; + } + + Query query = (Query)scc.getCommand(); + Expression rightExpr = SymbolMap.getExpression(query.getProjectedSymbols().get(0)); + + if (result.not && !isNonNull(query, rightExpr)) { + return result; + } + + result.query = query; + result.additionalCritieria = (Criteria)new CompareCriteria(scc.getLeftExpression(), scc.getOperator(), rightExpr).clone(); + } + if (crit instanceof ExistsCriteria) { + ExistsCriteria exists = (ExistsCriteria)crit; + if (!(exists.getCommand() instanceof Query)) { + return result; + } + //the correlations can only be in where (if no group by or aggregates) or having + result.query = (Query)exists.getCommand(); + } + return result; + } + + private boolean isNonNull(Query query, Expression rightExpr) + throws TeiidComponentException, QueryMetadataException { + if (rightExpr instanceof ElementSymbol) { + ElementSymbol es = (ElementSymbol)rightExpr; + if (metadata.elementSupports(es.getMetadataID(), SupportConstants.Element.NULL)) { + return false; + } + if (!isSimpleJoin(query)) { + return false; + } + } else if (rightExpr instanceof Constant) { + if (((Constant)rightExpr).isNull()) { + return false; + } + } else if (rightExpr instanceof AggregateSymbol) { + AggregateSymbol as = (AggregateSymbol)rightExpr; + if (as.getAggregateFunction() != Type.COUNT) { + return false; + } + } else { + return false; + } + return true; + } + + private boolean isSimpleJoin(Query query) { + if (query.getFrom() != null) { + for (FromClause clause : (List<FromClause>)query.getFrom().getClauses()) { + if (RuleCollapseSource.hasOuterJoins(clause)) { + return false; + } + } + } + return true; + } + + public boolean planQuery(Collection<GroupSymbol> leftGroups, boolean requireDistinct, PlannedResult plannedResult) throws QueryMetadataException, TeiidComponentException { + if (plannedResult.query.getLimit() != null || plannedResult.query.getFrom() == null) { + return false; + } + + plannedResult.query = (Query)plannedResult.query.clone(); + + List<GroupSymbol> rightGroups = plannedResult.query.getFrom().getGroups(); + Set<SingleElementSymbol> requiredExpressions = new HashSet<SingleElementSymbol>(); + final SymbolMap refs = plannedResult.query.getCorrelatedReferences(); + boolean addGroupBy = false; + if (refs != null) { + boolean hasAggregates = plannedResult.query.hasAggregates(); + Criteria where = plannedResult.query.getCriteria(); + if (plannedResult.query.getGroupBy() == null) { + plannedResult.query.setCriteria(null); + } + Criteria having = plannedResult.query.getHaving(); + plannedResult.query.setHaving(null); + if (hasCorrelatedReferences(plannedResult.query, refs)) { + return false; + } + if (plannedResult.query.getGroupBy() == null) { + processCriteria(leftGroups, plannedResult, rightGroups, requiredExpressions, refs, where, true); + if (hasAggregates) { + if (!plannedResult.nonEquiJoinCriteria.isEmpty()) { + return false; + } + addGroupBy = true; + } + } + processCriteria(leftGroups, plannedResult, rightGroups, requiredExpressions, refs, having, false); + } + + if (plannedResult.additionalCritieria != null) { + RuleChooseJoinStrategy.separateCriteria(leftGroups, rightGroups, plannedResult.leftExpressions, plannedResult.rightExpressions, Criteria.separateCriteriaByAnd(plannedResult.additionalCritieria), plannedResult.nonEquiJoinCriteria); + } + + if (plannedResult.leftExpressions.isEmpty()) { + //there's no equi-join + //TODO: if there are correlations a "cross" join may still be preferable + return false; + } + + plannedResult.leftExpressions = RuleChooseJoinStrategy.createExpressionSymbols(plannedResult.leftExpressions); + plannedResult.rightExpressions = RuleChooseJoinStrategy.createExpressionSymbols(plannedResult.rightExpressions); + + if (requireDistinct && !addGroupBy && !isDistinct(plannedResult.query, plannedResult.rightExpressions, metadata)) { + return false; + } + + if (addGroupBy) { + plannedResult.query.setGroupBy(new GroupBy(plannedResult.rightExpressions)); + } + + for (SingleElementSymbol ses : requiredExpressions) { + if (plannedResult.query.getSelect().getProjectedSymbols().indexOf(ses) == -1) { + plannedResult.query.getSelect().addSymbol(ses); + } + } + for (SingleElementSymbol ses : (List<SingleElementSymbol>)plannedResult.rightExpressions) { + if (plannedResult.query.getSelect().getProjectedSymbols().indexOf(ses) == -1) { + plannedResult.query.getSelect().addSymbol(ses); + } + } + return true; + } + + private void processCriteria(Collection<GroupSymbol> leftGroups, + PlannedResult plannedResult, List<GroupSymbol> rightGroups, + Set<SingleElementSymbol> requiredExpressions, final SymbolMap refs, + Criteria joinCriteria, boolean where) { + if (joinCriteria == null) { + return; + } + List<Criteria> crits = Criteria.separateCriteriaByAnd((Criteria)joinCriteria.clone()); + + for (Iterator<Criteria> critIter = crits.iterator(); critIter.hasNext();) { + Criteria conjunct = critIter.next(); + List<SingleElementSymbol> aggregates = new LinkedList<SingleElementSymbol>(); + List<SingleElementSymbol> elements = new LinkedList<SingleElementSymbol>(); + AggregateSymbolCollectorVisitor.getAggregates(conjunct, aggregates, elements); + ReferenceReplacementVisitor emv = new ReferenceReplacementVisitor(refs); + DeepPostOrderNavigator.doVisit(conjunct, emv); + if (!emv.replacedAny) { + //if not correlated, then leave it on the query + critIter.remove(); + if (where) { + plannedResult.query.setCriteria(Criteria.combineCriteria(plannedResult.query.getCriteria(), conjunct)); + } else { + plannedResult.query.setHaving(Criteria.combineCriteria(plannedResult.query.getHaving(), conjunct)); + } + } else { + requiredExpressions.addAll(aggregates); + requiredExpressions.addAll(elements); + } + } + RuleChooseJoinStrategy.separateCriteria(leftGroups, rightGroups, plannedResult.leftExpressions, plannedResult.rightExpressions, crits, plannedResult.nonEquiJoinCriteria); + } + + public static boolean isDistinct(Query query, List<SingleElementSymbol> expressions, QueryMetadataInterface metadata) + throws QueryMetadataException, TeiidComponentException { + boolean distinct = false; + if (query.getGroupBy() != null) { + distinct = true; + for (SingleElementSymbol groupByExpr : (List<SingleElementSymbol>)query.getGroupBy().getSymbols()) { + if (!expressions.contains(groupByExpr)) { + distinct = false; + break; + } + } + } + //TODO: a better check for distinct + return distinct || (query.getFrom().getGroups().size() == 1 && NewCalculateCostUtil.usesKey(expressions, metadata)); + } + + private boolean hasCorrelatedReferences(LanguageObject object, SymbolMap correlatedReferences) { + Collection<Reference> references = ReferenceCollectorVisitor.getReferences(object); + for (Reference reference : references) { + if (correlatedReferences.asMap().containsKey(reference.getExpression())) { + return true; + } + } + return false; + } + public String toString() { return "MergeCriteria"; //$NON-NLS-1$ } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlaceAccess.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlaceAccess.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePlaceAccess.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -233,7 +233,7 @@ * @param allUpperNames a set of all known groups in upper case * @return */ - static GroupSymbol recontextSymbol(GroupSymbol oldSymbol, + public static GroupSymbol recontextSymbol(GroupSymbol oldSymbol, Set<String> allUpperNames) { // Create new unique name String oldName = oldSymbol.getName(); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -255,6 +255,7 @@ NodeEditor.removeChildNode(critNode.getParent(), critNode); destination.addAsParent(critNode); if (groupSelects && destination == sourceNode) { + //Help with the detection of composite keys in pushed criteria RuleMergeCriteria.mergeChain(critNode, metadata); } } 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 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -781,7 +781,7 @@ throws QueryMetadataException, TeiidComponentException { Object accessModelID = accessNode.getProperty(NodeConstants.Info.MODEL_ID); - if(accessModelID == null) { + if(accessModelID == null && accessNode.getGroups().size() > 0) { GroupSymbol group = accessNode.getGroups().iterator().next(); if(metadata.isVirtualGroup(group.getMetadataID())) { return null; Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/SourceState.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/SourceState.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/SourceState.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -31,6 +31,7 @@ import org.teiid.common.buffer.BufferManager.TupleSourceType; import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidProcessingException; +import org.teiid.core.util.Assertion; import org.teiid.query.processor.BatchCollector; import org.teiid.query.processor.BatchIterator; import org.teiid.query.processor.relational.MergeJoinStrategy.SortOption; @@ -83,6 +84,7 @@ int[] indecies = new int[expressions.size()]; for (int i = 0; i < expressions.size(); i++) { indecies[i] = elements.indexOf(expressions.get(i)); + assert indecies[i] != -1; } return indecies; } Modified: trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -63,6 +63,10 @@ import org.teiid.query.metadata.QueryMetadataInterface; import org.teiid.query.metadata.TempMetadataAdapter; import org.teiid.query.metadata.TempMetadataStore; +import org.teiid.query.optimizer.relational.rules.NewCalculateCostUtil; +import org.teiid.query.optimizer.relational.rules.RuleMergeCriteria; +import org.teiid.query.optimizer.relational.rules.RulePlaceAccess; +import org.teiid.query.optimizer.relational.rules.RuleMergeCriteria.PlannedResult; import org.teiid.query.processor.relational.DependentValueSource; import org.teiid.query.resolver.QueryResolver; import org.teiid.query.resolver.util.ResolverUtil; @@ -143,6 +147,7 @@ import org.teiid.query.sql.util.SymbolMap; import org.teiid.query.sql.util.ValueIterator; import org.teiid.query.sql.visitor.AggregateSymbolCollectorVisitor; +import org.teiid.query.sql.visitor.CorrelatedReferenceCollectorVisitor; import org.teiid.query.sql.visitor.CriteriaTranslatorVisitor; import org.teiid.query.sql.visitor.ElementCollectorVisitor; import org.teiid.query.sql.visitor.EvaluatableVisitor; @@ -604,15 +609,24 @@ query.setCriteria(crit); } } + + if (from != null) { + writeSubqueriesAsJoins(query); + } query = rewriteGroupBy(query); // Rewrite having Criteria having = query.getHaving(); if(having != null) { - query.setHaving(rewriteCriteria(having)); + crit = rewriteCriteria(having); + if(crit == TRUE_CRITERIA) { + query.setHaving(null); + } else { + query.setHaving(crit); + } } - + rewriteExpressions(query.getSelect()); if (!query.getIsXML()) { @@ -630,6 +644,66 @@ return query; } + private void writeSubqueriesAsJoins(Query query) + throws TeiidComponentException, QueryMetadataException, + QueryResolverException { + if (query.getCriteria() == null) { + return; + } + RuleMergeCriteria rmc = new RuleMergeCriteria(null, null, null, this.context, this.metadata); + List<Criteria> current = Criteria.separateCriteriaByAnd(query.getCriteria()); + query.setCriteria(null); + List<GroupSymbol> groups = query.getFrom().getGroups(); + HashSet<String> names = new HashSet<String>(); + for (GroupSymbol gs : groups) { + names.add(gs.getCanonicalName()); + } + for (Iterator<Criteria> crits = current.iterator(); crits.hasNext();) { + PlannedResult plannedResult = rmc.findSubquery(crits.next()); + if (plannedResult.not || plannedResult.query == null || plannedResult.query.getProcessorPlan() != null) { + continue; + } + if (plannedResult.query.getCorrelatedReferences() == null) { + ArrayList<Reference> correlatedReferences = new ArrayList<Reference>(); + CorrelatedReferenceCollectorVisitor.collectReferences(plannedResult.query, groups, correlatedReferences); + if (!correlatedReferences.isEmpty()) { + SymbolMap map = new SymbolMap(); + for (Reference reference : correlatedReferences) { + map.addMapping(reference.getExpression(), reference.getExpression()); + } + plannedResult.query.setCorrelatedReferences(map); + } + } + if (!rmc.planQuery(groups, true, plannedResult)) { + continue; + } + if (query.getFrom().getGroups().size() != 1 || !NewCalculateCostUtil.usesKey(plannedResult.leftExpressions, metadata)) { + //TODO: check for grouping or distinct in the outer query + continue; + } + crits.remove(); + + GroupSymbol viewName = RulePlaceAccess.recontextSymbol(new GroupSymbol("X"), names); //$NON-NLS-1$ + Query q = createInlineViewQuery(viewName, plannedResult.query, metadata, plannedResult.query.getSelect().getProjectedSymbols()); + + Iterator iter = q.getSelect().getProjectedSymbols().iterator(); + HashMap<Expression, SingleElementSymbol> expressionMap = new HashMap<Expression, SingleElementSymbol>(); + for (SingleElementSymbol symbol : (List<SingleElementSymbol>)plannedResult.query.getSelect().getProjectedSymbols()) { + expressionMap.put(SymbolMap.getExpression(symbol), (SingleElementSymbol)iter.next()); + } + for (int i = 0; i < plannedResult.leftExpressions.size(); i++) { + plannedResult.nonEquiJoinCriteria.add(new CompareCriteria(SymbolMap.getExpression((Expression)plannedResult.leftExpressions.get(i)), CompareCriteria.EQ, (Expression)plannedResult.rightExpressions.get(i))); + } + Criteria mappedCriteria = Criteria.combineCriteria(plannedResult.nonEquiJoinCriteria); + ExpressionMappingVisitor.mapExpressions(mappedCriteria, expressionMap); + query.setCriteria(Criteria.combineCriteria(query.getCriteria(), mappedCriteria)); + query.getFrom().addClause((FromClause) q.getFrom().getClauses().get(0)); + query.getTemporaryMetadata().putAll(q.getTemporaryMetadata()); + //transform the query into an inner join + } + query.setCriteria(Criteria.combineCriteria(query.getCriteria(), Criteria.combineCriteria(current))); + } + /** * Converts a group by with expressions into a group by with only element symbols and an inline view * @param query @@ -877,7 +951,7 @@ } } - private Insert correctDatatypes(Insert insert) throws TeiidComponentException, TeiidProcessingException{ + private Insert correctDatatypes(Insert insert) { boolean needsView = false; for (int i = 0; !needsView && i < insert.getVariables().size(); i++) { SingleElementSymbol ses = (SingleElementSymbol)insert.getVariables().get(i); @@ -887,7 +961,7 @@ } if (needsView) { try { - insert.setQueryExpression(createInlineViewQuery(insert.getGroup(), insert.getQueryExpression(), metadata, insert.getVariables())); + insert.setQueryExpression(createInlineViewQuery(new GroupSymbol("X"), insert.getQueryExpression(), metadata, insert.getVariables())); //$NON-NLS-1$ } catch (TeiidException err) { throw new TeiidRuntimeException(err); } @@ -1019,6 +1093,17 @@ } else if(criteria instanceof TranslateCriteria) { criteria = rewriteCriteria((TranslateCriteria)criteria); } else if (criteria instanceof ExistsCriteria) { + ExistsCriteria exists = (ExistsCriteria)criteria; + if (exists.getCommand().getProcessorPlan() == null && exists.getCommand() instanceof Query) { + Query query = (Query)exists.getCommand(); + if (query.getLimit() == null && query.getProjectedSymbols().size() > 1) { + query.getSelect().clearSymbols(); + query.getSelect().addSymbol(new ExpressionSymbol("x", new Constant(1))); //$NON-NLS-1$ + } + } + if (exists.shouldEvaluate() && processing) { + return getCriteria(evaluator.evaluate(exists, null)); + } rewriteSubqueryContainer((SubqueryContainer)criteria, true); } else if (criteria instanceof SubquerySetCriteria) { SubquerySetCriteria sub = (SubquerySetCriteria)criteria; @@ -1139,16 +1224,8 @@ try { Boolean eval = evaluator.evaluateTVL(crit, Collections.emptyList()); - if (eval == null) { - return UNKNOWN_CRITERIA; - } + return getCriteria(eval); - if(Boolean.TRUE.equals(eval)) { - return TRUE_CRITERIA; - } - - return FALSE_CRITERIA; - } catch(ExpressionEvaluationException e) { throw new QueryValidatorException(e, "ERR.015.009.0001", QueryPlugin.Util.getString("ERR.015.009.0001", crit)); //$NON-NLS-1$ //$NON-NLS-2$ } @@ -1157,6 +1234,18 @@ return crit; } + private Criteria getCriteria(Boolean eval) { + if (eval == null) { + return UNKNOWN_CRITERIA; + } + + if(Boolean.TRUE.equals(eval)) { + return TRUE_CRITERIA; + } + + return FALSE_CRITERIA; + } + private Criteria rewriteCriteria(NotCriteria criteria) throws TeiidComponentException, TeiidProcessingException{ Criteria innerCrit = criteria.getCriteria(); if (innerCrit instanceof CompoundCriteria) { @@ -1448,7 +1537,7 @@ * @throws QueryValidatorException * @since 4.2 */ - private Criteria simplifyConvertFunction(CompareCriteria crit) throws TeiidComponentException, TeiidProcessingException{ + private Criteria simplifyConvertFunction(CompareCriteria crit) { Function leftFunction = (Function) crit.getLeftExpression(); Expression leftExpr = leftFunction.getArgs()[0]; @@ -1558,7 +1647,7 @@ return rewriteCriteria(crit); } - private Criteria simplifyParseFormatFunction(CompareCriteria crit) throws TeiidComponentException, TeiidProcessingException{ + private Criteria simplifyParseFormatFunction(CompareCriteria crit) { //TODO: this can be relaxed for order preserving operations if(!(crit.getOperator() == CompareCriteria.EQ || crit.getOperator() == CompareCriteria.NE)) { return crit; @@ -2342,7 +2431,7 @@ return insert; } - public static Query createInlineViewQuery(GroupSymbol group, + public static Query createInlineViewQuery(GroupSymbol inlineGroup, Command nested, QueryMetadataInterface metadata, List<SingleElementSymbol> actualSymbols) throws QueryMetadataException, @@ -2352,7 +2441,6 @@ Select select = new Select(); query.setSelect(select); From from = new From(); - GroupSymbol inlineGroup = new GroupSymbol(group.getName().replace('.', '_') + "_1"); //$NON-NLS-1$ from.addClause(new UnaryFromClause(inlineGroup)); TempMetadataStore store = new TempMetadataStore(); TempMetadataAdapter tma = new TempMetadataAdapter(metadata, store); Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/ExistsCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/lang/ExistsCriteria.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/sql/lang/ExistsCriteria.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -37,12 +37,13 @@ * "EXISTS (Select EmployeeID FROM Employees WHERE EmployeeName = 'Smith')". */ public class ExistsCriteria extends PredicateCriteria -implements SubqueryContainer, ContextReference { +implements SubqueryContainer<QueryCommand>, ContextReference { private static AtomicInteger ID = new AtomicInteger(); - private Command command; + private QueryCommand command; private String id = "$ec/id" + ID.getAndIncrement(); //$NON-NLS-1$ + private boolean shouldEvaluate; /** * Default constructor @@ -51,10 +52,18 @@ super(); } - public ExistsCriteria(Command subqueryCommand){ + public ExistsCriteria(QueryCommand subqueryCommand){ this.command = subqueryCommand; } + public boolean shouldEvaluate() { + return shouldEvaluate; + } + + public void setShouldEvaluate(boolean shouldEvaluate) { + this.shouldEvaluate = shouldEvaluate; + } + @Override public String getContextSymbol() { return id; @@ -65,11 +74,11 @@ return null; } - public Command getCommand() { + public QueryCommand getCommand() { return this.command; } - public void setCommand(Command subqueryCommand){ + public void setCommand(QueryCommand subqueryCommand){ this.command = subqueryCommand; } @@ -115,12 +124,6 @@ * @see java.lang.Object#clone() */ public Object clone() { - - Command copyCommand = null; - if(getCommand() != null) { - copyCommand = (Command) getCommand().clone(); - } - - return new ExistsCriteria(copyCommand); + return new ExistsCriteria((QueryCommand) this.command.clone()); } } Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/Query.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/lang/Query.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/sql/lang/Query.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -34,6 +34,7 @@ import org.teiid.query.sql.LanguageVisitor; import org.teiid.query.sql.symbol.ElementSymbol; import org.teiid.query.sql.symbol.SingleElementSymbol; +import org.teiid.query.sql.visitor.AggregateSymbolCollectorVisitor; /** * A representation of a data query. A query consists of various parts, @@ -449,4 +450,10 @@ public boolean returnsResultSet() { return into == null; } + + public boolean hasAggregates() { + return getGroupBy() != null + || (getHaving() != null && AggregateSymbolCollectorVisitor.getAggregates(getHaving(), false).isEmpty()) + || !AggregateSymbolCollectorVisitor.getAggregates(getSelect(), false).isEmpty(); + } } // END CLASS Modified: trunk/engine/src/main/java/org/teiid/query/sql/visitor/EvaluatableVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/visitor/EvaluatableVisitor.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/java/org/teiid/query/sql/visitor/EvaluatableVisitor.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -148,7 +148,11 @@ } public void visit(ExistsCriteria obj) { - evaluationNotPossible(EvaluationLevel.PUSH_DOWN); + if (obj.shouldEvaluate()) { + evaluationNotPossible(EvaluationLevel.PROCESSING); + } else { + evaluationNotPossible(EvaluationLevel.PUSH_DOWN); + } } public void visit(SubquerySetCriteria obj) { Modified: trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj =================================================================== --- trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2010-11-16 18:23:48 UTC (rev 2724) @@ -2782,7 +2782,7 @@ ExistsCriteria existsCriteria(ParseInfo info) : { ExistsCriteria existsCrit = null; - Command subquery = null; + QueryCommand subquery = null; } { <EXISTS> Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestOptimizer.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -99,6 +99,7 @@ public interface DependentJoin {} public interface DependentSelectNode {} + public interface SemiJoin {} public interface DependentProjectNode {} public interface DupRemoveNode {} public interface DupRemoveSortNode {} @@ -391,6 +392,9 @@ Class<?> nodeType = relationalNode.getClass(); if(nodeType.equals(JoinNode.class)) { JoinStrategy strategy = ((JoinNode)relationalNode).getJoinStrategy(); + if (((JoinNode)relationalNode).getJoinType().isSemi()) { + updateCounts(SemiJoin.class, counts, types); + } if (strategy instanceof NestedLoopJoinStrategy) { updateCounts(NestedLoopJoinStrategy.class, counts, types); } else if (strategy instanceof MergeJoinStrategy) { Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -31,11 +31,12 @@ import org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder; import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability; import org.teiid.query.processor.ProcessorPlan; +import org.teiid.query.rewriter.TestQueryRewriter; import org.teiid.query.unittest.FakeMetadataFacade; import org.teiid.query.unittest.FakeMetadataFactory; import org.teiid.translator.SourceSystemFunctions; - +@SuppressWarnings("nls") public class TestSubqueryPushdown { @Test public void testPushSubqueryBelowVirtual() throws Exception { @@ -250,7 +251,7 @@ } @Test public void testCorrelatedSubquery1() { - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select e1 FROM pm2.g1 WHERE pm1.g1.e2 = pm2.g1.e2)", example1(), //$NON-NLS-1$ + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select e1 FROM pm2.g1 WHERE pm1.g1.e2 = pm2.g1.e2)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT e1, pm1.g1.e2 FROM pm1.g1" }); //$NON-NLS-1$ checkNodeTypes(plan, new int[] { 1, // Access @@ -271,7 +272,7 @@ } @Test public void testCorrelatedSubquery2() { - ProcessorPlan plan = helpPlan("Select e1, (select e1 FROM pm2.g1 WHERE pm1.g1.e2 = pm2.g1.e2) from pm1.g1", example1(), //$NON-NLS-1$ + ProcessorPlan plan = helpPlan("Select e1, (select e1 FROM pm2.g1 WHERE pm1.g1.e2 = pm2.g1.e2) from pm1.g1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT e1, pm1.g1.e2 FROM pm1.g1" }); //$NON-NLS-1$ checkNodeTypes(plan, new int[] { 1, // Access @@ -386,25 +387,25 @@ caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, false); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select max(e1) FROM pm1.g2)", example1(), //$NON-NLS-1$ + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select max(e1) FROM pm1.g2)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ null, capFinder, - new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ + new String[] { "SELECT e1 AS c_0 FROM pm1.g1 ORDER BY c_0" }, SHOULD_SUCCEED); //$NON-NLS-1$ checkNodeTypes(plan, new int[] { 1, // Access 0, // DependentAccess - 1, // DependentSelect + 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy + 1, // MergeJoinStrategy 0, // Null - 0, // PlanExecution + 1, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll - }); + }); } /** @@ -418,7 +419,7 @@ capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ capFinder.addCapabilities("pm2", getTypicalCapabilities()); //$NON-NLS-1$ - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select e1 FROM pm2.g1)", example1(), //$NON-NLS-1$ + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select e1 FROM pm2.g1)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ null, capFinder, new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ checkNodeTypes(plan, new int[] { @@ -696,7 +697,7 @@ */ @Test public void testProjectSubqueryPushdown() { FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); - FakeMetadataFacade metadata = example1(); + FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached(); BasicSourceCapabilities caps = getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true); @@ -714,7 +715,7 @@ } @Test public void testScalarSubquery2() { - ProcessorPlan plan = helpPlan("Select e1, (select e1 FROM pm2.g1 where pm1.g1.e1 = 'x') as X from pm1.g1", example1(), //$NON-NLS-1$ + ProcessorPlan plan = helpPlan("Select e1, (select e1 FROM pm2.g1 where pm1.g1.e1 = 'x') as X from pm1.g1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT e1 FROM pm1.g1" }); //$NON-NLS-1$ checkNodeTypes(plan, new int[] { 1, // Access @@ -738,15 +739,87 @@ * Technically this is not a full push-down, but the subquery will be evaluated prior to pushdown */ @Test public void testCompareSubquery4() throws TeiidComponentException, TeiidProcessingException { - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 > (select e1 FROM pm2.g1 where e2 = 13)", example1(), //$NON-NLS-1$ + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 > (select e1 FROM pm2.g1 where e2 = 13)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0 WHERE g_0.e1 > (SELECT g_0.e1 FROM pm2.g1 AS g_0 WHERE g_0.e2 = 13)" }, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); } @Test public void testScalarSubquery1() throws TeiidComponentException, TeiidProcessingException { - ProcessorPlan plan = helpPlan("Select e1, (select e1 FROM pm2.g1 where e1 = 'x') from pm1.g1", example1(), //$NON-NLS-1$ + ProcessorPlan plan = helpPlan("Select e1, (select e1 FROM pm2.g1 where e1 = 'x') from pm1.g1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1, (SELECT g_0.e1 FROM pm2.g1 AS g_0 WHERE g_0.e1 = 'x') FROM pm1.g1 AS g_0" }, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); } + @Test public void testSubqueryRewriteToJoin() throws Exception { + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where exists (select pm1.g1.e1 FROM pm1.g1 where e1 = pm3.g1.e1)", "SELECT e1 FROM pm3.g1, (SELECT pm1.g1.e1 FROM pm1.g1) AS X__1 WHERE pm3.g1.e1 = X__1.e1", FakeMetadataFactory.example4()); + } + + @Test public void testSubqueryRewriteToJoin1() throws Exception { + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e1 in (select pm1.g1.e1 FROM pm1.g1)", "SELECT e1 FROM pm3.g1, (SELECT pm1.g1.e1 FROM pm1.g1) AS X__1 WHERE pm3.g1.e1 = X__1.e1", FakeMetadataFactory.example4()); + } + + @Test public void testSubqueryRewriteToJoinWithOtherCriteria() throws Exception { + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e1 in (select pm1.g1.e1 FROM pm1.g1 where e2 < pm3.g1.e2)", "SELECT e1 FROM pm3.g1, (SELECT pm1.g1.e1, e2 FROM pm1.g1) AS X__1 WHERE (X__1.e2 < pm3.g1.e2) AND (pm3.g1.e1 = X__1.e1)", FakeMetadataFactory.example4()); + } + + @Test public void testSubqueryRewriteToJoinWithAggregate() throws Exception { + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 < (select max(e2) FROM pm1.g1 where pm3.g1.e1 = e1)", "SELECT e1 FROM pm3.g1, (SELECT MAX(e2) AS MAX, e1 FROM pm1.g1 GROUP BY e1) AS X__1 WHERE (pm3.g1.e2 < X__1.MAX) AND (pm3.g1.e1 = X__1.e1)", FakeMetadataFactory.example4()); + } + + /** + * A join will not be used since the predicate cannot be applied after the grouping + * @throws Exception + */ + @Test public void testSubqueryRewriteToJoinWithAggregate1() throws Exception { + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 < (select max(e2) FROM pm1.g1 where pm3.g1.e1 = e1 and pm3.g1.e3 > e3)", "SELECT e1 FROM pm3.g1 WHERE pm3.g1.e2 < (SELECT MAX(e2) FROM pm1.g1 WHERE (e1 = pm3.g1.e1) AND (e3 < pm3.g1.e3))", FakeMetadataFactory.example4()); + } + + @Test public void testSubqueryRewriteToJoinWithAggregate2() throws Exception { + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 < (select max(e2) FROM pm1.g1 WHERE pm3.g1.e1 = e1 HAVING min(e3) < pm3.g1.e3)", "SELECT e1 FROM pm3.g1, (SELECT MAX(e2) AS MAX, e1, MIN(e3) AS MIN FROM pm1.g1 GROUP BY e1) AS X__1 WHERE (X__1.MIN < pm3.g1.e3) AND (pm3.g1.e2 < X__1.MAX) AND (pm3.g1.e1 = X__1.e1)", FakeMetadataFactory.example4()); + } + + /** + * A join will not be used here because of the not + * @throws Exception + */ + @Test public void testSubqueryRewriteNot() throws Exception { + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 not in (select e2 FROM pm1.g1 where pm3.g1.e1 = e1)", "SELECT e1 FROM pm3.g1 WHERE pm3.g1.e2 NOT IN (SELECT e2 FROM pm1.g1 WHERE e1 = pm3.g1.e1)", FakeMetadataFactory.example4()); + } + + /** + * A join will not be used here because of the all + * @throws Exception + */ + @Test public void testSubqueryRewriteAll() throws Exception { + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 = all (select e2 FROM pm1.g1 where pm3.g1.e1 = e1)", "SELECT e1 FROM pm3.g1 WHERE pm3.g1.e2 = ALL (SELECT e2 FROM pm1.g1 WHERE e1 = pm3.g1.e1)", FakeMetadataFactory.example4()); + } + + @Test public void testSubqueryExpressionJoin() throws Exception { + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 < (Select max(e2) from pm2.g2 where e1 = pm3.g1.e1 having convert(min(e2), string) > pm3.g1.e1)", "SELECT e1 FROM pm3.g1, (SELECT MAX(e2) AS MAX, e1, MIN(e2) AS MIN FROM pm2.g2 GROUP BY e1) AS X__1 WHERE (convert(X__1.MIN, string) > pm3.g1.e1) AND (pm3.g1.e2 < X__1.MAX) AND (pm3.g1.e1 = X__1.e1)", FakeMetadataFactory.example4()); + } + + /** + * Must be handled as a semi-join + */ + @Test public void testSemiJoin() { + ProcessorPlan plan = helpPlan("Select e1 from pm2.g2 where e2 in (select count(e2) FROM pm1.g2 group by e1 having e1 < pm2.g2.e3)", FakeMetadataFactory.example4(), //$NON-NLS-1$ + new String[] { "SELECT g_0.e2 AS c_0, g_0.e3 AS c_1, g_0.e1 AS c_2 FROM pm2.g2 AS g_0 ORDER BY c_0" }); //$NON-NLS-1$ + checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 0, // Grouping + 0, // NestedLoopJoinStrategy + 1, // MergeJoinStrategy + 0, // Null + 1, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } + } Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestRuleAccessPatternValidation.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestRuleAccessPatternValidation.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/relational/rules/TestRuleAccessPatternValidation.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -34,7 +34,6 @@ import org.teiid.query.optimizer.capabilities.CapabilitiesFinder; import org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder; import org.teiid.query.optimizer.relational.OptimizerRule; -import org.teiid.query.optimizer.relational.PlanHints; import org.teiid.query.optimizer.relational.RelationalPlanner; import org.teiid.query.optimizer.relational.RuleStack; import org.teiid.query.optimizer.relational.plantree.PlanNode; @@ -88,9 +87,9 @@ RelationalPlanner p = new RelationalPlanner(); p.initialize(query, null, METADATA, FINDER, null, null); PlanNode planNode = p.generatePlan(query); + RelationalPlanner planner = new RelationalPlanner(); + final RuleStack rules = planner.buildRules(); - final RuleStack rules = RelationalPlanner.buildRules(new PlanHints()); - PlanNode testPlan = helpExecuteRules(rules, planNode, METADATA, DEBUG); return testPlan; Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -5452,6 +5452,7 @@ caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true); caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_ORDERED, true); caps.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, true); + caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true); capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$ QueryMetadataInterface metadata = FakeMetadataFactory.exampleBQTCached(); @@ -5460,7 +5461,7 @@ ProcessorPlan plan = helpGetPlan(command, metadata, capFinder); Set actualQueries = TestOptimizer.getAtomicQueries(plan); - String expectedSql = "SELECT SUM(X_1.c_1), X_1.c_0 FROM (SELECT CASE WHEN BQT1.SmallA.IntKey >= 5000 THEN '5000 +' ELSE '0-999' END AS c_0, BQT1.SmallA.IntKey AS c_1 FROM BQT1.SmallA) AS v_0 GROUP BY X_1.c_0"; //$NON-NLS-1$ + String expectedSql = "SELECT SUM(v_0.c_1), v_0.c_0 FROM (SELECT CASE WHEN g_0.IntKey >= 5000 THEN '5000 +' ELSE '0-999' END AS c_0, g_0.IntKey AS c_1 FROM BQT1.SmallA AS g_0) AS v_0 GROUP BY v_0.c_0"; //$NON-NLS-1$ assertEquals(1, actualQueries.size()); assertEquals(expectedSql, actualQueries.iterator().next().toString()); Modified: trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -22,8 +22,7 @@ package org.teiid.query.processor.eval; -import static org.junit.Assert.assertEquals; -import static org.junit.Assert.fail; +import static org.junit.Assert.*; import java.io.Serializable; import java.math.BigDecimal; @@ -40,13 +39,14 @@ import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidException; import org.teiid.core.TeiidProcessingException; +import org.teiid.core.types.DataTypeManager; import org.teiid.query.eval.Evaluator; import org.teiid.query.function.FunctionDescriptor; -import org.teiid.query.function.SystemFunctionManager; import org.teiid.query.processor.FakeDataManager; import org.teiid.query.processor.ProcessorDataManager; import org.teiid.query.resolver.TestFunctionResolving; import org.teiid.query.sql.lang.CollectionValueIterator; +import org.teiid.query.sql.lang.IsNullCriteria; import org.teiid.query.sql.lang.Query; import org.teiid.query.sql.lang.SubqueryContainer; import org.teiid.query.sql.symbol.CaseExpression; @@ -412,4 +412,24 @@ assertEquals(new BigDecimal("0.3333333333333333"), Evaluator.evaluate(ex)); } + @Test public void testIsNull() throws Exception { + assertEquals(Boolean.TRUE, Evaluator.evaluate(new IsNullCriteria(new Constant(null, DataTypeManager.DefaultDataClasses.BOOLEAN)))); + } + + @Test public void testIsNull1() throws Exception { + assertEquals(Boolean.FALSE, Evaluator.evaluate(new IsNullCriteria(new Constant(Boolean.TRUE, DataTypeManager.DefaultDataClasses.BOOLEAN)))); + } + + @Test public void testIsNull3() throws Exception { + IsNullCriteria inc = new IsNullCriteria(new Constant(null, DataTypeManager.DefaultDataClasses.BOOLEAN)); + inc.setNegated(true); + assertEquals(Boolean.FALSE, Evaluator.evaluate(inc)); + } + + @Test public void testIsNull4() throws Exception { + IsNullCriteria inc = new IsNullCriteria(new Constant(Boolean.TRUE, DataTypeManager.DefaultDataClasses.BOOLEAN)); + inc.setNegated(true); + assertEquals(Boolean.TRUE, Evaluator.evaluate(inc)); + } + } Modified: trunk/engine/src/test/java/org/teiid/query/rewriter/TestOrderByRewrite.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/rewriter/TestOrderByRewrite.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/test/java/org/teiid/query/rewriter/TestOrderByRewrite.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -309,7 +309,7 @@ @Test public void testOrderByExpression() throws Exception { Query resolvedQuery = (Query) getCommand("SELECT 0 AS SOMEINT, pm1.g1.e2 as y FROM pm1.g1 ORDER BY e2 || e1, e3"); //$NON-NLS-1$ - assertEquals("SELECT X_1.SOMEINT, X_1.y FROM (SELECT 0 AS SOMEINT, pm1.g1.e2 AS y, (e2 || e1) AS EXPR1, e3 FROM pm1.g1) AS X_1 ORDER BY X_1.EXPR1, X_1.e3", resolvedQuery.toString()); //$NON-NLS-1$ + assertEquals("SELECT X.SOMEINT, X.y FROM (SELECT 0 AS SOMEINT, pm1.g1.e2 AS y, (e2 || e1) AS EXPR1, e3 FROM pm1.g1) AS X ORDER BY X.EXPR1, X.e3", resolvedQuery.toString()); //$NON-NLS-1$ } @Test public void testRewiteOrderBy() { Modified: trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2010-11-12 19:20:14 UTC (rev 2723) +++ trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2010-11-16 18:23:48 UTC (rev 2724) @@ -171,7 +171,7 @@ } } - static Command helpTestRewriteCommand(String original, String expected, QueryMetadataInterface metadata) throws TeiidException { + public static Command helpTestRewriteCommand(String original, String expected, QueryMetadataInterface metadata) throws TeiidException { Command command = QueryParser.getQueryParser().parseCommand(original); QueryResolver.resolveCommand(command, metadata); Command rewriteCommand = QueryRewriter.rewrite(command, metadata, null); @@ -1962,7 +1962,7 @@ */ @Test public void testRewriteSelectInto1() { String sql = "select distinct e2, e2, e3, e4 into pm1.g1 from pm1.g2"; //$NON-NLS-1$ - String expected = "INSERT INTO pm1.g1 (pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4) SELECT PM1_G1_1.e2 AS e1, PM1_G1_1.E2_0 AS e2, PM1_G1_1.e3, PM1_G1_1.e4 FROM (SELECT DISTINCT e2, e2 AS E2_0, e3, e4 FROM pm1.g2) AS pm1_g1_1"; //$NON-NLS-1$ + String expected = "INSERT INTO pm1.g1 (pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4) SELECT X.e2 AS e1, X.E2_0 AS e2, X.e3, X.e4 FROM (SELECT DISTINCT e2, e2 AS E2_0, e3, e4 FROM pm1.g2) AS X"; //$NON-NLS-1$ helpTestRewriteCommand(sql, expected); } @@ -2019,7 +2019,7 @@ procedure += "Select x from temp;\n"; //$NON-NLS-1$ procedure += "END\n"; //$NON-NLS-1$ - helpTestRewriteCommand(procedure, "CREATE VIRTUAL PROCEDURE\nBEGIN\nCREATE LOCAL TEMPORARY TABLE temp (x string, y integer, z integer);\nINSERT INTO temp (TEMP.x, TEMP.y, TEMP.z) SELECT TEMP_1.e2 AS x, TEMP_1.x AS y, TEMP_1.X_0 AS z FROM (SELECT pm1.g1.e2, 1 AS x, 2 AS X_0 FROM pm1.g1 ORDER BY pm1.g1.e2 LIMIT 1) AS temp_1;\nSELECT x FROM temp;\nEND"); //$NON-NLS-1$ + helpTestRewriteCommand(procedure, "CREATE VIRTUAL PROCEDURE\nBEGIN\nCREATE LOCAL TEMPORARY TABLE temp (x string, y integer, z integer);\nINSERT INTO temp (TEMP.x, TEMP.y, TEMP.z) SELECT X.e2 AS x, X.x AS y, X.X_0 AS z FROM (SELECT pm1.g1.e2, 1 AS x, 2 AS X_0 FROM pm1.g1 ORDER BY pm1.g1.e2 LIMIT 1) AS X;\nSELECT x FROM temp;\nEND"); //$NON-NLS-1$ } @Test public void testRewriteNot() {