Description of problem: SQL Server does not allow you to use order by in views, inline functions, derived tables, and subqueries unless TOP is also specified. When getting the size of a data query then the query is wrapped and select count(*) is applied to the wrapped query. When an order by has been applied to the data query and size is called then SQL Server reports this error. I came accross this problem due to incompatibility with SQL Server but it may also be a performance enhancement as well. Performance issues is probably why SQL Server does not allow this. It does not make sense to order the query when all your doing is getting the count of the query. I modified com.arsdigita.persistence.DataQueryImpl to account for this. In makeExpr(boolean size) I do a if(!size) test before running the following: for (int i = m_orders.size() - 1; i >= 0; i--) { String order = (String) m_orders.get(i); orders[i] = mapAndAddPaths(order); } and for (int i = orders.length - 1; i >= 0; i--) { expr = new Sort(expr, new Static(orders[i], m_bindings)); }
Only the second for loop (the one with new Sort) needs to be skipped during a size(). mapAndAddPaths should be called on every relevant path, including those in the order because it can change the size of the query if one of the paths traverses a property that is a collection.
The DataQueryImpl.isEmpty() function also needs to be modified here by calling DataQueryImpl.makeExpr(true) because DataSet.isEmpty() simply calls DataSet.size().