Broken table alias referencing in Rails 7.0.7

[kaoru]

Steps to reproduce

We're using a combination of "where missing" and "or + scope" to find goals without a state, either because they have no state record or because they have a state record with a NULL state column on it.

The behaviour has changed in Rails 7.0.7 to throw an ActiveRecord::StatementInvalid error, because on 7.0.6 the left joined table was not given an alias but on 7.0.7 it is given an alias and that alias is then not consistently used in the where clauses produced.

Note this looks a lot like https://github.com/rails/rails/issues/48334 but that bug is fixed on 7.0.6 and 7.0.7.

require "bundler/inline"

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

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

  # gem 'rails', '~> 7.0', '= 7.0.6'
  gem 'rails', '~> 7.0', '= 7.0.7'
  # gem 'rails', github: 'rails/rails'
  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 :goals, force: true do |t|
  end

  create_table :goal_states, force: true do |t|
    t.integer :goal_id
    t.string :state
  end
end

class Goal < ActiveRecord::Base
  has_one :state, class_name: 'GoalState', dependent: :destroy

  scope :no_state, -> { where.missing(:state).or(left_joins(:state).merge(GoalState.not_set)) }
end

class GoalState < ActiveRecord::Base
  belongs_to :goal

  scope :set, -> { where.not(state: nil) }
  scope :not_set, -> { where(state: nil) }
end

class BugTest < Minitest::Test
  def setup
    @g1 = Goal.create

    @g2 = Goal.create
    @g2.create_state(state: nil)

    @g3 = Goal.create
    @g3.create_state(state: 'cool')
  end

  # This works on Rails 7.0.6 and gives an ActiveRecord::StatementInvalid on
  # Rails 7.0.7 because the table alias behaviour has changed.
  def test_no_state
    puts
    puts Goal.no_state.to_sql
    puts

    assert_equal 2, Goal.no_state.count
    assert_equal Goal.no_state, [@g1, @g2]
  end
end

Expected behavior

On Rails 7.0.6 the SQL produced is:

SELECT "goals".* FROM "goals" LEFT OUTER JOIN "goal_states" ON "goal_states"."goal_id" = "goals"."id" WHERE ("goal_states"."id" IS NULL OR "goal_states"."state" IS NULL)

Actual behavior

On Rails 7.0.7 (and on the main branch of rails/rails) the SQL produced is:

SELECT "goals".* FROM "goals" LEFT OUTER JOIN "goal_states" "state" ON "state"."goal_id" = "goals"."id" WHERE ("state"."id" IS NULL OR "goal_states"."state" IS NULL)

Which throws an error:

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: goal_states.state

Because the missing gives the left join an alias of "state" but the check produced by the "OR" uses the table name "goal_states".

I've fixed this in our codebase by changing the code to scope :no_status, -> { left_joins(:status).merge(Goals::Status.not_set) } because technically with a left join the missing check is redundant anyway! But I thought I'd report the bug in case it's affecting other people with harder-to-fix query cases.

System configuration

Rails version: 7.0.7

Ruby version: 3.2.2