[Django] #32650: Cannot combine two queryset in a subquery
Django
-------------------------------------+-------------------------------------
Reporter: Raffaele | Owner: nobody
Salmaso |
Type: Bug | Status: new
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 |
-------------------------------------+-------------------------------------
[Sample project https://github.com/rsalmaso/django32-subquery-test and run
`./manage.py query`]
Django 3.2 fails this query (a combined queryset in a subquery):
{{{#!python
import datetime as dt
from decimal import Decimal
from django.conf import settings
from django.db import models
from django.db.models import Case, OuterRef, Q, Subquery, Value, When
from django.utils import timezone
class UserQuerySet(models.QuerySet):
def annotate_active_subscription_id(self):
return self.annotate(
active_subscription_id_db=Subquery(
Subscription.objects.active()
.annotate(
plan_order=Case(
When(plan__code="BASE", then=Value(1)),
default=Value(0),
output_field=models.PositiveSmallIntegerField(),
)
)
.filter(user=OuterRef("id"))
.order_by("plan_order", "-id")
.values("id")[:1]
)
)
class User(models.Model):
objects = models.Manager.from_queryset(UserQuerySet)()
class Plan(models.Model):
code = models.CharField(verbose_name="Codice", max_length=255)
class SubscriptionQuerySet(models.QuerySet):
def will_be_renewed_today(self):
today = dt.date.today()
return
self.filter(start_date__lte=today).exclude(user__subscriptions__start_date=today).distinct()
def active(self):
return self.filter(enabled=True).distinct() |
self.will_be_renewed_today()
class Subscription(models.Model):
user = models.ForeignKey(User, verbose_name="Utente",
on_delete=models.CASCADE, related_name="subscriptions")
plan = models.ForeignKey(Plan, on_delete=models.CASCADE,
verbose_name="Piano di abbonamento")
start_date = models.DateField(verbose_name="Data di inizio",
default=dt.date.today)
enabled = models.BooleanField(verbose_name="Abilitato", default=True)
objects = models.Manager.from_queryset(SubscriptionQuerySet)()
print(User.objects.annotate_active_subscription_id().count())
}}}
with django 3.1.8
{{{#!sql
SELECT
"subquery_user"."id",
(
SELECT
"subquery"."id"
FROM
(
SELECT
DISTINCT U0."id",
CASE WHEN (U2."code" = BASE) THEN 1 ELSE 0 END
FROM
"subquery_subscription" U0
INNER JOIN "subquery_plan" U2 ON (U0."plan_id" = U2."id")
WHERE
(
(
U0."enabled"
OR (
U0."start_date" <= 2021 - 04 - 13
AND NOT (
U0."user_id" IN (
SELECT
U2."user_id"
FROM
"subquery_subscription" U2
WHERE
U2."start_date" = 2021 - 04 - 13
)
)
)
)
AND U0."user_id" = "subquery_user"."id"
)
ORDER BY
CASE WHEN (U2."code" = BASE) THEN 1 ELSE 0 END ASC,
U0."id" DESC
LIMIT
1
) subquery
) AS "active_subscription_id_db"
FROM
"subquery_user"
}}}
with django 3.2 (
{{{#!sql
SELECT
"subquery_user"."id",
(
SELECT
"subquery"."id"
FROM
(
SELECT
DISTINCT U0."id",
CASE WHEN (U2."code" = BASE) THEN 1 ELSE 0 END
FROM
"subquery_subscription" U0
INNER JOIN "subquery_plan" U2 ON (U0."plan_id" = U2."id")
WHERE
(
(
U0."enabled"
OR (
U0."start_date" <= 2021 - 04 - 13
AND NOT (
EXISTS(
SELECT
(1) AS "a"
FROM
"subquery_subscription" V2
WHERE
(
V2."start_date" = 2021 - 04 - 13
AND V2."user_id" = V0."user_id"
)
LIMIT
1
)
)
)
) AND U0."user_id" = "subquery_user"."id"
)
ORDER BY
CASE WHEN (U2."code" = BASE) THEN 1 ELSE 0 END ASC,
U0."id" DESC
LIMIT
1
) subquery
) AS "active_subscription_id_db"
FROM
"subquery_user"
}}}
{{{#!python
Traceback (most recent call last):
File ".venvs/django32/lib/python3.8/site-
packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File ".venvs/django32/lib/python3.8/site-
packages/django/db/backends/sqlite3/base.py", line 423, in execute
return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: no such column: V0.user_id
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "./manage.py", line 22, in <module>
main()
File "./manage.py", line 18, in main
execute_from_command_line(sys.argv)
File ".venvs/django32/lib/python3.8/site-
packages/django/core/management/__init__.py", line 419, in
execute_from_command_line
utility.execute()
File ".venvs/django32/lib/python3.8/site-
packages/django/core/management/__init__.py", line 413, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File ".venvs/django32/lib/python3.8/site-
packages/django/core/management/base.py", line 354, in run_from_argv
self.execute(*args, **cmd_options)
File ".venvs/django32/lib/python3.8/site-
packages/django/core/management/base.py", line 398, in execute
output = self.handle(*args, **options)
File "/home/raf/src/fiscozen/django-
debug/subquery/management/commands/query.py", line 11, in handle
print(qs.count())
File ".venvs/django32/lib/python3.8/site-
packages/django/db/models/query.py", line 412, in count
return self.query.get_count(using=self.db)
File ".venvs/django32/lib/python3.8/site-
packages/django/db/models/sql/query.py", line 526, in get_count
number = obj.get_aggregation(using, ['__count'])['__count']
File ".venvs/django32/lib/python3.8/site-
packages/django/db/models/sql/query.py", line 511, in get_aggregation
result = compiler.execute_sql(SINGLE)
File ".venvs/django32/lib/python3.8/site-
packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
cursor.execute(sql, params)
File ".venvs/django32/lib/python3.8/site-
packages/django/db/backends/utils.py", line 98, in execute
return super().execute(sql, params)
File ".venvs/django32/lib/python3.8/site-
packages/django/db/backends/utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False,
executor=self._execute)
File ".venvs/django32/lib/python3.8/site-
packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File ".venvs/django32/lib/python3.8/site-
packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File ".venvs/django32/lib/python3.8/site-packages/django/db/utils.py",
line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File ".venvs/django32/lib/python3.8/site-
packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File ".venvs/django32/lib/python3.8/site-
packages/django/db/backends/sqlite3/base.py", line 423, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: no such column: V0.user_id
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32650>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> print(User.objects.annotate_active_subscription_id().count())
> }}}
>
New description:
objects = models.Manager.from_queryset(SubscriptionQuerySet)()
print(User.objects.annotate_active_subscription_id().count())
}}}
with django 3.1.8
with django 3.2 (
Tested with 3.2,
https://github.com/django/django/commit/d6314c4c2ef647efe0d12450214fc5b4a4055290
(next 3.2.1) and
https://github.com/django/django/commit/59552bea5790c97be0da0a6f16ccd0189857c7a7
(main)
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32650#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Raffaele Salmaso):
It seems to be related to https://code.djangoproject.com/ticket/32143
(https://github.com/django/django/commit/8593e162c9cb63a6c0b06daf045bc1c21eb4d7c1)
--
Ticket URL: <https://code.djangoproject.com/ticket/32650#comment:2>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Simon Charette
* status: new => assigned
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted
Comment:
Looks like the code doesn't properly handle nested subquery exclusion,
likely due to re-aliasing in `Query.trim_start`.
--
Ticket URL: <https://code.djangoproject.com/ticket/32650#comment:3>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
After [https://github.com/charettes/django/pull/new/ticket-32650 a bit of
investigation] it seems the issue might actually lies in
`sql.Query.combine` possibly with how it doesn't handle
`external_aliases`.
--
Ticket URL: <https://code.djangoproject.com/ticket/32650#comment:4>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
Comment:
It ended up being an issue in `Query.combine` when dealing with
`subq_aliases`.
--
Ticket URL: <https://code.djangoproject.com/ticket/32650#comment:5>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/32650#comment:6>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/32650#comment:7>
Django
-------------------------------------+-------------------------------------
| Charette
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"6d0cbe42c3d382e5393d4af48185c546bb0ada1f" 6d0cbe42]:
{{{
#!CommitTicketReference repository=""
revision="6d0cbe42c3d382e5393d4af48185c546bb0ada1f"
Fixed #32650 -- Fixed handling subquery aliasing on queryset combination.
This issue started manifesting itself when nesting a combined subquery
relying on exclude() since 8593e162c9cb63a6c0b06daf045bc1c21eb4d7c1 but
sql.Query.combine never properly handled subqueries outer refs in the
first place, see QuerySetBitwiseOperationTests.test_subquery_aliases()
(refs #27149).
Thanks Raffaele Salmaso for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32650#comment:8>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"48e19bae49f271cccbb8a8f4549c9366b7cecac6" 48e19bae]:
{{{
#!CommitTicketReference repository=""
revision="48e19bae49f271cccbb8a8f4549c9366b7cecac6"
[3.2.x] Fixed #32650 -- Fixed handling subquery aliasing on queryset
combination.
This issue started manifesting itself when nesting a combined subquery
relying on exclude() since 8593e162c9cb63a6c0b06daf045bc1c21eb4d7c1 but
sql.Query.combine never properly handled subqueries outer refs in the
first place, see QuerySetBitwiseOperationTests.test_subquery_aliases()
(refs #27149).
Thanks Raffaele Salmaso for the report.
Backport of 6d0cbe42c3d382e5393d4af48185c546bb0ada1f from main
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32650#comment:9>