[Django] #30510: Bulk creates with Oracle backend fail with mixed length arguments on LOB field
Django
field
-------------------------------------+-------------------------------------
Reporter: Mark | Owner: nobody
Gordon |
Type: | Status: new
Uncategorized |
Component: Database | Version: 2.1
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Consider the below model, containing one LOB field
{{{
from django.db import models
class Bar(models.Model):
baz = models.TextField()
}}}
When you make a `bulk_create` request, e.g. `Bar.objects.bulk_create([b0,
b1])` you will get generate a query like below.
`INSERT INTO "PROJECT_BAR" ("BAZ") SELECT * FROM (SELECT :arg1 col_0 FROM
DUAL UNION ALL SELECT :arg0 FROM DUAL)`
This works most of the time, however at some point logic was added to
automatically convert a string's type to `Database.CLOB` when it exceeded
4000 bytes from a normal string literal type. When this conversion happens
for some of the arguments but not all of the arguments in the ephemeral
table Oracle will complain that the type of the column of the unified
table is inconsistent and fail the query.
E.g. the following will fail
{{{
Bar.objects.bulk_create([Bar(baz='aaa'), Bar(baz='a'*5000)])
}}}
Generating the error
`django.db.utils.DatabaseError: ORA-01790: expression must have same
datatype as corresponding expression`
Note that when both objects have a long or both have a short `baz` field
this query succeeds.
I'm working on a patch
--
Ticket URL: <https://code.djangoproject.com/ticket/30510>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
field
-------------------------------------+-------------------------------------
| Gordon
Type: Uncategorized | Status: assigned
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: nobody => Mark Gordon
--
Ticket URL: <https://code.djangoproject.com/ticket/30510#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Mark Gordon | Owner: Mark
| Gordon
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* version: 2.1 => master
* keywords: => oracle bulk_create
* has_patch: 0 => 1
* type: Uncategorized => Bug
* stage: Unreviewed => Accepted
Comment:
Thanks for the report. Reproduced at
1d25354fb5f87d35968cd78b53d9560fd75f5b1a.
[https://github.com/django/django/pull/11416 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/30510#comment:2>
Django
-------------------------------------+-------------------------------------
| Kucuk
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle bulk_create | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: Mark Gordon => Ahmet Kucuk
Comment:
[https://github.com/django/django/pull/11857 Alternative PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/30510#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Mark Gordon | Owner: Ahmet
| Kucuk
Component: Database layer | Version: master
(models, ORM) |
Keywords: oracle bulk_create | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"dc890bef5ad8e9fccce55f3e64af72103ea6e8c1" dc890bef]:
{{{
#!CommitTicketReference repository=""
revision="dc890bef5ad8e9fccce55f3e64af72103ea6e8c1"
Fixed #30510 -- Fixed crash of QuerySet.bulk_create() with mixed-length
texts on Oracle.
Text with more than 4000 characters must be set to as a CLOB on Oracle
what caused a mixed datatype error (ORA-01790) when shorter text
appeared in the same operation.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30510#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Mark Gordon | Owner: Ahmet
| Kucuk
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: oracle bulk_create | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"9dc13f41b5c80941d4873ce0791b83afd8f7c1ca" 9dc13f4]:
{{{
#!CommitTicketReference repository=""
revision="9dc13f41b5c80941d4873ce0791b83afd8f7c1ca"
[3.0.x] Fixed #30510 -- Fixed crash of QuerySet.bulk_create() with mixed-
length texts on Oracle.
Text with more than 4000 characters must be set to as a CLOB on Oracle
what caused a mixed datatype error (ORA-01790) when shorter text
appeared in the same operation.
Backport of dc890bef5ad8e9fccce55f3e64af72103ea6e8c1 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30510#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Mark Gordon | Owner: Ahmet
| Kucuk
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: oracle bulk_create | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Georgi Yanchev (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/30510#comment:6>