Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#14244 closed (fixed)

In clause with 1000 or more items fails in Oracle

Reported by: rlynch Owned by:
Component: Database layer (models, ORM) Version: 1.2
Severity: Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: UI/UX:

Description

Oracle (not sure about other RDBMSs) fails with IN clauses that have more than 1000 parameters. An easy workaround is to create separate clauses with an OR between them. For example...

WHERE...
AND (my_column in (1, 2, ....999) OR my_column in (1000, 1001, ....1999) OR my_column in (....))
....

Attachments (1)

patch.txt (3.3 KB) - added by rlynch 5 years ago.
Patch of proposed fix

Download all attachments as: .zip

Change History (9)

Changed 5 years ago by rlynch

Patch of proposed fix

comment:1 Changed 5 years ago by rlynch

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement set
  • Resolution set to fixed
  • Status changed from new to closed

The fix has been tested manually but the unit test will be difficult to do because SQLite3 has a built in limitation of only allowing 1000 or less parameters in a query. SQLite3 does expose a setting to increase this but it is only exposed in the C or Python APSW libraries.

http://www.sqlite.org/capi3ref.html#SQLITE_LIMIT_ATTACHED

comment:2 Changed 5 years ago by rlynch

  • Resolution fixed deleted
  • Status changed from closed to reopened

comment:3 Changed 5 years ago by rlynch

  • Resolution set to fixed
  • Status changed from reopened to closed

comment:4 Changed 5 years ago by rlynch

  • Resolution fixed deleted
  • Status changed from closed to reopened

comment:5 Changed 5 years ago by rlynch

  • Owner rlynch deleted
  • Status changed from reopened to new

comment:6 Changed 5 years ago by ikelly

  • Triage Stage changed from Unreviewed to Accepted

comment:7 Changed 5 years ago by ikelly

  • Resolution set to fixed
  • Status changed from new to closed

(In [13859]) Fixed #14244: Allow lists of more than 1000 items to be used with the 'in' lookup in Oracle, by breaking them up into groups of 1000 items and ORing them together. Thanks to rlynch for the report and initial patch.

comment:8 Changed 5 years ago by ikelly

(In [13860]) [1.2.X] Fixed #14244: Allow lists of more than 1000 items to be used with the 'in' lookup in Oracle, by breaking them up into groups of 1000 items and ORing them together. Thanks to rlynch for the report and initial patch. Backport of [13859] from trunk.

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