Rails - Sep 2008 - 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 3 Sep 2008, at 23:02, stevemolitor-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
wrote:
>
> 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.

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

Fred

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On Sep 4, 2:40 am, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 3 Sep 2008, at 23:02,
stevemoli...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:
>
>
>
>
>
> > 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.
>
> Not that I know of (And i wrote that code - sorry!)
>
> Fred
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 4 Sep 2008, at 18:54,
"stevemolitor-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org"
<stevemolitor-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
 > wrote:
>
> 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.
> I''m not sure what the best way to fix this is, except to revert
back
> to the old way for oracle.
>
> Steve
>
> On Sep 4, 2:40 am, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>> On 3 Sep 2008, at 23:02,
stevemoli...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:
>>
>>
>>
>>
>>
>>> 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.
>>
>> Not that I know of (And i wrote that code - sorry!)
>>
>> Fred
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Sep 4, 2:25 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


On Sep 4, 5:02 pm,
"stevemoli...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org"
<stevemoli...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Sep 4, 2:25 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>
> > 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---