#18378: Q() combined with annotate() can produce bad SQL
----------------------------------------------+--------------------
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
