Ejemplo n.º 1
0
        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;
        }
Ejemplo n.º 2
0
        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;
        }
Ejemplo n.º 3
0
 private static void CostNoCostFilterExpression(Expression child, FilterExpression expression)
 {
     // Set the costs
     expression.CostTime = child.CostTime;
     expression.CostRows = child.CostRows;
 }
Ejemplo n.º 4
0
        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;
            }
        }
Ejemplo n.º 5
0
        private ITable StaticFilter(ITable child, FilterExpression expression)
        {
            // The filter operation
            Expression filterExp = expression.Filter;
            // Execute the static expression
            SqlObject[] result = Result(DoExecute(filterExp));
            // If true,
            if (result.Length == 1 &&
                SqlObject.Compare(result[0], new SqlObject(true)) == 0)
                return child;

            return new SubsetTable(child);
        }
Ejemplo n.º 6
0
        private ITable SortFilter(ITable table, FilterExpression expression)
        {
            // The filter operation which is a function that describes the sort terms
            Expression filterExp = expression.Filter;
            if (!(filterExp is FunctionExpression))
                throw new ArgumentException("Expected a function as argument to the filter.");

            ITable resultTable = table;

            // If there's something to sort,
            if (table.RowCount > 1) {
                // Get the composite function representing the sort collation,
                FunctionExpression compositeExp = (FunctionExpression) filterExp;
                if (!compositeExp.Name.Equals("composite"))
                    throw new ArgumentException("Invalid composite function for sorting.");

                // The natural ordering of the child
                Expression naturalChildOrder = GetTableOrderComposite(table);
                if (naturalChildOrder != null) {
                    if (naturalChildOrder.Equals(compositeExp))
                        // No sort necessary, already sorted
                        return table;

                    // TODO: test for the reverse condition, which we can optimize
                    //   with a reverse row iterator.
                }

                int paramCount = compositeExp.Parameters.Count;
                int termCount = paramCount / 2;
                IIndexSetDataSource rowIndex;
                bool naturalOrder = true;
                // If 1 sort term,
                if (termCount == 1) {
                    Expression sortExp = (Expression) compositeExp.Parameters[0];
                    naturalOrder = SqlObject.Equals((SqlObject)compositeExp.Parameters[1], SqlObject.True);
                    // Get the index candidate
                    string indexName = sortExp.IndexCandidate;
                    TableName indexTableName = sortExp.IndexTableName;
                    // Index available?
                    rowIndex = GetIndex(table, indexName);
                } else {
                    // Multiple terms,
                    // Get the index candidate if there is one
                    string indexName = compositeExp.IndexCandidate;
                    TableName indexTableame = compositeExp.IndexTableName;
                    // Index available?
                    rowIndex = GetIndex(table, indexName);
                }

                // If we have an index,
                if (rowIndex != null) {
                    IRowCursor sortedCursor = rowIndex.Select(SelectableRange.Full);
                    if (!naturalOrder)
                        // Reverse iterator,
                        sortedCursor = new ReverseRowCursor(sortedCursor);

                    SubsetTable sortedTable = new SubsetTable(table, sortedCursor);
                    sortedTable.IndexRequestFallthrough = true;
                    // Set the order composite function the describes how the subset is
                    // naturally sorted.
                    sortedTable.OrderComposite = (Expression) compositeExp.Clone();
                    resultTable = sortedTable;
                } else {
                    // NOTE: There's lots of directions we can take for optimizing this
                    //  sort.  For example, if the number of values being sorted meets some
                    //  criteria (such as all integers and less than 2 millions values)
                    //  then the values being sorted could be read onto the heap and sorted
                    //  in memory with a quick sort.

                    // Scan sort,
                    // The working set,
                    IIndex<RowId> workingSet = transaction.CreateTemporaryIndex<RowId>(table.RowCount);
                    // Create the resolver
                    IndexResolver resolver = CreateResolver(table, compositeExp);
                    // Iterator over the source table
                    IRowCursor tableCursor = table.GetRowCursor();

                    // Wrap in a forward prefetch iterator
                    tableCursor = new PrefetchRowCursor(tableCursor, table);

                    // Use a buffer,
                    RowId[] rowIds = new RowId[128];
                    while (tableCursor.MoveNext()) {
                        int count = 0;
                        while (tableCursor.MoveNext() && count < 128) {
                            rowIds[count] = tableCursor.Current;
                            ++count;
                        }
                        for (int i = 0; i < count; ++i) {
                            RowId rowid = rowIds[i];
                            // Get the value,
                            SqlObject[] values = resolver.GetValue(rowid);
                            // Insert the record into sorted order in the working_set
                            workingSet.Insert(values, rowid, resolver);
                        }
                    }

                    // TODO: record 'workingSet' for future resolution.

                    // The result,
                    IRowCursor sortedCursor = new DefaultRowCursor(workingSet.GetCursor());
                    SubsetTable sortedTable = new SubsetTable(table, sortedCursor);
                    sortedTable.IndexRequestFallthrough = true;
                    // Set the order composite function the describes how the subset is
                    // naturally sorted.
                    sortedTable.OrderComposite = (Expression) compositeExp.Clone();
                    resultTable = sortedTable;
                }
            }

            return resultTable;
        }
Ejemplo n.º 7
0
        private ITable SingleFilter(ITable table, FilterExpression expression)
        {
            // The filter operation
            Expression filterExp = expression.Filter;

            // A range set is eligible for indexes, etc
            if (filterExp.Type == ExpressionType.Function) {
                FunctionExpression functionExp = (FunctionExpression) filterExp;
                string funType = functionExp.Name;
                // Is this a range set?
                if (funType.Equals("range_set")) {
                    // Get the var and the range set
                    FetchVariableExpression varExp = (FetchVariableExpression) functionExp.Parameters[0];
                    Variable var = varExp.Variable;
                    SelectableRange rangeSet = (SelectableRange) functionExp.Parameters[1];
                    // Is the var an index candidate?
                    TableName indexTname = varExp.IndexTableName;
                    string indexName = varExp.IndexCandidate;

                    if (indexName != null) {
                        // Try and get this index in the parent
                        IIndexSetDataSource rowIndex = GetIndex(table, indexName);
                        if (rowIndex != null) {
                            // The order composite
                            Expression orderComposite = expression.OrderRequired;
                            return FilterByIndex(table, rowIndex, orderComposite, rangeSet);
                        }
                    }
                    // Here if; no index found we can use, so we fall through to the scan

                } else {
                    // Check to see if we have a simple expression we can use an index
                    // for.  For example, 'a = ?'
                    // Check the function is a simple comparison
                    if (QueryPlanner.IsSimpleComparison(funType)) {
                        // Do we have a var on one side and a simple static expression on the
                        // other?
                        Expression p0 = (Expression) functionExp.Parameters[0];
                        Expression p1 = (Expression)functionExp.Parameters[1];

                        Expression var_op = null;
                        Expression static_op = null;
                        String comparison = null;

                        ExpressionType p0type = p0.Type;
                        ExpressionType p1type = p1.Type;

                        if (p0type == ExpressionType.FetchVariable &&
                            p1type != ExpressionType.FetchVariable) {
                            var_op = p0;
                            static_op = p1;
                            comparison = funType;
                        } else if (p1type == ExpressionType.FetchVariable &&
                                   p0type != ExpressionType.FetchVariable) {
                            var_op = p1;
                            static_op = p0;
                            comparison = QueryPlanner.ReverseSimpleComparison(funType);
                        }

                        // Did we find an expression that is eligible?
                        if (comparison != null) {
                            // If the var_op is an index candidate.
                            string indexName = var_op.IndexCandidate;
                            TableName indexTableName = var_op.IndexTableName;
                            if (indexName != null) {
                                // We have an index,
                                // Try and get this index in the parent
                                IIndexSetDataSource rowIndex = GetIndex(table, indexName);
                                if (rowIndex != null) {
                                    // Is the static locally static?
                                    if (IsLocallyStatic(table, static_op)) {
                                        // Resolve the static,
                                        ITable staticResult = DoExecute(static_op);
                                        // Assert we have 1 column and 1 row
                                        if (staticResult.RowCount != 1 &&
                                            staticResult.Columns.Count != 1)
                                            throw new ApplicationException("Static operation gave incorrectly formatted result");

                                        // Get the value
                                        IRowCursor rowCursor = staticResult.GetRowCursor();
                                        if (!rowCursor.MoveNext())
                                            throw new ApplicationException();

                                        RowId rowId = rowCursor.Current;
                                        SqlObject staticVal = staticResult.GetValue(0, rowId);
                                        // The order composite
                                        Expression orderComposite = expression.OrderRequired;
                                        // Perform the index lookup,
                                        return FilterByIndex(table, rowIndex, orderComposite, comparison, staticVal);

                                    } else {
                                        // Here if; the static is not locally static
                                    }
                                } else {
                                    // Here if; the parent isn't base table with the index
                                    // definition
                                }
                            } else {
                                // Here if; the var isn't an index candidate
                            }
                        } else {
                            // Here if; there isn't a fetch var on a side or both sides are
                            // fetch vars
                        }

                    } else {
                        // TODO: Check if it's a logical operator, so we can check for
                        //   constructs like 'a = 3 or a = ? or a = ?' which we can
                        //   potentially convert into a pointer union between 3 index
                        //   lookups.

                        // Currently, fall through to a scan operation...
                    }
                }
            } else {
                // Here if; the filter is not based on a function operation.
            }

            // We filter by a full scan on the table with the operation.
            return FilterByScan(table, filterExp);
        }
Ejemplo n.º 8
0
        /// <summary>
        /// Prepares the given SearchExpression object.
        /// </summary>
        /// <param name="db"></param>
        /// <param name="fromSet"></param>
        /// <param name="expression"></param>
        /// <remarks>
        /// This goes through each element of the expression. If the 
        /// element is a variable it is qualified.
        /// If the element is a <see cref="TableSelectExpression"/> it's 
        /// converted to a <see cref="SelectStatement"/> object and prepared.
        /// </remarks>
        private static FilterExpression PrepareSearchExpression(IDatabaseConnection db, TableExpressionFromSet fromSet, FilterExpression expression)
        {
            // first check the expression is not null
            if (expression == null)
                return null;

            // This is used to prepare sub-queries and qualify variables in a
            // search expression such as WHERE or HAVING.

            // Prepare the sub-queries first
            expression = expression.Prepare(new ExpressionPreparerImpl(db, fromSet));

            // Then qualify all the variables.  Note that this will not qualify
            // variables in the sub-queries.
            expression = expression.Prepare(fromSet.ExpressionQualifier);

            return expression;
        }
Ejemplo n.º 9
0
        private ITable ExpressionTableFilter(ITable table, FilterExpression op)
        {
            // The filter operation which is a function that describes the output
            // columns
            Expression filterExp = op.Filter;
            if (filterExp.Type != ExpressionType.Function)
                throw new ApplicationException("Expected a function.");

            FunctionExpression functionExp = (FunctionExpression)filterExp;

            // Function name and parameter count,
            string funName = functionExp.Name;
            if (!funName.Equals("table_out"))
                throw new ArgumentException();

            int paramCount = functionExp.Parameters.Count;

            // Create the expression table data source
            ExpressionTable expressionTable = new ExpressionTable(table, this);

            // The number of parameters,
            for (int i = 0; i < paramCount; ++i) {
                Expression outExp = (Expression) functionExp.Parameters[i];
                // This will always be an aliasvarname with an operation child which is
                // the expression we perform for the column.
                if (!(outExp is AliasVariableNameExpression))
                    throw new ApplicationException("Expected ALIASVARNAME.");

                // The label,
                Variable v = ((AliasVariableNameExpression)outExp).Alias;
                string label = v.Name;
                // The actual function
                Expression funExp = ((AliasVariableNameExpression) outExp).Child;
                // Work out the type,
                SqlType expType = GetExpressionType(table, funExp);
                // Add the column
                expressionTable.AddColumn(label, expType, funExp);
            }

            // Return the operation table
            return expressionTable;
        }
Ejemplo n.º 10
0
 private ITable AggregateFilter(ITable child, FilterExpression expression)
 {
     // The filter operation
     FunctionExpression filterExp = (FunctionExpression) expression.Filter;
     // The filter operation is the sort composite
     AggregateTable aggregate = new AggregateTable(child, filterExp);
     // Create an empty index for the aggregate table and initialize
     // Note: Time cost of this is a scan on 'child'
     IIndex<long> emptyIndexContainer = transaction.CreateTemporaryIndex<long>(System.Math.Max(2, child.RowCount * 2));
     aggregate.InitGroups(this, emptyIndexContainer);
     // Set the order composite
     aggregate.SetOrderCompositeIsChild();
     // Return the table
     return aggregate;
 }
Ejemplo n.º 11
0
        /// <summary>
        /// Evalutes the WHERE clause of the table expression.
        /// </summary>
        /// <param name="searchExpression"></param>
        /// <returns></returns>
        public IQueryPlanNode PlanSearchExpression(FilterExpression searchExpression)
        {
            // First perform all outer tables.
            PlanAllOuterJoins();

            Expression exp = searchExpression == null ? null : searchExpression.Expression;
            return LogicalEvaluate(exp);
        }
Ejemplo n.º 12
0
        private Expression CostAnalysisAndTransform(SelectExpression selectExpression)
        {
            Expression joinGraph = selectExpression.Join;
            Expression filterGraph = selectExpression.Filter;

            // The required final ordering of the select expression if necessary.
            // This is either the 'group by' ordering for an aggregate statement,
            // or 'order by' if it's not an aggregate.

            // Are we an aggregate?
            Expression[] resultOrderExps;
            bool[] resultOrderAsc;
            Expression sortComposite = null;
            bool aggregateExpression = false;

            if (selectExpression.IsAggregated) {
                // Yes, do we have group by clause?
                int groupbyCount = selectExpression.GroupBy.Count;
                resultOrderExps = new Expression[groupbyCount];
                resultOrderAsc = new bool[groupbyCount];
                for (int i = 0; i < groupbyCount; ++i) {
                    resultOrderExps[i] = selectExpression.GroupBy[i];
                    resultOrderAsc[i] = true;      // All group by ordering is ascending
                }
                // Note the aggregate,
                aggregateExpression = true;
            } else {
                // Not an aggregate statement, do we have a order by clause?
                int orderbyCount = selectExpression.OrderBy.Count;
                resultOrderExps = new Expression[orderbyCount];
                resultOrderAsc = new bool[orderbyCount];
                for (int i = 0; i < orderbyCount; ++i) {
                    resultOrderExps[i] = selectExpression.OrderBy[i].Expression;
                    resultOrderAsc[i] = selectExpression.OrderBy[i].IsAscending;
                }
            }
            // The sort composite
            if (resultOrderExps.Length > 0) {
                sortComposite = FunctionExpression.Composite(resultOrderExps, resultOrderAsc);
            }

            // Create a new query transform object
            QueryPlanner planner = new QueryPlanner(transaction);
            planner.SetFilterGraph(filterGraph);
            planner.SetJoinGraph(joinGraph);
            planner.SetResultSortComposite(sortComposite);
            Expression cheapResolution = planner.FindCheapResolution();

            // If this is an aggregate query, we apply the aggregate filter to the
            // query plan.
            if (aggregateExpression) {
                FilterExpression aggregateFilter =
                      new FilterExpression("aggregate", cheapResolution, sortComposite);
                cheapResolution = aggregateFilter;
                // Is there a having clause?
                Expression havingExp = selectExpression.Having;
                if (havingExp != null) {
                    FilterExpression havingFilter =
                        new FilterExpression("single_filter", cheapResolution, havingExp);
                    cheapResolution = havingFilter;
                }
                // Is there an order by clause?
                int orderbyCount = selectExpression.OrderBy.Count;
                if (orderbyCount > 0) {
                    Expression[] orderExps = new Expression[orderbyCount];
                    bool[] order_asc = new bool[orderbyCount];
                    for (int i = 0; i < orderbyCount; ++i) {
                        orderExps[i] = selectExpression.OrderBy[i].Expression;
                        order_asc[i] = selectExpression.OrderBy[i].IsAscending;
                    }

                    Expression aggrSortComposite = FunctionExpression.Composite(orderExps, order_asc);
                    FilterExpression sortFilter = new FilterExpression("sort", cheapResolution, aggrSortComposite);
                    cheapResolution = sortFilter;
                }
            }

            // cheap_resolution is the best plan found, now add decoration such as
            // filter terms, etc
            int outCount = selectExpression.Output.Count;
            FunctionExpression outFunction = new FunctionExpression("table_out");
            for (int i = 0; i < outCount; ++i) {
                outFunction.Parameters.Add(selectExpression.Output[i].Expression);
            }
            // Set the filter,
            Expression outFilter = new FilterExpression("expression_table", cheapResolution, outFunction);

            QueryCostModel costModel = new QueryCostModel(transaction);
            costModel.ClearGraph(outFilter);
            costModel.Cost(outFilter, Double.PositiveInfinity, new int[1]);

            return outFilter;
        }
Ejemplo n.º 13
0
        private List<TableName> RandomPlanSchedule(IList<QueryPredicate> expressions, IList<Expression> danglingExps, Expression joinGraph)
        {
            // Collect the set of table sources around the left branch,
            List<Expression> expList = new List<Expression>();
            if (joinGraph is JoinExpression) {
                LeftDeepTableSources(expList, joinGraph);
            } else {
                expList.Add(joinGraph);
            }

            // Recurse on any right branches first (outer joins) and create a list of
            // sources
            List<TableName> sources = new List<TableName>();
            int sz = expList.Count;
            for (int i = 0; i < sz; ++i) {
                Expression expression = expList[i];
                if (expression is JoinExpression) {
                    sources.AddRange(RandomPlanSchedule(expressions, danglingExps, expression));
                } else {
                    TableName tn = ((AliasTableNameExpression) expression).Alias;
                    if (tn == null)
                        throw new SystemException();
                    sources.Add(tn);
                }
            }

            // Now 'sources' is our domain of tables to work with
            // By the time this method returns, the domain 'sources' must be entirely
            // joined together in the danglingExps list.

            // The list of predicates that are entirely dependant on tables in this
            // source.
            List<QueryPredicate> predicates = new List<QueryPredicate>();
            foreach(QueryPredicate expr in expressions) {
                int dependOnCount = expr.dependant_on.Count;
                // Some dependants
                if (dependOnCount > 0) {
                    bool touchAll = true;
                    foreach(TableName src in expr.dependant_on) {
                        if (!sources.Contains(src)) {
                            touchAll = false;
                            break;
                        }
                    }
                    // Add if the dependants of this expression are all contained within
                    // the source domain.
                    if (touchAll) {
                        predicates.Add(expr);
                    }
                }
            }

            while (true) {
                // Find a random predicate that can be scheduled in this domain.
                // Returns -1 if no predicate can be found in the set.  If this is the
                // case and the source domain isn't entirely joined, then we perform a
                // cartesian join on any dangling tables.
                int ri = RandomPredicate(predicates, danglingExps);

                // If no predicates found,
                if (ri == -1) {
                    // Break the main loop and return
                    break;
                }

                // Remove the predicate from the list
                QueryPredicate predicate = predicates[ri];
                predicates.RemoveAt(ri);
                // Pick the operations from the source this query is dependant on
                List<Expression> srcExps = new List<Expression>();
                List<int> srcIds = new List<int>();
                foreach(TableName tname in predicate.dependant_on) {
                    int id = 0;
                    foreach(Expression op in danglingExps) {
                        if (IsASourceOf(tname, op)) {
                            if (!srcIds.Contains(id)) {
                                srcIds.Add(id);
                                srcExps.Add(op);
                            }
                            break;
                        }
                        ++id;
                    }
                }

                // Error condition
                if (srcExps.Count == 0)
                    throw new ApplicationException("Unable to schedule predicate: " + predicate);

                // If we only found 1 predicate, we simply merge the predicate
                // expression as a scan operation.
                if (srcExps.Count <= 1) {
                    int expPos = srcIds[0];
                    Expression oldExp = danglingExps[expPos];
                    // Make a filter with 'old_op' as the child and predicate.expression
                    // as the filter
                    danglingExps[expPos] = new FilterExpression("single_filter", oldExp, predicate.expression);
                } else {
                    // If 2 or more

                    // If more than 2, we randomly pick sources to merge as a cartesian
                    // product while still maintaining the right requirement of the join
                    // if there is one.
                    if (srcExps.Count > 2) {
                        // Randomize the list
                        Util.CollectionsUtil.Shuffle(srcIds);
                        // If the predicate has a right dependancy, put it on the end
                        if (predicate.right_dependancy != null) {
                            TableName farRight = predicate.right_dependancy[0];
                            int i = 0;
                            foreach(int expId in srcIds) {
                                Expression op = danglingExps[expId];
                                if (IsASourceOf(farRight, op)) {
                                    // swap with last element
                                    int lastI = srcIds.Count - 1;
                                    int lid = srcIds[lastI];
                                    srcIds[lastI] = srcIds[i];
                                    srcIds[i] = lid;
                                }
                                ++i;
                            }
                        }

                        // Cartesian join the terms, left to right until we get to the last
                        // element.
                        Expression procExp = danglingExps[srcIds[0]];
                        for (int i = 1; i < srcIds.Count - 1; ++i) {
                            procExp = new JoinExpression(procExp, danglingExps[srcIds[i]], JoinType.Cartesian, null);
                        }

                        // Remove the terms from the current layout list
                        // Remember the expression on the right
                        Expression leftExp1 = procExp;
                        Expression rightExp1 = danglingExps[srcIds[srcIds.Count - 1]];

                        // Sort the id list
                        int[] idSet = srcIds.ToArray();
                        Array.Sort(idSet);
                        // Remove the values
                        for (int i = idSet.Length - 1; i >= 0; --i) {
                            danglingExps.RemoveAt(idSet[i]);
                        }

                        // Reset the src_ids and src_ops list
                        srcIds.Clear();
                        srcExps.Clear();

                        // Add the left and right expression
                        danglingExps.Add(leftExp1);
                        danglingExps.Add(rightExp1);
                        srcIds.Add(danglingExps.Count - 2);
                        srcIds.Add(danglingExps.Count - 1);
                        srcExps.Add(leftExp1);
                        srcExps.Add(rightExp1);
                    }

                    // Ok, down to 2 to merge,
                    int li;
                    // Do we have a right requirement?
                    if (predicate.right_dependancy != null) {
                        // Yes, so either one src is part of the right dependancy or they
                        // are both part of the right dependancy.
                        Expression exp1 = srcExps[0];
                        Expression exp2 = srcExps[1];
                        int op1_c = 0;
                        int op2_c = 0;
                        foreach(TableName tname in predicate.right_dependancy) {
                            if (IsASourceOf(tname, exp1)) {
                                ++op1_c;
                            }
                            if (IsASourceOf(tname, exp2)) {
                                ++op2_c;
                            }
                        }

                        // If they are both part of the right dependancy, we cartesian join
                        if (op1_c > 0 && op2_c > 0) {
                            // TODO:
                            throw new NotImplementedException();
                        }

                        // If op1 is part of the right dependancy,
                        if (op1_c > 0) {
                            li = 1;
                        } else {
                            // If exp2 is part of the right dependancy,
                            li = 0;
                        }
                    } else {
                        // No right dependancy,
                        // Heuristic - If one of the sources is not a fetch table command
                        // then we have a greater chance to pick that as our left.  This
                        // encourages left deep scan graphs which are the sorts of graphs
                        // we are interested in.
                        ExpressionType type0 = srcExps[0].Type;
                        ExpressionType type1 = srcExps[1].Type;

                        if (type0 != ExpressionType.AliasTableName &&
                            type1 == ExpressionType.AliasTableName) {
                            li = (random.Next(10) >= 2) ? 0 : 1;
                        } else if (type1 != ExpressionType.AliasTableName &&
                                   type0 == ExpressionType.AliasTableName) {
                            li = (random.Next(10) >= 2) ? 1 : 0;
                        } else {
                            // Randomly pick if both are fetch table operations
                            li = random.Next(2);
                        }
                    }

                    Expression leftExp = srcExps[li];
                    int leftId = srcIds[li];
                    Expression rightExp = srcExps[(li + 1)%2];
                    int rightId = srcIds[(li + 1)%2];

                    // Schedule the join operation,
                    // For 'join_inner', 'join_outer', etc
                    // FIXME: check this ...
                    JoinType jtype = !predicate.joinTypeSet ? JoinType.Inner : predicate.JoinType;
                    // string join_type = "scan-" + jtype;
                    JoinExpression join_op = new JoinExpression(leftExp, rightExp, jtype, predicate.expression);
                    // Remove the left and right id from the list
                    if (leftId > rightId) {
                        danglingExps.RemoveAt(leftId);
                        danglingExps.RemoveAt(rightId);
                    } else {
                        danglingExps.RemoveAt(rightId);
                        danglingExps.RemoveAt(leftId);
                    }
                    // Add the new join
                    danglingExps.Add(join_op);
                }
            }

            return sources;
        }
Ejemplo n.º 14
0
        private Expression ProduceRandomPlan(long planSeed, IList<QueryPredicate> expressions, IList<Expression> sourceListExps, 
            Expression joinGraph, Expression sortFunction, Expression staticExpression)
        {
            // The current list of dangling nodes - operations that have not yet been
            // joined by the query plan
            List<Expression> danglingExps = new List<Expression>();
            // Populate it with our source tables
            for (int i = 0; i < sourceListExps.Count; i++) {
                Expression sourceExp = sourceListExps[i];
                // Apply the static filter if necessary,
                if (staticExpression != null) {
                    sourceExp = new FilterExpression("static_filter", sourceExp, staticExpression);
                }
                danglingExps.Add(sourceExp);
            }

            random = new Random((int)planSeed);
            RandomPlanSchedule(expressions, danglingExps, joinGraph);

            // Connect any remaining dangling operations to a cartesian product.
            while (danglingExps.Count > 1) {
                // We randomly connect the remaining elements together
                int p1 = 0;
                int p2 = 1;
                if (danglingExps.Count > 2) {
                    p1 = random.Next(danglingExps.Count);
                    p2 = p1;
                    while (p2 == p1) {
                        p2 = random.Next(danglingExps.Count);
                    }
                }

                Expression left = danglingExps[p1];
                Expression right = danglingExps[p2];
                if (p1 > p2) {
                    danglingExps.RemoveAt(p1);
                    danglingExps.RemoveAt(p2);
                } else {
                    danglingExps.RemoveAt(p2);
                    danglingExps.RemoveAt(p1);
                }

                danglingExps.Add(new JoinExpression(left, right, JoinType.Cartesian, null));
            }

            // The remaining operation is the query plan,
            Expression plan = danglingExps[0];

            // Put the final ordering filter on the plan if the ordering is important
            if (sortFunction != null)
                plan = new FilterExpression("sort", plan, sortFunction);

            return plan;
        }
Ejemplo n.º 15
0
        /// <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;
        }
Ejemplo n.º 16
0
        private ITable Filter(ITable table, FilterExpression op)
        {
            // The filter name
            string filter_name = op.Name;
            if (filter_name.Equals("single_filter"))
                return SingleFilter(table, op);
            if (filter_name.Equals("expression_table"))
                return ExpressionTableFilter(table, op);
            if (filter_name.Equals("sort"))
                return SortFilter(table, op);
            if (filter_name.Equals("static_filter"))
                return StaticFilter(table, op);
            if (filter_name.Equals("aggregate"))
                return AggregateFilter(table, op);

            throw new ApplicationException("Unknown filter type " + filter_name);
        }
Ejemplo n.º 17
0
        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;
        }
Ejemplo n.º 18
0
        private static FilterExpression PrepareJoins(QueryTableSetPlanner tablePlanner, TableSelectExpression expression, TableExpressionFromSet fromSet, FilterExpression whereClause)
        {
            // Look at the join set and resolve the ON Expression to this statement
            JoiningSet joinSet = expression.From.JoinSet;
            var result = whereClause;

            // Perform a quick scan and see if there are any outer joins in the
            // expression.
            bool allInnerJoins = true;
            for (int i = 0; i < joinSet.TableCount - 1; ++i) {
                JoinType type = joinSet.GetJoinType(i);
                if (type != JoinType.Inner)
                    allInnerJoins = false;
            }

            // Prepare the joins
            for (int i = 0; i < joinSet.TableCount - 1; ++i) {
                JoinType type = joinSet.GetJoinType(i);
                Expression onExpression = joinSet.GetOnExpression(i);

                if (allInnerJoins) {
                    // If the whole join set is inner joins then simply move the on
                    // expression (if there is one) to the WHERE clause.
                    if (onExpression != null) {
                        result = result.Append(onExpression);
                    }
                } else {
                    // Not all inner joins,
                    if (type == JoinType.Inner && onExpression == null) {
                        // Regular join with no ON expression, so no preparation necessary
                    } else {
                        // Either an inner join with an ON expression, or an outer join with
                        // ON expression
                        if (onExpression == null)
                            throw new Exception("No ON expression in join.");

                        // Resolve the on_expression
                        onExpression = onExpression.Prepare(fromSet.ExpressionQualifier);
                        // And set it in the planner
                        tablePlanner.SetJoinInfoBetweenSources(i, type, onExpression);
                    }
                }
            }

            return result;
        }