Usage of id enum key results in unexpected filter with joins and subqueries

[nmichels]

Steps to reproduce

# frozen_string_literal: true

require "bundler/inline"
require "active_record"
require "minitest/autorun"
require "logger"

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :offers, force: true do |t|
    t.string :status
    t.integer :account_id
  end
  create_table :accounts, force: true do |t|
    t.string :name
    t.integer :group_id
  end
  create_table :groups, force: true do |t|
    t.string :name
    t.string :external_code
    t.integer :external_integration_attr
    t.integer :account_id
  end
  create_table :transactions, force: true do |t|
    t.integer :group_id
    t.decimal :amount
  end
end

class Offer < ActiveRecord::Base
  belongs_to :account
end

class Account < ActiveRecord::Base
  belongs_to :group
end


class Group < ActiveRecord::Base
  has_one :account
  enum external_integration_attr: ["id", "externalCode"]
end

class Transaction < ActiveRecord::Base
  belongs_to :group
end

class AddOfferAccountGroupAndTransactions < ActiveRecord::Migration[7.0]
  def change
    reversible do |dir|
      dir.up do
        Group.new(name: "Foo", external_integration_attr: "externalCode").save!
        Account.new(name: "Bar", group: Group.first).save!
        Offer.new(status: "approved", account: Account.first).save!
        Transaction.new(amount: 10, group: Group.first).save!
      end

      dir.down do
      end
    end
  end
end

class BugTest < Minitest::Test
  def test_migration_up
    
    AddOfferAccountGroupAndTransactions.migrate(:up)

    transactions = 
      Transaction
        .where(group_id: Group.joins(:account)
          .where(account: { id:  Offer.where(status: "approved").select(:account_id) })
          .select(:id))

    assert_equal 1, transactions.count
  end
end

Expected behavior

Query does not have the following filter: AND "groups"."external_integration_attr" = ? (["external_integration_attr", 0])

Actual behavior

Query has the following filter AND "groups"."external_integration_attr" = ? (["external_integration_attr", 0])

System configuration

Rails version:
Current main branch and 6.1.7.3

Ruby version:
Ruby 3.1.0 (alongside main branch) and Ruby 2.7.7 + Rails 6.1.7.3

[ghiculescu]

executable test case:

# frozen_string_literal: true

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"

  git_source(:github) { |repo| "https://github.com/#{repo}.git" }

  gem "rails", github: "rails/rails", branch: "main"
  gem "sqlite3"
end

require "active_record"
require "minitest/autorun"
require "logger"

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :offers, force: true do |t|
    t.string :status
    t.integer :account_id
  end
  create_table :accounts, force: true do |t|
    t.string :name
    t.integer :group_id
  end
  create_table :groups, force: true do |t|
    t.string :name
    t.string :external_code
    t.integer :external_integration_attr
    t.integer :account_id
  end
  create_table :transactions, force: true do |t|
    t.integer :group_id
    t.decimal :amount
  end
end

class Offer < ActiveRecord::Base
  belongs_to :account
end

class Account < ActiveRecord::Base
  belongs_to :group
end

class Group < ActiveRecord::Base
  has_one :account
  enum external_integration_attr: ["id", "externalCode"]
end

class Transaction < ActiveRecord::Base
  belongs_to :group
end

class BugTest < Minitest::Test
  def test_migration_up
    Group.new(name: "Foo", external_integration_attr: "externalCode").save!
    Account.new(name: "Bar", group: Group.first).save!
    Offer.new(status: "approved", account: Account.first).save!
    Transaction.new(amount: 10, group: Group.first).save!

    groups = Group.joins(:account).where(account: { id: Offer.where(status: "approved").select(:account_id) })
    assert_equal 1, groups.count
    assert_equal 1, groups.select(:id).to_a.count

    transactions = Transaction.where(group_id: groups.pluck(:id))
    assert_equal 1, transactions.count

    transactions = Transaction.where(group_id: groups.select(:id))
    assert_equal 1, transactions.count
  end
end

[ghiculescu]

As a workaround, enum external_integration_attr: ["id", "externalCode"], _prefix: true solves the issue.

[ghiculescu]

I don't think we should allow this in Rails: #48527