Django cast DATE field to DATETIME unexpectedly

[Qian Xu]

I posted a question at ​http://stackoverflow.com/questions/32795047/have-a-perforamance-issue-of-query-date-object-using-django-queryset

I'm using django 1.8.4 writing a webapp. The backend uses MySQL 5.6 (MyISAM). Recently the number of table records reaches 1 million, it will take 1-1.5 seconds to query all distinct record dates. But using MySQL client, it takes less than 0.001 second.

Django Code

class Model1(models.Model):
    date = models.DateField(db_index=True)

# benchmark code
db_dates = Model1.objects.dates("date", kind="day")

I dumped the django queries.

[{u'time': u'0.000', u'sql': u'SET SQL_AUTO_IS_NULL = 0'}, 
 {u'time': u'1.989', u'sql': u"SELECT DISTINCT CAST(DATE_FORMAT(`app1_model1`.`date`, '%Y-%m-%d 00:00:00') AS DATETIME) AS `datefield` FROM `app1_model1` WHERE `app1_model1`.`date` IS NOT NULL ORDER BY `datefield` ASC"}
]

I noticed that the second query did a type cast. The cast is not a necessary step. It slows down when the amount of records is huge. I pretty sure this is the root cause of the slow.

The table is created by django. The field is exactly DATE field. I'd like to know, why django cast DATE to DATETIME. Is it a bug?

Currently, I use a workaround:

db_dates = [dt['date'] for dt in Model1.objects.order_by('date').values('date').distinct()]