SQLServer (using pymssql) doesn't support ORDER BY <column> NULLS LAST; or ORDER BY <column> NULLS FIRST; but the SQLAlchemy adapter defaults to column.nullslast() on sorts sqlalchemyfdw.py#L259. This causes any query with an ORDER BY clause or any query that generates one ie SELECT DISTINCT <column> FROM <table> to fail.
When executing SELECT DISTINCT type FROM properties; I get the following error:
ERROR: Error in python: ProgrammingError
DETAIL: (pymssql.ProgrammingError) (102, "Incorrect syntax near 'NULLS'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n") [SQL: 'SELECT [PROPERTIES].type \nFROM [PROPERTIES] ORDER BY [PROPERTIES].type NULLS LAST']
Explain:
Unique (cost=20.00..10000250000.00 rows=1 width=100)
-> Foreign Scan on properties (cost=20.00..10000000000.00 rows=100000000 width=100)
Multicorn: SELECT "PROPERTIES".type
FROM "PROPERTIES" ORDER BY "PROPERTIES".type NULLS LAST
I'm assuming it's just falling through to the else case so maybe simply adding an explicit check for sortkey.nulls_last is sufficient but if users explicitly specify NULLS (LAST || FIRST) in the query it would still be an issue. I can certainly submit a PR with that as well as code to check the engine and ignore nulls_last/nulls_first altogether when the engine is pymssql. If you've got a preferred approach I can work that and submit a PR.
SQLServer (using pymssql) doesn't support
ORDER BY <column> NULLS LAST;orORDER BY <column> NULLS FIRST;but the SQLAlchemy adapter defaults tocolumn.nullslast()on sorts sqlalchemyfdw.py#L259. This causes any query with anORDER BYclause or any query that generates one ieSELECT DISTINCT <column> FROM <table>to fail.When executing
SELECT DISTINCT type FROM properties;I get the following error:Explain:
I'm assuming it's just falling through to the else case so maybe simply adding an explicit check for
sortkey.nulls_lastis sufficient but if users explicitly specifyNULLS (LAST || FIRST)in the query it would still be an issue. I can certainly submit a PR with that as well as code to check the engine and ignore nulls_last/nulls_first altogether when the engine is pymssql. If you've got a preferred approach I can work that and submit a PR.