Microsoft SQL Server miscompilation for ROWS PRECEDING/FOLLOWING

[suhas-dc]

On Microsoft SQL Server running a query with an OVER clause containing a row range fails. For example,

query = sa.select(sa.func.avg(col_one).over(order_by=[col_two], rows=(-5,1)))
connection.execute(query)

fails with ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW)").

The sqlalchemy query object is compiled into some thing similar to

SELECT avg(anon_1.[col_one]) OVER (ORDER BY anon_1.[col_two] ASC ROWS BETWEEN 
? PRECEDING AND ? FOLLOWING)
FROM dbo.table AS anon_1'''

And, pyodbc does not seem to support bind parameters for PRECEDING and FOLLOWING since running the above query as textual SQL will also fail with the same error. Compiling the query with literal_binds True substitutes the bind parameters giving a query pyodbc will run.

EDIT: I faced this issue on sqlalchemy 1.4 but it seems to me that the issue may also exist on sqlalchemy 2.0 after looking through the visit_over definition.

[zzzeek]

if it's just the parameters that need to be stated literally, rather than as binds, that can be fixed without too much problem, I would need a runnable example to work with

[zzzeek]

like this. can you test?

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 872f8584d..c4e570060 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -1988,6 +1988,10 @@ class MSSQLCompiler(compiler.SQLCompiler):
         self.tablealiases = {}
         super().__init__(*args, **kwargs)
 
+    def _format_frame_clause(self, range_, **kw):
+        kw['literal_binds'] = True
+        return super()._format_frame_clause(range_, **kw)
+
     def _with_legacy_schema_aliasing(fn):
         def decorate(self, *arg, **kw):
             if self.dialect.legacy_schema_aliasing:

[suhas-dc]

Yes, setting literal_binds in _format_frame_clause works.

I was working around this by overriding visit_over and setting literal_binds which I guess would also set literal_binds on _format_frame_clause.

    def visit_over(self, over, **kwargs):
        modified_kwargs = kwargs.copy()
        modified_kwargs["literal_binds"] = True
        return super().visit_over(over, **modified_kwargs)

[zzzeek]

OK i got it, will make an issue