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

[jun]

• Environment

  MacOS 10.15.3
  Django 3.1 
  MySQL 5.7.32
  mysqlclient==2.0.1
  

• 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")

[Simon Charette]

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.