Commits

Anonymous committed abc4444

Fixed #10113 -- Ensured that joined fields mentioned in order_by() are included in the GROUP_BY clause on those backends that require it. Thanks to Koen Biermans <koen.biermans@werk.belgie.be> for the report.

  • Participants
  • Parent commits 87ca42e

Comments (0)

Files changed (2)

django/db/models/sql/query.py

         """
         self.pre_sql_setup()
         out_cols = self.get_columns(with_col_aliases)
-        ordering = self.get_ordering()
+        ordering, ordering_group_by = self.get_ordering()
 
         # This must come after 'select' and 'ordering' -- see docstring of
         # get_from_clause() for details.
 
         if self.group_by:
             grouping = self.get_grouping()
+            if ordering:
+                # If the backend can't group by PK (i.e., any database
+                # other than MySQL), then any fields mentioned in the
+                # ordering clause needs to be in the group by clause.
+                if not self.connection.features.allows_group_by_pk:
+                    grouping.extend(ordering_group_by)
+            else:
+                ordering = self.connection.ops.force_no_ordering()
             result.append('GROUP BY %s' % ', '.join(grouping))
-            if not ordering:
-                ordering = self.connection.ops.force_no_ordering()
 
         if having:
             result.append('HAVING %s' % having)
 
     def get_ordering(self):
         """
-        Returns list representing the SQL elements in the "order by" clause.
+        Returns a tuple containing a list representing the SQL elements in the
+        "order by" clause, and the list of SQL elements that need to be added
+        to the GROUP BY clause as a result of the ordering.
+
         Also sets the ordering_aliases attribute on this instance to a list of
         extra aliases needed in the select.
 
         distinct = self.distinct
         select_aliases = self._select_aliases
         result = []
+        group_by = []
         ordering_aliases = []
         if self.standard_ordering:
             asc, desc = ORDER_DIR['ASC']
                 else:
                     order = asc
                 result.append('%s %s' % (field, order))
+                group_by.append(field)
                 continue
             col, order = get_order_dir(field, asc)
             if col in self.aggregate_select:
                     processed_pairs.add((table, col))
                     if not distinct or elt in select_aliases:
                         result.append('%s %s' % (elt, order))
+                        group_by.append(elt)
             elif get_order_dir(field)[0] not in self.extra_select:
                 # 'col' is of the form 'field' or 'field1__field2' or
                 # '-field1__field2__field', etc.
                         if distinct and elt not in select_aliases:
                             ordering_aliases.append(elt)
                         result.append('%s %s' % (elt, order))
+                        group_by.append(elt)
             else:
                 elt = qn2(col)
                 if distinct and col not in select_aliases:
                     ordering_aliases.append(elt)
                 result.append('%s %s' % (elt, order))
+                group_by.append(elt)
         self.ordering_aliases = ordering_aliases
-        return result
+        return result, group_by
 
     def find_ordering_name(self, name, opts, alias=None, default_order='ASC',
             already_seen=None):

tests/regressiontests/aggregation_regress/models.py

 >>> Publisher.objects.filter(pk=5).annotate(num_authors=Count('book__authors'), avg_authors=Avg('book__authors'), max_authors=Max('book__authors'), max_price=Max('book__price'), max_rating=Max('book__rating')).values()
 [{'max_authors': None, 'name': u"Jonno's House of Books", 'num_awards': 0, 'max_price': None, 'num_authors': 0, 'max_rating': None, 'id': 5, 'avg_authors': None}]
 
+# Regression for #10113 - Fields mentioned in order_by() must be included in the GROUP BY.
+# This only becomes a problem when the order_by introduces a new join.
+>>> Book.objects.annotate(num_authors=Count('authors')).order_by('publisher__name')
+[<Book: The Definitive Guide to Django: Web Development Done Right>, <Book: Practical Django Projects>, <Book: Paradigms of Artificial Intelligence Programming: Case Studies in Common Lisp>, <Book: Python Web Development with Django>, <Book: Artificial Intelligence: A Modern Approach>, <Book: Sams Teach Yourself Django in 24 Hours>]
+
 """
 }