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