Opened 2 years ago

Last modified 2 years ago

#24991 assigned Bug

Range types not properly converted to SQL in QuerySet.query.__str__()

Reported by: Villiers Strauss Owned by: Dmitry Dygalo
Component: contrib.postgres Version: 1.8
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description (last modified by Villiers Strauss)

The string representation of the QuerySet.query object, which is useful in debugging, does not convert range types to their SQL equivalents, instead it just displays the string representation of the range type.

>>> import datetime
>>> from django.utils.timezone import utc
>>> from psycopg.extras import NumericRange, DateRange, DateTimeTZRange
>>> from myapp.models import MyModel  # model defintion is irrelevant

>>> print(MyModel.objects.filter(num_rng__overlap=NumericRange(1, 5)).query)
SELECT [...] FROM "myapp_mymodel" WHERE ("myapp_mymodel"."num_rng" && NumericRange(1, 5, '[)'))

>>> print(MyModel.objects.filter(date_rng__overlap=DateRange(datetime.date(2015, 5, 1), datetime.date(2015, 6, 1))).query)
SELECT [...] FROM "myapp_mymodel" WHERE ("myapp_mymodel"."date_rng" && DateRange(datetime.date(2015, 5, 1), datetime.date(2015, 6, 1), '[)'))

>>> print(MyModel.objects.filter(dt_rng__overlap=DateTimeTZRange(datetime.datetime(2015, 5, 1, tzinfo=utc), datetime.datetime(2015, 6, 1, tzinfo=utc))).query)
SELECT [...] FROM "myapp_mymodel" WHERE ("myapp_mymodel"."dt_rng" && DateTimeTZRange(datetime.datetime(2015, 5, 1, 0, 0, tzinfo=<UTC>), datetime.datetime(2015, 6, 1, 0, 0, tzinfo=<UTC>), '[)'))

The expected output is numrange(1.0, 5.0), daterange('2015-05-01', '2015-06-01') and tstzrange('2015-05-01 00:00:00+00:00', '2015-06-01 00:00:00+00:00') respectively.

Change History (7)

comment:1 Changed 2 years ago by Tim Graham

Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

comment:2 Changed 2 years ago by Villiers Strauss

Description: modified (diff)

comment:3 Changed 2 years ago by Dmitry Dygalo

Owner: set to Dmitry Dygalo
Status: newassigned

comment:4 Changed 2 years ago by Dmitry Dygalo

Based on psycopg2.extensions.adapt output it should be converted as:
NumericRange(1, 5) to '[1,5)'
DateRange(datetime.date(2015, 5, 1), datetime.date(2015, 6, 1)) to daterange('2015-05-01'::date, '2015-06-01'::date, '[)')
DateTimeTZRange(datetime.datetime(2015, 5, 1, tzinfo=utc), datetime.datetime(2015, 6, 1, tzinfo=utc)) to tstzrange('2015-05-01T00:00:00+00:00'::timestamptz, '2015-06-01T00:00:00+00:00'::timestamptz, '[)')

Please correct me if I'm wrong. If it is true, then I'll try to provide PR for that.

comment:5 in reply to:  4 Changed 2 years ago by Villiers Strauss

Replying to Stranger6667:

Based on psycopg2.extensions.adapt output it should be converted as:
NumericRange(1, 5) to '[1,5)'
DateRange(datetime.date(2015, 5, 1), datetime.date(2015, 6, 1)) to daterange('2015-05-01'::date, '2015-06-01'::date, '[)')
DateTimeTZRange(datetime.datetime(2015, 5, 1, tzinfo=utc), datetime.datetime(2015, 6, 1, tzinfo=utc)) to tstzrange('2015-05-01T00:00:00+00:00'::timestamptz, '2015-06-01T00:00:00+00:00'::timestamptz, '[)')

Please correct me if I'm wrong. If it is true, then I'll try to provide PR for that.

That looks right. The output should ideally be exactly what gets executed in the database, to make debugging easier.

comment:6 Changed 2 years ago by Tim Graham

Has patch: set

Looks like #25705 provides a patch for this issue.

comment:7 Changed 2 years ago by Tim Graham

Patch needs improvement: set

Created a pull request with the patch from #25705 but there are some test failures.

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