Hello @JoelFeiner,
Your first problem is definitely a misuse of the ORM. Please refer to the documentation about values and annotate when dealing with aggregate functions.
The values method must be called before calling annotate with an aggregate function to produce an explicit grouping
Person.objects.values("first_name").annotate(
latest_dob=Max("date_of_birth")
)
As for the window function it’s not clear to me what you are trying to accomplish. The fact that the group by is performed against the selected column seems like a bug which I’m unsure how to solve but I’m surprised to see that SQLite doesn’t require that last_name be part of GROUP BY as other backends like Postgres and MySQL do.
That seems like a deviation of the standard and I’m not surprised the ORM doesn’t support it.
The reason why the GROUP BY bug against window function is so hard to solve is because the explicit grouping interface through values(*group_by).annotate() usage has always meant explicit group by values members but in the case of window functions this cannot be done for obvious reasons but I’m not convinced adding an implicit group by last_name would have either.
I think that at the very least the ORM should error out loudly when trying to explicitly group by window functions to denote that it doesn’t support it. The only way I can see to add support for it would be to have the explicit grouping logic performed by values(*group_by).annotate() ignore any group_by members that are window function but raise a warning or error out if the expressions referenced by them are not explicitly part of group_by (e.g. in your case last_name).