Opened 2 months ago

Closed 2 months ago

Last modified 2 months ago

#34171 closed Bug (fixed)

QuerySet.bulk_create() crashes on mixed case columns in unique_fields/update_fields.

Reported by: Joshua Brooks Owned by: Bhuvnesh
Component: Database layer (models, ORM) Version: 4.1
Severity: Release blocker Keywords:
Cc: Chih Sean Hsu Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Not sure exactly how to phrase this, but when I I'm calling bulk_update on the manager for a class with db_column set on fields the SQL is invalid. Ellipses indicate other fields excluded for clarity.

class ActivityBlackListed(models.Model):
    """
    Originally sourced from Activity_BlackListed in /home/josh/PNDS_Interim_MIS-Data.accdb (13 records)
    """

    class Meta:
        db_table = "Activity_BlackListed"

    blacklistid = models.IntegerField(primary_key=True, db_column="BlacklistID")
    sectorid = models.IntegerField(null=True, blank=True, db_column="SectorID")
    ...

qs.bulk_create(instances, update_conflicts=True, update_fields=["sectorid", ...], unique_fields=["blacklistid"])

The "INSERT" code does take into account the db_columns

INSERT INTO "Activity_BlackListed" ("BlacklistID",...) VALUES (%s,  ...),

The code which is generated for "ON CONFLICT" uses the field name and not the db_column which leads to a syntax error

'ON CONFLICT("blacklistid") DO UPDATE SET "sectorid" = EXCLUDED."sectorid", ...

PostgreSQL returns ERROR: column "blacklistid" does not exist at character 1508

What should be generated is I think:

'ON CONFLICT("BlacklistID") DO UPDATE SET "SectorID" = EXCLUDED."SectorID", ...

Change History (6)

comment:1 Changed 2 months ago by Mariusz Felisiak

Cc: Chih Sean Hsu added
Component: UncategorizedDatabase layer (models, ORM)
Severity: NormalRelease blocker
Summary: ON CONFLICT sql does not work with column aliasesQuerySet.bulk_create() crashes on mixed case columns in unique_fields/update_fields.
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

Thanks for the report! Bug in 0f6946495a8ec955b471ca1baaf408ceb53d4796. We should use columns instead of field names, e.g.

  • django/db/models/query.py

    diff --git a/django/db/models/query.py b/django/db/models/query.py
    index de49e1c58c..fcf0a0616c 100644
    a b class QuerySet(AltersData): 
    798798        self._prepare_for_bulk_create(objs)
    799799        with transaction.atomic(using=self.db, savepoint=False):
    800800            objs_with_pk, objs_without_pk = partition(lambda o: o.pk is None, objs)
     801            if update_fields:
     802                update_fields = [self.model._meta.get_field(name) for name in update_fields]
     803            if unique_fields:
     804                unique_fields = [self.model._meta.get_field(name) for name in unique_fields]
    801805            if objs_with_pk:
    802806                returned_columns = self._batched_insert(
    803807                    objs_with_pk,
  • django/db/models/sql/compiler.py

    diff --git a/django/db/models/sql/compiler.py b/django/db/models/sql/compiler.py
    index 0562a71dd1..caf36382b5 100644
    a b class SQLInsertCompiler(SQLCompiler): 
    17251725        on_conflict_suffix_sql = self.connection.ops.on_conflict_suffix_sql(
    17261726            fields,
    17271727            self.query.on_conflict,
    1728             self.query.update_fields,
    1729             self.query.unique_fields,
     1728            (f.column for f in self.query.update_fields),
     1729            (f.column for f in self.query.unique_fields),
    17301730        )
    17311731        if (
    17321732            self.returning_fields

Would you like to prepare a patch? (regression tests are required)

comment:2 Changed 2 months ago by Bhuvnesh

I guess no one is working on this one so i'll just open a quick PR.

comment:3 Changed 2 months ago by Bhuvnesh

Owner: changed from nobody to Bhuvnesh
Status: newassigned

comment:4 Changed 2 months ago by Mariusz Felisiak

Has patch: set
Triage Stage: AcceptedReady for checkin

comment:5 Changed 2 months ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

In 4035bab5:

Fixed #34171 -- Fixed QuerySet.bulk_create() on fields with db_column in unique_fields/update_fields.

Bug in 0f6946495a8ec955b471ca1baaf408ceb53d4796.

Thanks Joshua Brooks for the report.

comment:6 Changed 2 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In 1703224:

[4.1.x] Fixed #34171 -- Fixed QuerySet.bulk_create() on fields with db_column in unique_fields/update_fields.

Bug in 0f6946495a8ec955b471ca1baaf408ceb53d4796.

Thanks Joshua Brooks for the report.

Backport of 4035bab56f2862a25cd7bfba41a84e58672cb1cc from main

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