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
36025	__range lookup in conditional aggregate with subquery annotation does not use annotated related fields	Aashay Amballi	Simon Charette	"I'm encountering an issue with a Django ORM operation that uses the `__range` filter on related fields. Here is the relevant model setup and operation:


{{{
class Project(models.Model):
    name = models.CharField(max_length=100)
    description = models.TextField()
    start_date = models.DateField()
    end_date = models.DateField()

class LaborRecord(models.Model):
    actual_hours = models.DecimalField(max_digits=5, decimal_places=2)
    billable_hours = models.DecimalField(max_digits=5, decimal_places=2)
    object_id = models.PositiveIntegerField()
    content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)
    content_object = GenericForeignKey(""content_type"", ""object_id"")


class WorkOrder(models.Model):
    class Status(models.TextChoices):
        IN_PROGRESS = 'st_in_progress', _('In Progress')
        NEW = 'st_new', _('New')
        OPEN = 'st_open', _('Open')
        CANCELLED = 'st_cancelled', _('Cancelled')
        COMPLETED = 'st_completed', _('Completed')
        REJECTED = 'st_rejected', _('Rejected')

    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name=""project_work_orders"", null=True)
    name = models.CharField(max_length=100)
    description = models.TextField()
    due_date = models.DateTimeField()
    estimated_labor = models.DecimalField(max_digits=5, decimal_places=2, null=True)
    labor_records = GenericRelation('LaborRecord')
    status = models.CharField(max_length=20, choices=Status.choices, default=Status.NEW)
}}}

ORM Operation:


{{{
model_contentype_id = ContentType.objects.get_for_model(WorkOrder).id
labor_hour_sq = LaborRecord.objects.filter(object_id=OuterRef(""pk""), content_type_id=model_contentype_id).values(""object_id"")
billable_hours_sq = labor_hour_sq.annotate(billable_labor_hours=Sum(""billable_hours"")).values(""billable_labor_hours"")
actual_hours_sq = labor_hour_sq.annotate(actual_labor_hours=Sum(""actual_hours"")).values(""actual_labor_hours"")

queryset = Project.objects.first().project_work_orders.all()

filter_condition = Q(
    ~Q(status__in=['st_completed', 'st_cancelled', 'st_rejected']), 
    Q(due_date__isnull=True) | Q(due_date__date__range=(F('project__start_date'), F('project__end_date')))
)

query = queryset.annotate(
    billable_labor_hours=Subquery(billable_hours_sq),
    actual_labor_hours=Subquery(actual_hours_sq),
).aggregate(
    out_of_bound_count=Count(""id"", filter=filter_condition), 
    planned_hours=Sum(""estimated_labor"", filter=~Q(status__in=['st_completed', 'st_cancelled', 'st_rejected']), default=0), 
    completed_hours=Sum(""actual_labor_hours"", filter=Q(status__in=['st_completed', 'st_cancelled', 'st_rejected']), default=0)
)
}}}

When running this operation, the following error occurs:


{{{
django.db.utils.ProgrammingError: missing FROM-clause entry for table ""app_1_project""
LINE 1: ...LL OR (""__col3"" AT TIME ZONE 'UTC')::date BETWEEN ""app_1_pro...

}}}

The SQL generated by this operation is:


{{{
SELECT COUNT(""__col1"") FILTER (
    WHERE (NOT (""__col2"" IN ('completed', 'closed', 'sch_closed', 'cancelled', 'rejected')) 
    AND (""__col3"" IS NULL OR (""__col3"" AT TIME ZONE 'UTC')::date BETWEEN ""app_1_project"".""start_date"" AND ""app_1_project"".""end_date""))
), 
COALESCE(SUM(""__col4"") FILTER (WHERE NOT (""__col2"" IN ('st_completed', 'st_cancelled', 'st_rejected'))), 0),
COALESCE(SUM(""actual_labor_hours"") FILTER (WHERE ""__col2"" IN ('st_completed', 'st_cancelled', 'st_rejected')), 0)
FROM (
    SELECT (
        SELECT SUM(U0.""billable_hours"") AS ""billable_labor_hours""
        FROM ""app_1_laborrecord"" U0
        WHERE (U0.""content_type_id"" = 8 AND U0.""object_id"" = (""app_1_workorder"".""id""))
        GROUP BY U0.""object_id""
    ) AS ""billable_labor_hours"",
    (
        SELECT SUM(U0.""actual_hours"") AS ""actual_labor_hours""
        FROM ""app_1_laborrecord"" U0
        WHERE (U0.""content_type_id"" = 8 AND U0.""object_id"" = (""app_1_workorder"".""id""))
        GROUP BY U0.""object_id""
    ) AS ""actual_labor_hours"",
    ""app_1_workorder"".""id"" AS ""__col1"",
    ""app_1_workorder"".""status"" AS ""__col2"",
    ""app_1_workorder"".""due_date"" AS ""__col3"",
    ""app_1_workorder"".""estimated_labor"" AS ""__col4""
    FROM ""app_1_workorder""
    INNER JOIN ""app_1_project""
    ON (""app_1_workorder"".""project_id"" = ""app_1_project"".""id"")
    WHERE ""app_1_workorder"".""project_id"" = 1
) subquery
}}}

Initially, it seemed like a problem with alias field generation for annotated fields within the `__range` filter. 

Explicitly using `__gte` and `__lte` instead of using `__range` resolved the issue. Below is the example for it and the SQL generated by the ORM operation

{{{
filter_condition = Q(~Q(status__in=['st_completed', 'st_cancelled', 'st_rejected']),Q(due_date__isnull=True) | Q(due_date__date__gte=F('project_start_date'), due_date__date__lte=F('project_end_date')))
}}}


{{{
SELECT COUNT(""__col1"") FILTER (WHERE (NOT (""__col2"" IN ('st_completed', 'st_cancelled', 'st_rejected')) AND (""__col3"" IS NULL OR ((""__col3"" AT TIME ZONE 'UTC')::date >= (""__col4"") AND (""__col3"" AT TIME ZONE 'UTC')::date <= (""__col5""))))),
       COALESCE(SUM(""__col6"") FILTER (WHERE NOT (""__col2"" IN ('st_completed', 'st_cancelled', 'st_rejected'))), 0),
       COALESCE(SUM(""actual_labor_hours"") FILTER (WHERE ""__col2"" IN ('st_completed', 'st_cancelled', 'st_rejected')), 0)
  FROM (
        SELECT (
                SELECT SUM(U0.""billable_hours"") AS ""billable_labor_hours""
                  FROM ""app_1_laborrecord"" U0
                 WHERE (U0.""content_type_id"" = 8 AND U0.""object_id"" = (""app_1_workorder"".""id""))
                 GROUP BY U0.""object_id""
               ) AS ""billable_labor_hours"",
               (
                SELECT SUM(U0.""actual_hours"") AS ""actual_labor_hours""
                  FROM ""app_1_laborrecord"" U0
                 WHERE (U0.""content_type_id"" = 8 AND U0.""object_id"" = (""app_1_workorder"".""id""))
                 GROUP BY U0.""object_id""
               ) AS ""actual_labor_hours"",
               ""app_1_project"".""start_date"" AS ""project_start_date"",
               ""app_1_project"".""end_date"" AS ""project_end_date"",
               ""app_1_workorder"".""id"" AS ""__col1"",
               ""app_1_workorder"".""status"" AS ""__col2"",
               ""app_1_workorder"".""due_date"" AS ""__col3"",
               ""app_1_project"".""start_date"" AS ""__col4"",
               ""app_1_project"".""end_date"" AS ""__col5"",
               ""app_1_workorder"".""estimated_labor"" AS ""__col6""
          FROM ""app_1_workorder""
          LEFT OUTER JOIN ""app_1_project""
            ON (""app_1_workorder"".""project_id"" = ""app_1_project"".""id"")
         WHERE ""app_1_workorder"".""project_id"" = 1
       ) subquery
}}}

as you can see it created alias columns `__col4` for `project__start_date` and `__col5` for `project__end_date`. but for the `__range` it was directly trying to fetch from the table/model.

I tried annotating the project start and end dates as follows with `__range` filter and it didn't help either. Despite explicitly annotating the fields, the generated SQL remains unchanged from the original. Django does not recognize or utilize the aliased/annotated fields:

{{{
filter_condition = Q(~Q(status__in=['completed', 'closed', 'sch_closed', 'cancelled', 'rejected']), Q(due_date__isnull=True) | Q(due_date__date__range=(F('project_start_date'), F('project_end_date'))))
}}}

{{{
queryset.annotate(
    billable_labor_hours=Subquery(billable_hours_sq),
    actual_labor_hours=Subquery(actual_hours_sq),
    project_start_date=F(""project__start_date""),
    project_end_date=F(""project__end_date"")
).aggregate(
    out_of_bound_count=Count(""id"", filter=filter_condition), 
    planned_hours=Sum(""estimated_labor"", filter=~Q(status__in=['st_completed', 'st_cancelled', 'st_rejected']), default=0), 
    completed_hours=Sum(""actual_labor_hours"", filter=Q(status__in=['st_completed', 'st_cancelled', 'st_rejected']), default=0)
)
}}}


The issue seems related to ongoing discussions in Django's ticket #33929, but further investigation is needed to confirm a direct link."	Bug	closed	Database layer (models, ORM)	4.2	Normal	fixed	ORM	Aashay Amballi Simon Charette	Ready for checkin	1	0	0	0	0	0