This project is archived and is in readonly mode.

ActiveRecord 3 Regression: Certain includes hashes fail on AR3 that work on AR2.

#5181

Given the following setup:

Book
- name

Author
- first_name - last_name

Page
- page_number - content

And the following relationships:

Author has many Books
Book has many Pages
Book belongs to Author
Page belongs to Book

The following query breaks with ActiveRecord 3.0, that does not on ActiveRecord 2.0.

Author.find(:all, :include => [{:books => :pages}, :books], :conditions => "pages.page_number = 1")

Using Rails 3 scopes also fails:

Author.includes(:books).includes(:books => :pages).where({:pages => {:page_number => 1}})

When using sqlite3 as a database, the backtrace looks like the following:

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: books_authors.id: SELECT "authors"."id" AS t0_r0, "authors"."first_name" AS t0_r1, "authors"."last_name" AS t0_r2, "authors"."created_at" AS t0_r3, "authors"."updated_at" AS t0_r4, "books"."id" AS t1_r0, "books"."name" AS t1_r1, "books"."created_at" AS t1_r2, "books"."updated_at" AS t1_r3, "books"."author_id" AS t1_r4, "pages"."id" AS t2_r0, "pages"."book_id" AS t2_r1, "pages"."page_number" AS t2_r2, "pages"."content" AS t2_r3, "pages"."created_at" AS t2_r4, "pages"."updated_at" AS t2_r5, "books_authors"."id" AS t3_r0, "books_authors"."name" AS t3_r1, "books_authors"."created_at" AS t3_r2, "books_authors"."updated_at" AS t3_r3, "books_authors"."author_id" AS t3_r4 FROM "authors" LEFT OUTER JOIN "books" ON "books"."author_id" = "authors"."id" LEFT OUTER JOIN "pages" ON "pages"."book_id" = "books"."id" WHERE (pages.page_number = 1)

from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/connection_adapters/abstract_adapter.rb:210:in `log'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/connection_adapters/sqlite_adapter.rb:154:in `execute'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/connection_adapters/sqlite_adapter.rb:394:in `catch_schema_changes'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/connection_adapters/sqlite_adapter.rb:154:in `execute'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/connection_adapters/sqlite_adapter.rb:297:in `select'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/connection_adapters/abstract/query_cache.rb:56:in `select_all'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/base.rb:431:in `find_by_sql'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/relation.rb:64:in `to_a'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/relation/finder_methods.rb:182:in `find_with_associations'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/relation.rb:64:in `to_a'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/relation/finder_methods.rb:138:in `all'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/relation/finder_methods.rb:100:in `send'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/relation/finder_methods.rb:100:in `find'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/relation/finder_methods.rb:96:in `find'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/base.rb:403:in `__send__'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.0.0.beta4/lib/active_record/base.rb:403:in `find'
from (irb):8

This does not happen on Rails 2.0. Sample Rails app attached that shows the problem. A current workaround can be to merge the includes hash to be smarter, but since it also happens with scopes it's not possible to fix the problem entirely.

A query that works is the following:

Author.find(:all, :include => {:books => :pages}, :conditions => "pages.page_number = 1")

Or with Rails 3 scopes:

Author.includes(:books => :pages).where({:pages => {:page_number => 1}})

ar_regression.zip 99.3 KB

Reported by clocksarestupid · July 22nd, 2010 @ 10:58 PM

Activity

Neeraj Singh

Importance changed from to Low

works for me with rails edge.

def self.lab
  Project.includes(:tasks => :users).where({:users => {:name => 'John2'}})
end

generates following sql

SELECT "projects"."id" AS t0_r0, "projects"."name" AS t0_r1, 
"projects"."created_at" AS t0_r2, "projects"."updated_at" AS t0_r3, 
"tasks"."id" AS t1_r0, "tasks"."name" AS t1_r1, "tasks"."project_id" AS t1_r2, 
"users"."id" AS t2_r0, "users"."name" AS t2_r1 FROM "projects" 
LEFT OUTER JOIN "tasks" ON "tasks"."project_id" = "projects"."id" 
LEFT OUTER JOIN "tasks_users" ON "tasks_users"."task_id" = "tasks"."id" 
LEFT OUTER JOIN "users" ON "users"."id" = "tasks_users"."user_id" 
WHERE ("users"."name" = 'John2')

ActiveRecord::Schema.define(:version => 20100714212039) do

  create_table "projects", :force => true do |t|
    t.string   "name"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "tasks", :force => true do |t|
    t.string  "name"
    t.integer "project_id", :null => false
  end

  create_table "tasks_users", :id => false, :force => true do |t|
    t.integer "task_id", :null => false
    t.integer "user_id", :null => false
  end

  create_table "users", :force => true do |t|
    t.string "name"
  end

end

class Project < ActiveRecord::Base
  has_many :tasks, :uniq => true

  def self.setup
    Project.delete_all
    Task.delete_all
    User.delete_all

    pr2 = Project.create!(:name => 'pr2')

    task2 = Task.new(:name => 'task2')
    task2.project_id = pr2.id
    task2.save!

    task2.users.create(:name => 'John1')
    task2.users.create(:name => 'John2')
    task2.users.create(:name => 'John3')
  end

  def self.lab
    Project.includes(:tasks => :users).where({:users => {:name => 'John2'}})
  end
end

class Task < ActiveRecord::Base
  belongs_to :project
  has_and_belongs_to_many :users, :uniq => true
end

class User < ActiveRecord::Base
  has_and_belongs_to_many :tasks
end

July 26th, 2010 @ 08:32 PM

clocksarestupid
Neeraj, change your query to the following:

Project.includes([{:tasks => :users}, :tasks]).where({:users => {:name => 'John2'}})

Then it should fail.

July 26th, 2010 @ 09:02 PM
Neeraj Singh
Thanks @clocks. Yes in that case query is indeed failing.

July 26th, 2010 @ 09:14 PM
clocksarestupid
So it's missing a join table. In Rails 2.3.x it has 3 joins that it's performing and in Rails 3 it's missing one.

I've attached a simple file that is easier to debug with. There's a toggle flag at the top that will switch between ActiveRecord 3 and ActiveRecord 2. It will construct an in-memory SQLite3 database and try to perform the query.
- ar_test_case.rb 1.67 KB
July 26th, 2010 @ 09:16 PM
clocksarestupid
I've got the fix. There was an incorrect equality check in the joins that was excluding the last join. I've attached a patch that fixes the problem and also adds a unit test to verify that no exceptions are thrown.
- fix_equality_check_for_joins_5818.diff 2.78 KB
July 27th, 2010 @ 12:25 AM
clocksarestupid
- Tag changed from activerecord rails3, regression to activerecord rails3, patch activerecord, fix, fixed, regression
July 27th, 2010 @ 10:14 PM
Andrea Campi
- Tag changed from activerecord rails3, patch activerecord, fix, fixed, regression to activerecord rails3, activerecord, fix, fixed, patch, regression
bulk tags cleanup

October 11th, 2010 @ 07:17 AM
Santiago Pastorino
- State changed from new to open
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 core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.

February 2nd, 2011 @ 04:30 PM
Santiago Pastorino
- State changed from open to stale
February 2nd, 2011 @ 04:30 PM