# Subquery referencing window expression

**URL:** <https://forum.djangoproject.com/t/subquery-referencing-window-expression/19078>\
**Category:** ORM\
**Created:** [February 24, 2023, 8:52am UTC](https://forum.djangoproject.com/t/subquery-referencing-window-expression/19078 "2023-02-24T08:52:44Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![jnns](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/jnns/32/950_2.png) [@jnns](https://forum.djangoproject.com/u/jnns)\
**Post date:** [February 24, 2023, 8:52am UTC](https://forum.djangoproject.com/t/subquery-referencing-window-expression/19078/1 "2023-02-24T08:52:44Z")

</div>

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](https://code.djangoproject.com/ticket/28333) but it doesn’t work. It seems that the query compiler is missing information about the database connection.

[I added a test](https://github.com/jnns/django/commit/04d59e27503fb376d60314806876ecbae89d9b62) and this is the output:

> **Test output**
>
> ```auto
> ======================================================================
> 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:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![felixxm](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/felixxm/32/2479_2.png) [@felixxm](https://forum.djangoproject.com/u/felixxm)\
**Post date:** [February 24, 2023, 10:43am UTC](https://forum.djangoproject.com/t/subquery-referencing-window-expression/19078/2 "2023-02-24T10:43:41Z")

</div>

Thanks for the report 👍 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](https://github.com/django/django/blob/f9fe062de5fc0896d6bbbf3f260b5c44473b3c77/django/db/models/sql/compiler.py#L548)).

> [@jnns](#):
>
> As this clearly is not a regression I didn’t want to post it in the release blocker thread for 4.2.

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?

---

<div class="post-metadata">

**Author:** ![jnns](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/jnns/32/950_2.png) [@jnns](https://forum.djangoproject.com/u/jnns)\
**Post date:** [February 24, 2023, 11:32am UTC](https://forum.djangoproject.com/t/subquery-referencing-window-expression/19078/3 "2023-02-24T11:32:55Z")

</div>

Thanks for reassuring, Mariusz. I created ticket [#34368](https://code.djangoproject.com/ticket/34368).