[Django] #34659: mysql backend creates queries using CONVERT_TZ even when this is not supported
Django
supported
----------------------------------------------+------------------------
Reporter: Klaas van Schelven | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 4.2
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
----------------------------------------------+------------------------
To test whether a mysql server has the zoninfo database loaded (feature
name: `has_zoneinfo_database`), the [following
code](https://github.com/django/django/blob/1fe0b167af3611cca79e8a8092ee929312193c6f/django/db/backends/mysql/base.py#L410)
is run:
```
CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
```
However, this test is not actually used (i.e. there is no condition on the
feature being True) when [generating
sql](https://github.com/django/django/blob/main/django/db/backends/mysql/operations.py#L94),
despite the fact that the generated sql contains something that we know
will evaluate to `NULL`
This shows up when filtering like so:
```
Foo.objects(some_datetimefield__date="2023-06-16")
```
which evaluates to the empty queryset, despite there being such objects.
`USE_TZ = True`, obviously
--
Ticket URL: <https://code.djangoproject.com/ticket/34659>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
supported
Reporter: Klaas van Schelven | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 4.2
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Description changed by Klaas van Schelven:
Old description:
> To test whether a mysql server has the zoninfo database loaded (feature
> name: `has_zoneinfo_database`), the [following
> code](https://github.com/django/django/blob/1fe0b167af3611cca79e8a8092ee929312193c6f/django/db/backends/mysql/base.py#L410)
> is run:
>
> ```
> CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
> ```
>
> However, this test is not actually used (i.e. there is no condition on
> the feature being True) when [generating
> sql](https://github.com/django/django/blob/main/django/db/backends/mysql/operations.py#L94),
> despite the fact that the generated sql contains something that we know
> will evaluate to `NULL`
>
> This shows up when filtering like so:
>
> ```
> Foo.objects(some_datetimefield__date="2023-06-16")
> ```
> which evaluates to the empty queryset, despite there being such objects.
> `USE_TZ = True`, obviously
New description:
To test whether a mysql server has the zoninfo database loaded (feature
name: `has_zoneinfo_database`), the [following
code](https://github.com/django/django/blob/1fe0b167af3611cca79e8a8092ee929312193c6f/django/db/backends/mysql/base.py#L410)
is run:
{{{
CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
}}}
However, this test is not actually used (i.e. there is no condition on the
feature being True) when [generating
sql](https://github.com/django/django/blob/main/django/db/backends/mysql/operations.py#L94),
despite the fact that the generated sql contains something that we know
will evaluate to `NULL`
This shows up when filtering like so:
{{{
Foo.objects(some_datetimefield__date="2023-06-16")
}}}
which evaluates to the empty queryset, despite there being such objects.
`USE_TZ = True`, obviously
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34659#comment:1>
Django
Old description:
New description:
[https://github.com/django/django/blob/1fe0b167af3611cca79e8a8092ee929312193c6f/django/db/backends/mysql/base.py#L410
following code] is run:
{{{
CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
}}}
However, this test is not actually used (i.e. there is no condition on the
feature being True) when
[https://github.com/django/django/blob/main/django/db/backends/mysql/operations.py#L94
generating sql], despite the fact that the generated sql contains
something that we know will evaluate to `NULL`
This shows up when filtering like so:
{{{
Foo.objects(some_datetimefield__date="2023-06-16")
}}}
which evaluates to the empty queryset, despite there being such objects.
`USE_TZ = True`, obviously
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34659#comment:2>
Django
supported
------------------------------------+--------------------------------------
Reporter: Klaas van Schelven | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 4.2
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
------------------------------------+--------------------------------------
Description changed by Klaas van Schelven:
Old description:
> To test whether a mysql server has the zoninfo database loaded (feature
> name: `has_zoneinfo_database`), the
> [https://github.com/django/django/blob/1fe0b167af3611cca79e8a8092ee929312193c6f/django/db/backends/mysql/base.py#L410
> following code] is run:
>
> {{{
> CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
> }}}
>
>
> However, this test is not actually used (i.e. there is no condition on
> the feature being True) when
> [https://github.com/django/django/blob/main/django/db/backends/mysql/operations.py#L94
> generating sql], despite the fact that the generated sql contains
> something that we know will evaluate to `NULL`
>
> This shows up when filtering like so:
>
> {{{
> Foo.objects(some_datetimefield__date="2023-06-16")
> }}}
>
> which evaluates to the empty queryset, despite there being such objects.
> `USE_TZ = True`, obviously
New description:
To test whether a mysql server has the zoneinfo database loaded (feature
name: `has_zoneinfo_database`), the
[https://github.com/django/django/blob/1fe0b167af3611cca79e8a8092ee929312193c6f/django/db/backends/mysql/base.py#L410
following code] is run:
{{{
CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
}}}
However, this test is not actually used (i.e. there is no condition on the
feature being True) when
[https://github.com/django/django/blob/main/django/db/backends/mysql/operations.py#L94
generating sql], despite the fact that the generated sql contains
something that we know will evaluate to `NULL`
This shows up when filtering like so:
{{{
Foo.objects(some_datetimefield__date="2023-06-16")
}}}
which evaluates to the empty queryset, despite there being such objects.
`USE_TZ = True`, obviously
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34659#comment:3>
Django
supported
------------------------------------+--------------------------------------
Reporter: Klaas van Schelven | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 4.2
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
------------------------------------+--------------------------------------
Description changed by Klaas van Schelven:
Old description:
> To test whether a mysql server has the zoneinfo database loaded (feature
> name: `has_zoneinfo_database`), the
> [https://github.com/django/django/blob/1fe0b167af3611cca79e8a8092ee929312193c6f/django/db/backends/mysql/base.py#L410
> following code] is run:
>
> {{{
> CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
> }}}
>
>
> However, this test is not actually used (i.e. there is no condition on
> the feature being True) when
> [https://github.com/django/django/blob/main/django/db/backends/mysql/operations.py#L94
> generating sql], despite the fact that the generated sql contains
> something that we know will evaluate to `NULL`
>
> This shows up when filtering like so:
>
> {{{
> Foo.objects(some_datetimefield__date="2023-06-16")
> }}}
>
> which evaluates to the empty queryset, despite there being such objects.
> `USE_TZ = True`, obviously
New description:
To test whether a mysql server has the zoneinfo database loaded (feature
name: `has_zoneinfo_database`), the
[https://github.com/django/django/blob/1fe0b167af3611cca79e8a8092ee929312193c6f/django/db/backends/mysql/base.py#L410
following code] is run:
{{{
CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
}}}
However, this test is not actually used (i.e. there is no condition on the
feature being True) when
[https://github.com/django/django/blob/main/django/db/backends/mysql/operations.py#L94
generating sql], despite the fact that the generated sql contains
something that we know will evaluate to `NULL`, namely, the very same
`CONVERT_TZ` mentioned in the above.
This shows up when filtering like so:
{{{
Foo.objects(some_datetimefield__date="2023-06-16")
}}}
which evaluates to the empty queryset, despite there being such objects.
`USE_TZ = True`, obviously
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34659#comment:4>
Django
supported
------------------------------------+--------------------------------------
Reporter: Klaas van Schelven | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 4.2
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
------------------------------------+--------------------------------------
Description changed by Klaas van Schelven:
Old description:
> To test whether a mysql server has the zoneinfo database loaded (feature
> name: `has_zoneinfo_database`), the
> [https://github.com/django/django/blob/1fe0b167af3611cca79e8a8092ee929312193c6f/django/db/backends/mysql/base.py#L410
> following code] is run:
>
> {{{
> CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
> }}}
>
>
> However, this test is not actually used (i.e. there is no condition on
> the feature being True) when
> [https://github.com/django/django/blob/main/django/db/backends/mysql/operations.py#L94
> generating sql], despite the fact that the generated sql contains
> something that we know will evaluate to `NULL`, namely, the very same
> `CONVERT_TZ` mentioned in the above.
>
> This shows up when filtering like so:
>
> {{{
> Foo.objects(some_datetimefield__date="2023-06-16")
> }}}
>
> which evaluates to the empty queryset, despite there being such objects.
> `USE_TZ = True`, obviously
New description:
To test whether a mysql server has the zoneinfo database loaded (feature
name: `has_zoneinfo_database`), the
[https://github.com/django/django/blob/1fe0b167af3611cca79e8a8092ee929312193c6f/django/db/backends/mysql/base.py#L410
following code] is run:
{{{
CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
}}}
However, this test is not actually used (i.e. there is no condition on the
feature being True) when
[https://github.com/django/django/blob/main/django/db/backends/mysql/operations.py#L94
generating sql], despite the fact that the generated sql contains
something that we know will evaluate to `NULL`, namely, the very same
`CONVERT_TZ` mentioned in the above.
This shows up when filtering like so:
{{{
Foo.objects(some_datetimefield__date="2023-06-16")
}}}
which evaluates to the empty queryset, despite there being such objects.
On a mysql database without the zoneinfo database loaden, and with `USE_TZ
= True`, obviously
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34659#comment:5>
Django
Old description:
> On a mysql database without the zoneinfo database loaden, and with
> `USE_TZ = True`, obviously
New description:
To test whether a mysql server has the zoneinfo database loaded (feature
name: `has_zoneinfo_database`), the
[https://github.com/django/django/blob/1fe0b167af3611cca79e8a8092ee929312193c6f/django/db/backends/mysql/base.py#L410
following code] is run:
{{{
CONVERT_TZ('2001-01-01 01:00:00', 'UTC', 'UTC') IS NOT NULL
}}}
However, this test is not actually used (i.e. there is no condition on the
feature being True) when
[https://github.com/django/django/blob/main/django/db/backends/mysql/operations.py#L94
generating sql], despite the fact that the generated sql contains
something that we know will evaluate to `NULL`, namely, the very same
`CONVERT_TZ` mentioned in the above.
This shows up when filtering like so:
{{{
Foo.objects(some_datetimefield__date="2023-06-16")
}}}
which evaluates to the empty queryset, despite there being such objects.
On a mysql database without the zoneinfo database loaded, and with `USE_TZ
= True`, obviously
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34659#comment:6>
Django
supported
Reporter: Klaas van Schelven | Owner: nobody
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* status: new => closed
* resolution: => duplicate
* component: Uncategorized => Database layer (models, ORM)
Comment:
Duplicate of e.g. #33075.
When `USE_TZ` is `True`, fields are converted to the current time zone
before filtering (see
[https://docs.djangoproject.com/en/stable/ref/models/querysets/#date
__date docs]). This requires
[https://docs.djangoproject.com/en/stable/ref/models/querysets/#database-
time-zone-definitions time zone definitions in the database] as documented
(in both places).
--
Ticket URL: <https://code.djangoproject.com/ticket/34659#comment:7>
Django
supported
-------------------------------------+-------------------------------------
Reporter: Klaas van Schelven | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: duplicate
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 Klaas van Schelven):
"silently behaving incorrectly rather than failing explicitly, but
documented" is an unsatisfactory answer to me, but I do admit that the
point may be broader than the single weird behavior that I ran into.
Couldn't we turn this into a django system check or similar?
--
Ticket URL: <https://code.djangoproject.com/ticket/34659#comment:8>