Opened 3 years ago

Last modified 3 years ago

#28727 closed Bug

sqlite: CAST to DATE causes error — at Version 2

Reported by: direx Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: SQLite Cast
Cc: Simon Charette Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by direx)

When trying to cast a date object to a DateField in annotate an exception is raised:

import datetime
from django.contrib.auth.models import User
from django.db import models
from django.db.models.functions import Cast

User.objects.all().annotate(today=Cast(, models.DateField()))

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/tmp/testenv/lib/python2.7/site-packages/django/db/models/", line 226, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/tmp/testenv/lib/python2.7/site-packages/django/db/models/", line 250, in __iter__
  File "/tmp/testenv/lib/python2.7/site-packages/django/db/models/", line 1118, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/tmp/testenv/lib/python2.7/site-packages/django/db/models/", line 62, in __iter__
    for row in compiler.results_iter(results):
  File "/tmp/testenv/lib/python2.7/site-packages/django/db/models/sql/", line 842, in results_iter
    row = self.apply_converters(row, converters)
  File "/tmp/testenv/lib/python2.7/site-packages/django/db/models/sql/", line 827, in apply_converters
    value = converter(value, expression, self.connection, self.query.context)
  File "/tmp/testenv/lib/python2.7/site-packages/django/db/backends/sqlite3/", line 227, in convert_datefield_value
    value = parse_date(value)
  File "/tmp/testenv/lib/python2.7/site-packages/django/utils/", line 61, in parse_date
    match = date_re.match(value)
TypeError: expected string or buffer

This only seems to happen with SQLite, MySQL works as expected.

FYI, this is the SQL which is generated for SQLite:

SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined", CAST(2017-10-20 AS date) AS "today" FROM "auth_user"

Change History (2)

comment:1 Changed 3 years ago by Simon Charette

Hello direx, thanks for your report!

Could you post the entire traceback of the TypeError please. Feel free to remove the parts of the file paths that contain sensitive or data.

This is probably caused by the fact SQLite stores dates a string internally. Also, I think you'd want to use annotate(today=Value( instead.

comment:2 in reply to:  1 Changed 3 years ago by direx

Description: modified (diff)

OK, I have updated the original text in the ticket to include the full stracktrace.

Replying to Simon Charette:

Also, I think you'd want to use annotate(today=Value( instead.

Actually I am doing something entirely different (I need Coalesce() and Max()) and ran into this issue right there. The above code is just a minimum demonstration on how the bug can be triggered. I know that the code doesn't actually make sense, but I did not want to make the generated query more complex than it has to be for the example.

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