order_by on relationship not working for default ordering

[jwaschkau]

Django: 3.2.12
mssql-django: 1.1.2
SQL Server: 15.0.4153.1

When using

class Meta:
  ordering = ('my_relationship__my_value')

i get following error:

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 13 for
    SQL Server][SQL Server]The ORDER BY clause is invalid in views,
    inline functions, derived tables, subqueries, and common table
    expressions, unless TOP, OFFSET or FOR XML is also specified.
    (1033) (SQLExecDirectW)')

Seems to be a similar problem to mssql/compiler.py

I will provide more information on this issue at a later date if needed.

[absci]

Could you provide some more info about your models.py? I couldn't reproduce it with a simple CharField and DateTimeField for ordering.

[jwaschkau]

Could you provide some more info about your models.py? I couldn't reproduce it with a simple CharField and DateTimeField for ordering.

The issue seems to be more complicated and only happens when a model with default ordering is used to filter another model. I will try to create a sample project to replicate the behaviour.

[jwaschkau]

@absci After taking some time creating an example project the problem seems to be using select_for_update() for a query later used in a filter function. I uploaded a sample project to https://github.com/jwaschkau/django-mssql-issue109

The problem is the select_for_update() in following row https://github.com/jwaschkau/django-mssql-issue109/blob/53535de93ce5a88b2cbac508fe491f0b692dde63/issue109/tests.py#L24

The problem is not the ordering on relationships!

[jwaschkau]

The workaround i am currently using is resetting the ordering by calling order_by()

[absci]

This issue is fixed in dev, I'll be included in the next release.

[nawtybean]

Hi All, I just came to this issue where I'm connecting my Django Project to a Synapse DW (xxxxxx.sql.azuresynapse.net) with Meta: Managed = False in my models just to look up some dimensions. The problem is Synapse DW doesn't support OFFSET. These dims are pretty small. I commented out the following and now it works.

if not (do_offset or do_limit):
result.append("OFFSET 0 ROWS")