Cast() does not work with PositiveIntegerField on MySQL.

[Paolo D'Apice]

Hi,

I found that the wrong SQL is generated for MySQL 5.7 when using django.db.models.functions.Cast.

Suppose I have a model:

class Model(models.Model):
    id = models.AutoField()
    string = models.CharField()

This works correctly, cast a string to integer:

print Model.objects.annotate(as_int=functions.Cast('string', models.IntegerField())).query
SELECT `Model`.`id`, `Model`.`string`, CAST(`Model`.`string`) AS signed integer) AS `as_int` FROM `Model`

But if cast to unsigned integer (which is what I need):

print Model.objects.annotate(as_int=functions.Cast('string', models.PositiveIntegerField())).query
SELECT `Model`.`id`, `Model`.`string`, CAST(`Model`.`string`) AS integer UNSIGNED) AS `as_int` FROM `Model`

Here is the error:

Model.objects.annotate(as_int=functions.Cast('string', models.PositiveIntegerField()))
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'integer UNSIGNED) AS `id_int` FROM `Model` at line 1")

So it seems that casting to IntegerField uses the correct MySQL type signed integer but casting to PositiveInteger uses the wrong type integer unsigned (it should be unsigned integer, see the MySQL documentation at ​https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#function_cast).

[Mariusz Felisiak]

​PR

[Tim Graham <timograham@…>]

In 0f91ba1a:

Fixed #28329 -- Fixed Cast() with Big/Small/Positive/PositiveSmallIntegerField on MySQL.

[Mariusz Felisiak <felisiak.mariusz@…>]

In de0c774:

Refs #28329 -- Fixed Cast() with ForeignKey to integer fields on MySQL.