private AlgebraNode PushOverJoin(ComputeScalarAlgebraNode node) { JoinAlgebraNode joinAlgebraNode = (JoinAlgebraNode)node.Input; RowBufferEntry[] leftDefinedValues = AstUtil.GetDefinedValueEntries(joinAlgebraNode.Left); RowBufferEntry[] rightDefinedValues = AstUtil.GetDefinedValueEntries(joinAlgebraNode.Right); List <ComputedValueDefinition> remainingValueDefinitions = new List <ComputedValueDefinition>(); List <ComputedValueDefinition> leftPushedValueDefinitions = new List <ComputedValueDefinition>(); List <ComputedValueDefinition> rightPushedValueDefinitions = new List <ComputedValueDefinition>(); bool canPushToLeftSide = joinAlgebraNode.Op != JoinAlgebraNode.JoinOperator.RightOuterJoin && joinAlgebraNode.Op != JoinAlgebraNode.JoinOperator.FullOuterJoin; bool canPushToRightSide = joinAlgebraNode.Op != JoinAlgebraNode.JoinOperator.LeftOuterJoin && joinAlgebraNode.Op != JoinAlgebraNode.JoinOperator.FullOuterJoin; foreach (ComputedValueDefinition valueDefinition in node.DefinedValues) { RowBufferEntry[] referencedValues = AstUtil.GetRowBufferEntryReferences(valueDefinition.Expression); bool referencesProbeColumn = ArrayHelpers.Contains(referencedValues, joinAlgebraNode.ProbeBufferEntry); if (!referencesProbeColumn && canPushToLeftSide && AstUtil.DoesNotReference(referencedValues, rightDefinedValues)) { leftPushedValueDefinitions.Add(valueDefinition); } else if (!referencesProbeColumn && canPushToRightSide && AstUtil.DoesNotReference(referencedValues, leftDefinedValues)) { rightPushedValueDefinitions.Add(valueDefinition); } else { remainingValueDefinitions.Add(valueDefinition); } } if (leftPushedValueDefinitions.Count > 0) { ComputeScalarAlgebraNode computeScalarAlgebraNode = new ComputeScalarAlgebraNode(); computeScalarAlgebraNode.DefinedValues = leftPushedValueDefinitions.ToArray(); computeScalarAlgebraNode.Input = joinAlgebraNode.Left; joinAlgebraNode.Left = VisitAlgebraNode(computeScalarAlgebraNode); } if (rightPushedValueDefinitions.Count > 0) { ComputeScalarAlgebraNode computeScalarAlgebraNode = new ComputeScalarAlgebraNode(); computeScalarAlgebraNode.DefinedValues = rightPushedValueDefinitions.ToArray(); computeScalarAlgebraNode.Input = joinAlgebraNode.Right; joinAlgebraNode.Right = VisitAlgebraNode(computeScalarAlgebraNode); } if (remainingValueDefinitions.Count == 0) { return(joinAlgebraNode); } node.DefinedValues = remainingValueDefinitions.ToArray(); return(node); }
public override AlgebraNode VisitJoinAlgebraNode(JoinAlgebraNode node) { // Get declared tables of left and right RowBufferEntry[] leftDefinedValues = AstUtil.GetDefinedValueEntries(node.Left); RowBufferEntry[] rightDefinedValues = AstUtil.GetDefinedValueEntries(node.Right); // Analyze AND-parts of Condition if (node.Predicate != null) { List <ExpressionNode> leftAndParts = new List <ExpressionNode>(); List <ExpressionNode> rightAndParts = new List <ExpressionNode>(); List <ExpressionNode> remainingAndParts = new List <ExpressionNode>(); foreach (ExpressionNode andPart in AstUtil.SplitCondition(LogicalOperator.And, node.Predicate)) { // Check if we can push this AND-part down. if (AstUtil.AllowsLeftPushDown(node.Op) && AstUtil.ExpressionDoesNotReference(andPart, rightDefinedValues)) { leftAndParts.Add(andPart); } else if (AstUtil.AllowsRightPushDown(node.Op) && AstUtil.ExpressionDoesNotReference(andPart, leftDefinedValues)) { rightAndParts.Add(andPart); } else { remainingAndParts.Add(andPart); } } if (leftAndParts.Count > 0) { node.Left = GetFilterFromAndParts(leftAndParts, node.Left); } if (rightAndParts.Count > 0) { node.Right = GetFilterFromAndParts(rightAndParts, node.Right); } node.Predicate = AstUtil.CombineConditions(LogicalOperator.And, remainingAndParts); } // Visit children node.Left = VisitAlgebraNode(node.Left); node.Right = VisitAlgebraNode(node.Right); return(node); }
public override AlgebraNode VisitJoinAlgebraNode(JoinAlgebraNode node) { // Get declared tables of left and right RowBufferEntry[] leftDefinedValues = AstUtil.GetDefinedValueEntries(node.Left); RowBufferEntry[] rightDefinedValues = AstUtil.GetDefinedValueEntries(node.Right); // Replace outer joins by left-/right-/inner joins if (node.Op == JoinAlgebraNode.JoinOperator.RightOuterJoin || node.Op == JoinAlgebraNode.JoinOperator.FullOuterJoin) { if (IsAnyNullRejected(leftDefinedValues)) { if (node.Op == JoinAlgebraNode.JoinOperator.RightOuterJoin) { node.Op = JoinAlgebraNode.JoinOperator.InnerJoin; } else { node.Op = JoinAlgebraNode.JoinOperator.LeftOuterJoin; } } } if (node.Op == JoinAlgebraNode.JoinOperator.LeftOuterJoin || node.Op == JoinAlgebraNode.JoinOperator.FullOuterJoin) { if (IsAnyNullRejected(rightDefinedValues)) { if (node.Op == JoinAlgebraNode.JoinOperator.LeftOuterJoin) { node.Op = JoinAlgebraNode.JoinOperator.InnerJoin; } else { node.Op = JoinAlgebraNode.JoinOperator.RightOuterJoin; } } } // After converting an outer join to an inner one we can // sometimes eliminate the whole join. if (node.Op == JoinAlgebraNode.JoinOperator.InnerJoin) { // TODO: There is a problem. If the constant scan defines values this does not work. Acutally, // this is currently no problem as the only way to create such a plan is using derived // tables and in this phase the child will be a ResultNode. if (node.Left is ConstantScanAlgebraNode) { return(VisitAlgebraNode(WrapWithFilter(node.Right, node.Predicate))); } if (node.Right is ConstantScanAlgebraNode) { return(VisitAlgebraNode(WrapWithFilter(node.Left, node.Predicate))); } } // Analyze AND-parts of Condition if (node.Predicate == null) { // TODO: This does not work as the children are not yet rearranged. if (node.Op == JoinAlgebraNode.JoinOperator.LeftOuterJoin || node.Op == JoinAlgebraNode.JoinOperator.RightOuterJoin) { bool hasOuterReferences = AstUtil.GetOuterReferences(node).Length == 0; if (!hasOuterReferences) { if (node.Op == JoinAlgebraNode.JoinOperator.LeftOuterJoin && AstUtil.WillProduceAtLeastOneRow(node.Right) || node.Op == JoinAlgebraNode.JoinOperator.RightOuterJoin && AstUtil.WillProduceAtLeastOneRow(node.Left)) { node.Op = JoinAlgebraNode.JoinOperator.InnerJoin; return(VisitAlgebraNode(node)); } } } } else { foreach (ExpressionNode andPart in AstUtil.SplitCondition(LogicalOperator.And, node.Predicate)) { if (node.Op != JoinAlgebraNode.JoinOperator.FullOuterJoin) { // Check if we can derive from this AND-part that a table it depends on // is null-rejected. RowBufferEntry[] rowBufferEntries = AstUtil.GetRowBufferEntryReferences(andPart); foreach (RowBufferEntry rowBufferEntry in rowBufferEntries) { if (AstUtil.ExpressionYieldsNullOrFalseIfRowBufferEntryNull(andPart, rowBufferEntry)) { if (ArrayHelpers.Contains(leftDefinedValues, rowBufferEntry) && node.Op != JoinAlgebraNode.JoinOperator.LeftOuterJoin) { AddNullRejectedTable(rowBufferEntry); } else if (ArrayHelpers.Contains(rightDefinedValues, rowBufferEntry) && node.Op != JoinAlgebraNode.JoinOperator.RightOuterJoin) { AddNullRejectedTable(rowBufferEntry); } } } } } } // Visit children node.Left = VisitAlgebraNode(node.Left); node.Right = VisitAlgebraNode(node.Right); return(node); }
public override AlgebraNode VisitJoinAlgebraNode(JoinAlgebraNode node) { node.Left = VisitAlgebraNode(node.Left); node.Right = VisitAlgebraNode(node.Right); // Get defined values of left and right RowBufferEntry[] leftDefinedValues = AstUtil.GetDefinedValueEntries(node.Left); RowBufferEntry[] rightDefinedValues = AstUtil.GetDefinedValueEntries(node.Right); List <ExpressionNode> andPartsWithinJoin = new List <ExpressionNode>(); // Try to pull up AND-parts that contain outer references from a left sided filter and combine // them with the join predicate. // // NOTE: This is only possible if the join is not a LEFT OUTER or FULL OUTER JOIN since this // operation would change the join's semantic. if (node.Op != JoinAlgebraNode.JoinOperator.LeftOuterJoin && node.Op != JoinAlgebraNode.JoinOperator.FullOuterJoin) { FilterAlgebraNode leftAsFilter = node.Left as FilterAlgebraNode; if (leftAsFilter != null) { List <ExpressionNode> remainingAndParts = new List <ExpressionNode>(); foreach (ExpressionNode andPart in AstUtil.SplitCondition(LogicalOperator.And, leftAsFilter.Predicate)) { if (AndPartHasOuterReference(andPart, leftDefinedValues)) { andPartsWithinJoin.Add(andPart); } else { remainingAndParts.Add(andPart); } } leftAsFilter.Predicate = AstUtil.CombineConditions(LogicalOperator.And, remainingAndParts); if (leftAsFilter.Predicate == null) { node.Left = leftAsFilter.Input; } } } // Try to pull up AND-parts that contain outer references from a right sided filter and combine // them with the join predicate. // // NOTE: This is only possible if the join is not a RIGHT OUTER or FULL OUTER JOIN since this // operation would change the join's semantic. if (node.Op != JoinAlgebraNode.JoinOperator.RightOuterJoin && node.Op != JoinAlgebraNode.JoinOperator.FullOuterJoin) { FilterAlgebraNode rightAsFilter = node.Right as FilterAlgebraNode; if (rightAsFilter != null) { List <ExpressionNode> remainingAndParts = new List <ExpressionNode>(); foreach (ExpressionNode andPart in AstUtil.SplitCondition(LogicalOperator.And, rightAsFilter.Predicate)) { if (AndPartHasOuterReference(andPart, rightDefinedValues)) { andPartsWithinJoin.Add(andPart); } else { remainingAndParts.Add(andPart); } } rightAsFilter.Predicate = AstUtil.CombineConditions(LogicalOperator.And, remainingAndParts); if (rightAsFilter.Predicate == null) { node.Right = rightAsFilter.Input; } } } // If we found any AND-parts that could be pulled up, merge them with the join predicate. if (andPartsWithinJoin.Count > 0) { node.Predicate = AstUtil.CombineConditions(LogicalOperator.And, node.Predicate, AstUtil.CombineConditions(LogicalOperator.And, andPartsWithinJoin)); } // Now we try to extract AND-parts that contain outer references from the join predicate itself. // // NOTE: This is only possible if the node is not an OUTER JOIN. If the node is a SEMI JOIN the // operation is only legal if the AND-part does not reference any columns from the side that is // is used as filter criteria (i.e. for LSJ this is the right side, for RSJ this is the left // side). if (node.Op != JoinAlgebraNode.JoinOperator.LeftOuterJoin && node.Op != JoinAlgebraNode.JoinOperator.RightOuterJoin && node.Op != JoinAlgebraNode.JoinOperator.FullOuterJoin && node.Predicate != null) { List <ExpressionNode> andPartsAboveJoin = new List <ExpressionNode>(); List <ExpressionNode> remainingAndParts = new List <ExpressionNode>(); foreach (ExpressionNode andPart in AstUtil.SplitCondition(LogicalOperator.And, node.Predicate)) { if (AndPartHasOuterReference(andPart, leftDefinedValues, rightDefinedValues) && SemiJoinDoesNotDependOn(node.Op, andPart, leftDefinedValues, rightDefinedValues)) { andPartsAboveJoin.Add(andPart); } else { remainingAndParts.Add(andPart); } } node.Predicate = AstUtil.CombineConditions(LogicalOperator.And, remainingAndParts); if (andPartsAboveJoin.Count > 0) { FilterAlgebraNode filterAlgebraNode = new FilterAlgebraNode(); filterAlgebraNode.Predicate = AstUtil.CombineConditions(LogicalOperator.And, andPartsAboveJoin); filterAlgebraNode.Input = node; return(filterAlgebraNode); } } return(node); }
private AlgebraNode PushOverJoin(FilterAlgebraNode node) { JoinAlgebraNode inputNode = (JoinAlgebraNode)node.Input; // Get declared tables of left and right RowBufferEntry[] leftDefinedValues = AstUtil.GetDefinedValueEntries(inputNode.Left); RowBufferEntry[] rightDefinedValues = AstUtil.GetDefinedValueEntries(inputNode.Right); // Obviously, we cannot merge the filter with the join if the join is an outer join // (since it would change the join's semantics). // // Another less obvious restriction is that we cannot merge a filter with the join if // the join has a passthru predicate. In case the passthru predicte evaluates to true // the filter would not be applied. However, we are allowed to push the filter the over // join. bool canMerge = inputNode.Op != JoinAlgebraNode.JoinOperator.FullOuterJoin && inputNode.Op != JoinAlgebraNode.JoinOperator.LeftOuterJoin && inputNode.Op != JoinAlgebraNode.JoinOperator.RightOuterJoin && inputNode.PassthruPredicate == null; if (canMerge) { // We can merge the filter with the condition of the join. // // However, we have to make sure that the predicate does not reference the probe column. // Since not having a probe column is the most common case, we don't always split the // predicate into conjuncts. if (inputNode.ProbeBufferEntry == null || !ArrayHelpers.Contains(AstUtil.GetRowBufferEntryReferences(node.Predicate), inputNode.ProbeBufferEntry)) { // Either there is no probe column defined or the filter does not reference it. That means // no splitting necessary, we can just merge the whole predicate with the join predicate. inputNode.Predicate = AstUtil.CombineConditions(LogicalOperator.And, inputNode.Predicate, node.Predicate); return(VisitAlgebraNode(inputNode)); } else { // Unfortunately, the filter references the probe column. Now let's check whether we can merge // conjuncts of the predicate. List <ExpressionNode> remainingAndParts = new List <ExpressionNode>(); List <ExpressionNode> mergableAndParts = new List <ExpressionNode>(); foreach (ExpressionNode andPart in AstUtil.SplitCondition(LogicalOperator.And, node.Predicate)) { bool andPartReferencesProbeColumn = ArrayHelpers.Contains(AstUtil.GetRowBufferEntryReferences(andPart), inputNode.ProbeBufferEntry); if (andPartReferencesProbeColumn) { remainingAndParts.Add(andPart); } else { mergableAndParts.Add(andPart); } } if (mergableAndParts.Count > 0) { ExpressionNode combinedMergableAndParts = AstUtil.CombineConditions(LogicalOperator.And, mergableAndParts.ToArray()); inputNode.Predicate = AstUtil.CombineConditions(LogicalOperator.And, inputNode.Predicate, combinedMergableAndParts); node.Predicate = AstUtil.CombineConditions(LogicalOperator.And, remainingAndParts); if (node.Predicate == null) { return(VisitAlgebraNode(inputNode)); } } } } else { // The condition cannot be merged. Now we try to push AND-parts over the join. List <ExpressionNode> leftAndParts = new List <ExpressionNode>(); List <ExpressionNode> rightAndParts = new List <ExpressionNode>(); List <ExpressionNode> remainingAndParts = new List <ExpressionNode>(); foreach (ExpressionNode andPart in AstUtil.SplitCondition(LogicalOperator.And, node.Predicate)) { bool andPartReferencesProbeColumn = inputNode.ProbeBufferEntry != null && ArrayHelpers.Contains(AstUtil.GetRowBufferEntryReferences(andPart), inputNode.ProbeBufferEntry); if (!andPartReferencesProbeColumn && AstUtil.AllowsLeftPushOver(inputNode.Op) && AstUtil.ExpressionDoesNotReference(andPart, rightDefinedValues)) { // The AND-part depends only on the LHS and the join is inner/left. // So we are allowed to push this AND-part down. leftAndParts.Add(andPart); } else if (!andPartReferencesProbeColumn && AstUtil.AllowsRightPushOver(inputNode.Op) && AstUtil.ExpressionDoesNotReference(andPart, leftDefinedValues)) { // The AND-part depends only on the RHS and the join is inner/right. // So we are allowed to push this AND-part down. rightAndParts.Add(andPart); } else { remainingAndParts.Add(andPart); } } if (leftAndParts.Count > 0) { inputNode.Left = GetFilterFromAndParts(leftAndParts, inputNode.Left); } if (rightAndParts.Count > 0) { inputNode.Right = GetFilterFromAndParts(rightAndParts, inputNode.Right); } node.Predicate = AstUtil.CombineConditions(LogicalOperator.And, remainingAndParts); if (node.Predicate == null) { return(VisitAlgebraNode(inputNode)); } } node.Input = VisitAlgebraNode(node.Input); return(node); }
public override AlgebraNode VisitJoinAlgebraNode(JoinAlgebraNode node) { node.Left = VisitAlgebraNode(node.Left); node.Right = VisitAlgebraNode(node.Right); if (node.Predicate != null && (node.OuterReferences == null || node.OuterReferences.Length == 0) && ( node.Op == JoinAlgebraNode.JoinOperator.InnerJoin || node.Op == JoinAlgebraNode.JoinOperator.LeftOuterJoin || node.Op == JoinAlgebraNode.JoinOperator.RightOuterJoin || node.Op == JoinAlgebraNode.JoinOperator.FullOuterJoin) ) { RowBufferEntry[] leftDefinedEntries = AstUtil.GetDefinedValueEntries(node.Left); RowBufferEntry[] rightDefinedEntries = AstUtil.GetDefinedValueEntries(node.Right); EqualPredicatesExtractor equalPredicatesExtractor = new EqualPredicatesExtractor(leftDefinedEntries, rightDefinedEntries); ExpressionNode probeResidual = equalPredicatesExtractor.VisitExpression(node.Predicate); BinaryExpression[] equalPredicates = equalPredicatesExtractor.GetEqualPredicates(); if (equalPredicates.Length > 0) { BinaryExpression equalPredicate = equalPredicates[0]; ExpressionBuilder expressionBuilder = new ExpressionBuilder(); expressionBuilder.Push(probeResidual); if (equalPredicates.Length > 1) { for (int i = 1; i < equalPredicates.Length; i++) { expressionBuilder.Push(equalPredicates[i]); } expressionBuilder.PushNAry(LogicalOperator.And); } probeResidual = expressionBuilder.Pop(); if (probeResidual is ConstantExpression) { probeResidual = null; } AlgebraNode leftInput = node.Left; AlgebraNode rightInput = node.Right; if (node.Op == JoinAlgebraNode.JoinOperator.LeftOuterJoin) { node.Op = JoinAlgebraNode.JoinOperator.RightOuterJoin; leftInput = node.Right; rightInput = node.Left; ExpressionNode oldLeft = equalPredicate.Left; equalPredicate.Left = equalPredicate.Right; equalPredicate.Right = oldLeft; } RowBufferEntry leftEntry; RowBufferEntryExpression leftAsRowBufferEntryExpression = equalPredicate.Left as RowBufferEntryExpression; if (leftAsRowBufferEntryExpression != null) { leftEntry = leftAsRowBufferEntryExpression.RowBufferEntry; } else { leftEntry = new RowBufferEntry(equalPredicate.Left.ExpressionType); ComputedValueDefinition definedValue = new ComputedValueDefinition(); definedValue.Target = leftEntry; definedValue.Expression = equalPredicate.Left; ComputeScalarAlgebraNode computeScalarAlgebraNode = new ComputeScalarAlgebraNode(); computeScalarAlgebraNode.Input = leftInput; computeScalarAlgebraNode.DefinedValues = new ComputedValueDefinition[] { definedValue }; leftInput = computeScalarAlgebraNode; } RowBufferEntry rightEntry; RowBufferEntryExpression rightAsRowBufferEntryExpression = equalPredicate.Right as RowBufferEntryExpression; if (rightAsRowBufferEntryExpression != null) { rightEntry = rightAsRowBufferEntryExpression.RowBufferEntry; } else { rightEntry = new RowBufferEntry(equalPredicate.Right.ExpressionType); ComputedValueDefinition definedValue = new ComputedValueDefinition(); definedValue.Target = rightEntry; definedValue.Expression = equalPredicate.Right; ComputeScalarAlgebraNode computeScalarAlgebraNode = new ComputeScalarAlgebraNode(); computeScalarAlgebraNode.Input = rightInput; computeScalarAlgebraNode.DefinedValues = new ComputedValueDefinition[] { definedValue }; rightInput = computeScalarAlgebraNode; } HashMatchAlgebraNode hashMatchAlgebraNode = new HashMatchAlgebraNode(); hashMatchAlgebraNode.Op = node.Op; hashMatchAlgebraNode.Left = leftInput; hashMatchAlgebraNode.Right = rightInput; hashMatchAlgebraNode.BuildKeyEntry = leftEntry; hashMatchAlgebraNode.ProbeEntry = rightEntry; hashMatchAlgebraNode.ProbeResidual = probeResidual; return(hashMatchAlgebraNode); } } return(node); }