// Extract filter matching ABC's tablerefs
// ABC=[a,b]
// a.i=b.i AND a.j=b.j AND a.k+b.k=c.k => a.i=b.i AND a.j=b.j
// ABC=[a,b,c]
// a.i=b.i AND a.j=b.j AND a.k+b.k=c.k => a.k+b.k=c.k
Expr exactFilter(Expr fullfilter, List <LogicNode> ABC)
{
Expr ret = null;
if (fullfilter is null)
{
return(null);
}
List <TableRef> ABCtabrefs = new List <TableRef>();
foreach (var m in ABC)
{
ABCtabrefs.AddRange(m.InclusiveTableRefs());
}
var andlist = fullfilter.FilterToAndList();
foreach (var v in andlist)
{
var predicate = v as BinExpr;
var predicateRefs = predicate.tableRefs_;
if (ABCtabrefs.ListAEqualsB(predicateRefs))
{
ret = ret.AddAndFilter(predicate);
}
}
return(ret);
}
public override CGroupMember Apply(CGroupMember expr)
{
LogicJoin a_bc = expr.logic_ as LogicJoin;
LogicNode a = (a_bc.l_() as LogicMemoRef).Deref <LogicNode>();
LogicJoin bc = (a_bc.r_() as LogicMemoRef).Deref <LogicJoin>();
Expr bcfilter = bc.filter_;
var ab = new LogicJoin(a_bc.l_(), bc.l_());
var c = bc.r_();
var ab_c = new LogicJoin(ab, c);
Debug.Assert(!a.LeftReferencesRight(bc));
if (ab.LeftReferencesRight(c))
{
return(expr);
}
// pull up all join filters and re-push them back
Expr allfilters = bcfilter;
if (a_bc.filter_ != null)
{
allfilters = allfilters.AddAndFilter(a_bc.filter_);
}
if (allfilters != null)
{
var andlist = allfilters.FilterToAndList();
andlist.RemoveAll(e => ab_c.PushJoinFilter(e));
if (andlist.Count > 0)
{
ab_c.filter_ = andlist.AndListToExpr();
}
}
// Ideally if there is no cross join in the given plan but cross join
// in the new plan, we shall return the original plan. However, stop
// exploration now will prevent generating other promising plans. So
// we have to return the new plan.
//
if (expr.QueryOption().optimize_.memo_disable_crossjoin_)
{
if (a_bc.filter_ != null && bcfilter != null)
{
if (ab_c.filter_ is null || ab.filter_ is null)
{
return(expr);
}
}
}
return(new CGroupMember(ab_c, expr.group_));
}
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);
}
// from clause -
// pair each from item with cross join, their join conditions will be handled
// with where clause processing.
//
LogicNode transformFromClause()
{
LogicNode transformOneFrom(TableRef tab)
{
LogicNode from;
switch (tab)
{
case BaseTableRef bref:
if (bref.Table().source_ == TableDef.TableSource.Table)
{
from = new LogicScanTable(bref);
}
else
{
from = new LogicScanStream(bref);
}
if (bref.tableSample_ != null)
{
from = new LogicSampleScan(from, bref.tableSample_);
}
break;
case ExternalTableRef eref:
from = new LogicScanFile(eref);
break;
case QueryRef qref:
var plan = qref.query_.CreatePlan();
if (qref is FromQueryRef && queryOpt_.optimize_.remove_from_)
{
from = plan;
}
else
{
string alias = null;
if (qref is CTEQueryRef cq)
{
alias = cq.alias_;
}
else if (qref is FromQueryRef fq)
{
alias = fq.alias_;
}
var key = new NamedQuery(qref.query_, alias);
from = new LogicFromQuery(qref, plan);
subQueries_.Add(key);
// if from CTE, then it could be duplicates
if (!fromQueries_.ContainsKey(key))
{
fromQueries_.Add(key, from as LogicFromQuery);
}
}
break;
case JoinQueryRef jref:
// We will form join group on all tables and put a filter on top
// of the joins as a normalized form for later processing.
//
// from a join b on a1=b1 or a3=b3 join c on a2=c2;
// => from a , b, c where (a1=b1 or a3=b3) and a2=c2;
//
LogicJoin subjoin = new LogicJoin(null, null);
Expr filterexpr = null;
for (int i = 0; i < jref.tables_.Count; i++)
{
LogicNode t = transformOneFrom(jref.tables_[i]);
var children = subjoin.children_;
if (children[0] is null)
{
children[0] = t;
}
else
{
if (children[1] is null)
{
children[1] = t;
}
else
{
subjoin = new LogicJoin(t, subjoin);
}
subjoin.type_ = jref.joinops_[i - 1];
filterexpr = filterexpr.AddAndFilter(jref.constraints_[i - 1]);
}
}
Debug.Assert(filterexpr != null);
from = new LogicFilter(subjoin, filterexpr);
break;
default:
throw new InvalidProgramException();
}
return(from);
}
LogicNode root;
if (from_.Count >= 2)
{
var join = new LogicJoin(null, null);
var children = join.children_;
from_.ForEach(x =>
{
LogicNode from = transformOneFrom(x);
if (children[0] is null)
{
children[0] = from;
}
else
{
children[1] = (children[1] is null) ? from :
new LogicJoin(from, children[1]);
}
});
root = join;
}
else if (from_.Count == 1)
{
root = transformOneFrom(from_[0]);
}
else
{
root = new LogicResult(selection_);
}
// distributed plan is required if any table is distributed, subquery
// referenced tables are not counted here. So we may have the query
// shape with main queyr not distributed but subquery is.
//
bool hasdtable = false;
bool onlyreplicated = true;
from_.ForEach(x => checkifHasdtableAndifOnlyReplicated(x, ref hasdtable, ref onlyreplicated));
if (hasdtable)
{
Debug.Assert(!distributed_);
distributed_ = true;
// distributed table query can also use memo
// remote exchange is considered in memo optimization
queryOpt_.optimize_.memo_use_remoteexchange_ = true;
if (onlyreplicated)
{
root = new LogicGather(root, new List <int> {
0
});
}
else
{
root = new LogicGather(root);
}
}
return(root);
}
// 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);
}
// from clause -
// pair each from item with cross join, their join conditions will be handled
// with where clauss processing.
//
LogicNode transformFromClause()
{
LogicNode transformOneFrom(TableRef tab)
{
LogicNode from;
switch (tab)
{
case BaseTableRef bref:
from = new LogicScanTable(bref);
break;
case ExternalTableRef eref:
from = new LogicScanFile(eref);
break;
case QueryRef qref:
var plan = qref.query_.CreatePlan();
if (qref is FromQueryRef && queryOpt_.optimize_.remove_from_)
{
from = plan;
}
else
{
string alias = null;
if (qref is CTEQueryRef cq)
{
alias = cq.alias_;
}
else if (qref is FromQueryRef fq)
{
alias = fq.alias_;
}
var key = new NamedQuery(qref.query_, alias);
from = new LogicFromQuery(qref, plan);
subQueries_.Add(key);
// if from CTE, then it could be duplicates
if (!fromQueries_.ContainsKey(key))
{
fromQueries_.Add(key, from as LogicFromQuery);
}
}
break;
case JoinQueryRef jref:
// We will form join group on all tables and put a filter on top
// of the joins as a normalized form for later processing.
//
// from a join b on a1=b1 or a3=b3 join c on a2=c2;
// => from a , b, c where (a1=b1 or a3=b3) and a2=c2;
//
LogicJoin subjoin = new LogicJoin(null, null);
Expr filterexpr = null;
for (int i = 0; i < jref.tables_.Count; i++)
{
LogicNode t = transformOneFrom(jref.tables_[i]);
var children = subjoin.children_;
if (children[0] is null)
{
children[0] = t;
}
else
{
if (children[1] is null)
{
children[1] = t;
}
else
{
subjoin = new LogicJoin(t, subjoin);
}
subjoin.type_ = jref.joinops_[i - 1];
filterexpr = filterexpr.AddAndFilter(jref.constraints_[i - 1]);
}
}
Debug.Assert(filterexpr != null);
from = new LogicFilter(subjoin, filterexpr);
break;
default:
throw new Exception();
}
return(from);
}
LogicNode root;
if (from_.Count >= 2)
{
var join = new LogicJoin(null, null);
var children = join.children_;
from_.ForEach(x =>
{
LogicNode from = transformOneFrom(x);
if (children[0] is null)
{
children[0] = from;
}
else
{
children[1] = (children[1] is null) ? from :
new LogicJoin(from, children[1]);
}
});
root = join;
}
else if (from_.Count == 1)
{
root = transformOneFrom(from_[0]);
}
else
{
root = new LogicResult(selection_);
}
return(root);
}