Cannot combine two queryset in a subquery

[Raffaele Salmaso]

[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):

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

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 (

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"

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

Tested with 3.2, ​https://github.com/django/django/commit/d6314c4c2ef647efe0d12450214fc5b4a4055290 (next 3.2.1) and ​https://github.com/django/django/commit/59552bea5790c97be0da0a6f16ccd0189857c7a7 (main)

[Raffaele Salmaso]

It seems to be related to https://code.djangoproject.com/ticket/32143 (​https://github.com/django/django/commit/8593e162c9cb63a6c0b06daf045bc1c21eb4d7c1)

[Simon Charette]

Looks like the code doesn't properly handle nested subquery exclusion, likely due to re-aliasing in Query.trim_start.

[Simon Charette]

After ​a bit of investigation it seems the issue might actually lies in sql.Query.combine possibly with how it doesn't handle external_aliases.

[Simon Charette]

It ended up being an issue in Query.combine when dealing with subq_aliases.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 6d0cbe42:

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.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 48e19bae:

[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