Joining different subclasses of STI table produces wrong SQL (alias missing in WHERE clause)

[acanthite1855]

Hi,

The code is just an example, because I'm not allowed to share sources of my project. I need to filter Parent records based on a condition for Item1 and another condition for Item2. Item1 and Item2 are subclasses of STI class Item. Parent has has_many for both of the subclasses through a mapping table (ItemParent record). The problem is that the generated SQL has wrong condition for Item2. Though it generated an alias for the join condition, it does not use the alias in where clause.

Steps to reproduce

See the code below.

# frozen_string_literal: true

require "bundler/inline"

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

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

  # Activate the gem you are reporting the issue against.
  gem "activerecord", "~> 7.0.0"
  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

  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"

  create_table "item_parents", id: :serial, force: :cascade do |t|
    t.integer "item_id", null: false
    t.integer "parent_id", null: false
    t.index %w[item_id parent_id], name: "item_parents_item_id_parent_id_uindex", unique: true
  end

  create_table "items", id: :serial, force: :cascade do |t|
    t.string "name", limit: 100, null: false
    t.string "item_type", limit: 100, null: false
  end

  create_table "parents", id: :serial, force: :cascade do |t|
    t.string "name", limit: 100, null: false
  end

  add_foreign_key "item_parents", "items", name: "item_parents_items_id_fk"
  add_foreign_key "item_parents", "parents", name: "item_parents_parents_id_fk"
end

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true
end

class Item < ApplicationRecord
  self.primary_key = 'id'
  self.inheritance_column = 'item_type'

  has_many :item_parents
  has_many :parents, through: :item_parents, class_name: 'Parent', inverse_of: :items
end

class Item::Item1 < Item
end

class Item::Item2 < Item
end

class Parent < ApplicationRecord
  has_many :item_parents, inverse_of: :parent, primary_key: :id, foreign_key: :parent_id

  has_many :item1s, through: :item_parents, class_name: "Item::Item1", source: :item
  has_many :item2s, through: :item_parents, class_name: "Item::Item2", source: :item
end

class ItemParent < ApplicationRecord
  belongs_to :item
  belongs_to :parent, class_name: 'Parent', inverse_of: :ItemParent, primary_key: :parent_id
end

class BugTest < Minitest::Test
  def test_association_stuff

    sql_string = Parent
          .joins(:item1s)
          .where(Item::Item1.arel_table[:name].eq("i1"))
          .joins(:item2s)
          .where(Item::Item2.arel_table[:name].eq("i2"))
          .to_sql

    assert sql_string.include?("JOIN \"item_parents\" \"item_parents_parents_join\"")
    assert sql_string.include?("JOIN \"items\" \"item2s_parents\"")
    assert !sql_string.include?("\"items\".\"name\" = 'i2'"), "Must use an alias for Item2 condition"
  end
end

Expected behavior

SELECT "parents".*
FROM "parents"
         INNER JOIN "item_parents"
                    ON "item_parents"."parent_id" = "parents"."id"
         INNER JOIN "items"
                    ON "items"."id" = "item_parents"."item_id"
                        AND "items"."item_type" = 'Item::Item1'

         INNER JOIN "item_parents" "item_parents_parents_join"
                    ON "item_parents_parents_join"."parent_id" = "parents"."id"
         INNER JOIN "items" "item2s_parents"
                    ON "item2s_parents"."id" = "item_parents_parents_join"."item_id"
                        AND "item2s_parents"."item_type" = 'Item::Item2'

WHERE "items"."name" = 'i1' 
  AND "item2s_parents"."name" = 'i2'

Actual behavior

SELECT "parents".*
FROM "parents"
         INNER JOIN "item_parents"
                    ON "item_parents"."parent_id" = "parents"."id"
         INNER JOIN "items"
                    ON "items"."id" = "item_parents"."item_id"
                        AND "items"."item_type" = 'Item::Item1'

         INNER JOIN "item_parents" "item_parents_parents_join"
                    ON "item_parents_parents_join"."parent_id" = "parents"."id"
         INNER JOIN "items" "item2s_parents" -- OK. join has an alias
                    ON "item2s_parents"."id" = "item_parents_parents_join"."item_id"
                        AND "item2s_parents"."item_type" = 'Item::Item2'

WHERE "items"."name" = 'i1' 
  AND "items"."name" = 'i2' -- Wrong! Must be "item2s_parents"."name" = 'i2'

Another problem is that I cannot use raw SQL queries instead. We have many filters that applied dynamically based on HTTP requests, many scopes and ordering rules. Keeping track of what's already joined will be a nightmare.

Any workarounds for this?

System configuration

Rails version: 6.0.4.8 Ruby version: 2.7.3p183 (2021-04-05 revision 6847ee089d) [arm64-darwin20]

[bk-az]

Hi @lowercase1024 , you are applying similar where conditions in both cases but expecting different table names. Item::Item1 and Item::Item2 both share same table name. What you need to do instead is to tell active record on which table you want to apply conditions like this:

Parent.joins(:item1s)
      .where(items: { name: 'i1' })
      .joins(:item2s)
      .where(item2s_parents: { name: 'i2' })

This will give you the expected results.

[acanthite1855]

@bk-az, well yes, but actually no. As I said, we have many dynamic filters (a table on frontend where user can select columns to filter by). Sometimes user wants to filter only by item1s.name, sometimes only item1s.name, sometimes both. I cannot know beforehand whether a table has or has no alias. It would work however, if there was a way to force ActiveRecord aliasing everything.

And this is just an example, in reality there is many filters: case sensitive equality, case insensitive equality, LIKE, ILIKE, in, not in, etc. There is like 30 filters in total.

[rails-bot[bot]]

This issue has been automatically marked as stale because it has not been commented on for at least three months. The resources of the Rails team are limited, and so we are asking for your help. If you can still reproduce this error on the 7-0-stable branch or on main, please reply with all of the information you have about it in order to keep the issue open. Thank you for all your contributions.