// A Xs B => A LOJ B if max1row is assured
LogicJoin singleJoin2OuterJoin(LogicSingleJoin singJoinNode)
{
LogicJoin newjoin = singJoinNode;
if (!singJoinNode.max1rowCheck_)
{
newjoin = new LogicJoin(singJoinNode.l_(), singJoinNode.r_(), singJoinNode.filter_);
newjoin.type_ = JoinType.Left;
}
return(newjoin);
}
// expands scalar subquery filter to mark join
//
// LogicNode_A
// Filter: a.a2 = @1 AND <others1>
// <ExistSubqueryExpr> 1
// -> LogicNode_B
// Output: singleValueExpr
// Filter: b.b1[0]=?a.a1[0] AND <others2>
// =>
// LogicFilter-Movable
// Filter: (b.b1[0]=a.a1[0]) AND <others1> <others2>
// LogicFilter-NonMovable
// Filter: a.a2 = singleValueExpr
// SingleJoin
// Output: singleValueExpr
// LogicNode_A
// LogicNode_B
//
LogicNode scalarToSingleJoin(LogicNode planWithSubExpr, ScalarSubqueryExpr scalarExpr)
{
var newplan = planWithSubExpr;
// nodeB contains the join filter
var nodeSubquery = scalarExpr.query_.logicPlan_;
// make a single join
// nodeA
// <Subquery>
// nodeSubquery
// =>
// SingleJoin
// nodeA
// nodeSubquery
//
var singleJoinNode = new LogicSingleJoin(planWithSubExpr,
nodeSubquery);
switch (nodeSubquery)
{
case LogicAgg la:
// Filter: a.a1[0] =@1
// < ScalarSubqueryExpr > 1
// ->PhysicHashAgg(rows = 2)
// -> PhysicFilter(rows = 2)
// Filter: b.b2[1]=?a.a2[1] ...
newplan = djoinOnRightAggregation(singleJoinNode, scalarExpr);
break;
case LogicFilter lf:
newplan = djoinOnRightFilter(singleJoinNode, scalarExpr);
break;
default:
break;
}
// see if we can convert to an ordinary LOJ
newplan.VisitEach((parent, index, node) =>
{
if (node is LogicSingleJoin sn)
{
Debug.Assert(parent != null);
parent.children_[index] = singleJoin2OuterJoin(sn);
}
});
return(newplan);
}
// D Xs (Agg (T) group by A) => Agg(D Xs T) group by {A, output(D))
// a.i = (select max(b.i) from b where a.j=b.j group by b.k)
//
LogicNode djoinOnRightAggregation(LogicSingleJoin singleJoinNode, ScalarSubqueryExpr scalarExpr)
{
var nodeLeft = singleJoinNode.lchild_();
var aggNode = singleJoinNode.rchild_() as LogicAgg;
// ?a.j = b.j => b.j
var listexpr = aggNode.RetrieveCorrelatedFilters();
var extraGroubyVars = new List <Expr>();
foreach (var v in listexpr)
{
var bv = v as BinExpr;
// if we can't handle, bail out
if (bv is null)
{
return(nodeLeft);
}
var lbv = bv.lchild_() as ColExpr;
var rbv = bv.rchild_() as ColExpr;
if (lbv is null || rbv is null)
{
return(nodeLeft);
}
// now we can handle them, take the non-parameter column
Debug.Assert(!(lbv.isParameter_ && rbv.isParameter_));
if (!lbv.isParameter_)
{
extraGroubyVars.Add(lbv);
}
if (!rbv.isParameter_)
{
extraGroubyVars.Add(rbv);
}
}
// group by b.k => group by b.k, b.j
Debug.Assert(singleJoinNode.max1rowCheck_);
if (aggNode.groupby_ is null)
{
aggNode.groupby_ = extraGroubyVars;
singleJoinNode.max1rowCheck_ = false;
}
else
{
aggNode.groupby_.AddRange(extraGroubyVars);
}
// put a filter on the right side and pull up the aggregation
//
// agg
// filter
// ...
// =>
// filter
// agg
// ...
var filterNode = aggNode.child_() as LogicFilter;
aggNode.children_[0] = filterNode.child_();
filterNode.children_[0] = aggNode;
singleJoinNode.children_[1] = filterNode;
// now we have convert it to a right filter plan
var newplan = djoinOnRightFilter(singleJoinNode, scalarExpr);
return(newplan);
}
// D Xs (Filter(T)) => Filter(D Xs T)
LogicNode djoinOnRightFilter(LogicSingleJoin singleJoinNode, ScalarSubqueryExpr scalarExpr)
{
var nodeLeft = singleJoinNode.lchild_();
var nodeSubquery = singleJoinNode.rchild_();
var nodeSubqueryFilter = nodeSubquery.filter_;
Debug.Assert(scalarExpr.query_.selection_.Count == 1);
var singleValueExpr = scalarExpr.query_.selection_[0];
nodeSubquery.NullifyFilter();
// nullify nodeA's filter: the rest is push to top filter. However,
// if nodeA is a Filter|MarkJoin, keep its mark filter.
var trueCondition = ConstExpr.MakeConstBool(true);
var nodeLeftFilter = nodeLeft.filter_;
if (nodeLeftFilter != 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 = nodeLeftFilter.FilterToAndList();
var keeplist = andlist.Where(x => x.VisitEachExists(e => e.Equals(scalarExpr))).ToList();
andlist.RemoveAll(x => x.VisitEachExists(e => e.Equals(scalarExpr)));
if (andlist.Count == 0)
{
nodeLeft.NullifyFilter();
}
else
{
nodeLeft.filter_ = andlist.AndListToExpr();
if (keeplist.Count > 0)
{
nodeLeftFilter = keeplist.AndListToExpr();
}
else
{
nodeLeftFilter = trueCondition;
}
}
}
// make a non-movable filter on top of the single join to replace a1 > @1
LogicFilter nonMovableFilter = null;
if (nodeLeftFilter != null)
{
// a1 > @1 => a1 > c1
var decExpr = nodeLeftFilter.SearchAndReplace(scalarExpr, singleValueExpr);
nonMovableFilter = new LogicFilter(singleJoinNode, decExpr)
{
movable_ = false
};
}
// b1 = ?outsideRef => b1 = outsideRef
nodeSubqueryFilter.DeParameter(nodeLeft.InclusiveTableRefs());
// join filter within sbuquery
Expr pullupFilter = nodeSubqueryFilter;
if (nodeLeft.filter_ != null && !nodeLeft.filter_.HasSubQuery())
{
pullupFilter = pullupFilter.AddAndFilter(nodeLeft.filter_);
nodeLeft.NullifyFilter();
}
LogicFilter Filter = new LogicFilter(nonMovableFilter != null ?
(LogicNode)nonMovableFilter : singleJoinNode, pullupFilter);
return(Filter);
}
