Coalesce converts datetime to string on MySQL

[Ian Foote]

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

[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,)).

[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?

[Ian Foote]

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

[Ian Foote]

I think we should at least document that passing values directly to Coalesce on MySQL can cause problems without an explicit cast.

[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.

[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.

[Marc Tamlyn <marc.tamlyn@…>]

In 14dead0:

Fixed #24925 -- Document using Coalesce on MySQL

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

[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