Crash in QuerySet.annotate() with OuterRef

[Дилян Палаузов]

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?

[Simon Charette]

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

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

A.objects.annotate(
    not_matches=~Exists(
        B.objects.annotate(
            tag=OuterRef('tag'),
        ).filter(tag__regex=F('pattern')),
    ),
).filter(not_matches=True)

Please re-open if this doesn't work.

[Дилян Палаузов]

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/manager.py 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/query.py in annotate(self, *args, **kwargs)
    946
    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'

[Leo Antunes]

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

SomeModel.objects.annotate(mod=Subquery(SomeModel.objects.filter(pk=OuterRef('pk')%2).values('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):

SomeModel.objects.annotate(mod=Subquery(SomeModel.objects.filter(pk=OuterRef('pk')).values('pk')))

[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.

[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 = (
            Session.objects
            .filter(program=OuterRef('id'))
            .annotate(needed=OuterRef('volunteers_per_session') - Count('volunteer_attendances'))
            .order_by('-needed')
            .values('needed')[:1]
        )

        return self.annotate(
            volunteers_per_session=(F('num_student_seats') * F('student_volunteer_ratio')),
            needed=Subquery(sessions_query, output_field=models.IntegerField())
        )

[Filip Kilibarda]

Replying to 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 = (
            Session.objects
            .filter(program=OuterRef('id'))
            .annotate(needed=OuterRef('volunteers_per_session') - Count('volunteer_attendances'))
            .order_by('-needed')
            .values('needed')[:1]
        )

        return self.annotate(
            volunteers_per_session=(F('num_student_seats') * F('student_volunteer_ratio')),
            needed=Subquery(sessions_query, output_field=models.IntegerField())
        )

I'm having exactly the same issue. Have you found a workaround for this?

See this ​https://stackoverflow.com/q/47094982/6824752 for a weird workaround

[Shai Berger]

Replying to 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.

For anyone else who might be wondering, this refers to #29142

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In 2a431db:

Fixed #28621 -- Fixed crash of annotations with OuterRef.

[Dmitry Mugtasimov]

queryset.annotate(
                has_role=Exists(
                    JobTitle.objects.annotate(
                        title_vector=SearchVector(
                            OuterRef('title'),
                            config=SIMPLE_PG_SEARCH_CONFIGURATION)
                    ).filter(
                        title_vector=SearchQuery(
                            F('name'), config=SIMPLE_PG_SEARCH_CONFIGURATION)
                    )
                )
            )

results in

'ResolvedOuterRef' object has no attribute 'contains_aggregate'
Request Method:	GET
Request URL:	http://127.0.0.1:8000/admin/jobs/job/
Django Version:	2.2.2
Exception Type:	AttributeError
Exception Value:	
'ResolvedOuterRef' object has no attribute 'contains_aggregate'
Exception Location:	/usr/local/lib/python3.7/site-packages/django/db/models/expressions.py in <genexpr>, line 213
Python Executable:	/usr/local/bin/python
Python Version:	3.7.3
Python Path:	
['/code',
 '/code',
 '/usr/local/lib/python37.zip',
 '/usr/local/lib/python3.7',
 '/usr/local/lib/python3.7/lib-dynload',
 '/usr/local/lib/python3.7/site-packages']
Server time:	Mon, 22 Jul 2019 09:22:47 +0000

Environment:


Request Method: GET
Request URL: http://127.0.0.1:8000/admin/jobs/job/

Django Version: 2.2.2
Python Version: 3.7.3
Installed Applications:
['core',
 'django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'django.contrib.sites',
 'django.contrib.postgres',
 'allauth',
 'allauth.account',
 'drf_yasg',
 'rest_auth',
 'rest_auth.registration',
 'rest_framework',
 'rest_framework.authtoken',
 'colorful',
 'gm2m',
 'explorer',
 'allauth.socialaccount',
 'accounts',
 'questions',
 'colleges',
 'geo',
 'jobs',
 'advices',
 'referral']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware']



Traceback:

File "/usr/local/lib/python3.7/site-packages/django/core/handlers/exception.py" in inner
  34.             response = get_response(request)

File "/usr/local/lib/python3.7/site-packages/django/core/handlers/base.py" in _get_response
  115.                 response = self.process_exception_by_middleware(e, request)

File "/usr/local/lib/python3.7/site-packages/django/core/handlers/base.py" in _get_response
  113.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "/usr/local/lib/python3.7/contextlib.py" in inner
  74.                 return func(*args, **kwds)

File "/usr/local/lib/python3.7/site-packages/django/contrib/admin/options.py" in wrapper
  606.                 return self.admin_site.admin_view(view)(*args, **kwargs)

File "/usr/local/lib/python3.7/site-packages/django/utils/decorators.py" in _wrapped_view
  142.                     response = view_func(request, *args, **kwargs)

File "/usr/local/lib/python3.7/site-packages/django/views/decorators/cache.py" in _wrapped_view_func
  44.         response = view_func(request, *args, **kwargs)

File "/usr/local/lib/python3.7/site-packages/django/contrib/admin/sites.py" in inner
  223.             return view(request, *args, **kwargs)

File "/usr/local/lib/python3.7/site-packages/django/utils/decorators.py" in _wrapper
  45.         return bound_method(*args, **kwargs)

File "/usr/local/lib/python3.7/site-packages/django/utils/decorators.py" in _wrapped_view
  142.                     response = view_func(request, *args, **kwargs)

File "/usr/local/lib/python3.7/site-packages/django/contrib/admin/options.py" in changelist_view
  1672.             cl = self.get_changelist_instance(request)

File "/usr/local/lib/python3.7/site-packages/django/contrib/admin/options.py" in get_changelist_instance
  744.             sortable_by,

File "/usr/local/lib/python3.7/site-packages/django/contrib/admin/views/main.py" in __init__
  45.         self.root_queryset = model_admin.get_queryset(request)

File "/code/jobs/admin.py" in get_queryset
  76.         return queryset.annotate_with_has_role()

File "/code/jobs/models/job.py" in annotate_with_has_role
  68.                             config=SIMPLE_PG_SEARCH_CONFIGURATION)

File "/usr/local/lib/python3.7/site-packages/django/db/models/manager.py" in manager_method
  82.                 return getattr(self.get_queryset(), name)(*args, **kwargs)

File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py" in annotate
  1059.             if alias in annotations and annotation.contains_aggregate:

File "/usr/local/lib/python3.7/site-packages/django/utils/functional.py" in __get__
  80.         res = instance.__dict__[self.name] = self.func(instance)

File "/usr/local/lib/python3.7/site-packages/django/db/models/expressions.py" in contains_aggregate
  213.         return any(expr and expr.contains_aggregate for expr in self.get_source_expressions())

File "/usr/local/lib/python3.7/site-packages/django/db/models/expressions.py" in <genexpr>
  213.         return any(expr and expr.contains_aggregate for expr in self.get_source_expressions())

Exception Type: AttributeError at /admin/jobs/job/
Exception Value: 'ResolvedOuterRef' object has no attribute 'contains_aggregate'

[Mariusz Felisiak]

Please don't re-open already fixed ticket. This fix doesn't qualify for a backport, hence it will not be solved in Django 2.2.

[B Martsberger]

Out of curiosity, why does this fix not qualify for a backport?

[Mariusz Felisiak]

This doesn't qualify for a backport because it is not a regression in the Django 2.2 or a bug in a new feature.

[Dmitry Mugtasimov]

The fix does not qualify for backport because the code where it appears was heavily refactored somewhere between 1.11 and 2.2. However the bug remained in 2.2 and the bug qualifies to get fixed. The fix is already implemented, but not released yet. This is how I understood it.

[Dmitry Mugtasimov]

Here is explanation for humans: https://code.djangoproject.com/ticket/30652#comment:7

[GitHub <noreply@…>]

In ed6b14d4:

Refs #28621 -- Fixed crash of annotations with nested OuterRef.