/// <summary> /// This is called when two plans are merged together to set /// up the left and right join information for the new plan. /// </summary> /// <param name="left"></param> /// <param name="right"></param> /// <remarks> /// This sets the left join info from the left plan and the /// right join info from the right plan. /// </remarks> public void SetJoinInfoMergedBetween(PlanTableSource left, PlanTableSource right) { if (left.RightPlan != right) { if (left.RightPlan != null) { SetRightJoinInfo(left.RightPlan, left.RightJoinType, left.RightOnExpression); RightPlan.LeftPlan = this; } if (right.LeftPlan != null) { SetLeftJoinInfo(right.LeftPlan, right.LeftJoinType, right.LeftOnExpression); LeftPlan.RightPlan = this; } } if (left.LeftPlan != right) { if (LeftPlan == null && left.LeftPlan != null) { SetLeftJoinInfo(left.LeftPlan, left.LeftJoinType, left.LeftOnExpression); LeftPlan.RightPlan = this; } if (RightPlan == null && right.RightPlan != null) { SetRightJoinInfo(right.RightPlan, right.RightJoinType, right.RightOnExpression); RightPlan.LeftPlan = this; } } }
/// <summary> /// Naturally joins two <see cref="PlanTableSource"/> objects in this planner. /// </summary> /// <param name="plan1"></param> /// <param name="plan2"></param> /// <remarks> /// When this method returns the actual plans will be joined together. This method /// modifies <see cref="tableList"/>. /// </remarks> /// <returns></returns> private PlanTableSource NaturallyJoinPlans(PlanTableSource plan1, PlanTableSource plan2) { JoinType joinType; Expression onExpr; PlanTableSource 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 Exception( "Assertion failed - plans can not be naturally join because " + "the left/right join plans clash."); } // Else we must assume a non-dependant join (not an outer join). // Perform a natural join IQueryPlanNode node1 = new NaturalJoinNode(plan1.Plan, plan2.Plan); return MergeTables(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 Exception("Join type (" + joinType + ") is not supported."); } // Make a Planner object for joining these plans. var planner = new QueryTableSetPlanner(); planner.AddPlanTableSource(leftPlan.Copy()); planner.AddPlanTableSource(rightPlan.Copy()); // Evaluate the on expression IQueryPlanNode 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 MergeTables(plan1, plan2, node); }
/// <summary> /// Joins two tables when a plan is generated for joining the two tables. /// </summary> /// <param name="left"></param> /// <param name="right"></param> /// <param name="mergePlan"></param> /// <returns></returns> private PlanTableSource MergeTables(PlanTableSource left, PlanTableSource right, IQueryPlanNode mergePlan) { // Remove the sources from the table list. tableList.Remove(left); tableList.Remove(right); // Add the concatenation of the left and right tables. PlanTableSource cPlan = ConcatTableSources(left, right, mergePlan); cPlan.SetJoinInfoMergedBetween(left, right); cPlan.SetUpdated(); AddPlanTableSource(cPlan); // Return the name plan return cPlan; }
/// <summary> /// Returns the index of the given <see cref="PlanTableSource"/> in the /// table list. /// </summary> /// <param name="source"></param> /// <returns></returns> private int IndexOfPlanTableSource(PlanTableSource source) { int sz = tableList.Count; for (int i = 0; i < sz; ++i) { if (tableList[i] == source) return i; } return -1; }
/// <summary> /// Add a <see cref="PlanTableSource"/> to this planner. /// </summary> /// <param name="source"></param> private void AddPlanTableSource(PlanTableSource source) { tableList.Add(source); HasJoinOccured = true; }
/// <summary> /// Forms a new PlanTableSource that's the concatination of the given two /// <see cref="PlanTableSource"/> objects. /// </summary> /// <param name="left"></param> /// <param name="right"></param> /// <param name="plan"></param> /// <returns></returns> private static PlanTableSource ConcatTableSources(PlanTableSource left, PlanTableSource right, IQueryPlanNode plan) { // Merge the variable list var newVarList = new ObjectName[left.VariableNames.Length + right.VariableNames.Length]; int i = 0; foreach (ObjectName v in left.VariableNames) { newVarList[i] = v; ++i; } foreach (ObjectName v in right.VariableNames) { newVarList[i] = v; ++i; } // Merge the unique table names list var newUniqueList = new string[left.UniqueNames.Length + right.UniqueNames.Length]; i = 0; foreach (string uniqueName in left.UniqueNames) { newUniqueList[i] = uniqueName; ++i; } foreach (string uniqueName in right.UniqueNames) { newUniqueList[i] = uniqueName; ++i; } // Return the new table source plan. return new PlanTableSource(plan, newVarList, newUniqueList); }
/// <summary> /// Returns true if it is possible to naturally join the two plans. /// </summary> /// <param name="plan1"></param> /// <param name="plan2"></param> /// <remarks> /// Two plans can be joined under the following sitations: /// <list type="number"> /// <item>The left or right plan of the first source points /// to the second source.</item> /// <item>Either one has no left plan and the other has no /// right plan, or one has no right plan and the other has /// no left plan.</item> /// </list> /// </remarks> /// <returns></returns> private static int CanPlansBeNaturallyJoined(PlanTableSource plan1, PlanTableSource 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; }
/// <summary> /// Adds a single var plan to the given list. /// </summary> /// <param name="list"></param> /// <param name="table"></param> /// <param name="variable"></param> /// <param name="singleVar"></param> /// <param name="expParts"></param> /// <param name="op"></param> private static void AddSingleVarPlanTo(IList<SingleVarPlan> list, PlanTableSource table, ObjectName variable, ObjectName singleVar, Expression[] expParts, Operator op) { var exp = Expression.Binary(expParts[0], op.AsExpressionType(), expParts[1]); // Is this source in the list already? foreach (SingleVarPlan plan1 in list) { if (plan1.TableSource == table && (variable == null || plan1.Variable.Equals(variable))) { // Append to end of current expression plan1.Variable = variable; plan1.Expression = Expression.And(plan1.Expression, exp); return; } } // Didn't find so make a new entry in the list. SingleVarPlan plan = new SingleVarPlan(); plan.TableSource = table; plan.Variable = variable; plan.SingleVariable = singleVar; plan.Expression = exp; list.Add(plan); return; }
/// <summary> /// Sets the left join information for this plan. /// </summary> /// <param name="left"></param> /// <param name="joinType"></param> /// <param name="onExpression"></param> public void SetLeftJoinInfo(PlanTableSource left, JoinType joinType, Expression onExpression) { LeftPlan = left; LeftJoinType = joinType; LeftOnExpression = onExpression; }
/// <summary> /// Sets the right join information for this plan. /// </summary> /// <param name="right"></param> /// <param name="joinType"></param> /// <param name="onExpression"></param> public void SetRightJoinInfo(PlanTableSource right, JoinType joinType, Expression onExpression) { RightPlan = right; RightJoinType = joinType; RightOnExpression = onExpression; }
/// <summary> /// Forms a command plan <see cref="IQueryPlanNode"/> from the given /// <see cref="TableSelectExpression"/> and <see cref="TableExpressionFromSet"/>. /// </summary> /// <param name="db"></param> /// <param name="expression">Describes the <i>SELECT</i> command /// (or sub-command).</param> /// <param name="fromSet">Used to resolve expression references.</param> /// <param name="orderBy">A list of <see cref="ByColumn"/> objects /// that represent an optional <i>ORDER BY</i> clause. If this is null /// or the list is empty, no ordering is done.</param> /// <returns></returns> public static IQueryPlanNode FormQueryPlan(IDatabaseConnection db, TableSelectExpression expression, TableExpressionFromSet fromSet, IList <ByColumn> orderBy) { IQueryContext context = new DatabaseQueryContext(db); // ----- Resolve the SELECT list // If there are 0 columns selected, then we assume the result should // show all of the columns in the result. bool doSubsetColumn = (expression.Columns.Count != 0); // What we are selecting var columnSet = BuildColumnSet(expression, fromSet); // Prepare the column_set, columnSet.Prepare(context); ResolveOrderByRefs(columnSet, orderBy); // ----- // Set up plans for each table in the from clause of the command. For // sub-queries, we recurse. var tablePlanner = SetupPlanners(db, fromSet); // ----- // The WHERE and HAVING clauses FilterExpression whereClause = expression.Where; FilterExpression havingClause = expression.Having; whereClause = PrepareJoins(tablePlanner, expression, fromSet, whereClause); // Prepare the WHERE and HAVING clause, qualifies all variables and // prepares sub-queries. whereClause = PrepareSearchExpression(db, fromSet, whereClause); havingClause = PrepareSearchExpression(db, fromSet, havingClause); // Any extra Aggregate functions that are part of the HAVING clause that // we need to add. This is a list of a name followed by the expression // that contains the aggregate function. var extraAggregateFunctions = new List <Expression>(); if (havingClause != null && havingClause.Expression != null) { Expression newHavingClause = FilterHavingClause(havingClause.Expression, extraAggregateFunctions, context); havingClause = new FilterExpression(newHavingClause); } // Any GROUP BY functions, ObjectName[] groupByList; IList <Expression> groupByFunctions; var gsz = ResolveGroupBy(expression, fromSet, context, out groupByList, out groupByFunctions); // Resolve GROUP MAX variable to a reference in this from set ObjectName groupmaxColumn = ResolveGroupMax(expression, fromSet); // ----- // Now all the variables should be resolved and correlated variables set // up as appropriate. // If nothing in the FROM clause then simply evaluate the result of the // select if (fromSet.SetCount == 0) { return(EvaluateSingle(columnSet)); } // Plan the where clause. The returned node is the plan to evaluate the // WHERE clause. IQueryPlanNode node = tablePlanner.PlanSearchExpression(whereClause); Expression[] defFunList; string[] defFunNames; var fsz = MakeupFunctions(columnSet, extraAggregateFunctions, out defFunList, out defFunNames); node = PlanGroup(node, columnSet, groupmaxColumn, gsz, groupByList, groupByFunctions, fsz, defFunNames, defFunList); // The result column list List <SelectColumn> selectColumns = columnSet.SelectedColumns; int sz = selectColumns.Count; // Evaluate the having clause if necessary if (havingClause != null && havingClause.Expression != null) { // Before we evaluate the having expression we must substitute all the // aliased variables. Expression havingExpr = havingClause.Expression; havingExpr = SubstituteAliasedVariables(havingExpr, selectColumns); havingClause = new FilterExpression(havingExpr); PlanTableSource source = tablePlanner.SingleTableSource; source.UpdatePlan(node); node = tablePlanner.PlanSearchExpression(havingClause); } // Do we have a composite select expression to process? IQueryPlanNode rightComposite = null; if (expression.NextComposite != null) { TableSelectExpression compositeExpr = expression.NextComposite; // Generate the TableExpressionFromSet hierarchy for the expression, TableExpressionFromSet compositeFromSet = GenerateFromSet(compositeExpr, db); // Form the right plan rightComposite = FormQueryPlan(db, compositeExpr, compositeFromSet, null); } // Do we do a final subset column? ObjectName[] aliases = null; if (doSubsetColumn) { // Make up the lists ObjectName[] subsetVars = new ObjectName[sz]; aliases = new ObjectName[sz]; for (int i = 0; i < sz; ++i) { SelectColumn scol = selectColumns[i]; subsetVars[i] = scol.InternalName.Clone(); aliases[i] = scol.Alias.Clone(); } // If we are distinct then add the DistinctNode here if (expression.Distinct) { node = new DistinctNode(node, subsetVars); } // Process the ORDER BY? // Note that the ORDER BY has to occur before the subset call, but // after the distinct because distinct can affect the ordering of the // result. if (rightComposite == null && orderBy != null) { node = PlanForOrderBy(node, orderBy, fromSet, selectColumns); } // Rename the columns as specified in the SELECT node = new SubsetNode(node, subsetVars, aliases); } else { // Process the ORDER BY? if (rightComposite == null && orderBy != null) { node = PlanForOrderBy(node, orderBy, fromSet, selectColumns); } } // Do we have a composite to merge in? if (rightComposite != null) { // For the composite node = new CompositeNode(node, rightComposite, expression.CompositeFunction, expression.IsCompositeAll); // Final order by? if (orderBy != null) { node = PlanForOrderBy(node, orderBy, fromSet, selectColumns); } // Ensure a final subset node if (!(node is SubsetNode) && aliases != null) { node = new SubsetNode(node, aliases, aliases); } } return(node); }