Eager load associations in Oracle problem with more than 1000 records

I think Rails 2.1 introduced a bug when eager loading of associations with Oracle. In 2.1 ActiveRecord loads the included table with a big 'IN' query: 'where association.id in (1, 2, 3, 4...)'. The problem is that Oracle has a hard limit of 1000 values in an IN clause. I get this error:

'OCIError: ORA-01795: maximum number of expressions in a list is 1000

Of course getting 1000 records in one shot is probably too many; you'd want to paginate. But still sometimes you underestimate the size your db will grow too, and a really slow query is better than one that blows up.

Has anyone else noticed this problem and is there a simple solution? I know that I can specify a dummy order by or conditions clause on the included table to force a normal join, but I'd have to change a lot of code.

Thanks.

Steve

Not that I know of (And i wrote that code - sorry!)

Fred

Fred,

Did write the new eager loading code, or the oracle adapter?

I'm not sure what the best way to fix this is, except to revert back to the old way for oracle.

Steve

Fred,

Did write the new eager loading code, or the oracle adapter?

I wrote the eager loading code - I don't know the slightest thing
about oracle. Conceivably the array could be chopped into pieces
before we try and load it.

Yeah, either by issuing multiple 'where in' queries per 1000 ids, or something hokey like this:

   where (id in (1, 2, 3...)) or (id in (1001, 1002, 1003...)) or ....

I don't know if oracle has a limit on the maximum query string size....

Steve

I tried the following code, which is icky but seems to work fine. You'd probably want to only do this for database where this is an issue, so maybe the adapter could provide a method supplying the in clause limit (actually its the max # of expression in any oracle sql list).

From associations_preload.rb:

      def find_associated_records(ids, reflection, preload_options)         options = reflection.options         table_name = reflection.klass.quoted_table_name         interface = reflection.options[:as]

        id_sets =         condition_clauses =         limit = 1000         0.step(ids.size, limit) do |i|           id_sets << ids[i, limit]           if interface             condition_clauses << "#{reflection.klass.quoted_table_name}.#{connection.quote_column_name "#{interface}_id"} IN (?)"           else             foreign_key = reflection.primary_key_name             condition_clauses << "#{reflection.klass.quoted_table_name}.#{foreign_key} IN (?)"           end         end         conditions = condition_clauses.join(" OR ")

        if interface == reflection.options[:as]           conditions << " and #{reflection.klass.quoted_table_name}.#{connection.quote_column_name "#{interface}_type"} = '#{self.base_class.name.demodulize}'"         end

        conditions << append_conditions(options, preload_options)

        reflection.klass.find(:all,                               :select => (preload_options[:select] || options[:select] || "#{table_name}.*"),                               :include => preload_options[:include] || options[:include],                               :conditions => [conditions, ids],                               :joins => options[:joins],                               :group => preload_options[:group] || options[:group],                               :order => preload_options[:order] || options[:order])       end