10.12 GROUP BY and HAVING: set_group_by, get_group_by and split_having_qualify

Query.group_by: three forms, and who sets each

Query.group_by is None on a new query, and a comment on the attribute gives its forms.

QuerySet._annotate sets it as its last step, and only where an annotation added by that very call has contains_aggregate set: True where neither values nor values_list has been called, and otherwise set_group_by, which fixes the tuple at what the queryset selects, the columns values named first. QuerySet.alias goes through the same method and sets it the same way, and an annotation without an aggregate leaves it alone. The queryset’s side, and the order of values and annotate, is Annotations, ordering and the other methods that change the query. What follows was recorded from a running Django, on SQLite, over the models of From QuerySet to SQL: a line is a question and its answer after ->, or a call with the calls it set going nested under it and -> what each returned. A line that asks tail of a queryset answers with its statement from the "FROM" clause on, the select list and its parameters left out.

Query.group_by: None where nothing aggregates, True after an annotation that does, and a tuple after values and then annotate
    Ship.objects.all().query.group_by, Ship.objects.annotate(hands=Count('crew')).query.group_by, Ship.objects.annotate(low=Lower('name')).query.group_by  ->  (None, True, None)
    Ship.objects.values('home').annotate(hands=Count('crew')).query.group_by  ->  (Col(fleet_ship, fleet.Ship.home),)
    Ship.objects.values('home', 'name').annotate(hands=Count('crew')).query.group_by  ->  (Col(fleet_ship, fleet.Ship.home), Col(fleet_ship, fleet.Ship.name))
    Ship.objects.annotate(hands=Count('crew')).values('home').query.group_by  ->  (Col(fleet_ship, fleet.Ship.id), Col(fleet_ship, fleet.Ship.name), Col(fleet_ship, fleet.Ship.tonnage), Col(fleet_ship, fleet.Ship.home), Col(fleet_ship, fleet.Ship.captain))
    Ship.objects.values(low=Lower('name')).annotate(hands=Count('crew')).query.group_by, Ship.objects.values(low=Lower('name')).annotate(hands=Count('crew')).query.annotations  ->  ((Ref(low, Lower(Col(fleet_ship, fleet.Ship.name))),), {'low': Lower(Col(fleet_ship, fleet.Ship.name)), 'hands': Count(Col(crew_sailor, crew.Sailor.id))})
    Ship.objects.alias(hands=Count('crew')).query.group_by  ->  True
    Sailor.objects.values('signed_on__year').annotate(n=Count('id')).query.group_by, Sailor.objects.values('signed_on__year').annotate(n=Count('id')).query.annotations  ->  ((Ref(signed_on__year, ExtractYear(Col(crew_sailor, crew.Sailor.signed_on))),), {'signed_on__year': ExtractYear(Col(crew_sailor, crew.Sailor.signed_on)), 'n': Count(Col(crew_sailor, crew.Sailor.id))})

The first line shows None for a query that does not aggregate and for one whose annotation is a Lower, and True after a count. The tuple after values("home") holds the one column, and after values("home", "name") the two. The tuple after annotate and then values holds every column of the model: annotate had set True, and set_values, finding it, fixed the grouping at all the concrete fields before it narrowed the select list. The one with Lower holds a Ref, a reference to the annotation by its alias, and alias sets True as annotate does. In the last line a transform in a name given to values, the year a sailor signed on, has been moved into the annotations, and the tuple holds a Ref to it.

Query.set_group_by

Query.set_group_by turns the select list and the selected annotations into the tuple. Its docstring says the result will usually be the set of all non-aggregate fields in the return data, and that where the backend can group by the primary key the optimisation is made automatically; the second half is the compiler’s, told below.

With allow_aliases=True, which is the default and what _annotate passes, it first looks through the expressions values selected, in Query.select beside their names in Query.values_select, for one that is not a Col: a transform in a name given to values, as "signed_on__year" of a sailor is, which Query.add_fields resolves to the transform of the column. Each such expression is moved into Query.annotations under its name, added to the mask of selected annotations, and taken out of the select list, with Query.selected kept in step, so that the grouping can refer to it by alias. Then the tuple is built: every expression left in the select list, and for each selected annotation what its get_group_by_cols returns, which for an aggregate is nothing and for an expression without one is the expression itself (Aggregates: Aggregate, Count and get_aggregation, Expressions: resolve_expression and as_sql); where that is not empty, an annotation without an aggregate goes in as a Ref to its alias, under allow_aliases=True, and as its columns otherwise.

set_values and exists pass allow_aliases=False, with a comment saying why: each is about to clear the select list, and a Ref into it would be orphaned. In set_values the call comes where group_by is True, after every concrete field of the model has been added to the select, so that the tuple holds every column and the fields the caller named only decide what is selected of each group; where group_by is a tuple already, set_values replaces a Ref whose alias is no longer among the selected names by the annotation’s own expression. Query.exists does the same as set_values for True before it clears the select clause, which is how a grouped query keeps its "GROUP BY" in the statement that asks whether any row matches.

SQLCompiler.get_group_by

SQLCompiler.get_group_by returns the clause as a list of pairs of SQL and parameters, one for each expression grouped by. Its docstring admits that what the clause contains is hard to describe in other words than that it passes the test suite; a comment under it gives examples, and then the rule that makes them one: the query’s group_by is the minimal set, which no call of the queryset can narrow, and the columns of the "HAVING", "ORDER BY" and "SELECT" clauses are added to it, so that no form of a queryset forces a chosen "GROUP BY", a later annotation, ordering or filter being able to widen it. SQLCompiler.pre_sql_setup calls it last, after the select list is settled, after the ordering is resolved, which may join a table of its own, and after WhereNode.split_having_qualify has divided the where tree into the where part, the conditions for "WHERE", the having part, those for "HAVING", and the qualify part, those that refer to a window, since the having part’s columns go in too (The compiler: as_sql, execute_sql and results_iter).

With group_by of None the clause is empty. Otherwise the method gathers expressions in four steps and then writes them. From the query, where group_by is a tuple: a string is resolved through Query.resolve_ref; a Ref puts its alias in a set of grouped aliases and, once for each alias, its source expression among the expressions rather than the Ref itself; anything else goes in as it is. The source is the very expression the select list holds under that alias, so it can be written as the alias’s position, and the select list’s own contribution of it is skipped. From the select list, each selected expression’s position is noted where it has an alias, a member whose alias is among the grouped aliases is skipped, and the rest contribute their get_group_by_cols, so that a model’s columns contribute themselves and an aggregate contributes nothing. From the ordering, each expression that is not a reference to the select list contributes its get_group_by_cols, a comment saying that a reference needs nothing since every selected expression is already grouped by; and none of them contributes where the ordering is the model’s own, which is told below. From the having, each column WhereNode.get_group_by_cols collects from the having tree, where a lookup collects from its left side, and from its right side too where that is an expression, so that a comparison of an aggregate contributes nothing and a comparison of a column contributes the column.

Then SQLCompiler.collapse_group_by may shorten the list, and each expression is compiled. One that raises EmptyResultSet or FullResultSet, a condition that can match nothing or everything, is left out. Where the backend’s allows_group_by_select_index is set and the expression was noted at a position of the select list, its SQL is replaced by that position; otherwise the SQL goes through the expression’s select_format, which is what wraps a condition in "CASE WHEN" on a backend that cannot select a boolean. A pair already written is not written twice.

tail(Ship.objects.annotate(hands=Count('crew')))  ->  ('FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") GROUP BY "fleet_ship"."id", "fleet_ship"."name", "fleet_ship"."tonnage", "fleet_ship"."home_id", "fleet_ship"."captain_id"', ())
tail(Ship.objects.values('home').annotate(hands=Count('crew')))  ->  ('FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") GROUP BY 1', ())
tail(Ship.objects.values('home').annotate(hands=Count('crew')).order_by('name'))  ->  ('FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") GROUP BY 1, "fleet_ship"."name" ORDER BY "fleet_ship"."name" ASC', ())
tail(Ship.objects.values('home').annotate(hands=Count('crew')).filter(hands__gt=1))  ->  ('FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") GROUP BY 1 HAVING COUNT("crew_sailor"."id") > %s', (1,))
...
tail(Sailor.objects.values('signed_on__year').annotate(n=Count('id')))  ->  ('FROM "crew_sailor" GROUP BY 1', ())
...
tail(Ship.objects.annotate(hands=Count('crew')).annotate(low=Lower('name')))  ->  ('FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") GROUP BY "fleet_ship"."id", "fleet_ship"."name", "fleet_ship"."tonnage", "fleet_ship"."home_id", "fleet_ship"."captain_id", 7', ())

With annotate alone the grouping is every column of a ship, from True: the five columns the select list contributes, with nothing from the count; that the clause lists every column is this backend’s doing, where one that allows grouping by the selected primary keys would list one. After values("home") it is the one column of the minimal set, written as its position; order_by("name") adds the column the ordering names, and the filter on the count adds nothing the minimal set lacks. The year a sailor signed on, a transform in a values name, is grouped by as the position of the annotation it was moved to; and a second annotation without an aggregate stands at the seventh position of the select list and is grouped by that number. Here is the assembly for one query with each source of a column:

GROUP BY and HAVING assembled: set_values and set_group_by on the query, then split_having_qualify and get_group_by in the compiler
    grouped = Ship.objects.values('home').annotate(hands=Count('crew')).filter(Q(hands__gt=1) | Q(name='Petrel')).order_by('name')
      Query.set_values(('home',))
      Query.add_annotation(annotation=Count(F(crew)), alias='hands')
      Query.set_group_by
    grouped.query.get_compiler('default').as_sql()
      SQLCompiler.as_sql
        SQLCompiler.pre_sql_setup
          SQLCompiler.get_select  ->  (2 columns, klass_info for Ship, {'home': 0, 'hands': 1})
          SQLCompiler.get_order_by  ->  ['"fleet_ship"."name" ASC']
          WhereNode.split_having_qualify
            WhereNode.split_having_qualify  ->  (None, (OR: IntegerGreaterThan(Count(Col(crew_sailor, crew.Sailor.id)), 1), Exact(Col(fleet_ship, fleet.Ship.name), 'Petrel')), None)
            -> (None, (AND: (OR: IntegerGreaterThan(Count(Col(crew_sailor, crew.Sailor.id)), 1), Exact(Col(fleet_ship, fleet.Ship.name), 'Petrel'))), None)
          SQLCompiler.get_group_by
            SQLCompiler.collapse_group_by(expressions=[Col(fleet_ship, fleet.Ship.home), Col(fleet_ship, fleet.Ship.home), Col(fleet_ship, fleet.Ship.name), Col(fleet_ship, fleet.Ship.name)], having=[Col(fleet_ship, fleet.Ship.name)])  ->  [Col(fleet_ship, fleet.Ship.home), Col(fleet_ship, fleet.Ship.home), Col(fleet_ship, fleet.Ship.name), Col(fleet_ship, fleet.Ship.name)]
            -> ['1', '"fleet_ship"."name"']
          -> (extra_select of 0, order_by of 1, group_by of 2)
        -> ('SELECT "fleet_ship"."home_id" AS "home", COUNT("crew_sailor"."id") AS "hands" FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") GROUP BY 1, "fleet_ship"."name" HAVING (COUNT("crew_sailor"."id") > %s OR "fleet_ship"."name" = %s) ORDER BY "fleet_ship"."name" ASC', (1, 'Petrel'))

set_values put the home port in the select list, add_annotation the count among the annotations, and set_group_by made the tuple of the one column. In the compiler, get_order_by resolved the name, split_having_qualify sent the whole "OR" to the having, and collapse_group_by was handed the home port twice, from the minimal set and from the select list, and the name twice, from the ordering and from the having, and returned them as they were; the deduplication wrote each once, the home port as its position.

collapse_group_by and the two features

SQLCompiler.collapse_group_by does one thing, and only where the backend’s allows_group_by_selected_pks is set. It finds every expression that is the primary key column of a table, where BaseDatabaseFeatures.allows_group_by_selected_pks_on_model allows the model, which it does for a managed one, since a comment says an unmanaged model may stand for a view on which the reduction is not allowed; and it drops every other column of those tables, keeping any the having refers to. A comment names the reason: on such a backend the other columns are functionally dependent on the key, and grouping by the key is grouping by them. The feature is set on PostgreSQL, and on MySQL unless it is MariaDB with "ONLY_FULL_GROUP_BY" in its SQL mode; SQLite and Oracle have it off, and the recording switched it on for one block.

tail(Ship.objects.annotate(hands=Count('crew')))  ->  ('FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") GROUP BY "fleet_ship"."id"', ())
tail(Ship.objects.annotate(hands=Count('crew')).order_by('home__country'))  ->  ('FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") INNER JOIN "fleet_port" ON ("fleet_ship"."home_id" = "fleet_port"."id") GROUP BY "fleet_ship"."id", "fleet_port"."country" ORDER BY "fleet_port"."country" ASC', ())

The five columns of a ship became one. In the second statement the ordering joined the port’s table and added its country, which stays, since the port’s primary key is not among the grouped columns.

allows_group_by_select_index is the other feature, set on every backend but Oracle, and it decides whether a selected expression is written as its position. With it off, the expression is written out:

With allows_group_by_select_index switched off for this block: the expressions are written out where a position stood
    tail(Ship.objects.values('home').annotate(hands=Count('crew')).order_by('home'))  ->  ('FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") GROUP BY "fleet_ship"."home_id" ORDER BY 1 ASC', ())
    tail(Ship.objects.values(low=Lower('name')).annotate(hands=Count('crew')))  ->  ('FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") GROUP BY LOWER("fleet_ship"."name")', ())

The model’s ordering is dropped

A statement that groups does not take the model’s default ordering, the ordering of its Meta. SQLCompiler._order_by_pairs decides where the ordering comes from, and where it falls back on the model’s options it sets SQLCompiler._meta_ordering to that list (ORDER BY and DISTINCT: get_order_by, find_ordering_name and get_distinct). get_group_by reads the attribute and, where it is set, adds nothing from the ordering; and SQLCompiler.as_sql, once it has written a "GROUP BY", sets the ordering to None where the attribute is set, so that the "ORDER BY" is not written either. That is why the statement above for annotate(hands=Count('crew')) alone has no "ORDER BY" although a ship is ordered by name, and the statement with order_by("name") has one: an ordering given to the queryset is kept, and its column grouped by.

split_having_qualify

WhereNode.split_having_qualify returns three nodes, any of them None: the where part, the having part, and the qualify part, which the compiler writes as another statement around this one (Window expressions: Window, WindowFrame and the QUALIFY rewrite). pre_sql_setup calls it with must_group_by=True where the query’s group_by is not None; Query.get_aggregation calls it with nothing, to learn whether the query filters on an aggregate already.

A tree that contains neither an aggregate nor a window is returned whole as the where part. Otherwise the method works out whether this node’s children must stay together. The node’s own negation is combined with the negation it was called under, by exclusive or, since a "NOT" outside a "NOT" cancels it; and the children must remain connected, a comment says, to keep the logic, where the node is an "AND" under a negation, an "OR" not under one, or an "XOR". An "OR" cannot be tested half before the grouping and half after, since a row that fails the half in "WHERE" is gone before the half in "HAVING" is tested; and a negated "AND" is, by De Morgan’s law, an "OR" of negations. Where the children must remain connected and the node contains an aggregate and no window, the whole node is the having part; a comment says it is much cheaper to short-circuit than to split the children.

Otherwise the children are divided: a WhereNode among them is split by the same method, under this node’s effective negation, and its three parts go to the three lists; a leaf that contains a window goes to the qualify list, one that contains an aggregate to the having list, and any other to the where list. Then the one refusal, for a node that must remain connected and has a qualify part. Where nothing of the node belongs in "WHERE", or the query does not group, the whole node goes to qualify, to be tested in the statement wrapped around this one; where the query groups and part of the node belongs in "WHERE", the method raises NotImplementedError, for heterogeneous disjunctive predicates against window functions under conditional aggregation. A comment says the refusal is wider than it need be: it would only have to hold for a where part against a multi-valued relation, which could change what is aggregated, but that is hard to tell. Each list that is not empty becomes a node with this node’s connector and negation, made by Node.create, and the three are returned.

tail(Ship.objects.annotate(hands=Count('crew')).filter(hands__gt=1).filter(name='Petrel'))  ->  ('FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") WHERE "fleet_ship"."name" = %s GROUP BY "fleet_ship"."id", "fleet_ship"."name", "fleet_ship"."tonnage", "fleet_ship"."home_id", "fleet_ship"."captain_id" HAVING COUNT("crew_sailor"."id") > %s', ('Petrel', 1))
tail(Ship.objects.annotate(hands=Count('crew')).filter(~Q(hands__gt=1)))  ->  ('FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") GROUP BY "fleet_ship"."id", "fleet_ship"."name", "fleet_ship"."tonnage", "fleet_ship"."home_id", "fleet_ship"."captain_id" HAVING NOT (COUNT("crew_sailor"."id") > %s)', (1,))
tail(Ship.objects.annotate(hands=Count('crew')).filter(~(Q(hands__gt=1) & Q(name='Petrel'))))  ->  ('FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") GROUP BY "fleet_ship"."id", "fleet_ship"."name", "fleet_ship"."tonnage", "fleet_ship"."home_id", "fleet_ship"."captain_id" HAVING NOT (COUNT("crew_sailor"."id") > %s AND "fleet_ship"."name" = %s)', (1, 'Petrel'))
...
said(Ship.objects.annotate(hands=Count('crew')).filter(Q(hands__gt=1) | Q(name='Petrel')).query.where.split_having_qualify())  ->  (None, (AND: (OR: IntegerGreaterThan(Count(Col(crew_sailor, crew.Sailor.id)), 1), Exact(Col(fleet_ship, fleet.Ship.name), 'Petrel'))), None)
said(Ship.objects.annotate(hands=Count('crew')).filter(hands__gt=1, name='Petrel').query.where.split_having_qualify())  ->  ((AND: Exact(Col(fleet_ship, fleet.Ship.name), 'Petrel')), (AND: IntegerGreaterThan(Count(Col(crew_sailor, crew.Sailor.id)), 1)), None)
Ship.objects.annotate(hands=Count('crew')).filter(hands__gt=1).query.where.contains_aggregate, Ship.objects.annotate(hands=Count('crew')).filter(name='Petrel').query.where.contains_aggregate  ->  (True, False)

Two filters, which Query.add_q joins under "AND", are divided: the name is tested in "WHERE" and the count in "HAVING". A negated comparison of the count goes to "HAVING" with its "NOT". A negated "AND" of the two goes whole, and the three lines of answers at the end are the parts themselves: the "OR" of a count and a name is one having node, the "AND" of the same two is a where node of the name and a having node of the count, and the last line is the mark the method reads first, contains_aggregate on the tree, true for a filter on the count and false for one on the name.

The marks the method reads are WhereNode.contains_aggregate and WhereNode.contains_over_clause, each a cached property computed once for a tree by walking its nodes to their leaves and asking each leaf for the attribute of the same name; a NothingNode and an ExtraWhere answer False to both, the second with a comment calling its SQL a black box. A leaf that is a lookup computes the mark from its left side, and from its right side where that is an expression, so that a comparison of an annotation that counts carries the mark although the annotation’s alias is all the filter named (Lookups: Lookup, BuiltinLookup and the standard comparisons).

The having part in as_sql

SQLCompiler.as_sql compiles the having part as it does the where part, with WhereNode.as_sql, and leaves it empty where that raises FullResultSet (The where clause: build_filter, add_q and WhereNode). It writes the clauses in the statement’s order: once the "WHERE" is written, the grouping; and where there is one, a distinct with fields is refused with NotImplementedError, the ordering is replaced, where the query has none, by what the backend’s force_no_ordering returns, and the model’s ordering is dropped as told above; then, where there is a having part, the backend’s force_group_by is written where no grouping was, and "HAVING" with the condition. Both backend methods return an empty list in BaseDatabaseOperations. MySQL’s force_no_ordering returns an ordering by "NULL", which a docstring says keeps MySQL from ordering by the grouped columns of its own accord, and SQLite’s force_group_by returns "GROUP BY TRUE" on a version before 3.39, so that a "HAVING" without a "GROUP BY" is accepted.

The two clauses are seen together without the select list that brought them in the narrowed query of Query.exists, which Aggregates: Aggregate, Count and get_aggregation tells:

Ship.objects.annotate(hands=Count('crew')).filter(hands__gt=1).exists()
...
          SQL SELECT %s AS "a" FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") GROUP BY "fleet_ship"."id", "fleet_ship"."name", "fleet_ship"."tonnage", "fleet_ship"."home_id", "fleet_ship"."captain_id" HAVING COUNT("crew_sailor"."id") > %s LIMIT 1 with params (1, 1)

The statement selects a constant and groups by every column of a ship, the tuple that exists had set_group_by fix before it cleared the select list, and the filter on the count stands in "HAVING". get_aggregation reads the same having part to decide that a query already aggregates, and takes such a query inside a subquery rather than add its own aggregates to a statement that groups (Aggregates: Aggregate, Count and get_aggregation).