Alias table name (Arel) issues on Ruby 3 branch
On Ruby 3 branch, some specs are failing, example jobs:
- https://gitlab.com/gitlab-org/gitlab/-/jobs/2263905559
- https://gitlab.com/gitlab-org/gitlab/-/jobs/2263905530
- https://gitlab.com/gitlab-org/gitlab/-/jobs/2263905564
@nmilojevic1
looked into the reason behind the failures.
The issue is with Arel and alias table name support when using ORDER BY for joined associations, this wasn't huge problem before, because it could just “guess” the table alias and write raw SQL. This has changed somehow.
The issue is related to our ::Gitlab::Database.nulls_order implementation,
Lets look for example at Issue.order_escalation_status_desc
# issue.rb
scope :order_escalation_status_desc, -> { includes(:incident_management_issuable_escalation_status).order(::Gitlab::Database.nulls_last_order('incident_management_issuable_escalation_status.status', 'DESC')
It actually executes:
Issue.includes(:incident_management_issuable_escalation_status).order(Arel.sql('incident_management_issuable_escalation_status.status DESC NULLS LAST'))
'ruby-3-squashed' branch
In our 'ruby-3-squashed' branch, it breaks, because INNER JOIN incident_management_issuable_escalation_statuses
has no generated alias:
SELECT \"issues\".* FROM \"issues\" INNER JOIN \"incident_management_issuable_escalation_statuses\" ON \"incident_management_issuable_escalation_statuses\".\"issue_id\" = \"issues\".\"id\" ORDER BY incident_management_issuable_escalation_status.status DESC NULL LAST
Error:
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "incident_management_issuable_escalation_status"
LINE 1: ...ion_statuses"."issue_id" = "issues"."id" ORDER BY incident_m...
^
from /Users/nikola/.asdf/installs/ruby/3.0.3/lib/ruby/gems/3.0.0/gems/activerecord-6.1.4.6/lib/active_record/connection_adapters/postgresql_adapter.rb:672:in `exec_params'
Caused by PG::UndefinedTable: ERROR: missing FROM-clause entry for table "incident_management_issuable_escalation_status"
LINE 1: ...ion_statuses"."issue_id" = "issues"."id" ORDER BY incident_m...
^
'master' branch
On our master
branch it doesn't break brecause INNER JOIN incident_management_issuable_escalation_statuses
has alias incident_management_issuable_escalation_status
.
SELECT \"issues\".* FROM \"issues\" INNER JOIN \"incident_management_issuable_escalation_statuses\" \"incident_management_issuable_escalation_status\" ON \"incident_management_issuable_escalation_status\".\"issue_id\" = \"issues\".\"id\" ORDER BY incident_management_issuable_escalation_status.status DESC NULL LAST
There is an interesting article mentioning a similar issue in Rails 6.1.
The strange part is that Arel is part of ActiveRecord, which is integrated into rails. And we didn't update the rails version in the ruby-3 branch, we use the same version of ActiveRecord on both the ruby3 and master branch. But I can reproduce that alias is behaving differently.
We can fix this by using arel nulls_first and nulls_last instead of our custom implementation of raw sql literals in ::Gitlab::Database.nulls_order implementation, since Rails 6.1
introduced native support for nulls_first
and nulls_last
.