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)