Opened 7 years ago

Closed 5 years ago

Last modified 4 years ago

#28621 closed Bug (fixed)

Crash in QuerySet.annotate() with OuterRef

Reported by: Дилян Палаузов Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: dev
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 (19)

comment:1 by Simon Charette, 7 years ago

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 by Дилян Палаузов, 7 years ago

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 by Tim Graham, 7 years ago

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

comment:4 by Leo Antunes, 7 years ago

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 7 years ago by Leo Antunes (previous) (diff)

comment:5 by Matthew Schinckel, 7 years ago

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 by Ben Davis, 6 years ago

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

in reply to:  6 comment:7 by Filip Kilibarda, 6 years ago

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 6 years ago by Filip Kilibarda (previous) (diff)

comment:8 by Mariusz Felisiak, 6 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

in reply to:  5 comment:9 by Shai Berger, 6 years ago

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 by Mariusz Felisiak, 6 years ago

Has patch: set
Version: 1.11master

comment:11 by Simon Charette, 6 years ago

Triage Stage: AcceptedReady for checkin

comment:12 by GitHub <noreply@…>, 6 years ago

Resolution: fixed
Status: assignedclosed

In 2a431db:

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

comment:13 by Dmitry Mugtasimov, 5 years ago

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 by Mariusz Felisiak, 5 years ago

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 5 years ago by Mariusz Felisiak (previous) (diff)

comment:15 by B Martsberger, 5 years ago

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

comment:16 by Mariusz Felisiak, 5 years ago

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.

comment:17 by Dmitry Mugtasimov, 5 years ago

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.

comment:18 by Dmitry Mugtasimov, 5 years ago

comment:19 by GitHub <noreply@…>, 4 years ago

In ed6b14d4:

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

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