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