id summary reporter owner description type status component version severity resolution keywords cc stage has_patch needs_docs needs_tests needs_better_patch easy ui_ux 18378 Q() combined with annotate() can produce bad SQL joseph.helfer@… nobody "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." Bug closed Database layer (models, ORM) 1.3 Normal worksforme Accepted 0 0 0 0 0 0