Merge "Fixed compile for mssql dialect"

3 files changed, 45 insertions, 12 deletions

diff --git a/doc/build/changelog/unreleased_13/5751.rst b/doc/build/changelog/unreleased_13/5751.rst
new file mode 100644
index 000000000..44306ceb3
--- /dev/null
+++ b/doc/build/changelog/unreleased_13/5751.rst
@@ -0,0 +1,7 @@
+.. change::
+    :tags: bug, mssql
+    :tickets: 5751
+
+    Fixed bug where a CREATE INDEX statement was rendered incorrectly when
+    both ``mssql-include`` and ``mssql_where`` were specified. Pull request
+    courtesy @Adiorz.
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 9addbf31f..911e1791a 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -2286,18 +2286,6 @@ class MSDDLCompiler(compiler.DDLCompiler):
             ),
         )
 
-        whereclause = index.dialect_options["mssql"]["where"]
-
-        if whereclause is not None:
-            whereclause = coercions.expect(
-                roles.DDLExpressionRole, whereclause
-            )
-
-            where_compiled = self.sql_compiler.process(
-                whereclause, include_table=False, literal_binds=True
-            )
-            text += " WHERE " + where_compiled
-
         # handle other included columns
         if index.dialect_options["mssql"]["include"]:
             inclusions = [
@@ -2311,6 +2299,18 @@ class MSDDLCompiler(compiler.DDLCompiler):
                 [preparer.quote(c.name) for c in inclusions]
             )
 
+        whereclause = index.dialect_options["mssql"]["where"]
+
+        if whereclause is not None:
+            whereclause = coercions.expect(
+                roles.DDLExpressionRole, whereclause
+            )
+
+            where_compiled = self.sql_compiler.process(
+                whereclause, include_table=False, literal_binds=True
+            )
+            text += " WHERE " + where_compiled
+
         return text
 
     def visit_drop_index(self, drop):
diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py
index 568d346f5..8119612e1 100644
--- a/test/dialect/mssql/test_compiler.py
+++ b/test/dialect/mssql/test_compiler.py
@@ -20,6 +20,7 @@ from sqlalchemy import sql
 from sqlalchemy import String
 from sqlalchemy import Table
 from sqlalchemy import testing
+from sqlalchemy import text
 from sqlalchemy import union
 from sqlalchemy import UniqueConstraint
 from sqlalchemy import update
@@ -1281,6 +1282,31 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             schema.CreateIndex(idx), "CREATE INDEX foo ON test (x) INCLUDE (y)"
         )
 
+    def test_index_include_where(self):
+        metadata = MetaData()
+        tbl = Table(
+            "test",
+            metadata,
+            Column("x", Integer),
+            Column("y", Integer),
+            Column("z", Integer),
+        )
+        idx = Index(
+            "foo", tbl.c.x, mssql_include=[tbl.c.y], mssql_where=tbl.c.y > 1
+        )
+        self.assert_compile(
+            schema.CreateIndex(idx),
+            "CREATE INDEX foo ON test (x) INCLUDE (y) WHERE y > 1",
+        )
+
+        idx = Index(
+            "foo", tbl.c.x, mssql_include=[tbl.c.y], mssql_where=text("y > 1")
+        )
+        self.assert_compile(
+            schema.CreateIndex(idx),
+            "CREATE INDEX foo ON test (x) INCLUDE (y) WHERE y > 1",
+        )
+
     def test_try_cast(self):
         metadata = MetaData()
         t1 = Table("t1", metadata, Column("id", Integer, primary_key=True))