Not all database backends support grouping by a column number
|Reported by:||manfre||Owned by:||manfre|
|Component:||Database layer (models, ORM)||Version:||master|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
"SELECT ... GROUP BY 1" is not valid for all databases. This can happen in ORM generated queries when Django moves anything found in the ORDER BY over to the GROUP BY. E.g. "SELECT ... ORDER BY 1".
There are two ways of proceeding:
1. Don't promote the positional value from ORDER BY to GROUP BY, if the database doesn't support it.
It's possible that not including the positional values from the GROUP BY will cause different backends (those that support positional columns and those that do not) to return different results for certain queries. I cannot think of any example queries and the only usage of positional columns that I've noticed in the test suite are due to "ORDER BY 1". This change allows django-mssql to pass many aggregation and aggregation_regres tests that it previously failed with an error.
This change is trivial and contained to a few lines in SQLCompiler.get_grouping().
2. Look up the appropriate entry from the select by its position (indexed from 1, not 0) and include the select + params in the GROUP BY, instead of the position number.
This will not generate the correct query in all situations, but it should generate a valid query for the backend. Depending on the specifics of the query and how columns are front loaded to the select, it's quite possible that the intended ORDER BY position no longer lines up with the desired position when attempting to do the look up for GROUP BY.