group + offset (~ Kaminari) raises SQL error

[pcreux]

Hello,

When I attempt to paginate a collection containing a GROUP BY clause I get a SQLServer error. The generated SQL contains an ORDER BY [PRIMARY_KEY] while the primary key is not part of select or group by.

In issue #167, @metaskills explains that SQLServer requires an order. So when no order is specified, the adapter sets the table's Primary Key as the key to order with. This does not work when you group records though.

I assume that the fix is to sort by the first column of the GROUP BY clause when the results are grouped.

Does that make sense?

Example:

Receipt.group(:PurchaseOrderId).offset(3).all

Receipt Load (149.4ms)  EXEC sp_executesql N'
SELECT TOP (9223372036854775807) [__rnt].* 
FROM (SELECT ROW_NUMBER() OVER (
ORDER BY [Receipts].[Id] ASC) AS [__rn], [Receipts].* 
FROM [Receipts] GROUP BY PurchaseOrderId ) 
AS [__rnt] WHERE [__rnt].[__rn] > (3)
ORDER BY [__rnt].[__rn] ASC'

ActiveRecord::StatementInvalid: TinyTds::Error: 
Column 'Receipts.Id' is invalid in the select list because it 
is not contained in either an aggregate function or the 
GROUP BY clause.: 
EXEC sp_executesql N'SELECT TOP (9223372036854775807) [__rnt].* 
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [Receipts].[Id] ASC) 
AS [__rn], [Receipts].* FROM [Receipts] GROUP BY PurchaseOrderId ) 
AS [__rnt] WHERE [__rnt].[__rn] > (3) ORDER BY [__rnt].[__rn] ASC'

[metaskills]

Sounds reasonable, do you think you can put together a test in https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/test/cases/order_test_sqlserver.rb

[pcreux]

Sure! I'll do it in a couple of days as I have to setup a dev env for this.

[pirminis]

This problem is still not fixed, or I am not efficient enough to find answer.

[metaskills]

There has been major changes in the 4.2 adapter WRT offset and limit which now uses OFFSET and FETCH. Cheers and more info here. http://metaskills.net/2015/01/25/activerecord-sqlserver-v4.2.0---code-name-kantishna/