`#where` generates wrong query in case of self-referencing polymorphic relation

tim-semba commented 2 weeks ago

#where generates wrong query in case of self-referencing polymorphic relation

Steps to reproduce

Tables:

class Migration < ActiveRecord::Migration[7.1]
  def change
    create_table :posts do |t|
      t.string :title
      t.string :body
    end

    create_table :comments do |t|
      t.string :body
      t.references :parent, polymorphic: true
    end
  end
end

Models:

class Post < ApplicationRecord
  has_many :comments, as: :parent
end

class Comment < ApplicationRecord
  # Parent models: Post or Comment (allows nested comment)
  belongs_to :parent, polymorphic: true
  has_many :comments, as: :parent
end

IRB:

irb> Comment.joins(:comments).where(comments: { body: 'test' }).to_sql # NG
=> "SELECT `comments`.* FROM `comments` INNER JOIN `comments` `comments_comments` ON `comments_comments`.`parent_type` = 'Comment' AND `comments_comments`.`parent_id` = `comments`.`id` WHERE `comments`.`body` = 'test'"

FYI:
irb> Comment.joins(:comments).where(body: 'test').to_sql # OK
=> "SELECT `comments`.* FROM `comments` INNER JOIN `comments` `comments_comments` ON `comments_comments`.`parent_type` = 'Comment' AND `comments_comments`.`parent_id` = `comments`.`id` WHERE `comments`.`body` = 'test'"

irb> Post.joins(:comments).where(comments: { body: 'test' }).to_sql # OK
=> "SELECT `posts`.* FROM `posts` INNER JOIN `comments` ON `comments`.`parent_type` = 'Post' AND `comments`.`parent_id` = `posts`.`id` WHERE `comments`.`body` = 'test'"

Expected behavior

Comment.joins(:comments).where(comments: { body: 'test' }).to_sql should generate

SELECT `comments`.*
FROM `comments`
INNER JOIN `comments` `comments_comments` ON `comments_comments`.`parent_type` = 'Comment' AND `comments_comments`.`parent_id` = `comments`.`id`
WHERE `comments_comments`.`body` = 'test'

Actual behavior

Comment.joins(:comments).where(comments: { body: 'test' }).to_sql generates

SELECT `comments`.*
FROM `comments`
INNER JOIN `comments` `comments_comments` ON `comments_comments`.`parent_type` = 'Comment' AND `comments_comments`.`parent_id` = `comments`.`id`
WHERE `comments`.`body` = 'test'
--     ^^^^^^^^ Wrong table

System configuration

Rails version: 7.2,1 and 7.1.0

Ruby version: 3.3.0

kabirpathak commented 2 weeks ago

Reproduction steps:

# frozen_string_literal: true

require "bundler/inline"

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

  gem "rails", github: "rails/rails", branch: "main"
  gem "pry"
  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 :posts, force: true do |t|
    t.string :body
  end

  create_table :comments, force: true do |t|
    t.string :body
    t.references :parent, polymorphic: true
  end
end

class Post < ActiveRecord::Base
  has_many :comments, as: :parent
end

class Comment < ActiveRecord::Base
  belongs_to :parent, polymorphic: true
  has_many :comments, as: :parent
end

class BugTest < Minitest::Test
  def test_association_stuff
    post = Post.create(body: 'post')
    comment = post.comments.create(body: 'comment')
    nested_comment = comment.comments.create(body: 'nested_comment')
    sql = Comment.joins(:comments).where(comments: { body: 'nested_comment' }).to_sql
    puts sql

    assert_match(/comments_comments\.`body`/, sql) # fails
  end
end

Ruby 3.3.0 Rails 8.0.0.beta1

I'd like to open a PR to address this.

kabirpathak commented 1 week ago

On looking deeper, its seem this is not exactly a bug since other methods like pluck behave similarly. For example:

Comment.joins(:comments).pluck(:id) # comments.id
SELECT "comments"."id" FROM "comments" INNER JOIN "comments" "comments_comments" ON "comments_comments"."parent_type" = ? AND "comments_comments"."parent_id" = "comments"."id"  [["parent_type", "Comment"]]

Comment.joins(:comments).pluck(comments: [:id] ) # comments.id
SELECT "comments"."id" FROM "comments" INNER JOIN "comments" "comments_comments" ON "comments_comments"."parent_type" = ? AND "comments_comments"."parent_id" = "comments"."id"  [["parent_type", "Comment"]]

Comment.joins(:comments).pluck(comments_comments: [:id] ) # comments_comments.id
SELECT "comments_comments"."id" FROM "comments" INNER JOIN "comments" "comments_comments" ON "comments_comments"."parent_type" = ? AND "comments_comments"."parent_id" = "comments"."id"  [["parent_type", "Comment"]]

Based on what I could understand from the code, joins method makes use of ActiveRecord::Relation's @values hash to add alias to the final query.

Comments.joins(:comments) This will result in ActiveRecord::Relation object having .joins_values = [:comments] And this is passed on to where in case of method chaining (which is the case here), but is not made use of in build_where_clause.

I'm not sure if this change will be accepted? Maybe someone else could confirm?

rails / rails