Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#25772 closed Bug (fixed)

ArrayField: incorrent len lookup on empty arrays

Reported by: mrAdm Owned by: Tim Graham <timograham@…>
Component: contrib.postgres Version: 1.8
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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

Attachments (1)

25772-test.diff (659 bytes) - added by Tim Graham 5 years ago.

Download all attachments as: .zip

Change History (7)

comment:1 Changed 5 years ago by mrAdm

Description: modified (diff)

comment:2 Changed 5 years ago by Tim Graham

Summary: ArrayField: incorrent len lookupArrayField: incorrent len lookup on empty arrays
Triage Stage: UnreviewedAccepted

Regression test attached.

Changed 5 years ago by Tim Graham

Attachment: 25772-test.diff added

comment:3 Changed 5 years ago by Attila Tovt

Has patch: set

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

comment:4 Changed 5 years ago by Tim Graham <timograham@…>

Owner: set to Tim Graham <timograham@…>
Resolution: fixed
Status: newclosed

In 88fc9e28:

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

comment:5 Changed 5 years ago by Tim Graham <timograham@…>

In e4bd6923:

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

Backport of 88fc9e2826044110b7b22577a227f122fe9c1fb5 from master

comment:6 Changed 5 years ago by Tim Graham <timograham@…>

In 3ec4e73:

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

Backport of 88fc9e2826044110b7b22577a227f122fe9c1fb5 from master

Note: See TracTickets for help on using tickets.
Back to Top