[Django] #27808: Nested ArrayField with nullable base field generates invalid SQL
Django
--------------------------------------------+------------------------
Reporter: Josef Kolář | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.9
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
--------------------------------------------+------------------------
When I have model
{{{#!python
class NestedNullableIntegerArrayModel(PostgreSQLModel):
field = ArrayField(ArrayField(models.IntegerField(null=True)))
}}}
and trying to save data
{{{#!python
instance = NestedNullableIntegerArrayModel(field=[[None, None], [None,
None]])
instance.save()
}}}
and Django generates
{{{#!SQL
INSERT INTO "postgres_tests_nestednullableintegerarraymodel" ("field")
VALUES (%s) RETURNING
"postgres_tests_nestednullableintegerarraymodel"."id"
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27808>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Josef Kolář | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.9
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Description changed by Josef Kolář:
Old description:
> When I have model
> {{{#!python
> class NestedNullableIntegerArrayModel(PostgreSQLModel):
> field = ArrayField(ArrayField(models.IntegerField(null=True)))
> }}}
> and trying to save data
> {{{#!python
> instance = NestedNullableIntegerArrayModel(field=[[None, None], [None,
> None]])
> instance.save()
> }}}
> and Django generates
> {{{#!SQL
> INSERT INTO "postgres_tests_nestednullableintegerarraymodel" ("field")
> VALUES (%s) RETURNING
> "postgres_tests_nestednullableintegerarraymodel"."id"
> }}}
New description:
When I have model
{{{#!python
class NestedNullableIntegerArrayModel(PostgreSQLModel):
field = ArrayField(ArrayField(models.IntegerField(null=True)))
}}}
and trying to save data
{{{#!python
NestedNullableIntegerArrayModel(field=[[None, None], [None, None]]).save()
}}}
and Django generates
{{{#!sql
INSERT INTO "postgres_tests_nestednullableintegerarraymodel" ("field")
VALUES (%s) RETURNING
"postgres_tests_nestednullableintegerarraymodel"."id"
}}}
with expectable params
{{{#!python
([[None, None], [None, None]], )
}}}
and this query fails in postgres on
{{{
ERROR: column "field" is of type integer[] but expression is of type
text[]
LINE 1: ...estednullableintegerarraymodel" ("field") VALUES (ARRAY['{NU...
^
HINT: You will need to rewrite or cast the expression.
}}}
**But, if I use one of values is not None, model is saved successfully:**
{{{#!python
NestedNullableIntegerArrayModel(field=[[None, None], [None, 42]]).save()
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/27808#comment:1>
Django
Reporter: Josef Kolář | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.9
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Description changed by Josef Kolář:
Old description:
> When I have model
> {{{#!python
> class NestedNullableIntegerArrayModel(PostgreSQLModel):
> field = ArrayField(ArrayField(models.IntegerField(null=True)))
> }}}
> and trying to save data
> {{{#!python
> NestedNullableIntegerArrayModel(field=[[None, None], [None,
> None]]).save()
> }}}
> and Django generates
> {{{#!sql
> INSERT INTO "postgres_tests_nestednullableintegerarraymodel" ("field")
> VALUES (%s) RETURNING
> "postgres_tests_nestednullableintegerarraymodel"."id"
> }}}
> with expectable params
> {{{#!python
> ([[None, None], [None, None]], )
> }}}
> and this query fails in postgres on
> {{{
> ERROR: column "field" is of type integer[] but expression is of type
> text[]
> LINE 1: ...estednullableintegerarraymodel" ("field") VALUES
> (ARRAY['{NU...
> ^
> HINT: You will need to rewrite or cast the expression.
> }}}
> **But, if I use one of values is not None, model is saved successfully:**
> {{{#!python
> NestedNullableIntegerArrayModel(field=[[None, None], [None, 42]]).save()
> }}}
New description:
When I have model
{{{#!python
class NestedNullableIntegerArrayModel(PostgreSQLModel):
field = ArrayField(ArrayField(models.IntegerField(null=True)))
}}}
and trying to save data
{{{#!python
NestedNullableIntegerArrayModel(field=[[None, None], [None, None]]).save()
}}}
and Django generates
{{{#!sql
INSERT INTO "postgres_tests_nestednullableintegerarraymodel" ("field")
VALUES (%s) RETURNING
"postgres_tests_nestednullableintegerarraymodel"."id"
}}}
with expectable params
{{{#!python
([[None, None], [None, None]], )
}}}
and this query fails in postgres on
{{{
ERROR: column "field" is of type integer[] but expression is of type
text[]
LINE 1: ...estednullableintegerarraymodel" ("field") VALUES (ARRAY['{NU...
^
HINT: You will need to rewrite or cast the expression.
}}}
**But, if I use one of values is not None, model is saved successfully:**
{{{#!python
NestedNullableIntegerArrayModel(field=[[None, None], [None, 42]]).save()
}}}
Is it a problem of Django or I should find problem in psycopg?
--
--
Ticket URL: <https://code.djangoproject.com/ticket/27808#comment:2>
Django
Reporter: Josef Kolář | Owner: (none)
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.9
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Description changed by Josef Kolář:
Old description:
> When I have model
> {{{#!python
> class NestedNullableIntegerArrayModel(PostgreSQLModel):
> field = ArrayField(ArrayField(models.IntegerField(null=True)))
> }}}
> and trying to save data
> {{{#!python
> NestedNullableIntegerArrayModel(field=[[None, None], [None,
> None]]).save()
> }}}
> and Django generates
> {{{#!sql
> INSERT INTO "postgres_tests_nestednullableintegerarraymodel" ("field")
> VALUES (%s) RETURNING
> "postgres_tests_nestednullableintegerarraymodel"."id"
> }}}
> with expectable params
> {{{#!python
> ([[None, None], [None, None]], )
> }}}
> and this query fails in postgres on
> {{{
> ERROR: column "field" is of type integer[] but expression is of type
> text[]
> LINE 1: ...estednullableintegerarraymodel" ("field") VALUES
> (ARRAY['{NU...
> ^
> HINT: You will need to rewrite or cast the expression.
> }}}
> **But, if I use one of values is not None, model is saved successfully:**
> {{{#!python
> NestedNullableIntegerArrayModel(field=[[None, None], [None, 42]]).save()
> }}}
> Is it a problem of Django or I should find problem in psycopg?
New description:
When I have model
{{{#!python
class NestedNullableIntegerArrayModel(PostgreSQLModel):
field = ArrayField(ArrayField(models.IntegerField(null=True)))
}}}
and trying to save data
{{{#!python
NestedNullableIntegerArrayModel(field=[[None, None], [None, None]]).save()
}}}
Django generates
{{{#!sql
INSERT INTO "postgres_tests_nestednullableintegerarraymodel" ("field")
VALUES (%s) RETURNING
"postgres_tests_nestednullableintegerarraymodel"."id"
}}}
with expectable params
{{{#!python
([[None, None], [None, None]], )
}}}
but this query fails in postgres on
{{{
ERROR: column "field" is of type integer[] but expression is of type
text[]
LINE 1: ...estednullableintegerarraymodel" ("field") VALUES (ARRAY['{NU...
^
HINT: You will need to rewrite or cast the expression.
}}}
**But, if I use one of values is not None, model is saved successfully:**
{{{#!python
NestedNullableIntegerArrayModel(field=[[None, None], [None, 42]]).save()
}}}
Is it a problem of Django or I should find problem in psycopg?
--
--
Ticket URL: <https://code.djangoproject.com/ticket/27808#comment:3>
Django
Reporter: Josef Kolář | Owner: (none)
Type: Bug | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* version: 1.9 => master
Comment:
Not sure if it's a duplicate but it looks related to #24726 at least.
--
Ticket URL: <https://code.djangoproject.com/ticket/27808#comment:4>
Django
Reporter: Josef Kolář | Owner: (none)
Type: Bug | Status: new
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/27808#comment:5>
Django
Reporter: Josef Kolář | Owner: (none)
Type: Bug | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Marcus Gregersen):
This is due to following bug in psycopg2
https://github.com/psycopg/psycopg2/issues/325
--
Ticket URL: <https://code.djangoproject.com/ticket/27808#comment:6>
Django
Reporter: Josef Kolář | Owner: Hasan
| Ramezani
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Hasan Ramezani):
* status: new => assigned
* owner: (none) => Hasan Ramezani
* has_patch: 0 => 1
Comment:
Seems bug was fixed in `psycopg2`. I just added a test case to prove it.
--
Ticket URL: <https://code.djangoproject.com/ticket/27808#comment:7>
Django
Reporter: Josef Kolář | Owner: Hasan
| Ramezani
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by felixxm):
Bug is fixed in `psycopg2` 2.7.5+.
--
Ticket URL: <https://code.djangoproject.com/ticket/27808#comment:8>
Django
Reporter: Josef Kolář | Owner: Hasan
| Ramezani
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
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):
* status: assigned => closed
* has_patch: 1 => 0
* resolution: => fixed
--
Ticket URL: <https://code.djangoproject.com/ticket/27808#comment:9>
Django
Reporter: Josef Kolář | Owner: Hasan
| Ramezani
Type: Bug | Status: closed
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"84633905273fc916e3d17883810d9969c03f73c2" 84633905]:
{{{
#!CommitTicketReference repository=""
revision="84633905273fc916e3d17883810d9969c03f73c2"
Refs #27808 -- Added test for saving nested ArrayField with nullable base
field.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27808#comment:10>