ArrayField lookups crash on ArrayAgg() over AutoFields.

[Mikail]

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:

from django.db import models


class Item(models.Model):
    pass

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[]?

[Mariusz Felisiak]

Thanks for the report, all ArrayField's ​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).

[Mariusz Felisiak]

Hi Adam I solved this when preparing ​PR 11699. Are you OK to reassign ticket to me?

[Mariusz Felisiak]

​PR

[Adnan Umer]

No problem felixxm

[Carlton Gibson <carlton@…>]

In 521308e5:

Fixed #30715 -- Fixed crash of ArrayField lookups on ArrayAgg annotations over AutoField.

[Ivan Piskunov]

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?

[Mariusz Felisiak]

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.

[rizzaro]

Is there a workaround for older versions, felixxm? Thank you

[Mariusz Felisiak]

rizzaro, you can apply the patch to your local version or your own fork.

[rizzaro]

Unfortunately this is not possible on my situation but I've found JSONBAgg works for my case. Thanks!