Opened 2 years ago

Closed 8 weeks ago

Last modified 3 weeks ago

#28621 closed Bug (fixed)

Crash in QuerySet.annotate() with OuterRef

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

Description

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 (16)

comment:1 Changed 2 years 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

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.

comment:2 Changed 2 years 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/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'

comment:3 Changed 2 years 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 20 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:

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')))
Last edited 20 months ago by Leo Antunes (previous) (diff)

comment:5 Changed 19 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 16 months 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 = (
            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())
        )

comment:7 in reply to:  6 Changed 16 months ago by 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

Last edited 16 months ago by Filip Kilibarda (previous) (diff)

comment:8 Changed 13 months ago by felixxm

Owner: changed from nobody to felixxm
Status: newassigned

comment:9 in reply to:  5 Changed 8 months ago by 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

comment:10 Changed 6 months ago by felixxm

Has patch: set
Version: 1.11master

comment:11 Changed 6 months ago by Simon Charette

Triage Stage: AcceptedReady for checkin

comment:12 Changed 6 months ago by GitHub <noreply@…>

Resolution: fixed
Status: assignedclosed

In 2a431db:

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

comment:13 Changed 8 weeks ago by Dmitry Mugtasimov

Cc: Dmitry Mugtasimov added
Resolution: fixed
Status: closednew
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'

comment:14 Changed 8 weeks ago by felixxm

Resolution: fixed
Status: newclosed

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.

Last edited 8 weeks ago by felixxm (previous) (diff)

comment:15 Changed 3 weeks ago by B Martsberger

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

comment:16 Changed 3 weeks ago by felixxm

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.

Note: See TracTickets for help on using tickets.
Back to Top