Subquery referencing window expression

Django Internals ORM
1

I was hoping to be able to use the result of a WINDOW expression in a subquery in Django 4.2 after @charettes’s and @felixxm’s awesome work on #28333 but it doesn’t work. It seems that the query compiler is missing information about the database connection.

I added a test and this is the output:

Test output 
======================================================================
ERROR: test_filter_subquery (expressions_window.tests.WindowFunctionTests.test_filter_subquery)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib64/python3.11/unittest/case.py", line 57, in testPartExecutor
    yield
  File "/usr/lib64/python3.11/unittest/case.py", line 623, in run
    self._callTestMethod(testMethod)
  File "/usr/lib64/python3.11/unittest/case.py", line 579, in _callTestMethod
    if method() is not None:
  File "tests/expressions_window/tests.py", line 1025, in test_filter_subquery
    list(
  File "django/db/models/query.py", line 398, in __iter__
    self._fetch_all()
  File "django/db/models/query.py", line 1881, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "django/db/models/query.py", line 91, in __iter__
    results = compiler.execute_sql(
  File "django/db/models/sql/compiler.py", line 1545, in execute_sql
    sql, params = self.as_sql()
  File "django/db/models/sql/compiler.py", line 732, in as_sql
    extra_select, order_by, group_by = self.pre_sql_setup(
  File "django/db/models/sql/compiler.py", line 84, in pre_sql_setup
    self.setup_query(with_col_aliases=with_col_aliases)
  File "django/db/models/sql/compiler.py", line 73, in setup_query
    self.select, self.klass_info, self.annotation_col_map = self.get_select(
  File "django/db/models/sql/compiler.py", line 296, in get_select
    sql, params = self.compile(col)
  File "django/db/models/sql/compiler.py", line 542, in compile
    sql, params = node.as_sql(self, self.connection)
  File "django/db/models/expressions.py", line 1536, in as_sql
    subquery_sql, sql_params = self.query.as_sql(compiler, connection)
  File "django/db/models/sql/query.py", line 1150, in as_sql
    sql, params = self.get_compiler(connection=connection).as_sql()
  File "django/db/models/sql/compiler.py", line 751, in as_sql
    result, params = self.get_qualify_sql()
  File "django/db/models/sql/compiler.py", line 676, in get_qualify_sql
    inner_query_compiler = inner_query.get_compiler(
  File "django/db/models/sql/query.py", line 298, in get_compiler
    raise ValueError("Need either using or connection")
ValueError: Need either using or connection

As this clearly is not a regression I didn’t want to post it in the release blocker thread for 4.2. The odd nature of the exception makes me wonder if support for using a window expression in a subquery is easy to add/fix (now that filtering support and the subquery pushdown is implemented), or if this is a whole different beast.

EDIT: I’m wondering if it’s just because the connection parameter isn’t passed on:

diff --git a/django/db/models/sql/compiler.py b/django/db/models/sql/compiler.py
index 348cfa381a..f1e70eaac4 100644
--- a/django/db/models/sql/compiler.py
+++ b/django/db/models/sql/compiler.py
@@ -674,7 +674,7 @@ class SQLCompiler:
                 )
             )
         inner_query_compiler = inner_query.get_compiler(
-            self.using, elide_empty=self.elide_empty
+            self.using, self.connection, elide_empty=self.elide_empty
         )
         inner_sql, inner_params = inner_query_compiler.as_sql(
             # The limits must be applied to the outer query to avoid pruning
1 Like
2

Thanks for the report :+1: As far as I’m aware your analysis is correct and we should pass connection to the get_compiler() (as we do e.g. here).

I think we should consider this a bug in a new feature and a release blocker for Django 4.2. Can you create a new ticket?

3

Thanks for reassuring, Mariusz. I created ticket #34368.

1 Like