Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

Rails3 RC - Associations (has_one, has_many) does not work with models that are based on database views.

#5294 

class Model < ActiveRecord::Base
  has_one :property, :class_name => "My", :foreign_key => :id
  has_many :properties, :class_name => "My", :foreign_key => :id
end

class My < ActiveRecord::Base
  set_table_name :my_view
end

ruby-1.9.2-head >  ActiveRecord::Migration.create_table "models", :force => true do |t|
ruby-1.9.2-head >    t.string   "name"
ruby-1.9.2-head >    t.datetime "created_at"
ruby-1.9.2-head >    t.datetime "updated_at"
ruby-1.9.2-head >  end

ruby-1.9.2-head > ActiveRecord::Base.connection.execute "Create view my_view as select * from models"

ruby-1.9.2-head > Model.create!(:name => 'name')

ruby-1.9.2-head > Model.first.property

ActiveRecord::StatementInvalid: SQLite3::SQLException: near "FROM": syntax error: SELECT      FROM       "my_view" WHERE     ("my_view".id = 1) LIMIT 1
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract_adapter.rb:202:in `rescue in log'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract_adapter.rb:194:in `log'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/sqlite_adapter.rb:135:in `execute'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/sqlite_adapter.rb:284:in `select'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract/query_cache.rb:56:in `select_all'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/base.rb:427:in `find_by_sql'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation.rb:64:in `to_a'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation/finder_methods.rb:324:in `find_first'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation/finder_methods.rb:117:in `first'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation/finder_methods.rb:100:in `find'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation/finder_methods.rb:96:in `find'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/base.rb:399:in `find'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/has_one_association.rb:82:in `find_target'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_proxy.rb:237:in `load_target'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_proxy.rb:118:in `reload'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations.rb:1364:in `block in association_accessor_methods'
    from (irb):2
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/railties-3.0.0.rc/lib/rails/commands/console.rb:44:in `start'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/railties-3.0.0.rc/lib/rails/commands/console.rb:8:in `start'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/railties-3.0.0.rc/lib/rails/commands.rb:23:in `<top (required)>'
    from script/rails:6:in `require'



    from script/rails:6:in `<main>'

ruby-1.9.2-head > Model.first.properties

ActiveRecord::StatementInvalid: SQLite3::SQLException: near "FROM": syntax error: SELECT      FROM       "my_view" WHERE     ("my_view".id = 1)
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract_adapter.rb:202:in `rescue in log'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract_adapter.rb:194:in `log'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/sqlite_adapter.rb:135:in `execute'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/sqlite_adapter.rb:284:in `select'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/connection_adapters/abstract/query_cache.rb:56:in `select_all'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/base.rb:427:in `find_by_sql'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation.rb:64:in `to_a'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/relation/finder_methods.rb:138:in `all'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_collection.rb:70:in `block in find'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/base.rb:1076:in `with_scope'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_proxy.rb:203:in `with_scope'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_collection.rb:63:in `find'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_collection.rb:467:in `find_target'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_collection.rb:409:in `load_target'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/activerecord-3.0.0.rc/lib/active_record/associations/association_proxy.rb:145:in `inspect'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/railties-3.0.0.rc/lib/rails/commands/console.rb:44:in `start'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/railties-3.0.0.rc/lib/rails/commands/console.rb:8:in `start'
    from /home/rupert/.rvm/gems/ruby-1.9.2-head/gems/railties-3.0.0.rc/lib/rails/commands.rb:23:in `<top (required)>'
    from script/rails:6:in `require'
    from script/rails:6:in `<main>'ruby-1.9.2-head >

As you can see the generated sql query is invalid. There is nothing but spaces between SELECT and FROM words.
It worked fine on Rails3.beta4
I tested that the bug occurs on sqlite3 and postgresql, did not tested other databases.

Reported by Robert Pankowecki · August 4th, 2010 @ 11:46 AM

State: resolved
Milestone: 3.0.2
Assigned to: Aaron Patterson Aaron Patterson
Importance: Low

Activity

  1. Carl Tashian
    Carl Tashian

    With postgresql this errors out slightly differently:

    Model.size

    SQL (0.8ms) SELECT COUNT(*) FROM pg_tables WHERE tablename = 'v_my_view_name'

    NoMethodError: undefined method size' for Model(Table doesn't exist):Class<br/>

    from /Users/carl/.rvm/gems/ruby-1.9.2-rc2@og/gems/activerecord-3.0.0.rc/lib/active_record/base.rb:963:in `method_missing'

    This could very well be a bug in the pg gem, though!

    August 18th, 2010 @ 07:53 PM

  2. Robert Pankowecki
    Robert Pankowecki

    For me the error was exactly the same on postgresql and sqlite. Maybe something changed in later version...

    August 18th, 2010 @ 07:57 PM

  3. Sen
    Sen

    i got the same error on mysql2.

    August 23rd, 2010 @ 08:02 AM

  4. Andrew White
    Andrew White
    • Assigned user set to Aaron Patterson
    • Importance changed from to Low

    Hey guys, can someone come up with a test case for this please?
    RC2 is coming soon and if you'd like this fixed the help would be appreciated.

    August 23rd, 2010 @ 01:02 PM

  5. Robert Pankowecki
    Robert Pankowecki

    Yes, we can! :-) I can do it tomorrow (CET).

    August 23rd, 2010 @ 07:56 PM

  6. Jeremy Kemper
    Jeremy Kemper
    • Milestone cleared.
    • State changed from new to open

    August 27th, 2010 @ 11:21 PM

  7. Aaron Patterson
    Aaron Patterson
    • State changed from open to needs-more-info

    Using the code provided in this ticket and ruby 1.9.2-p0, I was not able to reproduce this bug.

    I am working against arel master and rails master. Would you mind trying with edge rails / arel?

    August 29th, 2010 @ 03:31 AM

  8. Jeremy Kemper
    Jeremy Kemper
    • Milestone cleared.

    August 30th, 2010 @ 04:10 AM

  9. Robert Pankowecki
    Robert Pankowecki

    How can I use latest rails in my project to reproduce it ?
    This is in my Gemfile:

    gem 'rails', :git => 'git://github.com/rails/rails.git'

    but running

    bundle install
    leads to:
    Updating git://github.com/rails/rails.git
Fetching source index for http://rubygems.org/
Bundler could not find compatible versions for gem "activesupport":
 In Gemfile:
 rails depends on
  activesupport (~> 3.0.0)
 rails depends on
  activesupport (3.1.0.beta)

    August 30th, 2010 @ 06:23 PM

  10. Neeraj Singh
    Neeraj Singh

    August 30th, 2010 @ 07:20 PM

  11. Robert Pankowecki
    Robert Pankowecki

    I checked that it works fine with Rails 3.0 final release and with master. You can close it.

    August 31st, 2010 @ 08:31 PM

  12. Aaron Patterson
    Aaron Patterson
    • State changed from needs-more-info to resolved

    Closing per Robert's comment. :-)

    September 28th, 2010 @ 06:18 PM

  13. Jeremy Kemper
    Jeremy Kemper
    • Milestone set to 3.0.2

    October 15th, 2010 @ 11:02 PM