id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32717	Incorrect SQL generation filtering OR-combined queries	Shaheed Haque	Simon Charette	"I'm running the just-released Django 3.2.1 and am seeing what I think is incorrect SQL generation involving this model (cut down for brevity):

{{{
from django.db import models as db_models

class Buss(db_models.Model):
    MAX_LENGTH = 25
    CHOICES = [('Universal', 'Universal'), ('GB', 'GB'), ('US', 'US'), ('Company', 'Company')]

    jurisdiction = db_models.CharField(max_length=MAX_LENGTH, choices=CHOICES)
    name = db_models.CharField(max_length=MAX_LENGTH)

    class Meta:
        unique_together = [('jurisdiction', 'name')]
}}}

I have a function which returns a queryset by combining 3 sets of busses using the ""|"" OR operator:

{{{
from paiyroll.models import Buss

def jurisdiction_qs(for_jurisdiction):
    # Get busses identified by ""jurisdiction_for"", and add other busses from 'Universal' and 'Company' where they don't clash.
    qs = Buss.objects.filter(jurisdiction=for_jurisdiction)
    if for_jurisdiction != 'Universal':
        qs = qs | Buss.objects.filter(jurisdiction='Universal'). \
            exclude(name__in=qs.values_list('name', flat=True))
    if for_jurisdiction != 'Company':
        qs = qs | Buss.objects.filter(jurisdiction='Company'). \
            exclude(name__in=qs.values_list('name', flat=True))
    return qs
}}}

In use, the function seems to work as expected:

{{{
In [7]: Buss.objects.filter(jurisdiction='GB').count()
Out[7]: 8

In [11]: Buss.objects.filter(jurisdiction__in=['GB','Universal','Company']).count()
Out[11]: 37

In [12]: jurisdiction_qs('GB').count()
Out[12]: 34
}}}

However, if the OR'd queryset is further filtered, the results are unpredictable. For example, this works:

{{{
In [13]: jurisdiction_qs('GB').filter(jurisdiction='US').count()
Out[13]: 0
}}}

but this - where the filter is by the original ""GB"" - returns 34 instead of 8:

{{{
In [14]: jurisdiction_qs('GB').filter(jurisdiction='GB').count()
Out[14]: 34
}}}

I can see that the SQL from the function looks OK:

{{{
str(jurisdiction_qs('GB').query)

SELECT ""paiyroll_buss"".""id"", ""paiyroll_buss"".""jurisdiction"", ""paiyroll_buss"".""name"", ""paiyroll_buss"".""description"" FROM ""paiyroll_buss"" WHERE (
    ""paiyroll_buss"".""jurisdiction"" = GB OR 
    (""paiyroll_buss"".""jurisdiction"" = Universal AND NOT 
        (""paiyroll_buss"".""name"" IN (SELECT U0.""name"" FROM ""paiyroll_buss"" U0 WHERE U0.""jurisdiction"" = GB))
    ) OR 
    (""paiyroll_buss"".""jurisdiction"" = Company AND NOT 
        (""paiyroll_buss"".""name"" IN (SELECT V0.""name"" FROM ""paiyroll_buss"" V0 WHERE (V0.""jurisdiction"" = GB OR (V0.""jurisdiction"" = Universal AND NOT 
            (V0.""name"" IN (SELECT U0.""name"" FROM ""paiyroll_buss"" U0 WHERE U0.""jurisdiction"" = GB))
        ))))
    )
)
}}}

In the working case, the above SQL is changed to end as follows:

{{{
str(jurisdiction_qs('GB').filter(jurisdiction='US').query)
SELECT ...WHERE (... AND ""paiyroll_buss"".""jurisdiction"" = US)
}}}

but in the broken case, the original SQL is returned!

{{{
str(jurisdiction_qs('GB').filter(jurisdiction='GB').query)

SELECT ""paiyroll_buss"".""id"", ""paiyroll_buss"".""jurisdiction"", ""paiyroll_buss"".""name"", ""paiyroll_buss"".""description"" FROM ""paiyroll_buss"" WHERE (""paiyroll_buss"".""jurisdiction"" = GB OR (""paiyroll_buss"".""jurisdiction"" = Universal AND NOT (""paiyroll_buss"".""name"" IN (SELECT U0.""name"" FROM ""paiyroll_buss"" U0 WHERE U0.""jurisdiction"" = GB))) OR (""paiyroll_buss"".""jurisdiction"" = Company AND NOT (""paiyroll_buss"".""name"" IN (SELECT V0.""name"" FROM ""paiyroll_buss"" V0 WHERE (V0.""jurisdiction"" = GB OR (V0.""jurisdiction"" = Universal AND NOT (V0.""name"" IN (SELECT U0.""name"" FROM ""paiyroll_buss"" U0 WHERE U0.""jurisdiction"" = GB))))))))
}}}

AFAIK, it is legal to add a .filter() to this kind of query, so I think this is a bug. On the mailing list (https://groups.google.com/g/django-users/c/iR6ArOi9OlY/m/bk0JDF_nDwAJ), there was a suggestion that using Q() might have helped but I could not see how to use Q() with ""exclude"".
"	Bug	closed	Database layer (models, ORM)	3.2	Release blocker	fixed		Simon Charette	Ready for checkin	1	0	0	0	0	0