Re: [Django] #31506: ExpressionWrapper() doesn't respect output_field when combining DateField and timedelta on PostgreSQL and MySQL.
Django
and timedelta on PostgreSQL and MySQL.
-------------------------------------+-------------------------------------
Reporter: Matthieu Rigal | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by felixxm):
* Attachment "test_31506.diff" added.
--
Ticket URL: <https://code.djangoproject.com/ticket/31506>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
and timedelta on PostgreSQL and MySQL.
-------------------------------------+-------------------------------------
Reporter: Matthieu Rigal | Owner:
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => TapanGujjar
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/31506#comment:2>
Django
and timedelta on PostgreSQL and MySQL.
-------------------------------------+-------------------------------------
Reporter: Matthieu Rigal | Owner:
| TapanGujjar
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by TapanGujjar):
Hi, the issue is because the DateField has no field converter or
db_converter to convert the value which we got from the database to the
DateField type. I can implement the field converter for the data field but
Is the DateField not having the field converter or db_converter by design?
--
Ticket URL: <https://code.djangoproject.com/ticket/31506#comment:3>
Django
and timedelta on PostgreSQL and MySQL.
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: TapanGujjar => (none)
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/31506#comment:5>
Django
and timedelta on PostgreSQL and MySQL.
-------------------------------------+-------------------------------------
Reporter: Matthieu Rigal | Owner: (none)
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by David Sanders):
There are a few ways to address this but IMHO I don't think anything
should be done here as this is documented PostgreSQL behaviour [1]
Therefore I'd recommend this ticket be marked `wontfix`.
I think it's the developers responsibility to cast in this situation
instead of using `ExpressionWrapper`:
{{{
next_segments = StartModel.objects.filter('start__gt':
OuterRef('start')).order_by('start')
qs = StartModel.objects.annotate(
end=Cast(
Subquery(next_segments.values('start')[:1]) -
datetime.timedelta(days=1),
output_field=DateField(),
)
)
}}}
[1] https://www.postgresql.org/docs/current/functions-datetime.html
#OPERATORS-DATETIME-TABLE
--
Ticket URL: <https://code.djangoproject.com/ticket/31506#comment:6>
Django
and timedelta on PostgreSQL and MySQL.
-------------------------------------+-------------------------------------
Reporter: Matthieu Rigal | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Sergey Fedoseev (removed)
--
Ticket URL: <https://code.djangoproject.com/ticket/31506#comment:7>
Django
and timedelta on PostgreSQL and MySQL.
-------------------------------------+-------------------------------------
Reporter: Matthieu Rigal | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: David Sanders (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/31506#comment:8>
Django
and timedelta on PostgreSQL and MySQL.
-------------------------------------+-------------------------------------
Reporter: Matthieu Rigal | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Matthieu Rigal):
Replying to [comment:6 David Sanders]:
> There are a few ways to address this but IMHO I don't think anything
should be done here as this is documented PostgreSQL behaviour [1]
>
> Therefore I'd recommend this ticket be marked `wontfix`.
>
> I think it's the developers responsibility to cast in this situation
instead of using `ExpressionWrapper`:
>
> {{{
> next_segments = StartModel.objects.filter('start__gt':
OuterRef('start')).order_by('start')
> qs = StartModel.objects.annotate(
> end=Cast(
> Subquery(next_segments.values('start')[:1]) -
datetime.timedelta(days=1),
> output_field=DateField(),
> )
> )
> }}}
>
>
> [1] https://www.postgresql.org/docs/current/functions-datetime.html
#OPERATORS-DATETIME-TABLE
Thanks David!
Seeing it from a PostgreSQL perspective, it makes sense, as interval has
the precision of a timestamp!
However, as this is pretty surprising from a Pythonic point-of-view, I
would rather consider extending the documentation to warn about this
potential trap. It should be clear that `timedelta` can only be cast (by
Psycopg2) to an interval and therefore does not allow the use of the `date
+/- integer → date` operation of PostgreSQL.
Besides the example with `Cast` one may add that in case this patterns is
recurrent, it may be worth adding that creating a custom Field `DateDelta`
translating to integer on PostrgeSQL side, could be an option...
--
Ticket URL: <https://code.djangoproject.com/ticket/31506#comment:9>
Django
and timedelta on PostgreSQL and MySQL.
-------------------------------------+-------------------------------------
Reporter: Matthieu Rigal | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by David Sanders):
Hi Matthieu,
In terms of new features for the framework, it's always a good idea to
start a thread on the mailing list or forum to see what support there is
for the feature: https://code.djangoproject.com/wiki/DevelopersMailingList
😊
Documentation updates are always welcomed. Small updates to documentation
don't require a ticket. I'm not the authority on what the exact
responsibilities are for ExpressionWrapper but I kinda view it as just
glueing up the expressions with correct types without any explicit casting
🤷♂️ Will probably need to clarify that with someone closer to the ORM
code.
--
Ticket URL: <https://code.djangoproject.com/ticket/31506#comment:10>