#24431: Combining extra(), annotate() or distinct() and count() can generate
invalid SQL
-------------------------------------+-------------------------------------
Reporter: briankrznarich | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8beta1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: extra distinct | Triage Stage: Accepted
aggregate count annotate |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by briankrznarich):
Our specific need is ordering on a CASE statement. As an example, for
any employee in an organizational hierarchy we wanted to show all of that
employee's subordinates first, followed by all of his superiors. The
subordinates are ordered top-down, the superiors are ordered bottom-up.
I was not aware of F() functions, those will be useful. Since you pointed
it out, I've started perusing the new conditional-expression
documentation, and it looks like we might be able to replace extra() with
that.
https://docs.djangoproject.com/en/1.8/ref/models/conditional-expressions/
extra()'s `tables=` argument doesn't work unless you also provide a
`where=` clause where you do the join yourself. Otherwise the table just
gets tacked on after a comma. (`SELECT * FROM
auth_permission,django_content_type WHERE....`). This only happens,
however, if the table wasn't already needed for a JOIN(which itself seems
contrary to the documentation, but anyway...).
As a consequence, and a final example of a stupid Django trick, I've
combined all of these misbehaviors into one fun queryset. You'd think
len(queryset) and queryset.count() would yield the same value, but....
{{{#!python
In [46]: q =
Permission.objects.all().select_related('content_type').extra(tables=['django_content_type'],select={'foo':"django_content_type.app_label"})
In [47]: q.count()
Out[47]: 2188688
In [48]: len(q)
Out[48]: 2581
In [49]: q.count()
Out[49]: 2581
}}}
The call to len() caches the queryset, and permanently alters the return
value of count(). The database counts show what is going on...
{{{
=> select count(*) from auth_permission;
count
-------
2581
(1 row)
=> select count(*) from django_content_type;
count
-------
848
(1 row)
=> select count(*) from django_content_type,auth_permission;
count
---------
2188688
}}}
Anyway, SQL generation is hard, but Django does an incredible job of it.
You just can't win 'em all.
Thanks for your help. As I said above, now the I know it exists, the
extra() problem is easy to work around. We'll certainly try to move to
F() and friends after 1.8 is in production.
--
Ticket URL: <https://code.djangoproject.com/ticket/24431#comment:7>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/072.a75e05b3aecb666cfb0f3e598a8c019b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
