[Django] #32461: PostgreSQL UNACCENT function not working on annotations

Django

unread,

Feb 19, 2021, 4:43:09 AM2/19/21

to django-...@googlegroups.com

#32461: PostgreSQL UNACCENT function not working on annotations
-------------------------------------+-------------------------------------
Reporter: Ismael | Owner: nobody
Jerez |
Type: Bug | Status: new
Component: Database | Version: 2.2
layer (models, ORM) | Keywords: annotate,
Severity: Normal | annotations, unaccent, postgresql
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Hi!

I want to use unaccent lookup for annotated fields in a Queryset but it is
not working. This is my example:

{{{
whens = [
{
'when': Q(field=True}),
'then': Value('Yes')
},
{
'when': Q(field=False}),
'then': Value('No')
},
]
case_whens = [models.When(when['when'], then=when['then']) for when in
whens]
queryset = queryset.annotate(
"field_custom"=models.Case(
*case_whens,
output_field=models.CharField()
)
)
}}}

This work fine and the new field (field_custom) returns correct values.

But when I use unaccent lookup in a filter...

{{{
result = queryset.filter(field_custom__unaccent__icontains="és")
}}}

This gets me an empty queryset as result. If I use the same filter on
another field (non-annotated one) works fine.

Thanks you in advance,
Ismael.

P.S.: This is just an example, it has no sense but I cannot show my
original code on here.

--
Ticket URL: <https://code.djangoproject.com/ticket/32461>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Feb 19, 2021, 5:34:04 AM2/19/21

to django-...@googlegroups.com

#32461: PostgreSQL UNACCENT function not working on annotations
-------------------------------------+-------------------------------------

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => needsinfo
* component: Database layer (models, ORM) => contrib.postgres

Comment:

Thanks for this report, however it works for me. I've tried with the
following test:

{{{
def test_unaccent_annotation(self):
from django.db.models import Value
qs = self.Model.objects.annotate(
field_custom=Value('àéÖŻÓłĆ'),
).filter(field_custom__unaccent__icontains='zolc')
}}}

Also, Django 2.2 doesn't receive bugfixes anymore. Can you reproduce this
issue with Django 3.1+? Can you also provide a sample project or an
example that is at lease semantically correct? I'm not sure how the
described queryset could return any rows since `field_custom` is always
`Yes` or `No` and you filter against `és`.

--
Ticket URL: <https://code.djangoproject.com/ticket/32461#comment:1>

Django

unread,

Feb 19, 2021, 5:53:31 AM2/19/21

to django-...@googlegroups.com

#32461: PostgreSQL UNACCENT function not working on annotations
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Ismael Jerez):

Thanks for your comment.

My apologise, I forgot to add "icontains" to the filtering code. We have
to work in Django 2.2 until my organization decides to upgrade to 3.x
version.

I will try to test in a simple way in order to validate if it is a bug of
Django 2.2 version or not.

I will reply you with my results soon.

Replying to [comment:1 Mariusz Felisiak]:

> Thanks for this report, however it works for me. I've tried with the
following test:
>
> {{{
> def test_unaccent_annotation(self):
> from django.db.models import Value
> qs = self.Model.objects.annotate(
> field_custom=Value('àéÖŻÓłĆ'),
> ).filter(field_custom__unaccent__icontains='zolc')
> }}}
>
> Also, Django 2.2 doesn't receive bugfixes anymore. Can you reproduce
this issue with Django 3.1+? Can you also provide a sample project or an
example that is at lease semantically correct? I'm not sure how the
described queryset could return any rows since `field_custom` is always
`Yes` or `No` and you filter against `és`.

--
Ticket URL: <https://code.djangoproject.com/ticket/32461#comment:2>

Django

unread,

Feb 19, 2021, 6:02:20 AM2/19/21

to django-...@googlegroups.com

#32461: PostgreSQL UNACCENT function not working on annotations
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Ismael Jerez):

It seems it works fine in a simple example like this:

{{{
queryset = queryset.annotate(new_field=Value("hello",
output_field=CharField()))
queryset.filter(new_field__unaccent__icontains="héllo")
}}}

My orginal code has more complexity so I have to keep looking for the
problem. I am sorry for opening a ticket without testing in a simple way
first.