Oracle list limit exceeded with __in filters

[DavidMAM]

Trying to retrieve a set of objects based on field values. I build the set of values as an array

namelist=[]

.. some code that does namelist.append(name) many times

obl=MyObject.objects.filter(namefield__in=namelist)

obl.count()

if namelist is longer than 1000 then Oracle throws an error

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/local/lib/python2.6/site-packages/django/db/models/query.py", line 292, in count
    return self.query.get_count()
  File "/usr/local/lib/python2.6/site-packages/django/db/models/sql/query.py", line 376, in get_count
    number = obj.get_aggregation()[None]
  File "/usr/local/lib/python2.6/site-packages/django/db/models/sql/query.py", line 348, in get_aggregation
    result = query.execute_sql(SINGLE)
  File "/usr/local/lib/python2.6/site-packages/django/db/models/sql/query.py", line 2369, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python2.6/site-packages/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.6/site-packages/django/db/backends/oracle/base.py", line 443, in execute
    raise e
DatabaseError: ORA-01795: maximum number of expressions in a list is 1000

I don't know if there is an easy way to combine querysets as splitting them into batches of a suitable number would be no problem if they could be combined later.

[Luke Plant]

It doesn't sound like batching of the querysets is what you need to do - rather it is batching in the construction of the SQL expression.

It sounds like you need to do:

mylist = MyObject.objects.filter(Q(namefield__in=namelist_1) | Q(namefield__in=namelist_2))

where you have limited namelist_1 to less than 1000 items, and put the rest into namelist_2 - and then obviously extend this to the general case (untested):

import operator
# split namelist into chunks:
namelists = [namelist[i:i+1000] for i in xrange(0, len(namelist), 1000)]
if len(namelists) > 0:
    q = reduce(operator.or_, [Q(namefield__in=l) for l in namelists])
    mylist = MyObject.objects.filter(q)
    ...

I can't decide whether we just have to accept this as a limitation of Oracle, or whether we should work around it by including the above logic into the Oracle backend. Is there precedent for working around this kind of limitation in the SQL that backends accept?

[Alex Gaynor]

Reformatted the message. Can you try this on trunk, I'm almost positive I saw ian kelly commit a fix for this.

[Łukasz Rekucki]

Replying to Alex:

Reformatted the message. Can you try this on trunk, I'm almost positive I saw ian kelly commit a fix for this.

That would be #14244. Closing as a duplicate.