Opened 3 years ago

Last modified 10 months ago

#24726 new Bug

ArrayField with IntegerRangeField as base field generates invalid SQL on Insert operations

Reported by: Ederson Mota Owned by:
Component: contrib.postgres Version: master
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

I'm with problems trying to create a model object that contains an ArrayField with IntegerRangeField as base.

I asked about this on #django, and for @knbk this seems a bug. I believe it may be right.

Model:

class PostgreSQLModel(models.Model):
    class Meta:
        abstract = True


class IntegerRangeArrayModel(PostgreSQLModel):
    field = ArrayField(IntegerRangeField())

The initial migration an the table are created fine.

                           Table "public.core_integerrangearraymodel"
 Column |    Type     |                                Modifiers
--------+-------------+--------------------------------------------------------------------------
 id     | integer     | not null default nextval('core_integerrangearraymodel_id_seq'::regclass)
 field  | int4range[] | not null
Indexes:
    "core_integerrangearraymodel_pkey" PRIMARY KEY, btree (id)

However, when I try to create an object, it fails with a SQL error.

integer_range_list = [
    (10, 20),
    (30, 40),
]

IntegerRangeArrayModel.objects.create(
    field=integer_range_list
)

======================================================================
ERROR: test_create_with_tuples (core.tests.TestIntegerRangeArray)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: column "field" is of type int4range[] but expression is of type text[]
LINE 1: ...O "core_integerrangearraymodel" ("field") VALUES (ARRAY['[10...
                                                             ^
HINT:  You will need to rewrite or cast the expression.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/eder/devel/integerrange-array/core/tests.py", line 17, in test_create_with_tuples
    field=integer_range_list
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/manager.py", line 127, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/query.py", line 348, in create
    obj.save(force_insert=True, using=self.db)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/base.py", line 710, in save
    force_update=force_update, update_fields=update_fields)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/base.py", line 738, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/base.py", line 822, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/base.py", line 861, in _do_insert
    using=using, raw=raw)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/manager.py", line 127, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/query.py", line 920, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 963, in execute_sql
    cursor.execute(sql, params)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/utils.py", line 97, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/utils/six.py", line 658, in reraise
    raise value.with_traceback(tb)
  File "/home/eder/.virtualenvs/integerrange-array/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "field" is of type int4range[] but expression is of type text[]
LINE 1: ...O "core_integerrangearraymodel" ("field") VALUES (ARRAY['[10...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

There are no difference if a NumericRange object is used instead.

integer_range_list = [
    NumericRange(10, 20),
    NumericRange(30, 40),
]

IntegerRangeArrayModel.objects.create(
    field=integer_range_list
)

The generated SQL is:

INSERT INTO "core_integerrangearraymodel" ("field") VALUES (ARRAY['[10,20)', '[30,40)']) RETURNING "core_integerrangearraymodel"."id"

It should be something like this:

INSERT INTO "core_integerrangearraymodel" ("field") VALUES (ARRAY['[10,20)'::int4range, '[30,40)'::int4range]) RETURNING "core_integerrangearraymodel"."id"

Or this:

INSERT INTO "core_integerrangearraymodel" ("field") VALUES ('{"[10,20)","[30,40)"}') RETURNING "core_integerrangearraymodel"."id";

I created a test project, with more details, and some test cases to help illustrate the problem.

https://github.com/edrmp/integerrange-array

There is Travis-CI too

https://travis-ci.org/edrmp/integerrange-array

Thanks in advance.

Attachments (1)

ticket_24726.diff (4.3 KB) - added by Ederson Mota 3 years ago.
Diff between django/django@master and edrmp/django@ticket_24726

Download all attachments as: .zip

Change History (14)

comment:1 Changed 3 years ago by Tim Graham

Triage Stage: UnreviewedAccepted

comment:2 Changed 3 years ago by Ederson Mota

I forked the Django repository and added some tests:

https://github.com/edrmp/django/tree/ticket_24726

Changed 3 years ago by Ederson Mota

Attachment: ticket_24726.diff added

Diff between django/django@master and edrmp/django@ticket_24726

comment:3 Changed 3 years ago by Ederson Mota

Taking a closer look at the exception generated by one of tests created mentioned above, lead me to some things that I would like to share.

The exception is trowed at db/backends/utils.py, line 64, method execute(), when trying to:

return self.cursor.execute(sql, params)

With the SQL:

INSERT INTO "postgres_tests_integerrangesarraymodel" ("int_ranges", "bigint_ranges") VALUES (%s, %s) RETURNING "postgres_tests_integerrangesarraymodel"."id"

And params:

([NumericRange(10, 20, '[)'), NumericRange(30, 40, '[)')], [NumericRange(7000000000, 10000000000, '[)'), NumericRange(50000000000, 70000000000, '[)')])

At this time, it's a method of psycopg2.extensions.cursor.

I wondered if this error will also occur using pycopg2 directly, and the answer is yes.

import psycopg2
from psycopg2.extras import NumericRange

# Connect to an existing database
conn = psycopg2.connect("dbname=integerrange-array user=postgres")

# Open a cursor to perform database operations
cur = conn.cursor()


# Pass data to fill a query placeholders and let Psycopg perform
# the correct conversion (no more SQL injections!)
cur.execute("INSERT INTO core_integerrangearraymodel (field) VALUES (%s)",
            ([NumericRange(10, 20, '[)'), NumericRange(30, 40, '[)')],)
            )

Traceback (most recent call last):
  File "test_psycopg2.py", line 14, in <module>
    ([NumericRange(10, 20, '[)'), NumericRange(30, 40, '[)')],)
psycopg2.ProgrammingError: column "field" is of type int4range[] but expression is of type text[]
LINE 1: ... INTO core_integerrangearraymodel (field) VALUES (ARRAY['[10...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

I think that this issue on psycopg2 is somehow related:

https://github.com/psycopg/psycopg2/issues/231

It's not a bug, but he describe a way to circumvent this behavior:

https://github.com/psycopg/psycopg2/issues/231#issuecomment-53741200

Thanks.

comment:4 Changed 3 years ago by Marc Tamlyn

This is a tricky one to fix. My inclination is that half the fault lies with postgres, and perhaps it could be fixed with psycopg2 as well.

The nicest solution is to add a ::int4range[] at the end of the parameter, but doing that is tricky to implement. It should be possible to register a custom typecaster for arrays of ranges with psycopg2. It feels like it should just work though...

I have raised https://github.com/psycopg/psycopg2/issues/313 with psycopg2 to get their feedback.

comment:5 Changed 2 years ago by Daniele Varrazzo

Hello,

I think your best shot is to use a NumericRange subtype with an adapter adding ::int4range. Adding ::int4range[] after the array is impractical as you should know subclass the list instead.

An example implementation is in this comment.

I'll think whether we should introduce these objects in the library, I'm not so sure, because usually end users are able to specify a placeholder like %s::int4range[] and work around the problem without introducing too many details of the Postgres data system into the Python objects.

comment:6 Changed 17 months ago by Adam Zapletal

Marc,

How would you like to proceed with this ticket? I'm willing to help close this with some guidance.

Fixing this would help my use case!

Thanks!

comment:7 Changed 16 months ago by Adam Zapletal

Owner: set to Adam Zapletal
Status: newassigned

comment:8 Changed 16 months ago by Adam Zapletal

https://github.com/Crossway/django/commit/718bfaa18a12fb059d9e5cf8c4c716402ac8cb4d

This patch integrates and updates the contributions from edrmp and dvarrazzo above. The tests pass, but I'm not sure I put the psycopg2 adapter bits in the right place. I guess this is more a proof of concept.

I think there needs to be more discussion about how arrays of range fields should work. Lookups don't work right now, and I'm not sure how they would.

Also, dvarrazzo's suggestion to subclass NumericRange works, but wouldn't that require range objects destined for the database to be created with the adapted subclasses? I'm not sure if that's desired for Django. I'm interested to hear what the core team thinks.

Anyway, I hope this patch is helpful!

I'm unassigning this ticket for now as I don't think I can make progress without some design discussion taking place.

comment:9 Changed 16 months ago by Adam Zapletal

Owner: Adam Zapletal deleted
Status: assignednew

comment:10 Changed 16 months ago by Adam Zapletal

I interacted with mjtamlyn over email, and he asked me to open a PR with my patch.

PR is here: https://github.com/django/django/pull/7099

comment:11 Changed 16 months ago by Adam Zapletal

Has patch: set

comment:12 Changed 14 months ago by Tim Graham

Patch needs improvement: set

I guess this isn't ready for review since it's marked "WIP".

comment:13 Changed 10 months ago by Tim Graham

#27808 is related or a duplicate.

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