ORM can produce invalid SQL when subclassing Query

bpea · October 15, 2024, 10:00pm

Hi all,

We wrote some custom logic that extended the Query class. It worked fine in 3.2 but not for 4.1, and it started producing invalid SQL. We filed a ticket (#35836) but haven’t made any progress since Query is an undocumented API.

Our use case is an automatic site filter - objects belonging to different customer sites are automatically filtered to the current site, so each queryset doesn’t have to be explicitly filtered. We did this at the Query level rather than filtering the default manager’s get_queryset . This has the useful effect that our DRF views can define a queryset attribute on their class definition (where there isn’t a ‘current’ site) and the query will be filtered when it is actually executed (in a request context on a particular site)

How should we change our usage of Query to fix the bug? Below is the contents of the ticket.

We have observed a bug in the ORM where invalid SQL can be produced.

A reproduction can be seen here:

And specifically:

github.com

benpearman/django-orm-bug/blob/master/demo/models.py

from django.db import models
from django.db.models import sql, Q, QuerySet


class SchoolQuery(sql.Query):
    def get_compiler(self, using=None, connection=None, elide_empty=True):
        q = self.clone()
        q_expr = Q(school_id=1)
        q.add_q(q_expr)
        return super(SchoolQuery, q).get_compiler(
            using=using, connection=connection, elide_empty=elide_empty
        )


class SchoolFilterQuerySetMixin:
    def __init__(self, model=None, query=None, using=None, hints=None):
        if query is None and model is not None:
            query = SchoolQuery(model)
        super().__init__(model=model, query=query, using=using, hints=hints)

This file has been truncated. show original

It is caused with the following steps:

Subclassing django.db.models.sql.Query and using it in a Queryset mixin
Creating a queryset with an exclude() expression which includes a subquery. EG Classroom.objects.exclude(student_set__id=student_A.id)
Then before calling super().get_compiler from our subclassed Query:

Clone the query
Adding a simple expression via cloned_query.add_q eg cloned_query.add_q(Q(school_id=1))
Passing the cloned query into super().get_compiler

Then evaluate the queryset, which produces invalid SQL and raises django.db.utils.OperationalError

Note the issue happens when get_compiler is invoked for the subquery, not on the main query itself.

This was working for us in Django 3.2, but not for Django 4.1

git bisect shows the breakage was introduced by this commit:
14c8504a37afad96ab93cf82f47b13bcc4d00621

Topic		Replies	Views
How to extract usable SQL from a QuerySet? ORM	2	4758	August 26, 2020
Subquery referencing window expression ORM	2	527	February 24, 2023
Simple(ish) SQL to ORM query Using the ORM	2	436	March 23, 2023
Django query auto add 'id' during query process then it error Using the ORM	2	108	June 11, 2024
New Error in 4.2 when querying sql db for too large decimals ORM	8	662	May 22, 2023

ORM can produce invalid SQL when subclassing Query

Related topics