private static int AssertBeNaturalJoin(TablePlan plan1, TablePlan plan2)
{
if (plan1.LeftPlan == plan2 || plan1.RightPlan == plan2)
{
return(0);
}
if (plan1.LeftPlan != null && plan2.LeftPlan != null)
{
// This is a left clash
return(2);
}
if (plan1.RightPlan != null && plan2.RightPlan != null)
{
// This is a right clash
return(1);
}
if ((plan1.LeftPlan == null && plan2.RightPlan == null) ||
(plan1.RightPlan == null && plan2.LeftPlan == null))
{
// This means a merge between the plans is fine
return(0);
}
// Must be a left and right clash
return(2);
}
public void MergeJoin(TablePlan left, TablePlan right)
{
if (left.RightPlan != right)
{
if (left.RightPlan != null)
{
RightJoin(left.RightPlan, left.RightJoinType, left.RightOnExpression);
RightPlan.LeftPlan = this;
}
if (right.LeftPlan != null)
{
LeftJoin(right.LeftPlan, right.LeftJoinType, right.LeftOnExpression);
LeftPlan.RightPlan = this;
}
}
if (left.LeftPlan != right)
{
if (LeftPlan == null && left.LeftPlan != null)
{
LeftJoin(left.LeftPlan, left.LeftJoinType, left.LeftOnExpression);
LeftPlan.RightPlan = this;
}
if (RightPlan == null && right.RightPlan != null)
{
RightJoin(right.RightPlan, right.RightJoinType, right.RightOnExpression);
RightPlan.LeftPlan = this;
}
}
}
public SingleColumnPlan(TablePlan tablePlan, ObjectName columnName, ObjectName uniqueName, SqlExpression expression)
{
TablePlan = tablePlan;
ColumnName = columnName;
UniqueName = uniqueName;
Expression = expression;
}
private int IndexOfPlan(TablePlan plan)
{
int sz = tablePlans.Count;
for (int i = 0; i < sz; ++i)
{
if (tablePlans[i] == plan)
{
return(i);
}
}
return(-1);
}
private TablePlan MergePlans(TablePlan left, TablePlan right, IQueryPlanNode mergePlan)
{
// Remove the sources from the table list.
tablePlans.Remove(left);
tablePlans.Remove(right);
// Add the concatenation of the left and right tables.
var newPlan = ConcatPlans(left, right, mergePlan);
newPlan.MergeJoin(left, right);
newPlan.SetUpdated();
AddPlan(newPlan);
return(newPlan);
}
private static TablePlan ConcatPlans(TablePlan left, TablePlan right, IQueryPlanNode plan)
{
// Merge the variable list
var newVarList = new ObjectName[left.ColumnNames.Length + right.ColumnNames.Length];
Array.Copy(left.ColumnNames, 0, newVarList, 0, left.ColumnNames.Length);
Array.Copy(right.ColumnNames, 0, newVarList, left.ColumnNames.Length, right.ColumnNames.Length);
// Merge the unique table names list
var newUniqueList = new string[left.UniqueNames.Length + right.UniqueNames.Length];
Array.Copy(left.UniqueNames, 0, newUniqueList, 0, left.UniqueNames.Length);
Array.Copy(right.UniqueNames, 0, newUniqueList, left.UniqueNames.Length, right.UniqueNames.Length);
// Return the new table source plan.
return(new TablePlan(plan, newVarList, newUniqueList));
}
private static void AddSingleColumnPlan(IList <SingleColumnPlan> list, TablePlan table, ObjectName columnName, ObjectName uniqueName, SqlExpression[] expParts, SqlExpressionType op)
{
var exp = SqlExpression.Binary(expParts[0], op, expParts[1]);
// Is this source in the list already?
foreach (var existingPlan in list)
{
if (existingPlan.TablePlan == table &&
(columnName == null || existingPlan.ColumnName.Equals(columnName)))
{
// Append to end of current expression
existingPlan.SetSource(columnName, SqlExpression.And(existingPlan.Expression, exp));
return;
}
}
// Didn't find so make a new entry in the list.
list.Add(new SingleColumnPlan(table, columnName, uniqueName, exp));
}
private TablePlan FindCommonPlan(IList <ObjectName> columnNames)
{
if (columnNames.Count == 0)
{
return(null);
}
TablePlan pivotPlan = null;
foreach (var columnName in columnNames)
{
var plan = FindPlan(columnName);
if (pivotPlan == null)
{
pivotPlan = plan;
}
else if (plan != pivotPlan)
{
return(null);
}
}
return(pivotPlan);
}
private int IndexOfPlan(TablePlan plan)
{
int sz = tablePlans.Count;
for (int i = 0; i < sz; ++i) {
if (tablePlans[i] == plan)
return i;
}
return -1;
}
private static TablePlan ConcatPlans(TablePlan left, TablePlan right, IQueryPlanNode plan)
{
// Merge the variable list
var newVarList = new ObjectName[left.ColumnNames.Length + right.ColumnNames.Length];
Array.Copy(left.ColumnNames, 0, newVarList, 0, left.ColumnNames.Length);
Array.Copy(right.ColumnNames, 0, newVarList, left.ColumnNames.Length - 1, right.ColumnNames.Length);
// Merge the unique table names list
var newUniqueList = new string[left.UniqueNames.Length + right.UniqueNames.Length];
Array.Copy(left.UniqueNames, 0, newUniqueList, 0, left.UniqueNames.Length);
Array.Copy(right.UniqueNames, 0, newUniqueList, left.UniqueNames.Length - 1, right.UniqueNames.Length);
// Return the new table source plan.
return new TablePlan(plan, newVarList, newUniqueList);
}
public SingleColumnPlan(TablePlan tablePlan, ObjectName columnName, ObjectName uniqueName, SqlExpression expression)
{
TablePlan = tablePlan;
ColumnName = columnName;
UniqueName = uniqueName;
Expression = expression;
}
private static int AssertBeNaturalJoin(TablePlan plan1, TablePlan plan2)
{
if (plan1.LeftPlan == plan2 || plan1.RightPlan == plan2)
return 0;
if (plan1.LeftPlan != null && plan2.LeftPlan != null)
// This is a left clash
return 2;
if (plan1.RightPlan != null && plan2.RightPlan != null)
// This is a right clash
return 1;
if ((plan1.LeftPlan == null && plan2.RightPlan == null) ||
(plan1.RightPlan == null && plan2.LeftPlan == null))
// This means a merge between the plans is fine
return 0;
// Must be a left and right clash
return 2;
}
public void RightJoin(TablePlan right, JoinType joinType, SqlExpression onExpression)
{
RightPlan = right;
RightJoinType = joinType;
RightOnExpression = onExpression;
}
public void MergeJoin(TablePlan left, TablePlan right)
{
if (left.RightPlan != right) {
if (left.RightPlan != null) {
RightJoin(left.RightPlan, left.RightJoinType, left.RightOnExpression);
RightPlan.LeftPlan = this;
}
if (right.LeftPlan != null) {
LeftJoin(right.LeftPlan, right.LeftJoinType, right.LeftOnExpression);
LeftPlan.RightPlan = this;
}
}
if (left.LeftPlan != right) {
if (LeftPlan == null && left.LeftPlan != null) {
LeftJoin(left.LeftPlan, left.LeftJoinType, left.LeftOnExpression);
LeftPlan.RightPlan = this;
}
if (RightPlan == null && right.RightPlan != null) {
RightJoin(right.RightPlan, right.RightJoinType, right.RightOnExpression);
RightPlan.LeftPlan = this;
}
}
}
public void LeftJoin(TablePlan left, JoinType joinType, SqlExpression onExpression)
{
LeftPlan = left;
LeftJoinType = joinType;
LeftOnExpression = onExpression;
}
public void RightJoin(TablePlan right, JoinType joinType, SqlExpression onExpression)
{
RightPlan = right;
RightJoinType = joinType;
RightOnExpression = onExpression;
}
public void AddPlan(TablePlan tablePlan)
{
tablePlans.Add(tablePlan);
HasJoin = true;
}
private TablePlan MergePlans(TablePlan left, TablePlan right, IQueryPlanNode mergePlan)
{
// Remove the sources from the table list.
tablePlans.Remove(left);
tablePlans.Remove(right);
// Add the concatenation of the left and right tables.
var newPlan = ConcatPlans(left, right, mergePlan);
newPlan.MergeJoin(left, right);
newPlan.SetUpdated();
AddPlan(newPlan);
return newPlan;
}
private void EvaluateSubLogic(List <SqlBinaryExpression> list, List <ExpressionPlan> plans)
{
foreach (var expression in list)
{
var orExprs = new[] { expression.Left, expression.Right };
// An optimizations here;
// If all the expressions we are ORing together are in the same table
// then we should execute them before the joins, otherwise they
// should go after the joins.
// The reason for this is because if we can lesson the amount of work a
// join has to do then we should. The actual time it takes to perform
// an OR search shouldn't change if it is before or after the joins.
TablePlan common = null;
foreach (var orExpr in orExprs)
{
var vars = orExpr.DiscoverReferences().ToArray();
bool breakRule = false;
// If there are no variables then don't bother with this expression
if (vars.Any())
{
// Find the common table source (if any)
var ts = FindCommonPlan(vars);
bool orAfterJoins = false;
if (ts == null)
{
// No common table, so OR after the joins
orAfterJoins = true;
}
else if (common == null)
{
common = ts;
}
else if (common != ts)
{
// No common table with the vars in this OR list so do this OR
// after the joins.
orAfterJoins = true;
}
if (orAfterJoins)
{
plans.Add(new SubLogicPlan(this, expression, 0.70f));
// Continue to the next logic expression
breakRule = true;
}
}
if (!breakRule)
{
// Either we found a common table or there are no variables in the OR.
// Either way we should evaluate this after the join.
plans.Add(new SubLogicPlan(this, expression, 0.58f));
}
}
}
}
private TablePlan NaturallyJoinPlans(TablePlan plan1, TablePlan plan2)
{
JoinType joinType;
SqlExpression onExpr;
TablePlan leftPlan, rightPlan;
// Are the plans linked by common join information?
if (plan1.RightPlan == plan2)
{
joinType = plan1.RightJoinType;
onExpr = plan1.RightOnExpression;
leftPlan = plan1;
rightPlan = plan2;
}
else if (plan1.LeftPlan == plan2)
{
joinType = plan1.LeftJoinType;
onExpr = plan1.LeftOnExpression;
leftPlan = plan2;
rightPlan = plan1;
}
else
{
// Assertion - make sure no join clashes!
if ((plan1.LeftPlan != null && plan2.LeftPlan != null) ||
(plan1.RightPlan != null && plan2.RightPlan != null))
{
throw new InvalidOperationException("Plans can not be naturally join because " +
"the left/right join plans clash.");
}
// Else we must assume a non-dependent join (not an outer join).
// Perform a natural join
var node1 = new NaturalJoinNode(plan1.Plan, plan2.Plan);
return(MergePlans(plan1, plan2, node1));
}
// This means plan1 and plan2 are linked by a common join and ON
// expression which we evaluate now.
bool outerJoin;
if (joinType == JoinType.Left)
{
// Mark the left plan
leftPlan.UpdatePlan(new MarkerNode(leftPlan.Plan, "OUTER_JOIN"));
outerJoin = true;
}
else if (joinType == JoinType.Right)
{
// Mark the right plan
rightPlan.UpdatePlan(new MarkerNode(rightPlan.Plan, "OUTER_JOIN"));
outerJoin = true;
}
else if (joinType == JoinType.Inner)
{
// Inner join with ON expression
outerJoin = false;
}
else
{
throw new InvalidOperationException(String.Format("Join type ({0}) is not supported.", joinType));
}
// Make a Planner object for joining these plans.
var planner = new QueryTablePlanner();
planner.AddPlan(leftPlan.Clone());
planner.AddPlan(rightPlan.Clone());
// Evaluate the on expression
var node = planner.LogicalEvaluate(onExpr);
// If outer join add the left outer join node
if (outerJoin)
{
node = new LeftOuterJoinNode(node, "OUTER_JOIN");
}
// And merge the plans in this set with the new node.
return(MergePlans(plan1, plan2, node));
}
private TablePlan NaturallyJoinPlans(TablePlan plan1, TablePlan plan2)
{
JoinType joinType;
SqlExpression onExpr;
TablePlan leftPlan, rightPlan;
// Are the plans linked by common join information?
if (plan1.RightPlan == plan2) {
joinType = plan1.RightJoinType;
onExpr = plan1.RightOnExpression;
leftPlan = plan1;
rightPlan = plan2;
} else if (plan1.LeftPlan == plan2) {
joinType = plan1.LeftJoinType;
onExpr = plan1.LeftOnExpression;
leftPlan = plan2;
rightPlan = plan1;
} else {
// Assertion - make sure no join clashes!
if ((plan1.LeftPlan != null && plan2.LeftPlan != null) ||
(plan1.RightPlan != null && plan2.RightPlan != null)) {
throw new InvalidOperationException("Plans can not be naturally join because " +
"the left/right join plans clash.");
}
// Else we must assume a non-dependent join (not an outer join).
// Perform a natural join
IQueryPlanNode node1 = new NaturalJoinNode(plan1.Plan, plan2.Plan);
return MergePlans(plan1, plan2, node1);
}
// This means plan1 and plan2 are linked by a common join and ON
// expression which we evaluate now.
bool outerJoin;
if (joinType == JoinType.Left) {
// Mark the left plan
leftPlan.UpdatePlan(new MarkerNode(leftPlan.Plan, "OUTER_JOIN"));
outerJoin = true;
} else if (joinType == JoinType.Right) {
// Mark the right plan
rightPlan.UpdatePlan(new MarkerNode(rightPlan.Plan, "OUTER_JOIN"));
outerJoin = true;
} else if (joinType == JoinType.Inner) {
// Inner join with ON expression
outerJoin = false;
} else {
throw new InvalidOperationException(String.Format("Join type ({0}) is not supported.", joinType));
}
// Make a Planner object for joining these plans.
var planner = new QueryTablePlanner();
planner.AddPlan(leftPlan.Clone());
planner.AddPlan(rightPlan.Clone());
// Evaluate the on expression
var node = planner.LogicalEvaluate(onExpr);
// If outer join add the left outer join node
if (outerJoin)
node = new LeftOuterJoinNode(node, "OUTER_JOIN");
// And merge the plans in this set with the new node.
return MergePlans(plan1, plan2, node);
}
public void AddPlan(TablePlan tablePlan)
{
tablePlans.Add(tablePlan);
HasJoin = true;
}
private static void AddSingleColumnPlan(IList<SingleColumnPlan> list, TablePlan table, ObjectName columnName, ObjectName uniqueName, SqlExpression[] expParts, SqlExpressionType op)
{
var exp = SqlExpression.Binary(expParts[0], op, expParts[1]);
// Is this source in the list already?
foreach (var existingPlan in list) {
if (existingPlan.TablePlan == table &&
(columnName == null || existingPlan.ColumnName.Equals(columnName))) {
// Append to end of current expression
existingPlan.SetSource(columnName, SqlExpression.And(existingPlan.Expression, exp));
return;
}
}
// Didn't find so make a new entry in the list.
list.Add(new SingleColumnPlan(table, columnName, uniqueName, exp));
}
public void LeftJoin(TablePlan left, JoinType joinType, SqlExpression onExpression)
{
LeftPlan = left;
LeftJoinType = joinType;
LeftOnExpression = onExpression;
}
