Queries with limits and no offset return the last items instead of the first ones

[gc-olivierho]

When using Redmine with MS SQL, the search results are the 10 last issues instead of the 10 first.
After tracing the Redmine and then Ruby code, I ended up suspecting the following lines, in activerecord-sqlserver-adapter-3.2.12/lib/arel/visitors/sqlserver.rb:

      def visit_Arel_Nodes_SelectStatementWithOutOffset(o, windowed=false)
(...)
        elsif eager_limiting_select_statement?(o)
          projections = projections.map { |x| projection_without_expression(x) }
          groups = projections.map { |x| projection_without_expression(x) }
          orders = orders.map do |x|
            expr = Arel.sql projection_without_expression(x.expr)
======>     x.descending? ? Arel::Nodes::Max.new([expr]) : Arel::Nodes::Min.new([expr])
          end

It seems that if x.descending is TRUE, then we apply a MAX() function, but we should also apply DESC ordering. However, this does not come into the query with this code.

Could someone give his opinion on this?

[gc-olivierho]

If anyone is still working on that code, or if any user has the same issue, here is how we fixed it on our side:
In activerecord-sqlserver-adapter-3.2.12/lib/arel/visitors/sqlserver.rb, we changed the line 151:

            x.descending? ? Arel::Nodes::Max.new([expr]) : Arel::Nodes::Min.new([expr])

to

            if x.descending?
              Arel::Nodes::Max.new([expr])
              groups += [expr]
              Arel::Nodes::Descending.new([expr])
            else
              Arel::Nodes::Min.new([expr])
            end

This worked for more than a month on our Redmine 2.3.3 site, with 30+ active users.

Please note that I am not a Ruby programmer and have very little knowledge in it. In particular, I still do not understand that code completely and reached that fix after several (if not many) trial and errors. Please anyone feel free to suggest a better way to address the issue I raised.

[metaskills]

There has been major changes in the 4.2 adapter WRT limit/offset since we now use OFFSET/FETCH. This issue is likely resolved in that version. Cheers and more info here. http://metaskills.net/2015/01/25/activerecord-sqlserver-v4.2.0---code-name-kantishna/