Problem with ordering and offset

[AndyS]

I'm using sqlserver-adapter (3.1.1) in combination with will_paginate
(3.0.2). I encounter strange behavior when trying to paginate with
complex queries regarding the ordering of the results. As long as the
query is not too complex to order of the results is correct but at a
certain level of complexity (adding where clauses, subqueries, etc.)
the order seems to be arbitrary.

If I'm right, the method which builds the sql for "pagination" is
"visit_Arel_Nodes_SelectStatementWithOffset". There, the specified
order fields are respected in the subquery where the "ROW_NUMBER" is
selected and aliased to "[__rn]". What I don't understand is how sql
server determines the order of the overall query (i.e. the outer
query) as there is no order by clause!? Is there a special reason, why
there is no "ORDER BY [__rnt].[__rn]" at the end of this outer query?
If I add this order-by clause at the end my problems disappear.

Thanks in advance!

Andy

[Ken Collins]

Morning Andy,

Great question! My understanding when using ROW_NUMBER for windowed results (limit/offset) is that it is not needed since the "OVER (ORDER BY..." will order the inner results and the select around it does not need it. However, if this is not the case and the extra "ORDER BY [__rnt].[__rn]" is needed, then I would be open to such a patch. Can you investigate this more and open a ticket with information and possibly a link or content back to this thread?

Lastly, one thing that throws up newcomers to SQL Server is that unlike other DBs, it does not like ambiguous results. This is why we have Arel helper methods like #rowtable_orders that find your specified order or use the tables primary key or first column. That code received a few patches up to the 3.2 work. So it could be possible that your issue is related to (a) not specifying an explicit order and (b) the #rowtable_order picking a column that would lead to ambiguous results.

 - HTH,

   Ken