Opened 7 years ago
Closed 7 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 )
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)
follow-up: 2 comment:1 by , 7 years ago
comment:2 by , 7 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 , 7 years ago
Cc: | 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.
follow-up: 5 comment:4 by , 7 years ago
Triage Stage: | Unreviewed → Accepted |
---|---|
Version: | 1.11 → master |
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.
comment:5 by , 7 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 , 7 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
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:8 by , 7 years ago
Keywords: | SQLite Cast added |
---|
comment:9 by , 7 years ago
Patch needs improvement: | set |
---|
comment:10 by , 7 years ago
Tim why you marked this patch as "needs improvement"? You didn't leave any comments or suggestions on PR.
comment:11 by , 7 years ago
Patch needs improvement: | unset |
---|
comment:12 by , 7 years ago
Sorry, I should have put a comment in here. The Oracle build on the pull request is failing.
comment:14 by , 7 years ago
Summary: | sqlite: CAST to DATE causes error → Cast crashes on SQLite when casting a Python date/datetime to Date/DateTimeField |
---|---|
Triage Stage: | Accepted → Ready for checkin |
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.