[Django] #18378: Q() combined with annotate() can produce bad SQL
Django
----------------------------------------------+--------------------
Reporter: joseph.helfer@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.3
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
A query like this:
{{{#!python
Model2.objects.annotate(bar=Count('foo')).filter(Q(bar__gt=0)|Q(model1__name='goo')).query
}}}
produces SQL like this
{{{#!sql
SELECT `foo_model2`.`id`, `foo_model2`.`model1_id`,
COUNT(`foo_model2`.`id`) AS `bar`
FROM `foo_model2`
INNER JOIN `foo_model1` ON (`foo_model2`.`model1_id` = `foo_model1`.`id`)
GROUP BY `foo_model2`.`id`, `foo_model2`.`id`, `foo_model2`.`model1_id`
HAVING (COUNT(`foo_model2`.`id`) > 0 OR `foo_model1`.`name` = foo ) ORDER
BY NULL
}}}
which results in an exception like this:
{{{
OperationalError: (1054, "Unknown column 'foo_model1.name' in 'having
clause'")
}}}
It seems that MySQL only allows non-aggregate references to column in
HAVING clauses if they are explicitly SELECTed (for example, the above
query works if `foo_model1.name` is added to the selected columns).
I should mention that this query ''does'' work if the "|" is changed for a
"&", because then the second condition is moved into a WHERE clause, and
it also works if `model1__name` is replaced with `model1__id`, because
then `foo_model1`.`name`is replaced with `foo_model2.model1_id` in the
HAVING clause, and the latter is explicitly selected.
--
Ticket URL: <https://code.djangoproject.com/ticket/18378>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Changes (by anonymous):
* needs_docs: => 0
* needs_tests: => 0
* needs_better_patch: => 0
Comment:
In the description, I changed the Django query and forgot to paste the new
SQL: the last "foo" in the SQL should of course be a "goo", and the
COUNT(foo_model2.id) should be COUNT(foo_model2.goo).
--
Ticket URL: <https://code.djangoproject.com/ticket/18378#comment:1>
Django
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Comment (by anonymous):
Replying to [comment:1 anonymous]:
> In the description, I changed the Django query and forgot to paste the
new SQL: the last "foo" in the SQL should of course be a "goo", and the
COUNT(foo_model2.id) should be COUNT(foo_model2.goo).
I mean, COUNT(foo_model2.foo)
--
Ticket URL: <https://code.djangoproject.com/ticket/18378#comment:2>
Django
Type: Bug | Status: new
(models, ORM) | Resolution:
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by akaariai):
* stage: Unreviewed => Accepted
Comment:
I tested this and the query works if the name column is added just into
the GROUP BY clause, even if it isn't in the SELECT list.
I can't see any sane reason for allowing the query only if the column is
in the select list. I am tempted to call this a bug in MySQL and just
wontfix this. But maybe I will resist that feeling...
--
Ticket URL: <https://code.djangoproject.com/ticket/18378#comment:3>
Django
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by anonymous):
Replying to [comment:3 akaariai]:
> I tested this and the query works if the name column is added just into
the GROUP BY clause, even if it isn't in the SELECT list.
That's a good point. Can you think of some way to make Django put the
column in the GROUP BY clause?
--
Ticket URL: <https://code.djangoproject.com/ticket/18378#comment:4>
Django
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by anonymous):
I think this should really be fixed, be it as it may an SQL bug.
In the meantime, I'm using the following workaround:
{{{#!python
ids = [x[0] for x in
Model2.objects.annotate(bar=Count('foo')).filter(Q(bar__gt=0)|Q(model1__name='goo')).values('id',
'model1__name')]
Model2.objects.filter(id__in=ids)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/18378#comment:5>
Django
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by joseph.helfer@…):
Replying to [comment:5 anonymous]:
> I think this should really be fixed, be it as it may an SQL bug.
>
> In the meantime, I'm using the following workaround:
> {{{#!python
> ids = [x[0] for x in
Model2.objects.annotate(bar=Count('foo')).filter(Q(bar__gt=0)|Q(model1__name='goo')).values('id',
'model1__name')]
> Model2.objects.filter(id__in=ids)
> }}}
I mean,
{{{#!python
ids = [x[0] for x in
Model2.objects.annotate(bar=Count('foo')).filter(Q(bar__gt=0)|Q(model1__name='goo')).values_list('id',
'model1__name')]
Model2.objects.filter(id__in=ids)
}}}
(`values_list`, not `values`)
--
Ticket URL: <https://code.djangoproject.com/ticket/18378#comment:6>
Django
Reporter: joseph.helfer@… | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.3
(models, ORM) | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Ian Foote):
* status: new => closed
* resolution: => worksforme
Comment:
I looked into this and couldn't replicate the problem on the {{{master}}}
branch. I assume the bug has been fixed in the 6 years since being
reported.
I used the {{{Book}}} model in {{{tests.annotations.models}}} to write
this query:
{{{#!python
qs = Book.objects.annotate(author_count=Count('authors')).filter(
Q(author_count__gt=0) | Q(publisher__name='Sams')
)
}}}
This gave reasonable results when running the test on mysql.
--
Ticket URL: <https://code.djangoproject.com/ticket/18378#comment:7>