[Django] #25772: ArrayField: incorrent len lookup
Django
----------------------------------+-----------------
Reporter: mrAdm | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.8
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+-----------------
Models:
{{{
NullableIntegerArrayModel.objects.create(field=[])
NullableIntegerArrayModel.objects.create(field=[1])
NullableIntegerArrayModel.objects.create(field=[2, 3])
NullableIntegerArrayModel.objects.create(field=[20, 30, 40])
NullableIntegerArrayModel.objects.create(field=None)
}}}
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25772>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: mrAdm | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.8
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 mrAdm):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> Models:
> {{{
> NullableIntegerArrayModel.objects.create(field=[])
> NullableIntegerArrayModel.objects.create(field=[1])
> NullableIntegerArrayModel.objects.create(field=[2, 3])
> NullableIntegerArrayModel.objects.create(field=[20, 30, 40])
> NullableIntegerArrayModel.objects.create(field=None)
> }}}
>
> 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
> }}}
New description:
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
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25772#comment:1>
Django
----------------------------------+------------------------------------
Reporter: mrAdm | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.8
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* Attachment "25772-test.diff" added.
Django
----------------------------------+------------------------------------
Reporter: mrAdm | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.8
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* stage: Unreviewed => Accepted
Comment:
Regression test attached.
--
Ticket URL: <https://code.djangoproject.com/ticket/25772#comment:2>
Django
----------------------------------+------------------------------------
Reporter: mrAdm | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.8
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Uran198):
* has_patch: 0 => 1
Comment:
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
{{{#!python
return '%s IS NOT NULL AND \
coalesce(array_length(%s, 1), 0)' % (lhs, lhs), params
}}}
or
{{{#!python
return 'CASE WHEN %s IS NULL THEN NULL \
ELSE coalesce(array_length(%s, 1), 0) \
END' % (lhs, lhs), params
}}}
[https://github.com/django/django/pull/5705 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/25772#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: mrAdm | Owner: Tim
| Graham <timograham@…>
Type: Bug | Status: closed
Component: contrib.postgres | Version: 1.8
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* owner: => Tim Graham <timograham@…>
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"88fc9e2826044110b7b22577a227f122fe9c1fb5" 88fc9e28]:
{{{
#!CommitTicketReference repository=""
revision="88fc9e2826044110b7b22577a227f122fe9c1fb5"
Fixed #25772 -- Corrected __len lookup on ArrayField for empty arrays.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25772#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: mrAdm | Owner: Tim
| Graham <timograham@…>
Type: Bug | Status: closed
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"3ec4e739dd4ac3f1dea92cdec858bf491f63d5e8" 3ec4e73]:
{{{
#!CommitTicketReference repository=""
revision="3ec4e739dd4ac3f1dea92cdec858bf491f63d5e8"
[1.8.x] Fixed #25772 -- Corrected __len lookup on ArrayField for empty
arrays.
Backport of 88fc9e2826044110b7b22577a227f122fe9c1fb5 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25772#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: mrAdm | Owner: Tim
| Graham <timograham@…>
Type: Bug | Status: closed
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"e4bd6923bdc3216fe958f1b1c50e95f4f77e2ada" e4bd6923]:
{{{
#!CommitTicketReference repository=""
revision="e4bd6923bdc3216fe958f1b1c50e95f4f77e2ada"
[1.9.x] Fixed #25772 -- Corrected __len lookup on ArrayField for empty
arrays.
Backport of 88fc9e2826044110b7b22577a227f122fe9c1fb5 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25772#comment:5>