I am using django's paginator to get pages of a query dat sorts on two columns. The first page is succesfully sorted, but the 2nd page is sorting on the wrong column.
I've tracked down the issue to the SQL: on the 2nd page, fields are aliased ([AAAA].[name_1]) to be able to use them in a sub-select. However, in the sorting subselect, this alias is not used! It is using the [AAAA].[name] instead, which is the name of another column.
2nd page query:
SELECT _row_num, [start], [name_1] FROM ( SELECT -- this order by [AAAA].[name] should use [AAAA].[name_1] instead ROW_NUMBER() OVER ( ORDER BY [AAAA].[name] ASC, [AAAA].[start] ASC) as _row_num, [AAAA].[start], [AAAA].[name_1] FROM ( SELECT DISTINCT [a].[name], [b].[start], [c].[name] as [name_1] FROM [b] INNER JOIN [a] ON ([b].[location_id] = [a].[id]) LEFT OUTER JOIN [e] ON ([b].[id] = [e].[id]) LEFT OUTER JOIN [c] ON ([e].[id] = [c].[id]) ) AS [AAAA] ) as QQQ where 50 < _row_num and _row_num <= 100