Fixed #32786 -- Moved subquery ordering clearing optimization to the _in lookup.

Co-Authored-By: Simon Charette <charette.s@gmail.com>

5 files changed, 22 insertions, 9 deletions

diff --git a/django/db/backends/base/features.py b/django/db/backends/base/features.py
index 4c89231295..c9964d5229 100644
--- a/django/db/backends/base/features.py
+++ b/django/db/backends/base/features.py
@@ -64,6 +64,9 @@ class BaseDatabaseFeatures:
     has_real_datatype = False
     supports_subqueries_in_group_by = True
 
+    # Does the backend ignore unnecessary ORDER BY clauses in subqueries?
+    ignores_unnecessary_order_by_in_subqueries = True
+
     # Is there a true datatype for uuid?
     has_native_uuid_field = False
 
diff --git a/django/db/backends/oracle/features.py b/django/db/backends/oracle/features.py
index 07f16eee71..2d29b59015 100644
--- a/django/db/backends/oracle/features.py
+++ b/django/db/backends/oracle/features.py
@@ -15,6 +15,7 @@ class DatabaseFeatures(BaseDatabaseFeatures):
     select_for_update_of_column = True
     can_return_columns_from_insert = True
     supports_subqueries_in_group_by = False
+    ignores_unnecessary_order_by_in_subqueries = False
     supports_transactions = True
     supports_timezones = False
     has_native_duration_field = True
diff --git a/django/db/models/lookups.py b/django/db/models/lookups.py
index defa566f21..8eb6702204 100644
--- a/django/db/models/lookups.py
+++ b/django/db/models/lookups.py
@@ -404,9 +404,16 @@ class In(FieldGetDbPrepValueIterableMixin, BuiltinLookup):
             placeholder = '(' + ', '.join(sqls) + ')'
             return (placeholder, sqls_params)
         else:
-            if not getattr(self.rhs, 'has_select_fields', True):
-                self.rhs.clear_select_clause()
-                self.rhs.add_fields(['pk'])
+            from django.db.models.sql.query import (  # avoid circular import
+                Query,
+            )
+            if isinstance(self.rhs, Query):
+                query = self.rhs
+                query.clear_ordering(clear_default=True)
+                if not query.has_select_fields:
+                    query.clear_select_clause()
+                    query.add_fields(['pk'])
+
             return super().process_rhs(compiler, connection)
 
     def get_group_by_cols(self, alias=None):
diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
index 11c83bfe1a..814271a1f6 100644
--- a/django/db/models/sql/query.py
+++ b/django/db/models/sql/query.py
@@ -1034,12 +1034,6 @@ class Query(BaseExpression):
         # Subqueries need to use a different set of aliases than the outer query.
         clone.bump_prefix(query)
         clone.subquery = True
-        # It's safe to drop ordering if the queryset isn't using slicing,
-        # distinct(*fields) or select_for_update().
-        if (self.low_mark == 0 and self.high_mark is None and
-                not self.distinct_fields and
-                not self.select_for_update):
-            clone.clear_ordering(force=True)
         clone.where.resolve_expression(query, *args, **kwargs)
         for key, value in clone.annotations.items():
             resolved = value.resolve_expression(query, *args, **kwargs)
@@ -1062,6 +1056,13 @@ class Query(BaseExpression):
         ]
 
     def as_sql(self, compiler, connection):
+        # Some backends (e.g. Oracle) raise an error when a subquery contains
+        # unnecessary ORDER BY clause.
+        if (
+            self.subquery and
+            not connection.features.ignores_unnecessary_order_by_in_subqueries
+        ):
+            self.clear_ordering(force=False)
         sql, params = self.get_compiler(connection=connection).as_sql()
         if self.subquery:
             sql = '(%s)' % sql
diff --git a/django/db/models/sql/where.py b/django/db/models/sql/where.py
index 795eff8395..2577e1d7a5 100644
--- a/django/db/models/sql/where.py
+++ b/django/db/models/sql/where.py
@@ -239,6 +239,7 @@ class SubqueryConstraint:
         self.alias = alias
         self.columns = columns
         self.targets = targets
+        query_object.clear_ordering(clear_default=True)
         self.query_object = query_object
 
     def as_sql(self, compiler, connection):