Ambiguous column name when using `scope.from`

[eileencodes]

We bumped GitHub to test Rails 6 and had a bunch of failures.

One of them was like this:

Column 'id' in field list is ambiguous

After doing a bisect I found this commit was the cause 0ee96d1 (cc/ @kamipo) but wasn't immediately able to reproduce it and wanted to verify we weren't doing something weird before pinging you.

It seems that this happens when we use from on a given scope and then pluck from that scope.

Here's a reproduction script with SQLite3:

# frozen_string_literal: true

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

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 :title
    t.integer :author_id
  end 

  create_table :categories, force: true do |t| 
    t.string :name
  end 

  create_table :categorizations, force: true do |t| 
    t.integer :post_id
    t.integer :category_id
  end 
end

class Post < ActiveRecord::Base
  has_many :categorizations
  has_many :categories, through: :categorizations
end

class Category < ActiveRecord::Base
  has_many :categorizations
  has_many :posts, through: :categorizations
end

class Categorization < ActiveRecord::Base
  belongs_to :post
  belongs_to :category
end

class BugTest < Minitest::Test
  def test_association_stuff
    p = Post.create!(title: "a post")

    p.categorizations.build(category: Category.create!(name: "a category"))

    scope = p.categories
    # this is the problematic line
    scope = scope.from("#{scope.quoted_table_name}")
    # throws a ambiguous column error
    scope.joins(:posts).pluck(:id)
  end
end

With this change the sql looks like:

SELECT "id" FROM "categories" INNER JOIN "categorizations" "categorizations_categories_join" ON "categorizations_categories_join"."category_id" = "categories"."id" INNER JOIN "posts" ON "posts"."id" = "categorizations_categories_join"."post_id" INNER JOIN "categorizations" ON "categories"."id" = "categorizations"."category_id" WHERE "categorizations"."post_id" = ?  [["post_id", 1]]

Without the change the sql looks like:

SELECT "categories"."id" FROM "categories" INNER JOIN "categorizations" "categorizations_categories_join" ON "categorizations_categories_join"."category_id" = "categories"."id" INNER JOIN "posts" ON "posts"."id" = "categorizations_categories_join"."post_id" INNER JOIN "categorizations" ON "categories"."id" = "categorizations"."category_id" WHERE "categorizations"."post_id" = ?  [["post_id", 1]]

cc/ @tenderlove @jhawthorn @cpruitt for visibility

[eileencodes]

Thanks for the quick fix @kamipo! 🎉

[eileencodes]

Hey @kamipo I'm reopening because this fixed one bug and the script but our use of from was more complex than I reproduced. Here's a new script showing the bug.

The change you linked requires that the FromClause has a name present or it uses the value. In this case there is no name and the value is not a single table name when passing the table name and the index with the from.

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

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 :title
    t.integer :author_id
  end 

  create_table :categories, force: true do |t| 
    t.string :name
    t.index [:name], name: "my_index"
  end 

  create_table :categorizations, force: true do |t| 
    t.integer :post_id
    t.integer :category_id
  end 
end

class Post < ActiveRecord::Base
  has_many :categorizations
  has_many :categories, through: :categorizations
end

class Category < ActiveRecord::Base
  has_many :categorizations
  has_many :posts, through: :categorizations
end

class Categorization < ActiveRecord::Base
  belongs_to :post
  belongs_to :category
end

class BugTest < Minitest::Test
  def test_association_stuff
    p = Post.create!(title: "a post")

    p.categorizations.build(category: Category.create!(name: "a category"))

    scope = p.categories
    # this is the problematic line
    scope = scope.from("#{scope.quoted_table_name} INDEXED BY my_index")
    # throws a ambiguous column error
    scope.joins(:posts).pluck(:id)
  end
end

[kamipo]

I see... that is a valid use case to use index hint.
I've relaxed the table name detection in #35441.

[eileencodes]

Checked our build and everything passed. Thanks @kamipo!