#32717: Incorrect SQL generation filtering OR-combined queries
-------------------------------------+-------------------------------------
Reporter: Shaheed | Owner: nobody
Haque |
Type: | Status: new
Uncategorized |
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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".
--
Ticket URL: <https://code.djangoproject.com/ticket/32717>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/055.acd4243e60da805578d5f6c24b7d8d9b%40djangoproject.com.
