[Django] #24925: Coalesce converts datetime to string on MySQL
Django
----------------------------------------------+--------------------
Reporter: Ian-Foote | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
Passing a `datetime` to `Coalesce` leads to a string `annotation` instead
of a `datetime`.
--
Ticket URL: <https://code.djangoproject.com/ticket/24925>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Ian-Foote | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Ian-Foote):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
A workaround for this is to `cast` the python datetime value to a MySQL
datetime using `RawSQL`: `RawSQL("cast(%s as datetime)", (datetime,))`.
--
Ticket URL: <https://code.djangoproject.com/ticket/24925#comment:1>
Django
Reporter: Ian-Foote | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by jarshwah):
Is this an issue with MySQL or the implementation of Coalesce within
Django? That is, does MySQL convert datetimes to strings in coalesce or
does Django?
--
Ticket URL: <https://code.djangoproject.com/ticket/24925#comment:2>
Django
Reporter: Ian-Foote | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Ian-Foote):
I've replicated the issue using mysqlclient `1.3.6` directly, so Django
isn't causing this.
--
Ticket URL: <https://code.djangoproject.com/ticket/24925#comment:3>
Django
Reporter: Ian-Foote | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Ian-Foote):
I think we should at least document that passing values directly to
`Coalesce` on MySQL can cause problems without an explicit `cast`.
--
Ticket URL: <https://code.djangoproject.com/ticket/24925#comment:4>
Django
Reporter: Ian-Foote | Owner: Ian-Foote
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Ian-Foote):
* owner: nobody => Ian-Foote
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/24925#comment:5>
Django
Reporter: Ian-Foote | Owner: Ian-Foote
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by shaib):
The first comment is not clear to me: It may refer to
{{{
cast (coalesce (field, now) as datetime)
}}}
or to
{{{
coalesce (field, cast (now as datetime))
}}}
The latter may be done completely automatically by Django, the former may
also be done automatically if an output field is provided.
Also note https://dev.mysql.com/doc/refman/5.6/en/date-and-time-
literals.html -- before 5.6.4 !MySql understood date literals as strings,
not sure how that applies to embedded variables; please try to re-test
this with a later-than-5.6.4 version.
--
Ticket URL: <https://code.djangoproject.com/ticket/24925#comment:6>
Django
Reporter: Ian-Foote | Owner: Ian-Foote
Type: Bug | Status: assigned
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Ian-Foote):
I meant the latter example. The cast does not appear to being
automatically applied by Django. I've just tested using MySQL 5.6.19 and
the problem still appears.
--
Ticket URL: <https://code.djangoproject.com/ticket/24925#comment:7>
Django
Reporter: Ian-Foote | Owner: Ian-Foote
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Marc Tamlyn <marc.tamlyn@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"14dead04acf4ac877d0f4025f142fe9e872ce8ac" 14dead0]:
{{{
#!CommitTicketReference repository=""
revision="14dead04acf4ac877d0f4025f142fe9e872ce8ac"
Fixed #24925 -- Document using Coalesce on MySQL
Add warning for using Coalesce with python values on MySQL and document
workaround.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24925#comment:8>
Django
Reporter: Ian-Foote | Owner: Ian-Foote
Type: Bug | Status: closed
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution: fixed
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"8d33889422040351785cd511fe28105c6b5bec71" 8d33889]:
{{{
#!CommitTicketReference repository=""
revision="8d33889422040351785cd511fe28105c6b5bec71"
[1.8.x] Fixed #24925 -- Document using Coalesce on MySQL
Add warning for using Coalesce with python values on MySQL and document
workaround.
Backport of 14dead04acf4ac877d0f4025f142fe9e872ce8ac from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24925#comment:9>