turn off use_insertmanyvalues for SQL Server

we will keep trying to find workarounds, however this
patch is the "turn it off" patch

Due to a critical bug identified in SQL Server, the SQLAlchemy
"insertmanyvalues" feature which allows fast INSERT of many rows while also
supporting RETURNING unfortunately needs to be disabled for SQL Server. SQL
Server is apparently unable to guarantee that the order of rows inserted
matches the order in which they are sent back by OUTPUT inserted when
table-valued rows are used with INSERT in conjunction with OUTPUT inserted.
We are trying to see if Microsoft is able to confirm this undocumented
behavior however there is no known workaround, other than it's not safe to
use table-valued expressions with OUTPUT inserted for now.

Fixes: #9603
Change-Id: I4b932fb8774390bbdf4e870a1f6cfe9a78c4b105

2 files changed, 22 insertions, 26 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 31745d600..808fdf16f 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -251,18 +251,11 @@ The process for fetching this value has several variants:
 
     INSERT INTO t (x) OUTPUT inserted.id VALUES (?)
 
-  As of SQLAlchemy 2.0, the :ref:`engine_insertmanyvalues` feature is also
-  used by default to optimize many-row INSERT statements; for SQL Server
-  the feature takes place for both RETURNING and-non RETURNING
-  INSERT statements.
-
-* The value of :paramref:`_sa.create_engine.insertmanyvalues_page_size`
-  defaults to 1000, however the ultimate page size for a particular INSERT
-  statement may be limited further, based on an observed limit of
-  2100 bound parameters for a single statement in SQL Server.
-  The page size may also be modified on a per-engine
-  or per-statement basis; see the section
-  :ref:`engine_insertmanyvalues_page_size` for details.
+  .. note::  SQLAlchemy 2.0 introduced the :ref:`engine_insertmanyvalues`
+     feature for SQL Server, which is used by default to optimize many-row
+     INSERT statements; however as of SQLAlchemy 2.0.9 this feature had
+     to be turned off for SQL Server as the database does not support
+     deterministic RETURNING of INSERT rows for a multi-row INSERT statement.
 
 * When RETURNING is not available or has been disabled via
   ``implicit_returning=False``, either the ``scope_identity()`` function or
@@ -3017,7 +3010,8 @@ class MSDialect(default.DefaultDialect):
     # may be changed at server inspection time for older SQL server versions
     supports_multivalues_insert = True
 
-    use_insertmanyvalues = True
+    # disabled due to #9603
+    use_insertmanyvalues = False
 
     # note pyodbc will set this to False if fast_executemany is set,
     # as of SQLAlchemy 2.0.9
@@ -3085,6 +3079,14 @@ class MSDialect(default.DefaultDialect):
 
         super().__init__(**opts)
 
+        if self.use_insertmanyvalues:
+            raise exc.ArgumentError(
+                "The use_insertmanyvalues feature on SQL Server is currently "
+                "not safe to use, as returned result rows may be returned in "
+                "random order.  Ensure use_insertmanyvalues is left at its "
+                "default of False (this setting changed in SQLAlchemy 2.0.9)"
+            )
+
         self._json_serializer = json_serializer
         self._json_deserializer = json_deserializer
 
diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index f9f2479ad..08c6bc48f 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -290,25 +290,19 @@ Pyodbc have been resolved as of SQLAlchemy 2.0.5. See the notes at
 Fast Executemany Mode
 ---------------------
 
-.. note:: SQLAlchemy 2.0 now includes an equivalent "fast executemany"
-   handler for INSERT statements that is more robust than the PyODBC feature
-   (but is not quite as performant particularly for very large datasets);
-   the feature is called :ref:`insertmanyvalues <engine_insertmanyvalues>`
-   and is enabled for all INSERT statements by default.
-   SQLAlchemy's feature integrates with the PyODBC ``setinputsizes()`` method
-   which allows for more accurate specification of datatypes, and additionally
-   uses a dynamically sized, batched approach that scales to any number of
-   columns and/or rows.
-
-   The SQL Server ``fast_executemany`` parameter may be used at the same time
-   as ``insertmanyvalues`` is enabled; however, the parameter will not be used
-   for INSERT statements that include RETURNING.
+  .. note::  SQLAlchemy 2.0 introduced the :ref:`engine_insertmanyvalues`
+     feature for SQL Server, which is used by default to optimize many-row
+     INSERT statements; however as of SQLAlchemy 2.0.9 this feature had
+     to be turned off for SQL Server as the database does not support
+     deterministic RETURNING of INSERT rows for a multi-row INSERT statement.
 
 .. versionchanged:: 2.0.9 - ``fast_executemany`` executions will be used
    for INSERT statements that don't include RETURNING, when
    ``fast_executemany`` is set. Previously, ``use_insertmanyvalues`` would
    cause ``fast_executemany`` to not be used in most cases.
 
+   ``use_insertmanyvalues`` is disabled for SQL Server overall as of 2.0.9.
+
 The PyODBC driver includes support for a "fast executemany" mode of execution
 which greatly reduces round trips for a DBAPI ``executemany()`` call when using
 Microsoft ODBC drivers, for **limited size batches that fit in memory**.  The