public ITable EvaluateAggregate(QueryProcessor processor, bool distinct, ITable group, Expression[] args) { if (!function.IsAggregate) throw new InvalidOperationException("The function is not an aggregate."); try { // Execute it object[] funArgs; if (invokeType == 6) { funArgs = new object[] { function.Name, processor, distinct, group, args }; } // The QueryProcessor, Expression[] construct else if (invokeType == 1) { funArgs = new object[] { processor, distinct, group, args }; } else { throw new ApplicationException("Unknown invoke type"); } return (ITable)method.Invoke(null, funArgs); } catch (MethodAccessException e) { throw new ApplicationException(e.Message, e); } catch (TargetInvocationException e) { throw new ApplicationException(e.InnerException.Message, e.InnerException); } }
public static ITable If(QueryProcessor processor, Expression[] args) { SqlObject[] conditional = QueryProcessor.Result(processor.Execute(args[0])); // If it evaluated to true, bool? b = conditional[0].Value.ToBoolean(); return b != null && b == true ? processor.Execute(args[1]) : processor.Execute(args[2]); }
public static ITable Length(QueryProcessor processor, Expression[] args) { if (args.Length != 1) throw new ArgumentException("The function LENGTH accepts only 1 argument."); Expression arg = args[0]; SqlObject resultLength; SqlObject obj = QueryProcessor.Result(processor.Execute(arg))[0]; if (obj.IsNull) { resultLength = SqlObject.MakeNull(SqlType.Numeric); } else { int length; SqlType obType = obj.Type; SqlValue obValue = obj.Value; // If it's a string, if (obType.IsString) { length = obValue.ToString().Length; } // If it's a binary, else if (obType.IsBinary) { length = obValue.Length - 1; } // Otherwise, return null, else { length = -1; } resultLength = length == -1 ? SqlObject.MakeNull(SqlType.Numeric) : new SqlObject((long) length); } return QueryProcessor.ResultTable(resultLength); }
public FilterExpression(string name, Expression child, Expression filter) : base(ExpressionType.Filter) { SetArgument("name", name); SetArgument("child", child); SetArgument("filter", filter); }
public static ITable GroupConcat(QueryProcessor processor, bool distinct, ITable group, Expression[] args) { // The output string StringBuilder return_string = new StringBuilder(); // Find the distinct subset of group if (distinct) group = processor.DistinctSubset(group, args); // Push the group table onto the processor stack processor.PushTable(group); // Iterator over the group IRowCursor i = group.GetRowCursor(); bool first = true; while (i.MoveNext()) { RowId rowid = i.Current; processor.UpdateTableRow(rowid); foreach (Expression op in args) { ITable val = processor.Execute(op); SqlObject ob = QueryProcessor.Result(val)[0]; if (!ob.IsNull) { if (!first) { return_string.Append(", "); } return_string.Append(SqlValue.FromObject(ob.Value).ToString()); first = false; } } } // Pop the table and return the result processor.PopTable(); return QueryProcessor.ResultTable(new SqlObject(return_string.ToString())); }
public AliasTableNameExpression(Expression child, TableName alias, SqlType returnType) : base(ExpressionType.AliasTableName) { SetArgument("child", child); SetArgument("alias", alias); if (returnType != null) SetArgument("return_type", returnType); }
public static ITable Avg(QueryProcessor processor, bool distinct, ITable group, Expression[] args) { // Aggregate function only can have 1 argument if (args.Length > 1) throw new ArgumentException("Only one argument permitted for SUM function."); return ProcessAggregate(processor, distinct, group, args, new AvgAggregateInspector()); }
private static string GetActionString(Expression expression) { if (expression is FetchStaticExpression) { SqlObject val = (SqlObject)expression.GetArgument("static"); return val.ToString(); } throw new ApplicationException("Expecting static expression"); }
public JoinExpression(Expression left, Expression right, JoinType type, Expression filter) : this() { SetArgument("left", left); SetArgument("right", right); SetArgument("type", type); if (filter != null) SetArgument("filter", filter); }
public UpdatableResultSetView(SystemTransaction transaction, IMutableTable backedTable, Expression[] project, IRowCursor select) { this.transaction = transaction; this.backedTable = backedTable; this.project = project; originalSelect = select; currentSelect = select; this.select = null; }
public static Expression WalkGraph(Expression op, IGraphInspector inspector) { // The pre walk call op = inspector.OnBeforeWalk(op); ExpressionType type = op.Type; switch (type) { case ExpressionType.Function: InspectParamList(inspector, op, "param_count", "arg"); break; case ExpressionType.Select: InspectParam(inspector, op, "join"); InspectParam(inspector, op, "filter"); InspectParam(inspector, op, "havingfilter"); InspectParamList(inspector, op, "out_count", "out"); InspectParamList(inspector, op, "groupby_count", "groupby"); InspectParamList(inspector, op, "orderby_count", "orderby"); break; case ExpressionType.Join: InspectParam(inspector, op, "left"); InspectParam(inspector, op, "right"); InspectParam(inspector, op, "filter"); break; // Single passthrough case ExpressionType.AliasTableName: case ExpressionType.AliasVariableName: InspectParam(inspector, op, "child"); break; case ExpressionType.Filter: InspectParam(inspector, op, "child"); InspectParam(inspector, op, "filter"); break; // Terminators case ExpressionType.FetchVariable: case ExpressionType.FetchStatic: case ExpressionType.FetchParameter: case ExpressionType.FetchGlob: case ExpressionType.FetchTable: break; default: throw new ArgumentException("Unknown operation " + op.Type); } // The post expression call op = inspector.OnAfterWalk(op); // Return the operation return op; }
public ITable Evaluate(QueryProcessor processor, Expression[] args) { // 'CAST' is a special case, if (function.Name.Equals("@cast")) { // Get the value to cast, and the type to cast it to, SqlObject val = QueryProcessor.Result(processor.Execute(args[0]))[0]; SqlObject castType = QueryProcessor.Result(processor.Execute(args[1]))[0]; string castTypeString = castType.Value.ToString(); SqlType type = SqlType.Parse(castTypeString); // Do the cast, SqlObject result = val.CastTo(type); // And return the result, return QueryProcessor.ResultTable(result); } if (function.IsAggregate) throw new InvalidOperationException("The function is aggregate."); try { // Execute it if (invokeType == 6) { object[] funArgs = { function.Name, processor, args }; return (ITable)method.Invoke(null, funArgs); } // The QueryProcessor, Expression[] construct if (invokeType == 1) { object[] funArgs = { processor, args }; return (ITable)method.Invoke(null, funArgs); } // The SqlObject construct if (invokeType == 2) { int sz = args.Length; // Resolve the arguments into TypedValues SqlObject[] obs = new SqlObject[sz]; for (int i = 0; i < sz; ++i) { obs[i] = QueryProcessor.Result(processor.Execute(args[i]))[0]; } // Set up the arguments and invoke the method object[] funArgs = { obs }; SqlObject result = (SqlObject)method.Invoke(null, funArgs); // Wrap on a FunctionTable and return return QueryProcessor.ResultTable(result); } throw new ApplicationException("Unknown invoke type"); } catch (MethodAccessException e) { throw new ApplicationException(e.Message, e); } catch (TargetInvocationException e) { throw new ApplicationException(e.InnerException.Message, e.InnerException); } }
private static void CostAggregateFilterOp(Expression child, FilterExpression expression) { // The child cost values double childRows = child.CostRows; double childTime = child.CostTime; // TODO: We should check for full range aggregate, in which case we // know there will only be 1 row result. // Set the costs expression.CostTime = childTime + (childRows * 1); expression.CostRows = childRows; }
public static ITable Least(QueryProcessor processor, Expression[] args) { SqlObject least = null; for (int i = 0; i < args.Length; ++i) { SqlObject ob = QueryProcessor.Result(processor.Execute(args[i]))[0]; if (ob.IsNull) return QueryProcessor.ResultTable(ob); if (least == null || SqlObject.Compare(ob, least) < 0) least = ob; } return QueryProcessor.ResultTable(least); }
public void Cost(Expression expression, double currentBestTime, int[] walkIteration) { // If this already has costing information, return if (expression.IsCostSet) return; ++walkIteration[0]; if (expression is FilterExpression) { // Cost the child Expression childExp = ((FilterExpression)expression).Child; Cost(childExp, currentBestTime, walkIteration); if (!childExp.IsCostSet || IsCostWorse(currentBestTime, childExp)) { return; } // Cost the filter operation CostFilterExpression(childExp, expression); } else if (expression is JoinExpression) { JoinExpression joinExp = (JoinExpression) expression; // Cost the left and right operations Expression left = joinExp.Left; Expression right = joinExp.Right; Cost(left, currentBestTime, walkIteration); if (!left.IsCostSet || IsCostWorse(currentBestTime, left)) return; Cost(right, currentBestTime, walkIteration); if (!right.IsCostSet || IsCostWorse(currentBestTime, right)) return; // Cost the join operation CostJoinExpression(left, right, joinExp); } else if (expression is AliasTableNameExpression) { // Fetch the table, apply the alias, and update the cost information. // The cost in time is 0 for a fetch operation because no scan operations // are necessary. ITable table = ExecuteExpression(expression); expression.CostTime = 0; expression.CostRows = table.RowCount; } else if (expression is FunctionExpression) { // Function should already be costed return; } else { throw new ApplicationException("Unrecognized operation type"); } }
public void AddColumn(string label, SqlType type, Expression expr) { OutputExpression outExpr = new OutputExpression(); outExpr.label = label; outExpr.type = type; outExpr.expression = expr; // If the expression is a 'FETCHVAR' type then we pass it through the // blob accessor methods in hopes of not having to materialize large // objects. if (expr is FetchVariableExpression) outExpr.var = ((FetchVariableExpression)expr).Variable; outputExps.Add(outExpr); }
private static bool IsDeferrable(Expression op) { if (op is FetchStaticExpression) { SqlObject val = (SqlObject)op.GetArgument("static"); string str = val.ToString(); if (str.Equals("deferrable")) return true; if (str.Equals("not deferrable")) return false; throw new ApplicationException("Unexpected deferrability type '" + str + "'"); } throw new ApplicationException("Expecting static expression"); }
private static bool IsDeferred(Expression expression) { if (expression is FetchStaticExpression) { SqlObject val = (SqlObject)expression.GetArgument("static"); string str = val.ToString(); if (str.Equals("initially deferred")) return true; if (str.Equals("initially immediate")) return false; throw new ApplicationException("Unexpected initial check type '" + str + "'"); } throw new ApplicationException("Expecting static expression"); }
public static ITable Count(QueryProcessor processor, bool distinct, ITable group, Expression[] args) { // Only 1 argument allowed if (args.Length > 1) throw new ArgumentException("Only one argument permitted for COUNT function."); // If the parameter is a function operation with name "star" then this is // a simple group size result Expression arg = args[0]; if (arg.Type == ExpressionType.Function && arg.GetArgument("name").Equals("star")) { return QueryProcessor.ResultTable(SqlObject.CastTo(group.RowCount, SqlType.Numeric)); } // Otherwise, if this is a distinct, if (distinct) { group = processor.DistinctSubset(group, args); // The above process removes null values so we return the count, return QueryProcessor.ResultTable(SqlObject.CastTo(group.RowCount, SqlType.Numeric)); } // Otherwise, we need to iterate through a count all none null values, return ProcessAggregate(processor, false, group, args, new CountAggregateInspector()); }
public void ClearGraph(Expression expression) { // The graph should only have 'FETCHTABLE', 'JOIN', 'FILTER' and 'FUNCTION' // operations in the graph. The FUNCTION expressions are themselves other // costed operation graphs. if (expression is FilterExpression) { // Recurse to the child ClearGraph(((FilterExpression)expression).Child); } else if (expression is JoinExpression) { JoinExpression joinExp = (JoinExpression) expression; // Recurse the left and right nodes ClearGraph(joinExp.Left); ClearGraph(joinExp.Right); } else if (expression is AliasTableNameExpression) { } else if (expression is FunctionExpression) { // This does not have costing information we should clear return; } else { throw new ApplicationException("Unknown operation type in graph."); } // Clear the costing information expression.UnsetCost(); }
public static Expression DereferenceExpression(Expression graph, Expression expression) { if (expression is FetchVariableExpression) { Variable v = Dereference(graph, ((FetchVariableExpression)expression).Variable); return v == null ? null : new FetchVariableExpression(v); } if (expression is FunctionExpression) { FunctionExpression functionExp = (FunctionExpression) expression; string functionName = functionExp.Name; Expression p0 = DereferenceExpression(graph, (Expression)functionExp.Parameters[0]); if (p0 == null) return null; Expression p1 = DereferenceExpression(graph, (Expression)functionExp.Parameters[1]); if (p1 == null) return null; return new FunctionExpression(functionName, new Expression[] { p0, p1 }); } throw new ApplicationException("Unexcepted operation"); }
public Expression[] MatchParameterExpressions(Expression[] expressions, string reference) { // Note that the return reference must be ancored from single elements at // the start or end, or must represent the entire param list. List<Expression> returnExps = new List<Expression>(expressions.Length); // If single element, int sz = parameters.Count; if (sz == 1) { FunctionParameter param = parameters[0]; string elem_ref = param.Reference; // Return all the expressions if refs match if (elem_ref.Equals(reference)) { foreach (Expression op in expressions) { returnExps.Add(op); } } } else { // Reference must anchor from the start and/or the end // Scan forward all the single elements, if they match the reference we add the // expression. int i = 0; for (; i < sz; i++) { FunctionParameter param = parameters[i]; string elem_ref = param.Reference; FunctionParameterMatch t = param.Match; if (t != FunctionParameterMatch.Exact) { break; } if (elem_ref.Equals(reference)) { returnExps.Add(expressions[i]); } } // If we didn't reach the end on the previous scan, do the same but // backwards from the end. if (i < sz) { i = sz - 1; for (; i >= 0; i--) { FunctionParameter param = parameters[i]; string elem_ref = param.Reference; FunctionParameterMatch t = param.Match; if (t != FunctionParameterMatch.Exact) { break; } if (elem_ref.Equals(reference)) { returnExps.Add(expressions[i]); } } } } return returnExps.ToArray(); }
private void CostFilterExpression(Expression child_op, Expression op) { FilterExpression filterExp = (FilterExpression)op; // The filter type, string filterType = filterExp.Name; if (filterType.Equals("single_filter")) { CostSingleFilterExpression(child_op, filterExp); } else if (filterType.Equals("sort")) { CostSortFilterExpression(child_op, filterExp); } else if (filterType.Equals("static_filter")) { CostStaticFilterExpression(child_op, filterExp); } else if (filterType.Equals("aggregate")) { CostAggregateFilterOp(child_op, filterExp); } else if (filterType.Equals("expression_table")) { CostNoCostFilterExpression(child_op, filterExp); } else { throw new ApplicationException("Unknown filter " + filterType); } }
public ITable Evaluate(QueryProcessor processor, Expression[] args) { if (evalContext == null) throw new InvalidOperationException("Evaluation context was not set"); return evalContext.Evaluate(processor, args); }
public ITable EvaluateAggregate(QueryProcessor processor, bool distinct, ITable group, Expression[] args) { if (evalContext == null) throw new InvalidOperationException("Evaluation context was not set"); return evalContext.EvaluateAggregate(processor, distinct, group, args); }
private static void CostStaticFilterExpression(Expression child, FilterExpression expression) { // The child cost values double childRows = child.CostRows; double childTime = child.CostTime; // The filter operation Expression filter = expression.Filter; double estimatedChildRows = childRows; // If it's a fetch static, if (filter is FetchStaticExpression) { SqlObject[] val = ((FetchStaticExpression)filter).Values; // If not true, the filter will filter all, bool isTrue = false; if (val[0].Type.IsBoolean) { bool? b = val[0].Value.ToBoolean(); if (b.HasValue && b.Value.Equals(true)) isTrue = true; } if (!isTrue) { estimatedChildRows = 0.0d; } } // Set the time cost expression.CostRows = estimatedChildRows; expression.CostTime = childTime; }
private static bool IsLocallyStatic(ITable domain_table, Expression op) { LocalStaticGraphInspector local_static_test = new LocalStaticGraphInspector(domain_table); QueryOptimizer.WalkGraph(op, local_static_test); return local_static_test.Result; }
private static void CostSortFilterExpression(Expression child, FilterExpression expression) { // The child cost values double childRows = child.CostRows; double childTime = child.CostTime; // If child has an index we can use for the sort or is already // sorted by the filter terms, we don't need to incur the cost of the // sort. string indexName; TableName indexTableName; // The filter operation Expression filter = expression.Filter; FunctionExpression functionExp = filter as FunctionExpression; // Filter must be a composite function if (functionExp == null || !functionExp.Name.Equals("composite")) throw new ApplicationException("Expected composite function."); // Get the terms, etc int paramCount = functionExp.Parameters.Count; int termCount = paramCount / 2; // If 1 sort term, if (termCount == 1) { Expression sortExp = (Expression)functionExp.Parameters[0]; // Get the index candidate indexName = sortExp.IndexCandidate; indexTableName = sortExp.IndexTableName; } else { // Multiple terms, // Get the index candidate if there is one indexName = filter.IndexCandidate; indexTableName = filter.IndexTableName; } bool indexLookup = false; // If we have an index candidate, if (indexName != null) { // Index found, // Is the child operation a table where the index is available? TableName indexedTable = FetchFirstIndexedTable(child); indexLookup = indexedTable != null && indexedTable.Equals(indexTableName); } // If no index candidate or index not available, check if the child // is already ordered by this composite, if (!indexLookup) indexLookup = GraphCollatedByComposite(child, filter); // Cost of index lookup if (indexLookup) { expression.CostTime = childTime + (BTreeLookupCost * 2.0d); } else { // Cost of sort operation with no index involved in the operation expression.CostTime = childTime + (childRows * BTreeLookupCost); } // Set the costs expression.CostRows = childRows; }
private static void CostNoCostFilterExpression(Expression child, FilterExpression expression) { // Set the costs expression.CostTime = child.CostTime; expression.CostRows = child.CostRows; }
private void CostSingleFilterExpression(Expression child, FilterExpression expression) { // The child cost values double childRows = child.CostRows; double childTime = child.CostTime; // The filter operation Expression filter = expression.Filter; // If the filter is a range_set function, and the child is a table // alias then we check for index candidates. string funType = (string) filter.GetArgument("name"); // We can work out an estimate of the time cost now, bool indexApplicable = false; string indexName = null; TableName tableName = null; Expression compositeIndexExp = null; // Fetch the first table to which index information is applicable TableName firstIndexedTable = FetchFirstIndexedTable(child); if (firstIndexedTable != null) { // Ok, child of filter is a fetch table, so look for clauses that we // can use an index for // Get the index candidate if (filter.Type == ExpressionType.Function) { Expression param0 = (Expression) filter.GetArgument("arg0"); // Check if we can use an index for a range set function Expression varExp = null; if (funType.Equals("range_set")) { varExp = param0; } else { // Index is still applicable for parameter queries. The operator // must be sufficiently simple and contain 1 variable that is an // index candidate. if (QueryPlanner.IsSimpleComparison(funType)) { // Does is contain 1 variable that is an index candidate? Expression param1 = (Expression) filter.GetArgument("arg1"); if (param0.Type == ExpressionType.FetchVariable && param1.Type != ExpressionType.FetchVariable) { varExp = param0; } else if (param0.Type != ExpressionType.FetchVariable && param1.Type == ExpressionType.FetchVariable) { varExp = param1; } } } if (varExp != null) { indexName = varExp.IndexCandidate; tableName = varExp.IndexTableName; if (indexName != null) { indexApplicable = true; // Set the indexed ops field, which is an array of operations // representing the term of the index compositeIndexExp = FunctionExpression.Composite(varExp, true); } } } } // We use the index to predict worst case cost in an accurate way if (indexApplicable && funType.Equals("range_set")) { // If we have an index, and the filter is a range set, we query the index // directly to get worst case probability. // Get the variable. SelectableRange rangeSet = (SelectableRange) filter.GetArgument("arg1"); // The time to perform this operation is selectable range set // elements * (2 * LOOKUP_COST) long filterTimeCost; if (indexApplicable) { // Index time cost filterTimeCost = rangeSet.Count() * (BTreeLookupCost * 2); // Notify the graph that this filter must be ordered by the terms of // the expression regardless of how the processor decides to solve the // operation. expression.OrderRequired = compositeIndexExp; } else { // Scan time cost filterTimeCost = (long)((double)childRows * 1.1); } // Have we done a size estimate already on this filter? long? resultSize = (long?)filter.GetArgument("result_size_lookup"); if (resultSize == null) { // Fetch the index on the table IIndexSetDataSource rowIndex = transaction.GetIndex(tableName, indexName); // Do the index lookup and cost appropriately IRowCursor result = rowIndex.Select(rangeSet); resultSize = result.Count; filter.SetArgument("result_size_lookup", resultSize.Value); } // Row count is the worst case, either the child rows or the number of // elements in the index, whichever is smaller. double newRowCount = System.Math.Min((double)resultSize, childRows); // Note, this information is a very precise worst case expression.CostRows = newRowCount; expression.CostTime = childTime + filterTimeCost; return; } else { // This is a parameter operation eg. 'a = ?', '? > b' // We know we if we have an index to resolve this which we use for // time costing, but we don't know anything specific about the value // being searched. We always assume that something will be matched. // The time cost of this operation double filterTimeCost; double newRowCount; if (indexApplicable) { // Index lookup filterTimeCost = BTreeLookupCost * 2.0d; // Notify the graph that this filter must be ordered by the terms of // the expression regardless of how the processor decides to solve the // operation. expression.OrderRequired = compositeIndexExp; } else { // Complete scan of child filterTimeCost = childRows * 1.1d; } // If we are a simple function if (QueryPlanner.IsSimpleComparison(funType)) { // Fetch the first variable that is locally referencable from the // arguments Variable var = null; Expression varExp = (Expression) filter.GetArgument("arg0"); if (varExp.Type == ExpressionType.FetchVariable) { var = Dereference(expression, (Variable)varExp.GetArgument("var")); if (var == null) { varExp = (Expression) filter.GetArgument("arg1"); if (varExp.Type == ExpressionType.FetchVariable) var = Dereference(expression, (Variable)varExp.GetArgument("var")); } } // If we can't dereference it, assume worst case if (var == null) { newRowCount = childRows; } else { // No index, so defer to a probability estimate, double? cachedProbability = (double?)filter.GetArgument("result_probability"); if (cachedProbability == null) { // Get the column statistics object for this ColumnStatistics col_stats = transaction.GetColumnStatistics(var); // Estimated probability of the given function truth over a sample // of the data. cachedProbability = col_stats.ProbabilityEstimate(funType); filter.SetArgument("result_probability", cachedProbability.Value); } double predictedRowCount = childRows * cachedProbability.Value; // Round up. newRowCount = predictedRowCount + 1; } } else if (funType.Equals("range_set")) { // If we are a range_set // Get the variable. Expression varExp = (Expression) filter.GetArgument("arg0"); SelectableRange rangeSet = (SelectableRange)filter.GetArgument("arg1"); // Get the var, Variable var = (Variable) varExp.GetArgument("var"); // Dereference this variable var = Dereference(expression, var); // If we can't dereference it, assume worst case if (var == null) { newRowCount = childRows; } else { double probability; // If the var is an index candidate, indexName = varExp.IndexCandidate; tableName = varExp.IndexTableName; if (indexName != null) { // There's an index we can use! // Fetch the index on the table IIndexSetDataSource rowIndex = transaction.GetIndex(tableName, indexName); // Have we done a size estimate already on this filter? long? resultSize = (long?)filter.GetArgument("result_size_lookup"); if (resultSize == null) { // Do the index lookup and cost appropriately IRowCursor result = rowIndex.Select(rangeSet); resultSize = result.Count; filter.SetArgument("result_size_lookup", resultSize.Value); } // Calculate the probability, long indexSize = rowIndex.Select(SelectableRange.Full).Count; if (indexSize > 0) { probability = (double)resultSize / indexSize; } else { probability = 0; } } else { // No index, so defer to a probability estimate, double? cached_probability = (double?)filter.GetArgument("result_probability"); if (cached_probability == null) { // Get the column statistics object for this ColumnStatistics col_stats = transaction.GetColumnStatistics(var); // Estimated probability of the given function truth over a // sample of the data. cached_probability = col_stats.ProbabilityEstimate(rangeSet); filter.SetArgument("result_probability", cached_probability.Value); } probability = cached_probability.Value; } double predictedRowCount = childRows * probability; // Round up. newRowCount = predictedRowCount + 1; } } else { // Otherwise not a simple function, and can't really predict anything // about this. // Assume worst case, newRowCount = childRows; } // Set the costs expression.CostRows = newRowCount; expression.CostTime = childTime + filterTimeCost; return; } }