500 error while sorting MRs with `milestone due date` criteria on MR user dashboard

Summary

While trying to run a filter on MRs in dashboard page with Approved By as one of the filter criteria, page runs into 500 error. Sentry log of the same is attached below.

Steps to reproduce.

  • Go to your MR dashboard on https://gitlab.com/dashboard/merge_requests
  • Go to Merged tab.
  • Add filter Approved By with any user selection, by default this can potentially try to load a lot of matches so you can additionally add Milestone as one more criteria.
  • Sort by Milestone Due Date
  • Hit Enter/Return to apply the filter.

Page runs into 500 error instead of loading the matching MRs.

Sentry Details

https://sentry.gitlab.net/gitlab/gitlabcom/issues/1670818/?referrer=gitlab_plugin

PG::GroupingError: ERROR:  column "milestones.due_date" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...uests"."id" HAVING (COUNT(users.id) = 1) ORDER BY milestones...
                                                             ^

  active_record/connection_adapters/postgresql_adapter.rb:675:in `exec_params'
    @connection.exec_params(sql, type_casted_binds)
  active_record/connection_adapters/postgresql_adapter.rb:675:in `block (2 levels) in exec_no_cache'
    @connection.exec_params(sql, type_casted_binds)
  active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
    yield
  active_support/concurrency/share_lock.rb:187:in `yield_shares'
    yield
  active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
    @lock.yield_shares(compatible: [:load]) do
...
(175 additional frame(s) were not displayed)

ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column "milestones.due_date" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...uests"."id" HAVING (COUNT(users.id) = 1) ORDER BY milestones...
                                                             ^

ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column "milestones.due_date" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...uests"."id" HAVING (COUNT(users.id) = 1) ORDER BY milestones...
                                                             ^

🛠 Workarounds

Change the sort order

Change the sort order to something other than Milestone due date.

See this comment for more info.

Use the API

From this comment, the following workaround works in some environments:

Use the Merge Request API with the approved_by_ids filter.

https://gitlab.example.com/api/v4/merge_requests?scope=all&state=opened&approved_by_ids[]=12345
Edited by Brie Carranza