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
27577	Django annotated query returns wrong results	Mahmood Khan	nobody	"I have three related models listed below and I would like to run an aggregate query to give me the total number of tasks completed by person but only those tasks that are in a specific folder.


{{{
class Person(models.Model):
    firstName = models.CharField(max_length=100, null=True, blank=True)
    lastName = models.CharField(max_length=100, null=True, blank=True)

class Folder(models.Model):
    title = models.CharField(max_length=254, null=True, blank=True)
    assignees = models.ManyToManyField(Person, related_name=""projects"")
    completedDate = models.DateTimeField(blank=True, null=True)

class Task(models.Model):
    title = models.CharField(max_length=254, null=True, blank=True)
    assignees = models.ManyToManyField(Person, related_name=""tasks"")
    folders = models.ManyToManyField(Folder, related_name=""tasks"")
}}}

And here is my attempted query:


{{{
tasks = Contact.objects.filter(tasks__folders__id='I58343DS89ASDF').distinct().filter(tasks__completedDate__gte='2016-10-01 00:00:00').annotate(total=Count('tasks'), name=F('firstName')).values('total', 'name')
}}}


This is giving me an incorrect count. So I inspected the SQL statement that is generated by this Django query. The SQL statement is:


{{{
SELECT DISTINCT COUNT(`wrike_task_assignees`.`task_id`) AS `total`, `wrike_contact`.`firstName` AS `name` FROM `wrike_contact` INNER JOIN `wrike_task_assignees` ON (`wrike_contact`.`id` = `wrike_task_assignees`.`contact_id`) INNER JOIN `wrike_task` ON (`wrike_task_assignees`.`task_id` = `wrike_task`.`id`) INNER JOIN `wrike_task_folders` ON (`wrike_task`.`id` = `wrike_task_folders`.`task_id`) INNER JOIN `wrike_task_assignees` T6 ON (`wrike_contact`.`id` = T6.`contact_id`) INNER JOIN `wrike_task` T7 ON (T6.`task_id` = T7.`id`) WHERE (`wrike_task_folders`.`folder_id` = I58343DS89ASDF AND T7.`completedDate` >= 2016-10-01 00:00:00) GROUP BY `wrike_contact`.`id` ORDER BY NULL
}}}


And a nicely formatted version is:


{{{
SELECT DISTINCT COUNT(ta.task_id) AS `total`, c.firstName AS `name` 
    FROM wrike_contact c INNER JOIN wrike_task_assignees ta ON (c.id = ta.contact_id)
    INNER JOIN wrike_task t ON (ta.task_id = t.id)
    INNER JOIN wrike_task_folders tf ON (t.id = tf.task_id)
    INNER JOIN wrike_task_assignees T6 ON (c.id = T6.contact_id)
    INNER JOIN wrike_task T7 ON (ta.task_id = T7.id) 
    WHERE (tf.folder_id = 'I58343DS89ASDF' 
        AND t.completedDate >= '2016-10-01 00:00:00') 
    GROUP BY c.id
    ORDER BY NULL
}}}


Notice that a couple INNER JOIN are repeated twice -- not sure why.

If I remove those duplicate INNER JOIN and then run the query, the results are correct. Here is the version of SQL statement in which I removed the duplicate INNER JOIN segments:


{{{
SELECT DISTINCT c.firstName AS name, COUNT(ta.task_id) AS `total` 
    FROM wrike_contact c 
    INNER JOIN wrike_task_assignees ta ON (c.id = ta.contact_id) 
    INNER JOIN wrike_task t ON (ta.task_id = t.id) 
    INNER JOIN wrike_task_folders tf ON (t.id = tf.task_id) 
    WHERE (tf.folder_id = 'I58343DS89ASDF' 
        AND t.completedDate >= '2016-10-01 00:00:00') 
    GROUP BY c.id 
    ORDER BY name;
}}}

I have searched the forum and couldn't find that this issue is already reported. So I reporting it here. "	Bug	closed	Database layer (models, ORM)	1.10	Normal	invalid	annotated query		Unreviewed	0	0	0	0	0	0