[Django] #33602: when use date_hierarchy in admin, it report a error. django 3.2.12, mysql-connector-python8.0.28, mysql server 8.0.28
Django
mysql-connector-python8.0.28, mysql server 8.0.28
-----------------------------------------+------------------------
Reporter: Eric Pan | Owner: nobody
Type: Uncategorized | Status: new
Component: contrib.admin | Version: 3.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 |
-----------------------------------------+------------------------
\site-packages\django\db\backends\mysql\operations.py line 58
def date_trunc_sql(self, lookup_type, field_name, tzname=None):
field_name = self._convert_field_to_tz(field_name, tzname)
fields = {
'year': '%%Y-01-01',
'month': '%%Y-%%m-01',
} # Use double percents to escape.
the problem is here.
'year': '%%Y-01-01',
'month': '%%Y-%%m-01',
this will generate the sql.
SELECT DISTINCT CAST(DATE_FORMAT([table name], '%%Y-01-01') AS DATE) AS
`datefield
the format ''%%Y-01-01'' is not right for mysql.
--
Ticket URL: <https://code.djangoproject.com/ticket/33602>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
mysql-connector-python8.0.28, mysql server 8.0.28
Reporter: Eric Pan | Owner: nobody
Component: Database layer | Version: 3.2
(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
Changes (by Tim Graham):
* status: new => closed
* resolution: => needsinfo
* component: contrib.admin => Database layer (models, ORM)
* type: Uncategorized => Bug
Old description:
> \site-packages\django\db\backends\mysql\operations.py line 58
>
> def date_trunc_sql(self, lookup_type, field_name, tzname=None):
> field_name = self._convert_field_to_tz(field_name, tzname)
> fields = {
> 'year': '%%Y-01-01',
> 'month': '%%Y-%%m-01',
> } # Use double percents to escape.
>
> the problem is here.
> 'year': '%%Y-01-01',
> 'month': '%%Y-%%m-01',
>
> this will generate the sql.
> SELECT DISTINCT CAST(DATE_FORMAT([table name], '%%Y-01-01') AS DATE) AS
> `datefield
>
> the format ''%%Y-01-01'' is not right for mysql.
New description:
\site-packages\django\db\backends\mysql\operations.py line 58
{{{
def date_trunc_sql(self, lookup_type, field_name, tzname=None):
field_name = self._convert_field_to_tz(field_name, tzname)
fields = {
'year': '%%Y-01-01',
'month': '%%Y-%%m-01',
} # Use double percents to escape.
}}}
the problem is here.
{{{
'year': '%%Y-01-01',
'month': '%%Y-%%m-01',
}}}
this will generate the SQL.
{{{
SELECT DISTINCT CAST(DATE_FORMAT([table name], '%%Y-01-01') AS DATE) AS
`datefield
}}}
the format `''%%Y-01-01''` is not right for mysql.
--
Comment:
This might be a bug in mysql-connector-python and/or
mysql.connector.django which aren't part of Django itself.
What does "the format ... is not right" mean? What's the error?
--
Ticket URL: <https://code.djangoproject.com/ticket/33602#comment:1>
Django
mysql-connector-python8.0.28, mysql server 8.0.28
-------------------------------------+-------------------------------------
Reporter: Eric Pan | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(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
-------------------------------------+-------------------------------------
Comment (by Eric Pan):
the error message is:
django.db.utils.DatabaseError: (1292, "1292: Incorrect datetime value:
'%Y-01-01'", None)
and the sql text sent to mysql db is 'SELECT DISTINCT
CAST(DATE_FORMAT([table name], '%%Y-01-01') AS DATE) AS `datefield
it seems the double %% not be escaped to single % in the final sql text
sent to mysql.
I don not know it's the issue of mysql-connector-python or Django it
self.
because the sql was generated in django module.
\site-packages\django\db\backends\mysql\operations.py line 58
--
Ticket URL: <https://code.djangoproject.com/ticket/33602#comment:2>
Django
mysql-connector-python8.0.28, mysql server 8.0.28
-------------------------------------+-------------------------------------
Reporter: Eric Pan | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: needsinfo =>
--
Ticket URL: <https://code.djangoproject.com/ticket/33602#comment:3>