Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#19351 closed Bug (fixed)

Two SQLite issues with bulk_insert

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

Description

SQLite + bulk_insert has two issues:

  1. If you insert objects with same values for all fields the objects will be collapsed.
  2. If you insert more than 500 objects and the objects have just a single field a SQLite limit will be hit.

The first issue is technically a data-loss issue. However it isn't likely to be hit - it means you are inserting exactly similar objects in a same batch, and that isn't likely. A backpatch to 1.4 seems to be in place as the fix is trivial: use UNION ALL instead of UNION when constructing the insert query.

Patches available here and here.

Change History (5)

comment:1 Changed 2 years ago by Anssi Kääriäinen <akaariai@…>

In a27582484cf814554907d2d1ad077852de36963f:

Fixed SQLite's collapsing of same-valued instances in bulk_create

SQLite used INSERT INTO tbl SELECT %s UNION SELECT %s, the problem
was that there should have been UNION ALL instead of UNION.

Refs #19351

comment:2 Changed 2 years ago by Anssi Kääriäinen <akaariai@…>

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

In 0a0a0d66b316598f7c296e8bf75749a14ce3ac49:

Fixed #19351 -- SQLite bulk_insert of more than 500 single-field objs

comment:3 Changed 2 years ago by Anssi Kääriäinen <akaariai@…>

In 625dc3f0722d80eaffacc39171ea7337ab089ae4:

[1.5.x] Fixed SQLite's collapsing of same-valued instances in bulk_create

SQLite used INSERT INTO tbl SELECT %s UNION SELECT %s, the problem
was that there should have been UNION ALL instead of UNION.

Refs #19351

Backpatch of a27582484cf814554907d2d1ad077852de36963f

comment:4 Changed 2 years ago by Anssi Kääriäinen <akaariai@…>

In 421e599ad34b93e9537bf4deeca343f5b2e69f06:

[1.5.x] Fixed #19351 -- SQLite bulk_insert of more than 500 single-field objs

Backpatch of 0a0a0d66b316598f7c296e8bf75749a14ce3ac49

comment:5 Changed 2 years ago by Anssi Kääriäinen <akaariai@…>

In c7dcb1d808597e0806fb352bb5ddc4b58c452f4c:

[1.4.x] Fixed SQLite's collapsing of same-valued instances in bulk_create

SQLite used INSERT INTO tbl SELECT %s UNION SELECT %s, the problem
was that there should have been UNION ALL instead of UNION.

Refs #19351

Backpatch of a27582484cf814554907d2d1ad077852de36963f

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