Filter non-integer characters from pyodbc SQL Server version

Adjusted the SQL Server version detection for pyodbc to only allow for
numeric tokens, filtering out non-integers, since the dialect does tuple-
numeric comparisons with this value.  This is normally true for all known
SQL Server / pyodbc drivers in any case.

Change-Id: I4ab18a07e19231091b5e877ba1fccd5eda72a992
Fixes: #4227

4 files changed, 47 insertions, 4 deletions

diff --git a/doc/build/changelog/unreleased_12/4227.rst b/doc/build/changelog/unreleased_12/4227.rst
new file mode 100644
index 000000000..992497d5a
--- /dev/null
+++ b/doc/build/changelog/unreleased_12/4227.rst
@@ -0,0 +1,9 @@
+.. change::
+    :tags: bug, mssql
+    :tickets: 4227
+    :versions: 1.3.0b1
+
+    Adjusted the SQL Server version detection for pyodbc to only allow for
+    numeric tokens, filtering out non-integers, since the dialect does tuple-
+    numeric comparisons with this value.  This is normally true for all known
+    SQL Server / pyodbc drivers in any case.
diff --git a/lib/sqlalchemy/connectors/pyodbc.py b/lib/sqlalchemy/connectors/pyodbc.py
index d8c3fcec4..41ba89de6 100644
--- a/lib/sqlalchemy/connectors/pyodbc.py
+++ b/lib/sqlalchemy/connectors/pyodbc.py
@@ -134,7 +134,7 @@ class PyODBCConnector(Connector):
             vers += (m.group(2),)
         return vers
 
-    def _get_server_version_info(self, connection):
+    def _get_server_version_info(self, connection, allow_chars=True):
         # NOTE: this function is not reliable, particularly when
         # freetds is in use.   Implement database-specific server version
         # queries.
@@ -145,7 +145,8 @@ class PyODBCConnector(Connector):
             try:
                 version.append(int(n))
             except ValueError:
-                version.append(n)
+                if allow_chars:
+                    version.append(n)
         return tuple(version)
 
     def set_isolation_level(self, connection, level):
diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index 14e8ae838..36bcc49b3 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -276,6 +276,8 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
 
     def _get_server_version_info(self, connection):
         try:
+            # "Version of the instance of SQL Server, in the form
+            # of 'major.minor.build.revision'"
             raw = connection.scalar(
                 "SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)")
         except exc.DBAPIError:
@@ -283,7 +285,7 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
             # 2008.  Before we had the VARCHAR cast above, pyodbc would also
             # fail on this query.
             return super(MSDialect_pyodbc, self).\
-                _get_server_version_info(connection)
+                _get_server_version_info(connection, allow_chars=False)
         else:
             version = []
             r = re.compile(r'[.\-]')
@@ -291,7 +293,7 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
                 try:
                     version.append(int(n))
                 except ValueError:
-                    version.append(n)
+                    pass
             return tuple(version)
 
     def is_disconnect(self, e, connection, cursor):
diff --git a/test/dialect/mssql/test_engine.py b/test/dialect/mssql/test_engine.py
index b69e62572..db54381cf 100644
--- a/test/dialect/mssql/test_engine.py
+++ b/test/dialect/mssql/test_engine.py
@@ -271,3 +271,34 @@ class VersionDetectionTest(fixtures.TestBase):
                 dialect._get_server_version_info(conn),
                 (11, 0, 9216, 62)
             )
+
+    def test_pyodbc_version_productversion(self):
+        dialect = pyodbc.MSDialect_pyodbc()
+
+        conn = Mock(scalar=Mock(return_value="11.0.9216.62"))
+        eq_(
+            dialect._get_server_version_info(conn),
+            (11, 0, 9216, 62)
+        )
+
+    def test_pyodbc_version_fallback(self):
+        dialect = pyodbc.MSDialect_pyodbc()
+        dialect.dbapi = Mock()
+
+        for vers, expected in [
+            ("11.0.9216.62", (11, 0, 9216, 62)),
+            ("notsqlserver.11.foo.0.9216.BAR.62", (11, 0, 9216, 62)),
+            ("Not SQL Server Version 10.5", (5, ))
+        ]:
+            conn = Mock(
+                scalar=Mock(
+                    side_effect=exc.DBAPIError("stmt", "params", None)),
+                connection=Mock(
+                    getinfo=Mock(return_value=vers)
+                )
+            )
+
+            eq_(
+                dialect._get_server_version_info(conn),
+                expected
+            )
\ No newline at end of file