#28727: sqlite: CAST to DATE causes error
-------------------------------------+-------------------------------------
Reporter: direx | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> 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(datetime.date.today(),
> models.DateField()))
>
> ...
>
> 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"
> }}}
New description:
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"
}}}
--
Comment (by direx):
OK, I have updated the original text in the ticket to include the full
stracktrace.
Replying to [comment:1 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28727#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/063.cfb6ddfd42da1f0999b6ccb2a9d68576%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
