Opened 3 weeks ago

Closed 3 weeks ago

Last modified 3 weeks ago

#36751 closed Bug (fixed)

Aggregation with an empty filter over a queryset with annotations crashes since Django 5.2.2

Reported by: Rafael Urben Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 5.2
Severity: Release blocker Keywords:
Cc: Rafael Urben, Youngkwang Yang Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

In Django 6.0, activating facets leads to an internal server error when the admin queryset contains certain annotations.

As an example, the following annotations lead to an error:

def get_queryset(self, request):
    return (
        super()
        .get_queryset(request)
        .annotate(
            annotation_has_usable_password=Case(
                When(
                    Q(password__isnull=False) & ~Q(password__startswith="!"), then=Value(True)
                ),
                default=Value(False),
                output_field=BooleanField(),
            ),
        )
    )

The error observed seems to depend on the database backend:

  • With sqlite3, I get 'NoneType' object has no attribute 'as_sql' (triggered in template rendering at site-packages\django\contrib\admin\templates\admin\change_list.html, error at line 72)
  • With mysql, I get When() supports a Q object, a boolean expression, or lookups as a condition. (triggered in template rendering at site-packages\django\contrib\admin\templates\admin\change_list.html, error at line 72)

I have created a small gist with a minimal reproducer admin.py that triggers the error:
​https://gist.github.com/rafaelurben/670658ffe1a9cc0cfee45380e8f148a0
The example works without an issue in Django 5.2.8 but fails on Django 6.0rc1 (tested in a new project with a new venv with sqlite and in an existing project with mysql).

I'm not sure if I have missed something in the Django 6.0 release notes, but this looks like a bug.

Traceback (sqlite):

Environment:


Request Method: GET
Request URL: http://localhost:8000/admin/auth/user/?_facets=True

Django Version: 6.0rc1
Python Version: 3.13.5
Installed Applications:
['django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'testapp']
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']


Template error:
In template C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\contrib\admin\templates\admin\change_list.html, error at line 72
   'NoneType' object has no attribute 'as_sql'
   62 :             <h2 id="changelist-filter-header">{% translate 'Filter' %}</h2>
   63 :             {% if cl.is_facets_optional or cl.has_active_filters %}<div id="changelist-filter-extra-actions">
   64 :               {% if cl.is_facets_optional %}<h3>
   65 :                 {% if cl.add_facets %}<a href="{{ cl.remove_facet_link }}" class="hidelink">{% translate "Hide counts" %}</a>
   66 :                 {% else %}<a href="{{ cl.add_facet_link }}" class="viewlink">{% translate "Show counts" %}</a>{% endif %}
   67 :               </h3>{% endif %}
   68 :               {% if cl.has_active_filters %}<h3>
   69 :                 <a href="{{ cl.clear_all_filters_qs }}">&#10006; {% translate "Clear all filters" %}</a>
   70 :               </h3>{% endif %}
   71 :             </div>{% endif %}
   72 :             {% for spec in cl.filter_specs %} {% admin_list_filter cl spec %} {% endfor %}
   73 :           </search>
   74 :           {% endif %}
   75 :         {% endblock %}
   76 :         <div>
   77 :           {% block search %}{% search_form cl %}{% endblock %}
   78 :           {% block date_hierarchy %}{% if cl.date_hierarchy %}{% date_hierarchy cl %}{% endif %}{% endblock %}
   79 : 
   80 :           <form id="changelist-form" method="post"{% if cl.formset and cl.formset.is_multipart %} enctype="multipart/form-data"{% endif %} novalidate>{% csrf_token %}
   81 :           {% if cl.formset %}
   82 :             <div>{{ cl.formset.management_form }}</div>


Traceback (most recent call last):
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\core\handlers\exception.py", line 55, in inner
    response = get_response(request)
               ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\core\handlers\base.py", line 221, in _get_response
    response = response.render()
               ^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\response.py", line 114, in render
    self.content = self.rendered_content
                   ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\response.py", line 92, in rendered_content
    return template.render(context, self._request)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\backends\django.py", line 107, in render
    return self.template.render(context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 173, in render
    return self._render(context)
           ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 165, in _render
    return self.nodelist.render(context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 1089, in render
    return SafeString("".join([node.render_annotated(context) for node in self]))
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 1050, in render_annotated
    return self.render(context)
           ^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\loader_tags.py", line 160, in render
    return compiled_parent._render(context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 165, in _render
    return self.nodelist.render(context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 1089, in render
    return SafeString("".join([node.render_annotated(context) for node in self]))
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 1050, in render_annotated
    return self.render(context)
           ^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\loader_tags.py", line 160, in render
    return compiled_parent._render(context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 165, in _render
    return self.nodelist.render(context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 1089, in render
    return SafeString("".join([node.render_annotated(context) for node in self]))
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 1050, in render_annotated
    return self.render(context)
           ^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\loader_tags.py", line 66, in render
    result = block.nodelist.render(context)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 1089, in render
    return SafeString("".join([node.render_annotated(context) for node in self]))
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 1050, in render_annotated
    return self.render(context)
           ^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\loader_tags.py", line 66, in render
    result = block.nodelist.render(context)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 1089, in render
    return SafeString("".join([node.render_annotated(context) for node in self]))
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 1050, in render_annotated
    return self.render(context)
           ^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\defaulttags.py", line 333, in render
    return nodelist.render(context)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 1089, in render
    return SafeString("".join([node.render_annotated(context) for node in self]))
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 1050, in render_annotated
    return self.render(context)
           ^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\defaulttags.py", line 249, in render
    nodelist.append(node.render_annotated(context))
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\base.py", line 1050, in render_annotated
    return self.render(context)
           ^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\template\library.py", line 322, in render
    output = self.func(*resolved_args, **resolved_kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\contrib\admin\templatetags\admin_list.py", line 517, in admin_list_filter
    "choices": list(spec.choices(cl)),
               ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\contrib\admin\filters.py", line 543, in choices
    facet_counts = self.get_facet_queryset(changelist) if add_facets else None
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\contrib\admin\filters.py", line 87, in get_facet_queryset
    return filtered_qs.aggregate(
           
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\query.py", line 594, in aggregate
    return self.query.chain().get_aggregation(self.db, kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\sql\query.py", line 633, in get_aggregation
    result = compiler.execute_sql(SINGLE)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\sql\compiler.py", line 1611, in execute_sql
    sql, params = self.as_sql()
                  ^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\sql\compiler.py", line 767, in as_sql
    extra_select, order_by, group_by = self.pre_sql_setup(
                                       
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\sql\compiler.py", line 86, in pre_sql_setup
    self.setup_query(with_col_aliases=with_col_aliases)
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\sql\compiler.py", line 75, in setup_query
    self.select, self.klass_info, self.annotation_col_map = self.get_select(
                                                            
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\sql\compiler.py", line 317, in get_select
    sql, params = self.compile(col)
                  ^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\sql\compiler.py", line 576, in compile
    sql, params = vendor_impl(self, self.connection)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\expressions.py", line 29, in as_sqlite
    sql, params = self.as_sql(compiler, connection, **extra_context)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\aggregates.py", line 193, in as_sql
    filter_sql, filter_params = compiler.compile(self.filter)
                                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\sql\compiler.py", line 576, in compile
    sql, params = vendor_impl(self, self.connection)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\expressions.py", line 29, in as_sqlite
    sql, params = self.as_sql(compiler, connection, **extra_context)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\aggregates.py", line 47, in as_sql
    return super().as_sql(compiler, connection, **extra_context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\expressions.py", line 1107, in as_sql
    arg_sql, arg_params = compiler.compile(arg)
                          ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\rafae\Coding\Django\reproducer_dj60_facets\.venv\Lib\site-packages\django\db\models\sql\compiler.py", line 578, in compile
    sql, params = node.as_sql(self, self.connection)
                  ^^^^^^^^^^^

Exception Type: AttributeError at /admin/auth/user/
Exception Value: 'NoneType' object has no attribute 'as_sql'

Change History (10)

comment:1 by Youngkwang Yang, 3 weeks ago

Cc: Youngkwang Yang added

comment:2 by Antoliny, 3 weeks ago

Triage Stage: Unreviewed → Accepted

Thank you for the detailed explanation Rafael !!

It seems that the issue occurs when adding date related fields to the filter.
When comparing 5.2 and 6.0, there is a slight difference in the results of the DateFieldListFilter class’s get_facet_counts method.

In 5.2, when param_dict is empty, the Count does not have a filter, but in 6.0, it does.

6.0

Count(F(id), filter=(AND:))

5.2

Count(F(id))

It seems that this filter is causing the error.
When I modified the return value of get_facet_counts like this, the problem did not occur:

return {
    f"{i}__c": models.Count(
        pk_attname,
        **({"filter": models.Q(**param_dict)} if param_dict else {})
    )
    for i, (_, param_dict) in enumerate(self.links)
}

However, I’m not sure if this is the actual solution.
Could this be an ORM related issue?

comment:4 by Simon Charette, 3 weeks ago

Component: contrib.admin → Database layer (models, ORM)
Owner: set to Simon Charette
Status: new → assigned

The ORM should likely cover this case but the fundamental problem here is that the admin creates empty Q objects and pass them to Aggregate.filter when there are no param_dict.

Notice how the following patch addresses the problem

  • django/contrib/admin/filters.py 

    diff --git a/django/contrib/admin/filters.py b/django/contrib/admin/filters.py
index 10a039af2a..d1967e0b88 100644
    a b class DateFieldListFilter(FieldListFilter):  
    534534
    535535    def get_facet_counts(self, pk_attname, filtered_qs):
    536536        return {
    537             f"{i}__c": models.Count(pk_attname, filter=models.Q(**param_dict))
     537            f"{i}__c": models.Count(
     538                pk_attname, filter=(models.Q(**param_dict) if param_dict else None)
     539            )
    538540            for i, (_, param_dict) in enumerate(self.links)
    539541        }

Before b8e5a8a9a2a767f584cbe89a878a42363706f939, which is effectively the origin of the regression, we we're turning Q() to None before assigning to AggregateFilter which elided such bogus filters.

comment:5 by Varun Kasyap Pentamaraju, 3 weeks ago

Cc: Varun Kasyap Pentamaraju added

comment:6 by Simon Charette, 3 weeks ago

Cc: Varun Kasyap Pentamaraju removed
Severity: Normal → Release blocker
Summary: Django Admin facets broken in 6.0 → Aggregation with an empty filter over a queryset with annotations crashes since Django 5.2.2
Version: 6.0 → 5.2

comment:7 by Simon Charette, 3 weeks ago

Has patch: set

This ​PR should address the issue.

comment:8 by GitHub <noreply@…>, 3 weeks ago

Resolution: → fixed
Status: assigned → closed

In 2a6e0bd7:

Fixed #36751 -- Fixed empty filtered aggregation crash over annotated queryset.

Regression in b8e5a8a9a2a767f584cbe89a878a42363706f939.

Refs #36404.

The replace_expressions method was innapropriately dealing with falsey
but not None source expressions causing them to also be potentially
evaluated when bool was invoked (e.g. QuerySet.bool evaluates
the queryset).

The changes introduced in b8e5a8a9a2, which were to deal with a similar
issue, surfaced the problem as aggregation over an annotated queryset
requires an inlining (or pushdown) of aggregate references which is
achieved through replace_expressions.

In cases where an empty Q object was provided as an aggregate filter,
such as when the admin facetting feature was used as reported, it would
wrongly be turned into None, instead of an empty WhereNode, causing a
crash at aggregate filter compilation.

Note that the crash signature differed depending on whether or not the
backend natively supports aggregate filtering
(supports_aggregate_filter_clause) as the fallback, which makes use
Case / When expressions, would result in a TypeError instead of a
NoneType AttributeError.

Thanks Rafael Urben for the report, Antoliny and Youngkwang Yang for
the triage.

comment:9 by Mariusz Felisiak <felisiak.mariusz@…>, 3 weeks ago

In abce629:

[6.0.x] Fixed #36751 -- Fixed empty filtered aggregation crash over annotated queryset.

Regression in b8e5a8a9a2a767f584cbe89a878a42363706f939.

Refs #36404.

The replace_expressions method was innapropriately dealing with falsey
but not None source expressions causing them to also be potentially
evaluated when bool was invoked (e.g. QuerySet.bool evaluates
the queryset).

The changes introduced in b8e5a8a9a2, which were to deal with a similar
issue, surfaced the problem as aggregation over an annotated queryset
requires an inlining (or pushdown) of aggregate references which is
achieved through replace_expressions.

In cases where an empty Q object was provided as an aggregate filter,
such as when the admin facetting feature was used as reported, it would
wrongly be turned into None, instead of an empty WhereNode, causing a
crash at aggregate filter compilation.

Note that the crash signature differed depending on whether or not the
backend natively supports aggregate filtering
(supports_aggregate_filter_clause) as the fallback, which makes use
Case / When expressions, would result in a TypeError instead of a
NoneType AttributeError.

Thanks Rafael Urben for the report, Antoliny and Youngkwang Yang for
the triage.
Backport of 2a6e0bd72d4a69725b957d6748a4b834f21b12b5 from main

comment:10 by Mariusz Felisiak <felisiak.mariusz@…>, 3 weeks ago

In 1e73277:

[5.2.x] Fixed #36751 -- Fixed empty filtered aggregation crash over annotated queryset.

Regression in b8e5a8a9a2a767f584cbe89a878a42363706f939.

Refs #36404.

The replace_expressions method was innapropriately dealing with falsey
but not None source expressions causing them to also be potentially
evaluated when bool was invoked (e.g. QuerySet.bool evaluates
the queryset).

The changes introduced in b8e5a8a9a2, which were to deal with a similar
issue, surfaced the problem as aggregation over an annotated queryset
requires an inlining (or pushdown) of aggregate references which is
achieved through replace_expressions.

In cases where an empty Q object was provided as an aggregate filter,
such as when the admin facetting feature was used as reported, it would
wrongly be turned into None, instead of an empty WhereNode, causing a
crash at aggregate filter compilation.

Note that the crash signature differed depending on whether or not the
backend natively supports aggregate filtering
(supports_aggregate_filter_clause) as the fallback, which makes use
Case / When expressions, would result in a TypeError instead of a
NoneType AttributeError.

Thanks Rafael Urben for the report, Antoliny and Youngkwang Yang for
the triage.
Backport of 2a6e0bd72d4a69725b957d6748a4b834f21b12b5 from main

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