// If there is OR in the predicate, can't turn into a filter // LogicNode existsToMarkJoin(LogicNode nodeA, ExistSubqueryExpr existExpr, ref bool canReplace) { bool exprIsNotORExprAndEqualsToExistExpr(Expr x, Expr existExpr) { return((!(x is LogicOrExpr)) && x.Equals(existExpr)); } // nodeB contains the join filter var nodeB = existExpr.query_.logicPlan_; var nodeBFilter = nodeB.filter_; nodeB.NullifyFilter(); // nullify nodeA's filter: the rest is push to top filter. However, // if nodeA is a Filter|MarkJoin, keep its mark filter. var markerFilter = new ExprRef(new MarkerExpr(nodeBFilter.tableRefs_, existExpr.subqueryid_), 0); var nodeAFilter = nodeA.filter_; Debug.Assert(!(nodeA is null)); // a1 > @1 and a2 > @2 and a3 > 2, existExpr = @1 // keeplist: a1 > @1 and a3 > 2 // andlist after removal: a2 > @2 // nodeAFilter = a1 > @1 and a3 > 2 // consider a1>0 and (@1 or @2) // a1>0 and (@1 or (@2 and @3)) //(@1 or marker@2) and marker@3 existExpr = @1 // keep list (@1 or marker@2) // andlist after remove @3 // nodeAFilter = (@1 or marker@2) var andlist = nodeAFilter.FilterToAndList(); var keeplist = andlist.Where(x => x.VisitEachExists(e => e.Equals(existExpr))).ToList(); andlist.RemoveAll(x => exprIsNotORExprAndEqualsToExistExpr(x, existExpr) || ((x is LogicOrExpr) && !hasAnyExtraSubqueryExprInOR(x, existExpr))); // if there is any (#marker@1 or @2), the root should be replace, // i.e. the (#marker@1 or @2) keeps at the top for farther unnesting canReplace = andlist.Find(x => (x is LogicOrExpr) && hasAnyExtraSubqueryExprInOR(x, existExpr)) != null; if (andlist.Count == 0 || canReplace) { // nodeA is root, a ref parameter. (why it is a ref parameter without "ref" or "out" ) nodeA.NullifyFilter(); } else { nodeA.filter_ = andlist.AndListToExpr(); nodeAFilter = keeplist.Count > 0 ? keeplist.AndListToExpr() : markerFilter; } // make a mark join LogicMarkJoin markjoin; if (existExpr.hasNot_) { markjoin = new LogicMarkAntiSemiJoin(nodeA, nodeB, existExpr.subqueryid_); } else { markjoin = new LogicMarkSemiJoin(nodeA, nodeB, existExpr.subqueryid_); } // make a filter on top of the mark join collecting all filters Expr topfilter; topfilter = nodeAFilter.SearchAndReplace(existExpr, markerFilter); nodeBFilter.DeParameter(nodeA.InclusiveTableRefs()); // find all expr contains Parameter col and move it to the toper var TableRefs = nodeA.InclusiveTableRefs(); topfilter = topfilter.AddAndFilter(nodeBFilter); LogicFilter Filter = new LogicFilter(markjoin, topfilter); var notDeparameterExpr = findAndfetchParameterExpr(ref nodeA); if (notDeparameterExpr.Count > 0) { topfilter = notDeparameterExpr.AndListToExpr(); Filter = new LogicFilter(Filter, topfilter); } return(Filter); }
// expands [NOT] EXISTS filter to mark join // // LogicNode_A // Filter: @1 AND|OR <others1> // <ExistSubqueryExpr> 1 // -> LogicNode_B // Filter: b.b1[0]=?a.a1[0] AND|OR <others2> // => // Filter // Filter: #marker AND|OR <others1> // MarkJoin // Filter: (b.b1[0]=a.a1[0]) AND|OR <others2> as #marker // LogicNode_A // LogicNode_B // // further convert DJoin to semi-join here is by decorrelate process // LogicNode existsToMarkJoin(LogicNode nodeA, ExistSubqueryExpr existExpr) { var nodeAIsOnMarkJoin = nodeA is LogicFilter && (nodeA.child_() is LogicMarkJoin || nodeA.child_() is LogicSingleJoin); // nodeB contains the join filter var nodeB = existExpr.query_.logicPlan_; var nodeBFilter = nodeB.filter_; nodeB.NullifyFilter(); // nullify nodeA's filter: the rest is push to top filter. However, // if nodeA is a Filter|MarkJoin, keep its mark filter. var markerFilter = new ExprRef(new MarkerExpr(), 0); var nodeAFilter = nodeA.filter_; if (nodeAIsOnMarkJoin) { nodeA.filter_ = markerFilter; } else { if (nodeAFilter != null) { // a1 > @1 and a2 > @2 and a3 > 2, scalarExpr = @1 // keeplist: a1 > @1 and a3 > 2 // andlist after removal: a2 > @2 // nodeAFilter = a1 > @1 and a3 > 2 // var andlist = nodeAFilter.FilterToAndList(); var keeplist = andlist.Where(x => x.VisitEachExists(e => e.Equals(existExpr))).ToList(); andlist.RemoveAll(x => x.VisitEachExists(e => e.Equals(existExpr))); if (andlist.Count == 0) { nodeA.NullifyFilter(); } else { nodeA.filter_ = andlist.AndListToExpr(); if (keeplist.Count > 0) { nodeAFilter = keeplist.AndListToExpr(); } else { nodeAFilter = markerFilter; } } } } // make a mark join LogicMarkJoin markjoin; if (existExpr.hasNot_) { markjoin = new LogicMarkAntiSemiJoin(nodeA, nodeB); } else { markjoin = new LogicMarkSemiJoin(nodeA, nodeB); } // make a filter on top of the mark join collecting all filters Expr topfilter; if (nodeAIsOnMarkJoin) { topfilter = nodeAFilter.SearchReplace(existExpr, LiteralExpr.MakeLiteral("true", new BoolType())); } else { topfilter = nodeAFilter.SearchReplace(existExpr, markerFilter); } nodeBFilter.DeParameter(nodeA.InclusiveTableRefs()); topfilter = topfilter.AddAndFilter(nodeBFilter); LogicFilter Filter = new LogicFilter(markjoin, topfilter); return(Filter); }