Opened 3 years ago

Last modified 3 years ago

#18378 new Bug

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 (6)

comment:1 in reply to: ↑ description ; follow-up: Changed 3 years ago by anonymous

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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

comment:2 in reply to: ↑ 1 Changed 3 years ago by 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)

comment:3 follow-up: Changed 3 years ago by akaariai

  • Triage Stage changed from Unreviewed to 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 in reply to: ↑ 3 Changed 3 years ago by 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?

comment:5 follow-up: Changed 3 years ago 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:

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 in reply to: ↑ 5 Changed 3 years ago by 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)

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