Interpret empty LIMIT, expression LIMIT correctly

Fixed issue in MSSQL dialect where an expression-based OFFSET value in a
SELECT would be rejected, even though the dialect can render this
expression inside of a ROW NUMBER-oriented LIMIT/OFFSET construct.

Fixes: #4973
Change-Id: I040d34f781791c4ed5a727e1b8fb98c68ddd0622

4 files changed, 36 insertions, 2 deletions

diff --git a/doc/build/changelog/unreleased_13/4973.rst b/doc/build/changelog/unreleased_13/4973.rst
new file mode 100644
index 000000000..06498e976
--- /dev/null
+++ b/doc/build/changelog/unreleased_13/4973.rst
@@ -0,0 +1,8 @@
+.. change::
+    :tags: bug, mssql
+    :tickets: 4973
+
+    Fixed issue in MSSQL dialect where an expression-based OFFSET value in a
+    SELECT would be rejected, even though the dialect can render this
+    expression inside of a ROW NUMBER-oriented LIMIT/OFFSET construct.
+
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 5d4de4a33..261ebe5c2 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -1612,7 +1612,10 @@ class MSSQLCompiler(compiler.SQLCompiler):
         if select._distinct:
             s += "DISTINCT "
 
-        if select._simple_int_limit and not select._offset:
+        if select._simple_int_limit and (
+            select._offset_clause is None
+            or (select._simple_int_offset and select._offset == 0)
+        ):
             # ODBC drivers and possibly others
             # don't support bind params in the SELECT clause on SQL Server.
             # so have to use literal here.
diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py
index 6b3244c30..b0f58563a 100644
--- a/test/dialect/mssql/test_compiler.py
+++ b/test/dialect/mssql/test_compiler.py
@@ -8,6 +8,7 @@ from sqlalchemy import Index
 from sqlalchemy import insert
 from sqlalchemy import Integer
 from sqlalchemy import literal
+from sqlalchemy import literal_column
 from sqlalchemy import MetaData
 from sqlalchemy import PrimaryKeyConstraint
 from sqlalchemy import schema
@@ -784,6 +785,28 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             eq_(len(c._result_columns), 2)
             assert t.c.x in set(c._create_result_map()["x"][1])
 
+    def test_simple_limit_expression_offset_using_window(self):
+        t = table("t", column("x", Integer), column("y", Integer))
+
+        s = (
+            select([t])
+            .where(t.c.x == 5)
+            .order_by(t.c.y)
+            .limit(10)
+            .offset(literal_column("20"))
+        )
+
+        self.assert_compile(
+            s,
+            "SELECT anon_1.x, anon_1.y "
+            "FROM (SELECT t.x AS x, t.y AS y, "
+            "ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn "
+            "FROM t "
+            "WHERE t.x = :x_1) AS anon_1 "
+            "WHERE mssql_rn > 20 AND mssql_rn <= :param_1 + 20",
+            checkparams={"param_1": 10, "x_1": 5},
+        )
+
     def test_limit_offset_using_window(self):
         t = table("t", column("x", Integer), column("y", Integer))
 
diff --git a/test/requirements.py b/test/requirements.py
index fd093f270..471e68f4c 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -627,7 +627,7 @@ class DefaultRequirements(SuiteRequirements):
     def sql_expression_limit_offset(self):
         return (
             fails_if(
-                ["mysql", "mssql"],
+                ["mysql"],
                 "Target backend can't accommodate full expressions in "
                 "OFFSET or LIMIT",
             )