[Django] #30715: ArrayField is converting AutoField to serial[] being an invalid pSQL type
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: (none)
NyanKiyoshi |
Type: Bug | Status: new
Component: | Version: 2.2
contrib.postgres | Keywords: array, serial,
Severity: Normal | postgres, psql, auto, id
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When doing a annotation using the `ArrayAgg()` function over the `id/pk`
(`django.db.models.fields.AutoField`) field it is casted to `serial[]`
which is an invalid type in pSQL.
From this, we get the following error (caused by
https://github.com/django/django/blob/b10d322c41f66dc7c77c36f90a3532269b25ea93/django/contrib/postgres/fields/array.py#L81-L83):
{{{
psycopg2.errors.UndefinedObject: type "serial[]" does not exist
LINE 1: ... HAVING ARRAY_AGG("item_item"."id" ) @> ARRAY[1,2]::serial[]
}}}
Example:
{{{
#!python
from django.db import models
class Item(models.Model):
pass
}}}
{{{
#!python
from django.contrib.postgres.aggregates import ArrayAgg
from django.db.models import F
from django.test import TestCase
from .models import Item
class TestQueryAutoIDAsArray(TestCase):
def test_query(self):
qs = Item.objects.annotate(pk_list=ArrayAgg(F("id")))
qs = qs.filter(pk_list__contains=[1, 2])
assert len(qs[:]) == 0
}}}
Maybe we should cast auto fields to `int[]`?
--
Ticket URL: <https://code.djangoproject.com/ticket/30715>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: contrib.postgres | Version: 2.2
Severity: Normal | Resolution:
Keywords: array, serial, | Triage Stage:
postgres, psql, auto, id | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Patryk Zawadzki (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/30715#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: NyanKiyoshi | Owner: (none)
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: array, serial, | Triage Stage: Accepted
postgres, psql, auto, id |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* version: 2.2 => master
* stage: Unreviewed => Accepted
Comment:
Thanks for the report, all `ArrayField`'s
[https://github.com/django/django/blob/b10d322c41f66dc7c77c36f90a3532269b25ea93/django/contrib/postgres/fields/array.py#L193-L222
lookups] are affected, using a `Cast()` expression (that already handle
this) instead of `'%s::%s'` should fix this issue (tests should land in
`postgres_tests/test_array.py`).
--
Ticket URL: <https://code.djangoproject.com/ticket/30715#comment:2>
Django
-------------------------------------+-------------------------------------
| Umer
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: array, serial, | Triage Stage: Accepted
postgres, psql, auto, id |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: (none) => Adnan Umer
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/30715#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: NyanKiyoshi | Owner: Adnan
| Umer
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: array, serial, | Triage Stage: Accepted
postgres, psql, auto, id |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
Hi Adam I solved this when preparing
[https://github.com/django/django/pull/11699/ PR 11699]. Are you OK to
reassign ticket to me?
--
Ticket URL: <https://code.djangoproject.com/ticket/30715#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: NyanKiyoshi | Owner: Adnan
| Umer
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: array, serial, | Triage Stage: Accepted
postgres, psql, auto, id |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/11699/ PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/30715#comment:5>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: array, serial, | Triage Stage: Accepted
postgres, psql, auto, id |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: Adnan Umer => felixxm
Comment:
No problem felixxm
--
Ticket URL: <https://code.djangoproject.com/ticket/30715#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: NyanKiyoshi | Owner: felixxm
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
postgres, psql, auto, id | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/30715#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: NyanKiyoshi | Owner: felixxm
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: array, serial, | Triage Stage: Ready for
postgres, psql, auto, id | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"521308e575e4510ef4256f2ba2943a5e570c9328" 521308e5]:
{{{
#!CommitTicketReference repository=""
revision="521308e575e4510ef4256f2ba2943a5e570c9328"
Fixed #30715 -- Fixed crash of ArrayField lookups on ArrayAgg annotations
over AutoField.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30715#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: NyanKiyoshi | Owner: felixxm
Component: contrib.postgres | Version: 2.2
Severity: Normal | Resolution:
postgres, psql, auto, id | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* version: master => 2.2
* resolution: fixed =>
Comment:
Hi felixxm, sorry to tell, but this issue still present in Django 2.2.12
and 2.2.x branch as well.
Also it does not affect only `pk` fields, but at least `ForeignKey` too.
Could you please apply your fix to 2.2?
--
Ticket URL: <https://code.djangoproject.com/ticket/30715#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: NyanKiyoshi | Owner: felixxm
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
postgres, psql, auto, id | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* version: 2.2 => master
* resolution: => fixed
Comment:
The issue has been present since the feature was introduced. Per our
backporting policy this means it doesn't qualify for a backport to 2.2.x
anymore.
See https://docs.djangoproject.com/en/dev/internals/release-process/ for
more details.
--
Ticket URL: <https://code.djangoproject.com/ticket/30715#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: NyanKiyoshi | Owner: felixxm
Type: Bug | Status: closed
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: array, serial, | Triage Stage: Ready for
postgres, psql, auto, id | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by rizzaro):
Is there a workaround for older versions, felixxm? Thank you
--
Ticket URL: <https://code.djangoproject.com/ticket/30715#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: NyanKiyoshi | Owner: felixxm
Type: Bug | Status: closed
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: array, serial, | Triage Stage: Ready for
postgres, psql, auto, id | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
rizzaro, you can apply the patch to your local version or your own fork.
--
Ticket URL: <https://code.djangoproject.com/ticket/30715#comment:12>