Inconsistencies when eager loading with fully-qualified table names using Oracle

Eager loading will revert to the left-outer-join strategy when using fully-qualified table names on Oracle. For example:

  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
  
  Emp.find(1, :include => :dept).dept.nombre       # ok
  Emp.find(:first, :include => :dept).dept.nombre  # ok

This code works as written because the default one-query-per-association strategy is used; as a result the select is used and nombre gets set on the dept record. However if you qualifty the table name then the select is not used in some cases; find using an id breaks but find :first still works.

  class Dept < ActiveRecord::Base
    set_table_name "schema_name.dept"
  end
      
  class Emp < ActiveRecord::Base
    set_table_name "schema_name.emp"
    belongs_to :dept, :select => "id, name nombre"
  end
  
  Emp.find(1, :include => :dept).dept.nombre       # NoMethodError
  Emp.find(:first, :include => :dept).dept.nombre  # ok

In the id case eager loading reverts to the left-outer-join strategy and the select is ignored. The difference can be traced to the tables_in_string method which is responsible for extracting tables from the select string. This is how it's written in 2.3.5:

  # [active_record/association.rb]
  def tables_in_string(string)
    return [] if string.blank?
    string.scan(/([\.a-zA-Z_]+).?\./).flatten
  end

Turns out the regexp doesn't handle the fully-qualified names properly. There were some patches that partially fix this in 3.0.0 but the fix isn't complete. This is how the same method is currently:

  # [active_record/relation.rb]
  def tables_in_string(string)
    return [] if string.blank?
    # always convert table names to downcase as in Oracle quoted table names are in uppercase
    # ignore raw_sql_ that is used by Oracle adapter as alias for limit/offset subqueries
    string.scan(/([a-zA-Z_][\.\w]+).?\./).flatten.map(&:downcase).uniq - ['raw_sql_']
  end

This handles word-based schemas, but it will not handle other valid oracle schemas like 'schema$name' nor will it handle tables with a db link. The method can be fixed with a fancier regexp:

  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(TABLES_IN_STRING).flatten.map {|str| str.downcase }.uniq - ['raw_sql_']
    end
  end

  class Dept < ActiveRecord::Base
    set_table_name "schema_name.dept"
  end
      
  class Emp < ActiveRecord::Base
    set_table_name "schema_name.emp"
    belongs_to :dept, :select => "id, name nombre"
  end

  Emp.find(1, :include => :dept).dept.nombre       # ok
  Emp.find(:first, :include => :dept).dept.nombre  # ok

But this is getting kinda wild. Moreover I'd bet it's not correct for other databases. I propose moving the tables_in_string method to the adapter so that each database can use it's own logic at this point.

A couple links:

• a test of the regexp above
• a more thorough study of the select issue