Subquery with order_by results in The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

[randlet]

I ran into the error:

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. (1033) (SQLExecDirectW)')

when performing a simple API query with Django Rest Framework / Django Rest Framework Filters and I have narrowed it down to an issue when using a Subquery with an ordered queryset.

Here is a reproducible test case using the django-mssql-backend testapp with a freshly created/migrated SQL Server 2019 database:

from django.db.models import Subquery
from testapp.models import Author, Post

authors = [Author.objects.create(name="Author %d" % i for i in range(5)]
posts = [Post.objects.create(title="Post by %s" % author.name, author=author) for author in authors] 

subquery = Subquery(Author.objects.order_by("name").values("pk")) 
print(Post.objects.filter(author__in=subquery))

Which results in:

---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
C:\deploy\venvs\qatrack3\lib\site-packages\django\db\backends\utils.py in _execute(self, sql, params, *ignored_wrapper_args)
     83             else:
---> 84                 return self.cursor.execute(sql, params)
     85

E:\django-mssql-backend\sql_server\pyodbc\base.py in execute(self, sql, params)
    554         try:
--> 555             return self.cursor.execute(sql, params)
    556         except Database.Error as e:

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. (1033) (SQLExecDirectW)')

The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)
<ipython-input-4-ca584aaf3f61> in <module>
----> 1 print(Post.objects.filter(author__in=subquery))

C:\deploy\venvs\qatrack3\lib\site-packages\django\db\models\query.py in __repr__(self)
    248
    249     def __repr__(self):
--> 250         data = list(self[:REPR_OUTPUT_SIZE + 1])
    251         if len(data) > REPR_OUTPUT_SIZE:
    252             data[-1] = "...(remaining elements truncated)..."

C:\deploy\venvs\qatrack3\lib\site-packages\django\db\models\query.py in __iter__(self)
    272                - Responsible for turning the rows into model objects.
    273         """
--> 274         self._fetch_all()
    275         return iter(self._result_cache)
    276

C:\deploy\venvs\qatrack3\lib\site-packages\django\db\models\query.py in _fetch_all(self)
   1240     def _fetch_all(self):
   1241         if self._result_cache is None:
-> 1242             self._result_cache = list(self._iterable_class(self))
   1243         if self._prefetch_related_lookups and not self._prefetch_done:
   1244             self._prefetch_related_objects()

C:\deploy\venvs\qatrack3\lib\site-packages\django\db\models\query.py in __iter__(self)
     53         # Execute the query. This will also fill compiler.select, klass_info,
     54         # and annotations.
---> 55         results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
     56         select, klass_info, annotation_col_map = (compiler.select, compiler.klass_info,
     57                                                   compiler.annotation_col_map)

C:\deploy\venvs\qatrack3\lib\site-packages\django\db\models\sql\compiler.py in execute_sql(self, result_type, chunked_fetch, chunk_size)
   1140             cursor = self.connection.cursor()
   1141         try:
-> 1142             cursor.execute(sql, params)
   1143         except Exception:
   1144             # Might fail for server-side cursors (e.g. connection closed)

C:\deploy\venvs\qatrack3\lib\site-packages\django\db\backends\utils.py in execute(self, sql, params)
     65
     66     def execute(self, sql, params=None):
---> 67         return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
     68
     69     def executemany(self, sql, param_list):

C:\deploy\venvs\qatrack3\lib\site-packages\django\db\backends\utils.py in _execute_with_wrappers(self, sql, params, many, executor)
     74         for wrapper in reversed(self.db.execute_wrappers):
     75             executor = functools.partial(wrapper, executor)
---> 76         return executor(sql, params, many, context)
     77
     78     def _execute(self, sql, params, *ignored_wrapper_args):

C:\deploy\venvs\qatrack3\lib\site-packages\django\db\backends\utils.py in _execute(self, sql, params, *ignored_wrapper_args)
     82                 return self.cursor.execute(sql)
     83             else:
---> 84                 return self.cursor.execute(sql, params)
     85
     86     def _executemany(self, sql, param_list, *ignored_wrapper_args):

C:\deploy\venvs\qatrack3\lib\site-packages\django\db\utils.py in __exit__(self, exc_type, exc_value, traceback)
     87                 if dj_exc_type not in (DataError, IntegrityError):
     88                     self.wrapper.errors_occurred = True
---> 89                 raise dj_exc_value.with_traceback(traceback) from exc_value
     90
     91     def __call__(self, func):

C:\deploy\venvs\qatrack3\lib\site-packages\django\db\backends\utils.py in _execute(self, sql, params, *ignored_wrapper_args)
     82                 return self.cursor.execute(sql)
     83             else:
---> 84                 return self.cursor.execute(sql, params)
     85
     86     def _executemany(self, sql, param_list, *ignored_wrapper_args):

E:\django-mssql-backend\sql_server\pyodbc\base.py in execute(self, sql, params)
    553         self.last_params = params
    554         try:
--> 555             return self.cursor.execute(sql, params)
    556         except Database.Error as e:
    557             self.connection._on_error(e)

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. (1033) (SQLExecDirectW)')

[randlet]

The SQL that query generates:

SELECT 
	"testapp_post"."id", 
	"testapp_post"."title", 
	"testapp_post"."author_id", 
	"testapp_post"."alt_editor_id" 
FROM 
	"testapp_post" 
WHERE 
	"testapp_post"."author_id" IN (
		SELECT U0."id" FROM "testapp_author" U0 ORDER BY U0."name" ASC
	);

And to "fix" it you can add an OFFSET 0 ROWS to the subquery:

SELECT 
	"testapp_post"."id", 
	"testapp_post"."title", 
	"testapp_post"."author_id", 
	"testapp_post"."alt_editor_id" 
FROM 
	"testapp_post" 
WHERE 
	"testapp_post"."author_id" IN (
		SELECT U0."id" FROM "testapp_author" U0 ORDER BY U0."name" ASC OFFSET 0 ROWS
	);

or a TOP 100 PERCENT to the subquery:

SELECT 
	"testapp_post"."id", 
	"testapp_post"."title", 
	"testapp_post"."author_id", 
	"testapp_post"."alt_editor_id" 
FROM 
	"testapp_post" 
WHERE 
	"testapp_post"."author_id" IN (
		SELECT TOP 100 PERCENT U0."id" FROM "testapp_author" U0 ORDER BY U0."name" ASC
	);

[randlet]

My naive fix is to add an OFFSET 0 ROWS to any query which has an ordering but no offset or limit:

qatrackplus@677f200

            if order_by:
                ordering = []
                for _, (o_sql, o_params, _) in order_by:
                    ordering.append(o_sql)
                    params.extend(o_params)
                result.append('ORDER BY %s' % ', '.join(ordering))


                if not (do_offset or do_limit):
                    result.append("OFFSET 0 ROWS")

I'm not sure if that is the best way to do it though. If someone can give me some more guidance I can create a PR.