[Django] #28329: Wrong type conversion with MySQL 5.7

[Django]

#28329: Wrong type conversion with MySQL 5.7
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
dapicester |
Type: Bug | Status: new
Component: Database | Version: 1.10
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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).

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

[Django]

#28329: Cast() does not work with PositiveIntegerField on MySQL.
-------------------------------------+-------------------------------------
Reporter: Paolo D'Apice | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

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

Changes (by felixxm):

* version: 1.10 => master
* stage: Unreviewed => Accepted

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

[Django]

#28329: Cast() does not work with PositiveIntegerField on MySQL.
-------------------------------------+-------------------------------------
Reporter: Paolo D'Apice | Owner: nobody

Type: Bug | Status: new

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by felixxm):

* easy: 0 => 1

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

[Django]

#28329: Cast() does not work with PositiveIntegerField on MySQL.
-------------------------------------+-------------------------------------

Reporter: Paolo D'Apice | Owner: felixxm
Type: Bug | Status: assigned

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by felixxm):

* owner: nobody => felixxm
* status: new => assigned
* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/8666 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/28329#comment:3>

[Django]

#28329: Cast() does not work with PositiveIntegerField on MySQL.
-------------------------------------+-------------------------------------
Reporter: Paolo D'Apice | Owner: felixxm

Type: Bug | Status: closed

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

Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham <timograham@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"0f91ba1adc037345474749faa64d36a4077b3fb8" 0f91ba1a]:
{{{
#!CommitTicketReference repository=""
revision="0f91ba1adc037345474749faa64d36a4077b3fb8"
Fixed #28329 -- Fixed Cast() with
Big/Small/Positive/PositiveSmallIntegerField on MySQL.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28329#comment:4>

[Django]

#28329: Cast() does not work with PositiveIntegerField on MySQL.
-------------------------------------+-------------------------------------

Reporter: Paolo D'Apice | Owner: Mariusz
| Felisiak
Type: Bug | Status: closed
Component: Database layer | Version: dev

(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"de0c7744be93800b2239eb061a2d8f7c669c05a8" de0c774]:
{{{
#!CommitTicketReference repository=""
revision="de0c7744be93800b2239eb061a2d8f7c669c05a8"
Refs #28329 -- Fixed Cast() with ForeignKey to integer fields on MySQL.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28329#comment:5>