Opened 14 years ago

Closed 14 years ago

Last modified 14 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: no UI/UX: no

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 14 years ago.
Patch of proposed fix

Download all attachments as: .zip

Change History (9)

by rlynch, 14 years ago

Attachment: patch.txt​ added

Patch of proposed fix

comment:1 by rlynch, 14 years ago

Patch needs improvement: set
Resolution: → fixed
Status: new → 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 by rlynch, 14 years ago

Resolution: fixed
Status: closed → reopened

comment:3 by rlynch, 14 years ago

Resolution: → fixed
Status: reopened → closed

comment:4 by rlynch, 14 years ago

Resolution: fixed
Status: closed → reopened

comment:5 by rlynch, 14 years ago

Owner: rlynch removed
Status: reopened → new

comment:6 by Erin Kelly, 14 years ago

Triage Stage: Unreviewed → Accepted

comment:7 by Erin Kelly, 14 years ago

Resolution: → fixed
Status: new → 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 by Erin Kelly, 14 years ago

(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