Opened 12 years ago
Closed 6 years ago
#18378 closed Bug (worksforme)
Q() combined with annotate() can produce bad SQL
Reported by: | Owned by: | nobody | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.3 |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Accepted | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
A query like this:
Model2.objects.annotate(bar=Count('foo')).filter(Q(bar__gt=0)|Q(model1__name='goo')).query
produces SQL like this
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.
Change History (7)
follow-up: 2 comment:1 by , 12 years ago
comment:2 by , 12 years ago
Replying to 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)
follow-up: 4 comment:3 by , 12 years ago
Triage Stage: | Unreviewed → Accepted |
---|
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...
comment:4 by , 12 years ago
Replying to 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?
follow-up: 6 comment:5 by , 12 years ago
I think this should really be fixed, be it as it may an SQL bug.
In the meantime, I'm using the following workaround:
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)
comment:6 by , 12 years ago
Replying to 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:
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,
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
)
comment:7 by , 6 years ago
Resolution: | → worksforme |
---|---|
Status: | new → closed |
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:
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.
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).