#25937: Failure when using expressions.DateTime on NULL values and aggregating
-------------------------------------+-------------------------------------
Reporter: trecouvr | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm aggregate | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by charettes):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> Running query like `qs.annotate(time=DateTime('removed'), 'month',
> timezone.UTC()).values('time').annotate(Count('pk'))` fails with
> `<repr(<gill.contrib.sales.models.TransactionRowQuerySet at 0x10cc99320>)
> failed: ValueError: Database returned an invalid value in
> QuerySet.datetimes(). Are time zone definitions for your database and
> pytz installed?>`. I noted the failure on MySQL and PostgreSQL, Django
> 1.8.X and 1.9.X. I did not try on other db backends or other Django
> versions.
>
> To reproduce the behaviour:
>
> 1. create a model:
> {{{
> class Potato(models.Model):
> ..
> removed = models.DateTimeField(null=True, blank=True)
> }}}
>
> 2. Insert some of them
> {{{
> Potato.objects.create(removed=timezone.now())
> Potato.objects.create(removed=None)
> ..
> }}}
>
> 3. Fire the request
> {{{
> # Failure
> Potato.annotate(time=DateTime('removed'), 'month',
> timezone.UTC()).values('time').annotate(c=Count('pk'))
>
> # Success
> Potato.filter(removed__isnull=False).annotate(time=DateTime('removed'),
> 'month', timezone.UTC()).values('time').annotate(c=Count('pk'))
> }}}
>
> Note: I printed the raw sql generated by Django and fired it directly in
> a db shell without any error, so the problem seems to come from the code
> Django uses to convert the result from the backend to python code,
> especially the line with NULL coming from the aggregation:
>
> {{{
> +---------------------+-----------+
> | time | pk__count |
> +---------------------+-----------+
> | NULL | 17 |
> | 2015-01-01 00:00:00 | 7 |
> ..
> }}}
New description:
Running query like `qs.annotate(time=DateTime('removed', 'month',
timezone.UTC())).values('time').annotate(Count('pk'))` fails with
`<repr(<gill.contrib.sales.models.TransactionRowQuerySet at 0x10cc99320>)
failed: ValueError: Database returned an invalid value in
QuerySet.datetimes(). Are time zone definitions for your database and pytz
installed?>`. I noted the failure on MySQL and PostgreSQL, Django 1.8.X
and 1.9.X. I did not try on other db backends or other Django versions.
To reproduce the behaviour:
1. create a model:
{{{
class Potato(models.Model):
..
removed = models.DateTimeField(null=True, blank=True)
}}}
2. Insert some of them
{{{
Potato.objects.create(removed=timezone.now())
Potato.objects.create(removed=None)
..
}}}
3. Fire the request
{{{
# Failure
Potato.objects.annotate(time=DateTime('removed', 'month',
timezone.UTC())).values('time').annotate(c=Count('pk'))
# Success
Potato.objects.filter(removed__isnull=False).annotate(time=DateTime('removed',
'month', timezone.UTC())).values('time').annotate(c=Count('pk'))
}}}
Note: I printed the raw sql generated by Django and fired it directly in a
db shell without any error, so the problem seems to come from the code
Django uses to convert the result from the backend to python code,
especially the line with NULL coming from the aggregation:
{{{
+---------------------+-----------+
| time | pk__count |
+---------------------+-----------+
| NULL | 17 |
| 2015-01-01 00:00:00 | 7 |
..
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25937#comment:1>
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/066.4d28c2e3bc52245c848bf58dd85a633c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
