Error 500 when listing merge requests with approver and sorting by merged date
- Sentry error: https://sentry.gitlab.net/gitlab/gitlabcom/issues/3328667
- Kibana errors: https://log.gprd.gitlab.net/goto/9e416b60-3ac1-11ed-8656-f5f2137823ba
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]