Opened 6 years ago

Closed 6 years ago

#28727 closed Bug (fixed)

Cast crashes on SQLite when casting a Python date/datetime to Date/DateTimeField

Reported by: direx Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: dev
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.get_or_create(username="test")
User.objects.all().annotate(today=Cast(datetime.date.today(), models.DateField()))

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/tmp/testenv/lib/python2.7/site-packages/django/db/models/query.py", line 226, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/tmp/testenv/lib/python2.7/site-packages/django/db/models/query.py", line 250, in __iter__
    self._fetch_all()
  File "/tmp/testenv/lib/python2.7/site-packages/django/db/models/query.py", line 1118, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/tmp/testenv/lib/python2.7/site-packages/django/db/models/query.py", line 62, in __iter__
    for row in compiler.results_iter(results):
  File "/tmp/testenv/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 842, in results_iter
    row = self.apply_converters(row, converters)
  File "/tmp/testenv/lib/python2.7/site-packages/django/db/models/sql/compiler.py", 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/operations.py", line 227, in convert_datefield_value
    value = parse_date(value)
  File "/tmp/testenv/lib/python2.7/site-packages/django/utils/dateparse.py", 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 (15)

comment:1 by Simon Charette, 6 years ago

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(datetime.date.today())) instead.

in reply to:  1 comment:2 by direx, 6 years ago

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(datetime.date.today())) 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.

comment:3 by Simon Charette, 6 years ago

Cc: Simon Charette added

Well aggregation won't work on temporal data times as documented.

SQLite can’t handle aggregation on date/time fields out of the box. This is because there are no native date/time fields in SQLite and Django currently emulates these features using a text field. Attempts to use aggregation on date/time fields in SQLite will raise NotImplementedError.

You might hit another wall there.

Last edited 6 years ago by Simon Charette (previous) (diff)

comment:4 by Simon Charette, 6 years ago

Triage Stage: UnreviewedAccepted
Version: 1.11master

I managed to reproduce locally but I'm not sure what the resolution should be here. I feel like this is an abuse of the Cast function as it wasn't meant to be used with Python primitives, Value should be used instead.

in reply to:  4 comment:5 by direx, 6 years ago

Replying to Simon Charette:

I managed to reproduce locally but I'm not sure what the resolution should be here. I feel like this is an abuse of the Cast function as it wasn't meant to be used with Python primitives, Value should be used instead.

In the Django docs it is expicitely stated that Cast is required in combination with Python types and Coalesce. It says:

A Python value passed to Coalesce on MySQL may be converted to an incorrect type unless explicitly cast to the correct database type

See the example right here:

https://docs.djangoproject.com/en/1.11/ref/models/database-functions/#coalesce

The code in the given example does not work on SQLite and triggers this very bug, so I think this is a valid issue for all current Django versions.

comment:6 by Mariusz Felisiak, 6 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

The main issue is that SQLite doesn't have DATE or DATETIME datatypes, therefore CAST returns incorrect result:

sqlite> select CAST('2017-10-10' AS DATE);
2017

comment:7 by Mariusz Felisiak, 6 years ago

Has patch: set

comment:8 by Mariusz Felisiak, 6 years ago

Keywords: SQLite Cast added

comment:9 by Tim Martin, 6 years ago

Patch needs improvement: set

comment:10 by Mariusz Felisiak, 6 years ago

Tim why you marked this patch as "needs improvement"? You didn't leave any comments or suggestions on PR.

comment:11 by Mariusz Felisiak, 6 years ago

Patch needs improvement: unset

comment:12 by Tim Martin, 6 years ago

Sorry, I should have put a comment in here. The Oracle build on the pull request is failing.

comment:13 by Mariusz Felisiak, 6 years ago

Yes, but it's not related with this PR (see PR9468 and discussion).

comment:14 by Tim Graham, 6 years ago

Summary: sqlite: CAST to DATE causes errorCast crashes on SQLite when casting a Python date/datetime to Date/DateTimeField
Triage Stage: AcceptedReady for checkin

comment:15 by GitHub <noreply@…>, 6 years ago

Resolution: fixed
Status: assignedclosed

In 4420761e:

Fixed #28727 -- Fixed Cast crash on SQLite when casting a Python date/datetime to Date/DateTimeField.

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