#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: Normal | Resolution:
Keywords: extra distinct | Triage Stage:
aggregate count annotate | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by briankrznarich:
Old description:
> 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:
> {{{#!python
> 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:
> {{{#!python
> 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
> {{{#!python
> inner_query.select_related = False
> }}}
> To
> {{{#!python
> 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.
New description:
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 tables. If the `extra()` components
rely on those tables, the generated 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:
{{{#!python
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:
{{{#!python
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:
{{{#!python
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
{{{#!python
inner_query.select_related = False
}}}
To
{{{#!python
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#comment:2>
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.51b10a42f96b8c5ad03eb5b805ce46cc%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
