This project is archived and is in readonly mode.

#5200 ✓ stale
bahuvrihi

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

Reported by bahuvrihi | July 26th, 2010 @ 05:59 PM

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:

Comments and changes to this ticket

  • Neeraj Singh

    Neeraj Singh July 26th, 2010 @ 08:21 PM

    • Importance changed from “” to “Low”

    What version of rails you used?

  • bahuvrihi

    bahuvrihi July 26th, 2010 @ 08:28 PM

    I am using activerecord-2.3.5 but as discussed there is reason to believe it is also active in 3.0(beta?).

  • matin
  • Santiago Pastorino

    Santiago Pastorino February 2nd, 2011 @ 04:35 PM

    • 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.

  • Santiago Pastorino

    Santiago Pastorino February 2nd, 2011 @ 04:35 PM

    • State changed from “open” to “stale”

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

Tickets have moved to Github

The new ticket tracker is available at https://github.com/rails/rails/issues

Shared Ticket Bins

People watching this ticket

Tags

Referenced by

Pages