Opened 6 years ago

Closed 6 years ago

Last modified 6 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 6 years ago.
Patch of proposed fix

Download all attachments as: .zip

Change History (9)

Changed 6 years ago by rlynch

Attachment: patch.txt added

Patch of proposed fix

comment:1 Changed 6 years ago by rlynch

Patch needs improvement: set
Resolution: fixed
Status: newclosed

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 6 years ago by rlynch

Resolution: fixed
Status: closedreopened

comment:3 Changed 6 years ago by rlynch

Resolution: fixed
Status: reopenedclosed

comment:4 Changed 6 years ago by rlynch

Resolution: fixed
Status: closedreopened

comment:5 Changed 6 years ago by rlynch

Owner: rlynch deleted
Status: reopenednew

comment:6 Changed 6 years ago by Ian Kelly

Triage Stage: UnreviewedAccepted

comment:7 Changed 6 years ago by Ian Kelly

Resolution: fixed
Status: newclosed

(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 6 years ago by Ian Kelly

(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