Opened 3 years ago

Closed 3 years ago

#32300 closed Bug (wontfix)

"BIGINT UNSIGNED value is out of range" occurs when substraction operation between PositiveBigIntegerField and BigIntegerField using F()

Reported by: jun Owned by: nobody
Component: Database layer (models, ORM) Version: 3.1
Severity: Normal Keywords: mysql unsigned
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by jun)

  • Environment
    MacOS 10.15.3
    Django 3.1 
    MySQL 5.7.32
  • Model
    class SomeModel(models.Model):
        positive_big_int_column = models.PositiveBigIntegerField(default=0)
        big_int_column = models.BigIntegerField(default=0)
  • Test
    SomeModel.objects.create(positive_big_int_column=100, big_int_column=300)
    qs = SomeModel.objects.filter(positive_big_int_column=100)
    qs.update(big_int_column=F('positive_big_int_column') - F('big_int_column'))
  • Expected query

UPDATE SomeModel SET big_int_column=(100-300)

  • Error

IntegrityError: (1690, "BIGINT UNSIGNED value is out of range in blabla")

Change History (2)

comment:1 Changed 3 years ago by jun

Description: modified (diff)

comment:2 Changed 3 years ago by Simon Charette

Keywords: mysql unsigned added; F expression removed
Resolution: wontfix
Status: newclosed

It can be worked around by using an explicit Cast expression

qs.update(big_int_column=Cast(F('positive_big_int_column'), BigIntegerField()) - F('big_int_column'))

But you risk data corruption if positive_big_int_column is larger than what MySQL allows storing an a signed bigint. PostgreSQL and Oracle backends don't suffer from that as they implement PositiveBigIntegerField using a check constraint. SQLite simply have an integer type without constraints at all so it's also not affected.

I'll close as wontfix as it's really just a MySQL quirk due to the usage of UNSIGNED data types to implement PositiveIntegerField variants. I guess there isn't much that can be done until we drop support for MySQL < 8 since the latter introduces CHECK constraints support.

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