ArrayField: incorrent len lookup on empty arrays

[mrAdm]

Models:

NullableIntegerArrayModel.objects.create(field=[])
NullableIntegerArrayModel.objects.create(field=[1])
NullableIntegerArrayModel.objects.create(field=[2, 3])
NullableIntegerArrayModel.objects.create(field=[20, 30, 40])

Queries:

NullableIntegerArrayModel.objects.filter(field__len=0)
# return: empty queryset
NullableIntegerArrayModel.objects.filter(field__len__lt=2)
# return only one model (field=[1])

Reason:
PostgreSQL function array_length(ARRAY[]::int4[], 1) return null if array empty.

The solution is to use a function PostgreSQL: COALESCE ().

SELECT * FROM ... WHERE COALESCE(array_length("field", 1), 0)=0

[Tim Graham]

Regression test attached.

[Attila Tovt]

In case field is None coalesce will return 0. So,

SELECT * FROM ... WHERE COALESCE(array_length("field", 1), 0)=0

will also return entries with field==None.
The solution is probably to change line in ​https://github.com/django/django/blob/master/django/contrib/postgres/fields/array.py#L205 to

        return '%s IS NOT NULL AND \
                coalesce(array_length(%s, 1), 0)' % (lhs, lhs), params

or

        return 'CASE WHEN %s IS NULL THEN NULL \
                ELSE coalesce(array_length(%s, 1), 0) \
                END' % (lhs, lhs), params

​PR

[Tim Graham <timograham@…>]

In 88fc9e28:

Fixed #25772 -- Corrected len lookup on ArrayField for empty arrays.

[Tim Graham <timograham@…>]

In e4bd6923:

[1.9.x] Fixed #25772 -- Corrected len lookup on ArrayField for empty arrays.

Backport of 88fc9e2826044110b7b22577a227f122fe9c1fb5 from master

[Tim Graham <timograham@…>]

In 3ec4e73:

[1.8.x] Fixed #25772 -- Corrected len lookup on ArrayField for empty arrays.

Backport of 88fc9e2826044110b7b22577a227f122fe9c1fb5 from master