[Django] #32300: "BIGINT UNSIGNED value is out of range" occurs when substraction operation between PositiveBigIntegerField and BigIntegerField using F()

[Django]

#32300: "BIGINT UNSIGNED value is out of range" occurs when substraction operation
between PositiveBigIntegerField and BigIntegerField using F()
-------------------------------------+-------------------------------------
Reporter: jun | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) |
Severity: Normal | Keywords: F expression
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
* Environment
{{{
MacOS 0.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")

--
Ticket URL: <https://code.djangoproject.com/ticket/32300>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#32300: "BIGINT UNSIGNED value is out of range" occurs when substraction operation
between PositiveBigIntegerField and BigIntegerField using F()
-------------------------------------+-------------------------------------
Reporter: jun | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: F expression | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by jun:

Old description:

> * Environment
> {{{
> MacOS 0.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")

New description:

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

--

--
Ticket URL: <https://code.djangoproject.com/ticket/32300#comment:1>

[Django]

#32300: "BIGINT UNSIGNED value is out of range" occurs when substraction operation
between PositiveBigIntegerField and BigIntegerField using F()
-------------------------------------+-------------------------------------
Reporter: jun | Owner: nobody

Type: Bug | Status: closed

Component: Database layer | Version: 3.1
(models, ORM) |

Severity: Normal | Resolution: wontfix
Keywords: mysql unsigned | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* keywords: F expression => mysql unsigned
* status: new => closed
* resolution: => wontfix

Comment:

It can be worked around by using an explicit `Cast` expression

{{{#!python
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.
[https://www.sqlite.org/datatype3.html 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/32300#comment:2>