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
22629	.annotate with two foreign-key count attributes counts wrong on SQLite	jps@…	nobody	"When using .annote on a model with two foreign-key relations and counting both of them, the count comes out wrong on SQLite.

Example code:
{{{
a1 = Alfa.objects.create()
a2 = Alfa.objects.create()
b1 = Bravo.objects.create()

d  = Delta.objects.create()
d.alfas.add(a1)
d.alfas.add(a2)
d.bravos.add(b1)

d_count = Delta.objects.annotate(num_alfas=Count('alfas'), num_bravos=Count('bravos')).get(id=d.id)

self.assertEquals(2, d_count.num_alfas,  ""Expected to find two alfa objects counted"")
self.assertEquals(1, d_count.num_bravos, ""Expected to find one bravo object counted"")
}}}


Test code is also available at
[https://github.com/shezi/django/commit/5a565bd532bbf5d055304835adefbf40aaf467e2]

The reason for the bug is that SQLite handles the generated LEFT OUTER JOIN differently than other DBMSs. In SQLite, the second LEFT OUTER JOIN is added onto the already-joined table, such that IDs from the first join get duplicated.

One workaround is to add {{{ distinct=True }}} to the second count argument. This, of course, changes both the meaning and the execution speed for the query."	Bug	closed	Database layer (models, ORM)	1.6	Normal	duplicate			Accepted	0	0	0	0	0	0