Opened 12 years ago

Closed 6 years ago

#18378 closed Bug (worksforme)

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

Reported by: joseph.helfer@… 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.nameis replaced with foo_model2.model1_id in the HAVING clause, and the latter is explicitly selected.

Change History (7)

in reply to:  description ; comment:1 by anonymous, 12 years ago

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

in reply to:  1 comment:2 by anonymous, 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)

comment:3 by Anssi Kääriäinen, 12 years ago

Triage Stage: UnreviewedAccepted

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

in reply to:  3 comment:4 by anonymous, 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?

comment:5 by anonymous, 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)

in reply to:  5 comment:6 by joseph.helfer@…, 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 Ian Foote, 6 years ago

Resolution: worksforme
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top