Invalid in select list because (...) not in group by clause (but it is)
martstr opened this issue · 2 comments
Software versions
- Django: 4.1.4
- mssql-django: 1.2
- python: 3.9.10
- SQL Server: 14.0.3456.2
- OS: Windows Server 2019
Problem
The instance runs on an airgapped system, and I therefore ask for forgiveness for just describing a skeleton of the issue and not having all the newest versions. I'll try to build a minimal example if this is insufficient to identify the issue.
I have Django code building a query that looks like this:
SELECT
[employee].[employee_num] AS [eno],
COALESCE([employee].[exam_year], ([employee].[year_of_birth] + 25)) AS [eyear],
MAX(CASE WHEN [...]) AS [a1],
[...]
FROM [...] INNER JOIN [employee] ON [...]
WHERE [...]
GROUP BY
[employee].[employee_num],
COALESCE([employee].[exam_year], ([employee].[year_of_birth] + 25)),Expected and actual behaviour
When running against the test SQLite database, it returns a queryset consisting of the employee number, year of exam (or year of birth + 25), plus a number of columns from a related table.
When using SQL Server, I instead get the error message
[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'employee.exam_year' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (8120) (SQLExecDirectW)
Interestingly, the exact same query works just just fine when run directly from Microsoft SQL Server Management Studio (v. 18.11). This leads me to believe that the error message is raised unecessarily. However, whether the error originates from here or SQL Driver is unknown to me.
I've now done what I should have tried originally: Workarounds.
(1) Installing OBCD Driver 18. Did not work. At all.
(2) Simply adding 'exam_year' and 'year_of_birth' to QuerySet.values(). I don't need them, but now the syntax checker will find them in both the select and group by sections. Worked like a charm.
So, I think there's still something (non-critical) here to improve at Microsoft's side, but for anyone coming here for a solution to this exact problem: (2) above works.