[Django] #30726: 'week' queryset returns no objects
Django
-------------------------------------+-------------------------------------
Reporter: recvfrom | Owner: nobody
Type: | Status: new
Uncategorized |
Component: Database | Version: 2.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Using the 'week' queryset modifier results in no objects being returned,
despite there being objects in the database. Here's a simplified version
of the model being used:
{{{
import datetime
import pytz
def timestamp():
return pytz.utc.localize(datetime.datetime.utcnow())
class ReportConfig(models.Model):
last_modified = models.DateTimeField(default=timestamp)
}}}
From the shell:
{{{
In [54]: ReportConfig.objects.count()
Out[54]: 853
In [55]:
ReportConfig.objects.filter(last_modified__week__gte=0,last_modified__week__lte=53).count()
Out[55]: 0
In [56]: ReportConfig.objects.filter(last_modified__week=34).count()
Out[56]: 0
In [57]: ReportConfig.objects.last().last_modified.isocalendar()[1]
Out[57]: 34
}}}
I'm using Django 2.2.4 and MySQL Server version: 5.7.25-log
--
Ticket URL: <https://code.djangoproject.com/ticket/30726>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: recvfrom | Owner: nobody
Component: Database layer | Version: 2.2
(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
-------------------------------------+-------------------------------------
Comment (by Nasir Hussain):
Looks like its fixed in master branch by this
[https://github.com/django/django/commit/cef3f2d3c64055c9fc1757fd61dba24b557a2add
commit] which is part of #28373. We might need to take that fix to 2.2.5
too.
--
Ticket URL: <https://code.djangoproject.com/ticket/30726#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: recvfrom | Owner: nobody
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
* type: Uncategorized => Bug
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/30726#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* has_patch: 1 => 0
* version: 2.2 => master
* resolution: => needsinfo
* stage: Accepted => Unreviewed
Comment:
I cannot reproduce this issue on Django == 2.2.4 or master. Can you
provide a sample project?
--
Ticket URL: <https://code.djangoproject.com/ticket/30726#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Andrew Williams):
Looking more into this, it turns out that this is a side-effect of mysql
not having the timezone definitions in the database (See
https://docs.djangoproject.com/en/dev/ref/models/querysets/#database-time-
zone-definitions)
Behind the scenes, the
'filter(last_modified__week__gte=0,last_modified__week__lte=53).count()'
query above gets translated into:
{{{
SELECT COUNT(*) AS `__count` FROM `mymodel_reportconfig` WHERE
(WEEK(CONVERT_TZ(`mymodel_reportconfig`.`last_modified`, 'UTC', 'UTC'), 3)
>= 0 AND WEEK(CONVERT_TZ(`mymodel_reportconfig`.`last_modified`, 'UTC',
'UTC'), 3) <= 53)
}}}
The CONVERT_TZ calls return NULL due to the timezone definitions not being
loaded:
{{{
mysql> SELECT
-> CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central') AS
time1,
-> CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central') AS
time2;
+-------+-------+
| time1 | time2 |
+-------+-------+
| NULL | NULL |
+-------+-------+
1 row in set (0.00 sec)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30726#comment:4>