ActiveRecord 6.0.4 introduces extra JOIN on certain through assciations, breaking multi-database preloading

[qnighy]

Steps to reproduce

Here is the reproduction script:

require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'
  gem 'activerecord', '6.0.4'  # error
  # gem 'activerecord', '6.0.3.7'  # OK
  gem 'sqlite3'
end

require 'active_record'
require 'minitest'
require 'logger'
require 'tmpdir'

tmpdir = Dir.mktmpdir
at_exit { FileUtils.remove_entry tmpdir }

main_db = File.join(tmpdir, 'main.sqlite3')
sub_db = File.join(tmpdir, 'sub.sqlite3')
ActiveRecord::Base.configurations = {
  'default_env' => {
    'primary' => {
      'adapter' => 'sqlite3',
      'database' => main_db
    },
    'sub' => {
      'adapter' => 'sqlite3',
      'database' => sub_db
    }
  }
}

ActiveRecord::Base.logger = Logger.new(STDOUT)
# ActiveRecord::Base.logger.level = 0

ActiveRecord::Base.establish_connection(:primary)
ActiveRecord::Schema.define do
  create_table :users, force: true do |t|
  end

  create_table :posts, force: true do |t|
    t.integer :user_id
    t.datetime :created_at
  end
end

ActiveRecord::Base.establish_connection(:sub)
ActiveRecord::Schema.define do
  create_table :post_images, force: true do |t|
    t.integer :post_id
    t.string :purpose
  end
end


class User < ActiveRecord::Base
  has_many :posts

  has_one :latest_post, -> { order(created_at: :desc) }, class_name: 'Post'
  has_one :cover_image, through: :latest_post
end

class Post < ActiveRecord::Base
  belongs_to :user
  has_many :post_images
  has_one :cover_image, -> { where(purpose: 'cover_image') }, class_name: 'PostImage'
end

class PostImage < ActiveRecord::Base
  belongs_to :post
end

User.establish_connection(:primary)
Post.establish_connection(:primary)
PostImage.establish_connection(:sub)

class BugTest < Minitest::Test
  def test_foo
    user = User.create!
    post = user.posts.create!
    post_image = post.post_images.create!(purpose: 'inline')
    user = User.preload(:cover_image).find(user.id)
    assert_nil user.cover_image
  end
end
Minitest.autorun

Expected behavior

The test passes.

Actual behavior

The test fails with exception ActiveRecord::StatementInvalid: SQLite3::SQLException: no such table: post_images.

System configuration

Rails version: 6.0.4

Ruby version: 2.7.3

Bisection points to a415e80 from #39378.

Previously it preloaded the through association step by step. To load User#cover_image, it first loads User#latest_post and then Post#cover_image, allowing the latter to use WHERE .. IN loading strategy.

After a415e80 it dispatches a query that directly loads User#cover_image. During the query's construction it demands to check post_image.purpose = 'cover_image', leading to a joined query.

[ghiculescu]

Do you happen to know if this is still an issue in 6.1? According to https://guides.rubyonrails.org/maintenance_policy.html#bug-fixes 6.0 is only getting security patches. I'm not sure how likely it is that there will be more releases in that series.