Error 500 when listing merge requests with approver and sorting by merged date

G::GroupingError: ERROR:  column "merge_request_metrics.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..."."merged_at" AS merge_request_metrics_merged_at, "merge_req...
                                                             ^
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/postgresql_adapter.rb:672:in `exec_params'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/postgresql_adapter.rb:672:in `block (2 levels) in exec_no_cache'
  from activesupport (6.1.6.1) lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
  from activesupport (6.1.6.1) lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
  from activesupport (6.1.6.1) lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/postgresql_adapter.rb:671:in `block in exec_no_cache'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/abstract_adapter.rb:696:in `block (2 levels) in log'
  from activesupport (6.1.6.1) lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
  from activesupport (6.1.6.1) lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
  from activesupport (6.1.6.1) lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
  from activesupport (6.1.6.1) lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
  from activesupport (6.1.6.1) lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/abstract_adapter.rb:695:in `block in log'
  from activesupport (6.1.6.1) lib/active_support/notifications/instrumenter.rb:24:in `instrument'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/abstract_adapter.rb:687:in `log'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/postgresql_adapter.rb:670:in `exec_no_cache'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/postgresql_adapter.rb:649:in `execute_and_clear'
  from marginalia (1.10.0) lib/marginalia.rb:95:in `execute_and_clear_with_marginalia'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/postgresql/database_statements.rb:55:in `exec_query'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/abstract/database_statements.rb:532:in `select'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/abstract/database_statements.rb:69:in `select_all'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/abstract/query_cache.rb:101:in `block in select_all'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/abstract/query_cache.rb:118:in `block in cache_sql'
  from activesupport (6.1.6.1) lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
  from activesupport (6.1.6.1) lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
  from activesupport (6.1.6.1) lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
  from activesupport (6.1.6.1) lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
  from activesupport (6.1.6.1) lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/abstract/query_cache.rb:109:in `cache_sql'
  from activerecord (6.1.6.1) lib/active_record/connection_adapters/abstract/query_cache.rb:101:in `select_all'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:104:in `block in read_using_load_balancer'
  from lib/gitlab/database/load_balancing/load_balancer.rb:55:in `read'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:103:in `read_using_load_balancer'
  from lib/gitlab/database/load_balancing/connection_proxy.rb:48:in `select_all'
  from activerecord (6.1.6.1) lib/active_record/querying.rb:47:in `find_by_sql'
  from activerecord (6.1.6.1) lib/active_record/relation.rb:843:in `block in exec_queries'
  from activerecord (6.1.6.1) lib/active_record/relation.rb:861:in `skip_query_cache_if_necessary'
  from activerecord (6.1.6.1) lib/active_record/relation.rb:828:in `exec_queries'
  from activerecord (6.1.6.1) lib/active_record/relation.rb:631:in `load'
  from activerecord (6.1.6.1) lib/active_record/relation.rb:249:in `records'
  from activerecord (6.1.6.1) lib/active_record/relation/delegation.rb:88:in `each'
  from lib/gitlab/issuable_metadata.rb:67:in `map'
  from lib/gitlab/issuable_metadata.rb:67:in `block in issuable_ids'
  from lib/gitlab/utils/strong_memoize.rb:44:in `strong_memoize'
  from lib/gitlab/issuable_metadata.rb:62:in `issuable_ids'
  from lib/gitlab/issuable_metadata.rb:28:in `data'
  from app/controllers/concerns/issuable_collections_action.rb:29:in `merge_requests'
  from actionpack (6.1.6.1) lib/action_controller/metal/basic_implicit_render.rb:6:in `send_action'

Sample error link: https://gitlab.com/gitlab-org/charts/gitlab/-/merge_requests?scope=all&state=opened&approver_usernames[]=stanhu

SQL:

/*application:web,correlation_id:01GDKK4TE064V02WSB0BHQB4CX,endpoint_id:DashboardController#merge_requests,db_config_name:main_replica*/ SELECT "merge_requests".*, "merge_request_metrics"."merged_at" AS merge_request_metrics_merged_at, "merge_request_metrics"."id" AS merge_request_metrics_id FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT $1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $2 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= $3)) OR projects.visibility_level IN ($4,$5)) AND ("project_features"."merge_requests_access_level" IS NULL OR "project_features"."merge_requests_access_level" IN ($6,$7) OR ("project_features"."merge_requests_access_level" = $8 AND EXISTS (SELECT $9 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $10 AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= $11)))) AND "projects"."archived" = $12 AND "merge_requests"."id" IN (SELECT "merge_requests"."id" FROM ((SELECT "merge_requests".* FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" INNER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" INNER JOIN "approval_merge_request_rules_users" ON "approval_merge_request_rules_users"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id" INNER JOIN "users" ON "users"."id" = "approval_merge_request_rules_users"."user_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT $13 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $14 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= $15)) OR projects.visibility_level IN ($16,$17)) AND ("project_features"."merge_requests_access_level" IS NULL OR "project_features"."merge_requests_access_level" IN ($18,$19) OR ("project_features"."merge_requests_access_level" = $20 AND EXISTS (SELECT $21 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $22 AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= $23)))) AND "projects"."archived" = $24 AND "users"."username" = $25 GROUP BY "merge_requests"."id" HAVING (COUNT(users.id) = $26))
UNION
(SELECT "merge_requests".* FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" INNER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" INNER JOIN "approval_merge_request_rules_groups" ON "approval_merge_request_rules_groups"."approval_merge_request_rule_id" = "approval_merge_request_rules"."id" INNER JOIN "namespaces" ON "namespaces"."id" = "approval_merge_request_rules_groups"."group_id" AND "namespaces"."type" = $27 INNER JOIN "members" ON "members"."source_type" = $28 AND "members"."requested_at" IS NULL AND "members"."access_level" != $29 AND "members"."source_id" = "namespaces"."id" AND "members"."type" = $30 INNER JOIN "users" ON "users"."id" = "members"."user_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT $31 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $32 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= $33)) OR projects.visibility_level IN ($34,$35)) AND ("project_features"."merge_requests_access_level" IS NULL OR "project_features"."merge_requests_access_level" IN ($36,$37) OR ("project_features"."merge_requests_access_level" = $38 AND EXISTS (SELECT $39 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $40 AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= $41)))) AND "projects"."archived" = $42 AND "users"."username" = $43 GROUP BY "merge_requests"."id" HAVING (COUNT(users.id) = $44))
UNION
(SELECT "merge_requests".* FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id" INNER JOIN "approval_project_rules_users" ON "approval_project_rules_users"."approval_project_rule_id" = "approval_project_rules"."id" INNER JOIN "users" ON "users"."id" = "approval_project_rules_users"."user_id" LEFT OUTER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT $45 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $46 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= $47)) OR projects.visibility_level IN ($48,$49)) AND ("project_features"."merge_requests_access_level" IS NULL OR "project_features"."merge_requests_access_level" IN ($50,$51) OR ("project_features"."merge_requests_access_level" = $52 AND EXISTS (SELECT $53 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $54 AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= $55)))) AND "projects"."archived" = $56 AND "approval_merge_request_rules"."id" IS NULL AND "users"."username" = $57 GROUP BY "merge_requests"."id" HAVING (COUNT(users.id) = $58))
UNION
(SELECT "merge_requests".* FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" INNER JOIN "approval_project_rules" ON "approval_project_rules"."project_id" = "projects"."id" INNER JOIN "approval_project_rules_groups" ON "approval_project_rules_groups"."approval_project_rule_id" = "approval_project_rules"."id" INNER JOIN "namespaces" ON "namespaces"."id" = "approval_project_rules_groups"."group_id" AND "namespaces"."type" = $59 INNER JOIN "members" ON "members"."source_type" = $60 AND "members"."requested_at" IS NULL AND "members"."access_level" != $61 AND "members"."source_id" = "namespaces"."id" AND "members"."type" = $62 INNER JOIN "users" ON "users"."id" = "members"."user_id" LEFT OUTER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT $63 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $64 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= $65)) OR projects.visibility_level IN ($66,$67)) AND ("project_features"."merge_requests_access_level" IS NULL OR "project_features"."merge_requests_access_level" IN ($68,$69) OR ("project_features"."merge_requests_access_level" = $70 AND EXISTS (SELECT $71 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $72 AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= $73)))) AND "projects"."archived" = $74 AND "approval_merge_request_rules"."id" IS NULL AND "users"."username" = $75 GROUP BY "merge_requests"."id" HAVING (COUNT(users.id) = $76))) merge_requests INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT $77 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $78 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= $79)) OR projects.visibility_level IN ($80,$81)) AND ("project_features"."merge_requests_access_level" IS NULL OR "project_features"."merge_requests_access_level" IN ($82,$83) OR ("project_features"."merge_requests_access_level" = $84 AND EXISTS (SELECT $85 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $86 AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= $87)))) AND "projects"."archived" = $88) AND "merge_requests"."target_project_id" = "merge_request_metrics"."target_project_id" GROUP BY "merge_requests"."id", "merge_request_metrics"."merged_at" ORDER BY "merge_request_metrics"."merged_at" DESC NULLS LAST, "merge_request_metrics"."id" DESC LIMIT $90 OFFSET $89

I think this patch fixes the problem:

diff --git a/app/models/concerns/issuable.rb b/app/models/concerns/issuable.rb
index 1c325a32aad..e6087b613a8 100644
--- a/app/models/concerns/issuable.rb
+++ b/app/models/concerns/issuable.rb
@@ -383,7 +383,8 @@ def grouping_columns(sort)
         milestone_table = Milestone.arel_table
         grouping_columns << milestone_table[:id]
         grouping_columns << milestone_table[:due_date]
-      elsif %w(merged_at_desc merged_at_asc).include?(sort)
+      elsif %w(merged_at_desc merged_at_asc merged_at).include?(sort)
+        grouping_columns << MergeRequest::Metrics.arel_table[:id]
         grouping_columns << MergeRequest::Metrics.arel_table[:merged_at]
       elsif %w(closed_at_desc closed_at_asc).include?(sort)
         grouping_columns << MergeRequest::Metrics.arel_table[:closed_at]