#24431: Combining extra(), annotate() or distinct() and count() can generate
invalid SQL generation
-------------------------------------+-------------------------------------
Reporter: briankrznarich | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8beta1
(models, ORM) | Keywords: extra distinct
Severity: Normal | aggregate count annotate
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
When applying a count() to a queryset that contains extra() values and
either an annotation, or a distinct() call, get_aggregation() discards any
existing select_related entries. If the extra() components rely on those
joins, the SQL will be invalid.
The failures are new to 1.8, and do not appear to be present in 1.7. (I
discovered the problem when upgrading fom 1.7 to beta for testing).
Example:
Permission.objects.all().select_related('content_type').extra(select={'foo':"django_content_type.app_label>'q'"}).distinct().count()
ProgrammingError: missing FROM-clause entry for table
"django_content_type"
LINE 1: SELECT COUNT('*') FROM (SELECT DISTINCT (django_content_type.
Remove the distinct(), or the count(), and it's fine (this is a contrived
example, I know it makes no practical sense).
Another Example with some toy models:
class Address(models.Model):
city = models.CharField(max_length=40)
class Author(models.Model):
name = models.CharField(max_length=40)
address = models.ForeignKey(Address)
class BlogPost(models.Model):
data = models.CharField(max_length=40)
author = models.ForeignKey(Author)
For each author, select a count of their total blog entries, as well as a
custom boolean value based on their address:
Author.objects.all().annotate(blogpost_count=Count('blogpost')).select_related('address').extra(select={'in_maryland':"address.state='MD'"}).count()
This is fine until you append the count()
We hit this issue where TastyPie applies count() to then end of some
complicated querysets, far far away from where we constructed them. In
our case, we're using extra() values for an order_by that requires several
comparisons.
A potential fix is in django/db/models/sql/query.py line 396 in
get_aggregation().
Change
inner_query.select_related = False
To
if not self._extra: #Skip optimization, extra() fields might need these
joins
inner_query.select_related = False
The cause appears to be a performance optimization (don't do joins if the
results don't appear the affect the aggregate).
This has similar keywords and errors to #11329, but I think the underlying
cause is different and possibly easier to address.
--
Ticket URL: <https://code.djangoproject.com/ticket/24431>
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/057.08a64af52e757b8c4152360b8a13a492%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
