Redmine

The following tests randomly fail on MySQL:

• IssuesControllerTest#test_index_sort_by_spent_hours
• IssuesControllerTest#test_index_sort_by_total_spent_hours

For example:

Failure:
IssuesControllerTest#test_index_sort_by_total_spent_hours [test/functional/issues_controller_test.rb:1432]:
--- expected
+++ actual
@@ -1 +1 @@
-[154.25, 1.0, 0.0, 0.0, 0.0, 0.0]
+[154.25, 0.0, 0.0, 0.0, 1.0, 0.0]

bin/rails test test/functional/issues_controller_test.rb:1428

The subquery used to sort issues by spent time contains the condition generated by Project.allowed_to_condition, which includes projects.id NOT IN (SELECT project_id FROM members WHERE ...). This NOT IN subquery returns the projects where the user or its builtin group ("Anonymous users" or "Non member users") is a member. In the test, the user is anonymous and neither the user nor "Anonymous users" is a member of any project, so the subquery returns no rows.

In this case, MySQL appears to return incorrect results depending on the execution plan: the sum of spent hours computed for sorting can be wrong, so the issues are sorted incorrectly. I observed this on MySQL 8.4.13 and 9.4.0, while MySQL 8.0.46 returns correct results for the same query.

The attached patch omits the subquery when the user and its builtin group have no memberships. The omitted condition is always true in that case, so the result does not change.

Besides fixing the test failure, this change is an improvement in itself: the generated SQL becomes simpler for such users, regardless of the database.