Issues

Issue #49 wontfix

Pagination doesn't work with aliased names

Anonymous created an issue

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

Comments (6)

  1. elessarwebb

    Ah, my ticket, sorry for the formatting, it got deleted:

    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
    
  2. elessarwebb

    Sorry, because producing a test case is quite challenging, i'd like to know exactly what you need; is an abstract Django test case enough, or would you need an actual django test?

  3. Michael Manfre repo owner

    It doesn't matter whether you or I write a test that stresses this issue. The benefits of you writing the test are that it means the test will match up with your usage, instead of potentially a slightly different usage that exercises a different issue. It will also get resolved faster because I'll have a failing test as a starting point, instead of a description of the problem that I need to turn in to a failing test.

    At the bare minimum, I would need to see Django code (specifically the filter and order_by) that would generate SQL like that.

  4. Log in to comment