Opened 14 years ago

Last modified 14 years ago

#14441 closed

Oracle list limit exceeded with __in filters — at Version 2

Reported by: DavidMAM Owned by: nobody
Component: Database layer (models, ORM) Version: 1.1
Severity: Keywords: Oracle
Cc: Triage Stage: Design decision needed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Alex Gaynor)

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.

Change History (2)

comment:1 by Luke Plant, 14 years ago

Triage Stage: UnreviewedDesign decision needed

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?

comment:2 by Alex Gaynor, 14 years ago

Description: modified (diff)

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

Note: See TracTickets for help on using tickets.
Back to Top