Opened 4 years ago

Last modified 17 months ago

#31506 new Bug

ExpressionWrapper() doesn't respect output_field when combining DateField and timedelta on PostgreSQL and MySQL.

Reported by: Matthieu Rigal Owned by:
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Josh Smeaton, David Sanders Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

The problem actually exists since 1.11 to 2.2, probably also in 3.X. Lastly tested with Django 2.2.12 and psycopg2 2.8.3

Take the model

StartModel(models.Model):
    start = models.DateField()

When working on the queryset in this way:

next_segments = StartModel.objects.filter('start__gt': OuterRef('start')).order_by('start')
qs = StartModel.objects.annotate(
            end=ExpressionWrapper(
                Subquery(next_segments.values('start')[:1]) - datetime.timedelta(days=1),
                output_field=DateField(),
            )
        )

qs.first().end is of type datetime, not date...

Attachments (1)

test_31506.diff (1.1 KB ) - added by Mariusz Felisiak 4 years ago.

Download all attachments as: .zip

Change History (11)

comment:1 by Mariusz Felisiak, 4 years ago

Cc: Josh Smeaton added
Summary: ExpressionWrapper on DateField + timedelta always returns DateTimeField, should DateFieldExpressionWrapper() doesn't respect output_field when combining DateField and timedelta on PostgreSQL and MySQL.
Triage Stage: UnreviewedAccepted
Version: 2.2master

Thanks for this ticket. I reproduced this issue on MySQL and PostgreSQL. I attached a simple test.

Reproduced at 060d9d4229c436c44cf8e3a301f34c4b1f9f6c85.

by Mariusz Felisiak, 4 years ago

Attachment: test_31506.diff added

comment:2 by TapanGujjar, 4 years ago

Owner: changed from nobody to TapanGujjar
Status: newassigned

comment:3 by TapanGujjar, 4 years ago

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?

comment:4 by Sergey Fedoseev, 4 years ago

Cc: Sergey Fedoseev added

ExpressionWrapper allows you to specify the type returned by backend and at least on PostgreSQL it's not date:

test=# SELECT pg_typeof('1999-01-08'::date - '1 days'::interval);
          pg_typeof          
-----------------------------
 timestamp without time zone
(1 row)

Instead you could use

ExpressionWrapper(
    Subquery(next_segments.values('start')[:1]) - 1,
    output_field=DateField(),
)

comment:5 by TapanGujjar, 3 years ago

Owner: TapanGujjar removed
Status: assignednew

comment:6 by David Sanders, 17 months ago

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

comment:7 by Sergey Fedoseev, 17 months ago

Cc: Sergey Fedoseev removed

comment:8 by David Sanders, 17 months ago

Cc: David Sanders added

in reply to:  6 comment:9 by Matthieu Rigal, 17 months ago

Replying to 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...

comment:10 by David Sanders, 17 months ago

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.

Note: See TracTickets for help on using tickets.
Back to Top