Q() combined with annotate() can produce bad SQL

[joseph.helfer@…]

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.nameis replaced with foo_model2.model1_id in the HAVING clause, and the latter is explicitly selected.

[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).

[anonymous]

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)

[Anssi Kääriäinen]

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...

[anonymous]

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?

[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)

[joseph.helfer@…]

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)

[Ian Foote]

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.