search

michiya / django-pyodbc-azure

Django backend for Microsoft SQL Server and Azure SQL Database using pyodbc
https://pypi.python.org/pypi/django-pyodbc-azure
BSD 3-Clause "New" or "Revised" License
321 stars 140 forks source link

Can't .count() a query set with subquery annotations. #183

Open hagen93 opened 5 years ago

hagen93 commented 5 years ago

I'm using this db driver to interface with a SQL Server 2012, and one of the queries we recently introduced into our application was to order a query set by some related data by means of subquery annotations, which is then paginated by Django REST Framework.

During this pagination DRF calls .count() on the queryset which generates a query along the lines of

SELECT COUNT_BIG(*)
FROM
(
  SELECT id, (the annotated subquery)
  FROM table
  GROUP BY id, (the annotated subquery)
) subquery

which is basically the original query we used to order our data, but with the ordering stripped and wrapped in a counting query.

In cases where the original query is annotated Django additionally does a GROUP BY (as seen above, see Django's Query.get_aggregation() function for implementation) on the query for reasons unbeknownst to me; but as GROUP BY with data from a subquery is not supported in SQL Server I believe that this driver should refuse to add that part (or just the subquery part of the clause) to the query and possibly work around the problem in another way, because now this instead causes execution to stop with the exception ProgrammingError at / ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. (144) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)').

How can this be fixed? I am willing to contribute a fix if I can get some directions on how to approach the problem 😄.

OskarPersson commented 4 years ago

I am getting the same exact error for the same reasons, have you made any progress here @hagen93?

I'm looking into fixing this in https://github.com/ESSolutions/django-mssql-backend

hagen93 commented 4 years ago

No unfortunately not. Instead we had to drop support for MSSQL in our app because of this bug. I would definitely be interested in a fix if you can get it working.

OskarPersson commented 4 years ago

This is (hopefully) fixed in https://github.com/ESSolutions/django-mssql-backend/pull/15