#24726: ArrayField with IntegerRangeField as base field generates invalid SQL on
Insert operations
----------------------------------+------------------------------------
Reporter: edrmp | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: master
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 edrmp):
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:
{{{#!python
return self.cursor.execute(sql, params)
}}}
With the SQL:
{{{#!sql
INSERT INTO "postgres_tests_integerrangesarraymodel" ("int_ranges",
"bigint_ranges") VALUES (%s, %s) RETURNING
"postgres_tests_integerrangesarraymodel"."id"
}}}
And params:
{{{#!python
([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.
{{{#!python
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24726#comment:3>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/063.2b76b90d091dfe1fb34b829daaadb600%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
