Opened 8 months ago

Last modified 2 days ago

#28621 new Bug

Crash in QuerySet.annotate() with OuterRef

Reported by: Дилян Палаузов Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords: QuerySet.extra
Cc: Leo Antunes Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


class A(models.Model):
  tag = models.CharField()

class B(models.Model):
  pattern = models.CharField()

A single query that retrieves all A.tag, not matching B.pattern:

SELECT a.tag FROM app_a as a WHERE NOT EXISTS (SELECT 1 FROM app_b as b WHERE a.tag ~ b.pattern);

Is it possibe to write a queryset for this, without .extra and .raw?

Change History (6)

comment:1 Changed 8 months ago by Simon Charette

Resolution: invalid
Status: newclosed

I think you can use the Exists expression available since Django 1.11

from django.db.models import Exists, F, OuterRef


Please re-open if this doesn't work.

comment:2 Changed 8 months ago by Дилян Палаузов

Resolution: invalid
Status: closednew

I get error:

In [7]: A.objects.annotate(not_matches=~Exists(B.objects.annotate(tag=OuterRef('tag')).filter(tag__regex=F('pattern')))).filter(not_matches=True)
AttributeError                            Traceback (most recent call last)
<ipython-input-7-0a83a61e3d5b> in <module>()
----> 1 A.objects.annotate(not_matches=~Exists(B.objects.annotate(tag=OuterRef('tag')).filter(tag__regex=F('pattern')))).filter(not_matches=True)

django/db/models/ in manager_method(self, *args, **kwargs)
     83         def create_method(name, method):
     84             def manager_method(self, *args, **kwargs):
---> 85                 return getattr(self.get_queryset(), name)(*args, **kwargs)
     86             manager_method.__name__ = method.__name__
     87             manager_method.__doc__ = method.__doc__

django/db/models/ in annotate(self, *args, **kwargs)
    947         for alias, annotation in clone.query.annotations.items():
--> 948             if alias in annotations and annotation.contains_aggregate:
    949                 if clone._fields is None:
    950                     clone.query.group_by = True

AttributeError: 'ResolvedOuterRef' object has no attribute 'contains_aggregate'

comment:3 Changed 8 months ago by Tim Graham

Component: UncategorizedDatabase layer (models, ORM)
Summary: WHERE NOT EXISTS (... ~ ...) without extra() ?Crash in QuerySet.annotate() with OuterRef
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

comment:4 Changed 4 months ago by Leo Antunes

Cc: Leo Antunes added

Another slightly simpler example of the same problem, using a model that only has a pk:


This generates the related 'ResolvedOuterRef' object has no attribute 'relabeled_clone' exception. I believe these two issues are related, since they both point at OuterRef() behaving differently than what one would expect with, e.g., F().

Simply removing "%2" is enough to make it work (though not with the expected results, of course):

Last edited 4 months ago by Leo Antunes (previous) (diff)

comment:5 Changed 3 months ago by Matthew Schinckel

The relabeled_clone issue is now fixed: I missed that there is also a contains_aggregate part. I'll have a look at that now.

comment:6 Changed 2 days ago by Ben Davis

I am also getting this error. Is there a workaround?

AttributeError: 'ResolvedOuterRef' object has no attribute 'contains_aggregate'

My query looks like this:

class ProgramQuerySet(models.QuerySet):
    def with_remaining_volunteers_needed(self):
        sessions_query = (
            .annotate(needed=OuterRef('volunteers_per_session') - Count('volunteer_attendances'))

        return self.annotate(
            volunteers_per_session=(F('num_student_seats') * F('student_volunteer_ratio')),
            needed=Subquery(sessions_query, output_field=models.IntegerField())
Note: See TracTickets for help on using tickets.
Back to Top