PostgreSQL UNACCENT function not working on annotations

[Ismael Jerez]

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.

[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.

[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 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.

[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.

Thanks you anyway,
Ismael.