Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#24925 closed Bug (fixed)

Coalesce converts datetime to string on MySQL

Reported by: Ian Foote Owned by: Ian Foote
Component: Database layer (models, ORM) Version: 1.8
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Passing a datetime to Coalesce leads to a string annotation instead of a datetime.

Change History (9)

comment:1 Changed 7 years ago by Ian Foote

A workaround for this is to cast the python datetime value to a MySQL datetime using RawSQL: RawSQL("cast(%s as datetime)", (datetime,)).

comment:2 Changed 7 years ago by Josh Smeaton

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?

comment:3 Changed 7 years ago by Ian Foote

I've replicated the issue using mysqlclient 1.3.6 directly, so Django isn't causing this.

comment:4 Changed 7 years ago 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.

comment:5 Changed 7 years ago by Ian Foote

Owner: changed from nobody to Ian Foote
Status: newassigned

comment:6 Changed 7 years ago by Shai Berger

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.

comment:7 Changed 7 years ago 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.

comment:8 Changed 7 years ago by Marc Tamlyn <marc.tamlyn@…>

Resolution: fixed
Status: assignedclosed

In 14dead0:

Fixed #24925 -- Document using Coalesce on MySQL

Add warning for using Coalesce with python values on MySQL and document
workaround.

comment:9 Changed 7 years ago by Tim Graham <timograham@…>

In 8d33889:

[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

Note: See TracTickets for help on using tickets.
Back to Top