filtering by annotation executes sql function twice

[Tim-Erwin]

I have two models A and B connected by a ManyToOne relation (A has many Bs). When I filter A by the number of related Bs

A.objects.annotate(myCount=Count('bs')).filter(myCount__gt=2)

Django creates a (my)SQL query like so:

SELECT `a`.`id`, `a`.`name`, COUNT(`b`.`id`) AS `myCount` FROM `a` LEFT OUTER JOIN `b` ON ( `a`.`id` = `b`.`a_id` ) GROUP BY `a`.`id` HAVING COUNT(`b`.`id`) > 2 ORDER BY NULL LIMIT 21;

In principle this works, but COUNT is executed twice instead of using the alias 'myCount'. This might not matter in this particular example but I have a custom SQL function that I want to use the same way. It is much more costly and thus the query execution time is basically doubled.

I cannot see any scenario where executing the function twice is of any benefit. I'd suggest to fix this so the alias is used in the HAVING clause.

[Tim Graham]

Not sure how difficult this might be to implement or if there are problems with doing so that I'm not seeing. If not, we could document the limitation.

[Josh Smeaton]

Referring to aliases in the having clause is only supported by MySQL, and only if the ONLY_FULL_GROUP_BY option is not enabled (so it's supported by default). It is not standard SQL.

To support the SQL syntax of referring to aliases, Django would have to detect (or document) whether or not ONLY_FULL_GROUP_BY is active, and then only generate this kind of SQL for MySQL backend with ONLY_FULL_GROUP_BY disabled.

Are you sure that there is a performance difference between using the alias or using the function again? I would be extremely surprised if the query with the alias wasn't rewritten by the query optimiser to the standard structure anyway. I'm finding it very difficult to determine whether or not there is a performance difference without doing some tests - google-fu isn't turning anything up.

If we can determine a performance difference, then this ticket will be worth following up. If there is no performance difference though, we should close as wontfix, because the style of SQL used shouldn't matter in this case.

Tim-Erwin, are you able to share any concrete performance figures or execution plans?

[Tim Graham]

Closing as needsinfo in absence of follow up.