Hello,

recently I got quite interesting problem. We have a case when we select from one table, but do filtering and sorting by values in another table. I noticed that if query has sorting with column which is not in select result set - sorting is performed in memory and Limit is not added to the query.

I traced down the responsible code which is DynamicQueryEngineBase.cs method HandleDistinctEmit. It even has comment: // check if the sortclause fields are in the selectlist.

Is this correct? Do I do something wrong about this? In sql sorting and filtering by field which is not in select list works fine, not sure why you do this in the framework? If I add my sort field to select list, limit is added without problems.

regards Mantas

Hi Mantas,

It has to do with the ability to determine unique rows, in a order/paging scenario in which no duplicate rows are allowed. This is mentioned in the docs:

NOTE: If you specify a sort clause or a set of sortclauses and a RelationCollection (which is almost always the case with a typed list) while you also specify that duplicates are not allowed, be sure the sort clauses are referring to fields in the result-set, otherwise the database can't obey the sort rule and will throw an exception, since all fields mentioned in an ORDER BY clause (which is the result of a sort clause) have to be in the resultset when a DISTINCT statement (the result of the specification that no duplicate rows have to be retrieved) is included. When you want to sort on a field which has an aggregate function or an expression applied to it, be sure to specify the aggregate function or expression object to the field in the SortClause as well, with the same alias.

Related thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=16756

If you still think that there is something we have to look at, please post more information (version, rtl, code, generated sql, etc), as mentioned in the guidelines: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722