Study of AR select + include

patch.rb

# Patch fixes issue on oracle.
class ActiveRecord::Base
  NONQUOTED_OBJECT_NAME   = /[A-Za-z][A-z0-9$#]{0,29}/
  NONQUOTED_DATABASE_LINK = /[A-Za-z][A-z0-9$#\.@]{0,127}/
  TABLES_IN_STRING = /((?:#{NONQUOTED_OBJECT_NAME}\.)?#{NONQUOTED_OBJECT_NAME}(?:@#{NONQUOTED_DATABASE_LINK})?)\..?/
  
  def self.tables_in_string(string)
    return [] if string.blank?
    # string.scan(/([\.a-zA-Z_]+).?\./).flatten
    string.scan(TABLES_IN_STRING).flatten.map {|str| str.downcase }.uniq - ['raw_sql_']
  end
end

select_with_include.rb

# This is a study of how ActiveRecord deals with select + include (ie eager
# loading with limits on what data gets returned) when running on Oracle.  The
# tests must be run in a folder with a standard config/database.yml file.
#
#   % RAILS_ENV=test ruby select_with_include_test.rb
#
# These tests reveal several setups where AR behaves inconsistently, or
# changes its behavior entirely when you fully qualify table names with a
# schema. The two especially troubling findings are:
#
# * schemas that contain non-word (but legal) characters can break AR even
#   though the adapter and oracle allow them
# * fully qualified table names break eager loading of associations that have
#   a select, if in conjuction with find by primary key (but not :all or
#   :first)
#
# Both of these are ultimately caused by eager loading reverting to the 'left
# outer join' strategy rather than using a one-query-per-association strategy
# to load data.
#
# == error 1
#
# The specific culprit in the first error is this method which extracts table
# names from all the select, condition, join statements.
#
#   [active_record/associations.rb]
#   def tables_in_string(string)
#     return [] if string.blank?
#     string.scan(/([\.a-zA-Z_]+).?\./).flatten
#   end
#
# As you can see non-word characters will not be extracted as a part of the
# table name, and hence schemas like ops$user will be left out.  The extracted
# table names are used to determine if the left outer join strategy is needed
# (see the references_eager_loaded_tables? method).  If the schema is not
# extracted then the reversion can be mistakenly triggered.
#
# == error 2
#
# Has to do with the same method as above, plus a case issue.
#
##############################################################################
# Setup Notes
#
# * SCHEMA MUST be downcase or everything breaks (I don't know why)
# * The assertions check that a 'nombre' method is available on a record.  The
#   nombre method will be set if the select statement is actually used in the
#   query -- the queries rename one existing column as nombre.
# * Wrap some statements in with_logger to print the SQL to STDERR
#
###############################################################################

require 'rubygems'
gem 'activerecord', '= 2.3.5'

require 'test/unit'
require 'active_record'

env = ENV['RAILS_ENV'] || 'test'
config_file = File.expand_path('../config/database.yml', __FILE__)
configs = YAML.load_file(config_file)[env]

ActiveRecord::Base.logger = Logger.new(STDERR)
ActiveRecord::Base.logger.level = Logger::FATAL
ActiveRecord::Base.establish_connection(configs)
SCHEMA = configs['username'].downcase

class Test::Unit::TestCase
  def with_logger
    ActiveRecord::Base.logger.level = Logger::DEBUG
    yield
  ensure
    ActiveRecord::Base.logger.level = Logger::FATAL
  end
  
  def assert_select_used(obj)
    assert_equal true, obj.respond_to?(:nombre)
  end

  def assert_select_not_used(obj)
    assert_equal false, obj.respond_to?(:nombre)
  end
end

##############################################################################
# Tests
###############################################################################

class SelectOnFindWithIncludeTest < Test::Unit::TestCase
  
  #
  # test select on find
  #
  
  class Dept < ActiveRecord::Base
    set_table_name 'dept'
  end

  class Emp < ActiveRecord::Base
    set_table_name 'emp'
    belongs_to :dept
  end
  
  class SchemaEmp < ActiveRecord::Base
    set_table_name "#{SCHEMA}.emp"
    belongs_to :dept
  end
  
  def test_eager_loading_with_select_works_if_columns_are_unqualified
    assert_select_used Emp.find(:first, :select => 'first_name nombre, dept_id', :include => :dept)
    assert_select_used SchemaEmp.find(:first, :select => 'first_name nombre, dept_id', :include => :dept)
  end
  
  # This is a trouble case, but easy to avoid (get your code right)
  def test_eager_loading_with_select_does_not_work_if_columns_are_partially_qualified
    assert_select_not_used SchemaEmp.find(:first, :select => "emp.first_name nombre, emp.dept_id", :include => :dept)
  end
  
  # This is the BIG trouble case (fails for ops$)
  def test_eager_loading_with_select_works_if_columns_are_fully_qualified
    assert_select_used Emp.find(:first, :select => 'emp.first_name nombre, emp.dept_id', :include => :dept)
    assert_select_used SchemaEmp.find(:first, :select => "#{SCHEMA}.emp.first_name nombre, #{SCHEMA}.emp.dept_id", :include => :dept)
  end
  
  # Note the inconsistency -- join is ok with partial qualify
  def test_eager_loading_with_select_works_with_partially_qualified_join
    assert_select_used Emp.find(:first, 
      :select => "job.name nombre, emp.dept_id",
      :joins  => "join job on job.id = emp.job_id",
      :include => :dept)
      
    assert_select_used SchemaEmp.find(:first, 
      :select => "job.name nombre, emp.dept_id",
      :joins  => "join job on job.id = emp.job_id",
      :include => :dept)
  end
  
  def test_eager_loading_with_select_works_with_fully_qualified_join
    assert_select_used SchemaEmp.find(:first, 
      :select => "#{SCHEMA}.job.name nombre, #{SCHEMA}.emp.dept_id",
      :joins  => "join #{SCHEMA}.job on #{SCHEMA}.job.id = #{SCHEMA}.emp.job_id",
      :include => :dept)
  end
end

class SelectOnAssocWithIncludeTest < Test::Unit::TestCase
  
  #
  # test belongs_to with select
  #
  
  class Dept < ActiveRecord::Base
    set_table_name 'dept'
  end

  class Emp < ActiveRecord::Base
    set_table_name 'emp'
    belongs_to :dept, :select => 'id, name nombre'
  end
  
  class SchemaDept < ActiveRecord::Base
    set_table_name "#{SCHEMA}.dept"
  end
  
  class SchemaEmp < ActiveRecord::Base
    set_table_name "#{SCHEMA}.emp"
    belongs_to :dept, :class_name => 'SelectOnAssocWithIncludeTest::SchemaDept', :foreign_key => 'dept_id', :select => "#{SCHEMA}.dept.id, #{SCHEMA}.dept.name nombre"
  end
  
  class PartialSchemaEmp < ActiveRecord::Base
    set_table_name "#{SCHEMA}.emp"
    belongs_to :dept, :class_name => 'SelectOnAssocWithIncludeTest::SchemaDept', :foreign_key => 'dept_id', :select => "dept.id, dept.name nombre"
  end
  
  class UnqualifiedSchemaEmp < ActiveRecord::Base
    set_table_name "#{SCHEMA}.emp"
    belongs_to :dept, :class_name => 'SelectOnAssocWithIncludeTest::SchemaDept', :foreign_key => 'dept_id', :select => "id, name nombre"
  end
  
  def test_lazy_loading_select_on_bt_assoc_works_if_columns_are_fully_qualified
    assert_select_used Emp.find(:all).first.dept
    assert_select_used SchemaEmp.find(:all).first.dept
    assert_select_used PartialSchemaEmp.find(:all).first.dept
    assert_select_used UnqualifiedSchemaEmp.find(:all).first.dept
    
    assert_select_used Emp.find(:first).dept
    assert_select_used SchemaEmp.find(:first).dept
    assert_select_used PartialSchemaEmp.find(:first).dept
    assert_select_used UnqualifiedSchemaEmp.find(:first).dept
    
    assert_select_used Emp.find(1).dept
    assert_select_used SchemaEmp.find(1).dept
    assert_select_used PartialSchemaEmp.find(1).dept
    assert_select_used UnqualifiedSchemaEmp.find(1).dept
  end
  
  def test_eager_loading_select_on_bt_assoc_works_if_columns_are_fully_qualified
    assert_select_used Emp.find(:all, :include => :dept).first.dept
    assert_select_used SchemaEmp.find(:all, :include => :dept).first.dept
    assert_select_used PartialSchemaEmp.find(:all, :include => :dept).first.dept
    assert_select_used UnqualifiedSchemaEmp.find(:all, :include => :dept).first.dept
    
    assert_select_used Emp.find(:first, :include => :dept).dept
    assert_select_used SchemaEmp.find(:first, :include => :dept).dept
    assert_select_used PartialSchemaEmp.find(:first, :include => :dept).dept
    assert_select_used UnqualifiedSchemaEmp.find(:first, :include => :dept).dept
    
    # BIG trouble -- find by id bombs for the last three
    assert_select_used Emp.find(1, :include => :dept).dept
    assert_select_used SchemaEmp.find(1, :include => :dept).dept
    assert_select_used PartialSchemaEmp.find(1, :include => :dept).dept
    assert_select_used UnqualifiedSchemaEmp.find(1, :include => :dept).dept
  end
  
  class Job < ActiveRecord::Base
    set_table_name 'job'
    has_many :emps
    has_many :depts, :through => :emps, :select => 'dept.id, dept.name nombre'
  end
  
  #
  # test has_many through with select
  #
  
  class SchemaJob < ActiveRecord::Base
    set_table_name "#{SCHEMA}.job"
    has_many :emps, :class_name => 'SelectOnAssocWithIncludeTest::SchemaEmp', :foreign_key => 'job_id'
    has_many :depts, :through => :emps, :select => "#{SCHEMA}.dept.id, #{SCHEMA}.dept.name nombre"
  end
  
  class PartialSchemaJob < ActiveRecord::Base
    set_table_name "#{SCHEMA}.job"
    has_many :emps, :class_name => 'SelectOnAssocWithIncludeTest::SchemaEmp', :foreign_key => 'job_id'
    has_many :depts, :through => :emps, :select => "dept.id, dept.name nombre"
  end
  
  class UnqualifiedSchemaJob < ActiveRecord::Base
    set_table_name "#{SCHEMA}.job"
    has_many :emps, :class_name => 'SelectOnAssocWithIncludeTest::SchemaEmp', :foreign_key => 'job_id'
    has_many :depts, :through => :emps, :select => "id, name nombre"
  end
  
  def test_lazy_loading_select_on_hmt_assoc_works_if_columns_are_fully_qualified
    assert_select_used Job.find(:all).first.depts.first
    assert_select_used SchemaJob.find(:all).first.depts.first
    assert_select_used PartialSchemaJob.find(:all).first.depts.first
    assert_raises(ActiveRecord::StatementInvalid) { UnqualifiedSchemaJob.find(:all).first.depts.first }
    
    assert_select_used Job.find(:first).depts.first
    assert_select_used SchemaJob.find(:first).depts.first
    assert_select_used PartialSchemaJob.find(:first).depts.first
    assert_raises(ActiveRecord::StatementInvalid) { UnqualifiedSchemaJob.find(:first).depts.first }
    
    assert_select_used Job.find(1).depts.first
    assert_select_used SchemaJob.find(1).depts.first
    assert_select_used PartialSchemaJob.find(1).depts.first
    assert_raises(ActiveRecord::StatementInvalid) { UnqualifiedSchemaJob.find(1).depts.first }
  end
  
  def test_eager_loading_select_on_hmt_assoc_works_if_columns_are_fully_qualified
    assert_select_used Job.find(:all, :include => :depts).first.depts.first
    assert_select_used SchemaJob.find(:all, :include => :depts).first.depts.first
    assert_select_used PartialSchemaJob.find(:all, :include => :depts).first.depts.first
    assert_select_used UnqualifiedSchemaJob.find(:all, :include => :depts).first.depts.first
    
    assert_select_used Job.find(:first, :include => :depts).depts.first
    assert_select_used SchemaJob.find(:first, :include => :depts).depts.first
    assert_select_used PartialSchemaJob.find(:first, :include => :depts).depts.first
    assert_select_used UnqualifiedSchemaJob.find(:first, :include => :depts).depts.first
    
    # BIG trouble -- find by id bombs for the last three
    assert_select_used Job.find(1, :include => :depts).depts.first
    assert_select_used SchemaJob.find(1, :include => :depts).depts.first
    assert_select_used PartialSchemaJob.find(1, :include => :depts).depts.first
    assert_select_used UnqualifiedSchemaJob.find(1, :include => :depts).depts.first
  end
end