Re: Strange query when using annotate and count

Hello Cristiano,

I understand your frustration but please avoid using the developer mailing list
as a second tier support channel. I suggest you try the IRC #django channel if
you need to want to get faster support.

What's happening here is that annotate() really means "select this field" while
in your other case you use a lookup (summary__icontains) which are only going to
be added to the WHERE clause of your query.

I'm not sure why you are annotating your queryset without referring to it in
a filter clause later on but the ORM cannot simply ignore it when you are
performing your `count()` because some annotations could interfere with grouping
somehow.

There is an open ticket[0] to add support for an `alias()` method that would
allow the ORM to clear/ignore the specified expressions if it's not referenced
in the query.

In the mean time I think the best approach would be to avoid annotating the
queryset if your don't need to reference the score.

Cheers,
Simon

[0] https://code.djangoproject.com/ticket/27719

Le mardi 21 novembre 2017 08:46:21 UTC-5, Cristiano Coelho a écrit :

Hmm, should I try with the dev mailing list? Guess it's something no one faced before?

El martes, 14 de noviembre de 2017, 22:54:23 (UTC-3), Cristiano Coelho escribió:

I'm getting some very odd query when combining annotate with count. See the following:

>>> q = Vulnerability.objects.annotate(score=WordTrigramCustomSimilarity('test','summary'))
>>> q.count()
3094
>>> print connection.queries[-1]
'SELECT COUNT(*) 
FROM (
    SELECT "vulnerabilities_vulnerability"."id" AS Col1, custom_word_similarity(\'test\', "vulnerabilities_vulnerability"."summary") AS "score" 
    FROM "vulnerabilities_vulnerability" 
    GROUP BY "vulnerabilities_vulnerability"."id", custom_word_similarity(\'test\', "vulnerabilities_vulnerability"."summary")
) subquery
>>> q2 = Vulnerability.objects.filter(summary__icontains='test')
>>> q2.count()
33
>>> print connection.queries[-1]
'SELECT COUNT(*) AS "__count" 
FROM "vulnerabilities_vulnerability" 
WHERE UPPER("vulnerabilities_vulnerability"."summary"::text) LIKE UPPER(\'%test%\')

Custom function code, is this what's causing the odd count behavior? Did I miss anything?

class WordTrigramCustomSimilarity(Func):
    function = 'custom_word_similarity' 
    def __init__(self, string, expression, **extra):
        if not hasattr(string, 'resolve_expression'):
            string = Value(string)
        super(WordTrigramCustomSimilarity, self).__init__(string, expression, output_field=FloatField(), **extra)

I would expect for the query to be a simple count, rather than a nested query with a useless group by (correct me if I'm wrong).

The issue gets even worse if the function is expensive, since it gets called when it's not needed at all, more than once.

Also the issue behaves pretty much the same if the queryset includes filtering and ordering but I didn't include it here for simplicity.

Using Django 1.11.7 + postgres (psycopg) backend.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/f8348886-0a8b-46e0-8bd5-46597ba87408%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.