Defect #29581
closed
Issues in paginated views may be lost because sorting criteria are not unique
0%
Description
This is a problem that the issue that should be present is not displayed on the issues/index.
The same problem may exist even at index other than issues/index.
This problem occurs when using PostgreSQL.
If you use only non-unique fields such as trackers or categories as the sorting criteria, there is a possibility that some issues will not be displayed on pagination views.
This phenomenon is described in the document of PostgreSQL. (As a specification rather than a bug)
https://www.postgresql.org/docs/8.3/static/queries-limit.html
Questions about the same problem:
https://stackoverflow.com/questions/13580826/postgresql-repeating-rows-from-limit-offset
I attached file is the test I wrote to reproduce this problem.
If you are using PostgreSQL, that test will fail. ( Non-paginated issue ids and paginated issue ids should be the same. )
Failure: --- expected +++ actual @@ -1 +1 @@ -[2, 12, 11, 8, 7, 5, 3, 1, 13] # Non-paginated issue ids +[11, 12, 12, 7, 7, 5, 3, 1, 13] # Paginated issue ids
Files
Related issues
Updated by Mizuki ISHIKAWA over 5 years ago
- File fix-29581.patch fix-29581.patch added
I wrote a patch to solve this problem.
I fixed to add unique fields (ex: issues.id, time_entries.id) as sort criteria.
Updated by vzvu 3k6k almost 5 years ago
order_option += ['issues.id ASC'] unless order_option.include?("issues.id DESC") || order_option.include?("issues.id ASC")
Is it ok to use `issues.id ASC` as a default implicit order?
IssueQuery#default_sort_criteria uses issues.id DESC.
(Pair-reviewed with maimai77)
Updated by Mizuki ISHIKAWA almost 5 years ago
- File fix-29581-v2.patch fix-29581-v2.patch added
vzvu 3k6k wrote:
order_option += ['issues.id ASC'] unless order_option.include?("issues.id DESC") || order_option.include?("issues.id ASC")Is it ok to use `issues.id ASC` as a default implicit order?
IssueQuery#default_sort_criteriausesissues.id DESC.(Pair-reviewed with maimai77)
Thank you for reviewing fix-29581.patch.
As you point out, it seems natural to use the same sort criteria as IssueQuery#default_sort_criteria.
I have attached the file to fixed patch.
Updated by vzvu 3k6k almost 5 years ago
Thank you for your response! Your v2 patch looks good to me.
Updated by Seiei Miyagi almost 5 years ago
'issues.id DESC'
Is it OK to write table name of the Issue model directly?
In app/models/issue_query.rb, It seems code like following is more preferable.
"#{Issue.table_name}.id DESC"
Updated by Mizuki ISHIKAWA almost 5 years ago
- File fix-29581-v3.patch fix-29581-v3.patch added
Seiei Miyagi wrote:
'issues.id DESC'
Is it OK to write table name of the Issue model directly?
In app/models/issue_query.rb, It seems code like following is more preferable.[...]
Thank you for pointing it out.
I changed the way of writing table names.
Updated by Jean-Philippe Lang almost 5 years ago
- Status changed from New to Closed
- Assignee set to Jean-Philippe Lang
- Resolution set to Fixed
Patch committed, thanks.
Updated by Go MAEDA over 4 years ago
- Has duplicate Defect #31924: Paging misses some entries added
Updated by Go MAEDA about 4 years ago
- Related to Defect #32737: Duplicate sort keys for issue query cause SQL error with SQL Server added