Refs #33308 -- Enabled explicit GROUP BY and ORDER BY aliases.

This ensures explicit grouping from using values() before annotating an
aggregate function groups by selected aliases if supported.

The GROUP BY feature is disabled on Oracle because it doesn't support it.

2 files changed, 33 insertions, 7 deletions

diff --git a/django/db/models/sql/compiler.py b/django/db/models/sql/compiler.py
index f566546307..e4605918d9 100644
--- a/django/db/models/sql/compiler.py
+++ b/django/db/models/sql/compiler.py
@@ -131,9 +131,12 @@ class SQLCompiler:
             # Converts string references to expressions.
             for expr in self.query.group_by:
                 if not hasattr(expr, "as_sql"):
-                    expressions.append(self.query.resolve_ref(expr))
-                else:
-                    expressions.append(expr)
+                    expr = self.query.resolve_ref(expr)
+                if not self.connection.features.allows_group_by_refs and isinstance(
+                    expr, Ref
+                ):
+                    expr = expr.source
+                expressions.append(expr)
         # Note that even if the group_by is set, it is only the minimal
         # set to group by. So, we need to add cols in select, order_by, and
         # having into the select in any case.
@@ -344,7 +347,13 @@ class SQLCompiler:
                 if not self.query.standard_ordering:
                     field = field.copy()
                     field.reverse_ordering()
-                yield field, False
+                if isinstance(field.expression, F) and (
+                    annotation := self.query.annotation_select.get(
+                        field.expression.name
+                    )
+                ):
+                    field.expression = Ref(field.expression.name, annotation)
+                yield field, isinstance(field.expression, Ref)
                 continue
             if field == "?":  # random
                 yield OrderBy(Random()), False
@@ -432,6 +441,10 @@ class SQLCompiler:
         """
         result = []
         seen = set()
+        replacements = {
+            expr: Ref(alias, expr)
+            for alias, expr in self.query.annotation_select.items()
+        }
 
         for expr, is_ref in self._order_by_pairs():
             resolved = expr.resolve_expression(self.query, allow_joins=True, reuse=None)
@@ -461,7 +474,7 @@ class SQLCompiler:
                         q.add_annotation(expr_src, col_name)
                     self.query.add_select_col(resolved, col_name)
                     resolved.set_source_expressions([RawSQL(f"{order_by_idx}", ())])
-            sql, params = self.compile(resolved)
+            sql, params = self.compile(resolved.replace_expressions(replacements))
             # Don't add the same column twice, but the order direction is
             # not taken into account so we strip it. When this entire method
             # is refactored into expressions, then we can check each part as we
diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
index 61e39b5153..e454a6e868 100644
--- a/django/db/models/sql/query.py
+++ b/django/db/models/sql/query.py
@@ -2220,8 +2220,8 @@ class Query(BaseExpression):
         primary key, and the query would be equivalent, the optimization
         will be made automatically.
         """
-        # Column names from JOINs to check collisions with aliases.
         if allow_aliases:
+            # Column names from JOINs to check collisions with aliases.
             column_names = set()
             seen_models = set()
             for join in list(self.alias_map.values())[1:]:  # Skip base table.
@@ -2231,7 +2231,20 @@ class Query(BaseExpression):
                         {field.column for field in model._meta.local_concrete_fields}
                     )
                     seen_models.add(model)
-
+            if self.values_select:
+                # If grouping by aliases is allowed assign selected values
+                # aliases by moving them to annotations.
+                group_by_annotations = {}
+                values_select = {}
+                for alias, expr in zip(self.values_select, self.select):
+                    if isinstance(expr, Col):
+                        values_select[alias] = expr
+                    else:
+                        group_by_annotations[alias] = expr
+                self.annotations = {**group_by_annotations, **self.annotations}
+                self.append_annotation_mask(group_by_annotations)
+                self.select = tuple(values_select.values())
+                self.values_select = tuple(values_select)
         group_by = list(self.select)
         if self.annotation_select:
             for alias, annotation in self.annotation_select.items():