[Django] #24044: filtering by annotation executes sql function twice
Django
----------------------------------------------+----------------------------
Reporter: Tim-Erwin | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.7
Severity: Normal | Keywords: annotate
| filter
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24044>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Tim-Erwin | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: annotate filter | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: => 0
* needs_tests: => 0
* version: 1.7 => master
* needs_docs: => 0
* type: Bug => Cleanup/optimization
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24044#comment:1>
Django
Reporter: Tim-Erwin | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: annotate filter | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by jarshwah):
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/24044#comment:2>
Django
Reporter: Tim-Erwin | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: annotate filter | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by jarshwah):
* cc: josh.smeaton@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/24044#comment:3>
Django
Reporter: Tim-Erwin | Owner: nobody
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Keywords: annotate filter | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: new => closed
* resolution: => needsinfo
Comment:
Closing as needsinfo in absence of follow up.
--
Ticket URL: <https://code.djangoproject.com/ticket/24044#comment:4>