LogicNode inToMarkJoin(LogicNode planWithSubExpr, InSubqueryExpr inExpr)
{
LogicNode nodeA = planWithSubExpr;
// nodeB contains the join filter
var nodeB = inExpr.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_, inExpr.subqueryid_), 0);
var nodeAFilter = extractCurINExprFromNodeAFilter(nodeA, inExpr, markerFilter);
// consider SQL ...a1 in select b1 from...
// a1 is outerExpr and b1 is selectExpr
Expr outerExpr = inExpr.child_();
Debug.Assert(inExpr.query_.selection_.Count == 1);
Expr selectExpr = inExpr.query_.selection_[0];
BinExpr inToEqual = BinExpr.MakeBooleanExpr(outerExpr, selectExpr, "=", true);
// make a mark join
LogicMarkJoin markjoin;
if (inExpr.hasNot_)
{
markjoin = new LogicMarkAntiSemiJoin(nodeA, nodeB, inExpr.subqueryid_);
}
else
{
markjoin = new LogicMarkSemiJoin(nodeA, nodeB, inExpr.subqueryid_);
}
// make a filter on top of the mark join collecting all filters
Expr topfilter = nodeAFilter.SearchAndReplace(inExpr, markerFilter);
nodeBFilter.DeParameter(nodeA.InclusiveTableRefs());
topfilter = topfilter.AddAndFilter(nodeBFilter);
// TODO mutiple nested insubquery subquery
// seperate the overlapping code with existsToSubquery to a new method
// when the PR in #support nestted exist subquery pass
LogicFilter Filter = new LogicFilter(markjoin, topfilter);
Filter = new LogicFilter(Filter, inToEqual);
return(Filter);
}
// 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);
}