Opened 6 months ago

Closed 6 months ago

Last modified 6 months ago

#32650 closed Bug (fixed)

Cannot combine two queryset in a subquery

Reported by: Raffaele Salmaso Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 3.2
Severity: Release blocker Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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)

Change History (9)

comment:1 Changed 6 months ago by Raffaele Salmaso

Description: modified (diff)

comment:3 Changed 6 months ago by Simon Charette

Owner: changed from nobody to Simon Charette
Severity: NormalRelease blocker
Status: newassigned
Triage Stage: UnreviewedAccepted

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

comment:4 Changed 6 months ago by 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.

comment:5 Changed 6 months ago by Simon Charette

Has patch: set
Patch needs improvement: set

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

comment:6 Changed 6 months ago by Simon Charette

Patch needs improvement: unset

comment:7 Changed 6 months ago by Mariusz Felisiak

Triage Stage: AcceptedReady for checkin

comment:8 Changed 6 months ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

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.

comment:9 Changed 6 months ago by 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

Note: See TracTickets for help on using tickets.
Back to Top