[MSSQL-Django] Decimal in money range bound as string, causing arithmetic overflow on numeric columns

[bewithgaurav]

Describe the bug

mssql_python binds a Python Decimal whose value falls within the SQL Server money range (about -214,748 to +214,748) as a string (SQL_VARCHAR / SQL_C_CHAR), based on the value alone, ignoring the target column type. SQL Server then does a server-side varchar to numeric conversion at execution. When the value does not fit the target numeric/decimal column, that conversion overflows and raises, instead of the value simply not matching. pyodbc binds the same value as SQL_NUMERIC client-side, so the comparison just returns no rows.

Exception message:

DataError: ... Arithmetic overflow error converting varchar to data type numeric.

The heuristic is in mssql_python/cursor.py (_map_sql_type, line 646). A Decimal within SMALLMONEY_MIN..SMALLMONEY_MAX (line 797) or MONEY_MIN..MONEY_MAX returns a SQL_VARCHAR string bind. Only values outside those ranges reach the SQL_NUMERIC path (line 819) that binds with self-derived precision.

To reproduce

import mssql_python
from decimal import Decimal

conn = mssql_python.connect("Server=localhost;Database=tempdb;UID=sa;PWD=...;Encrypt=no;TrustServerCertificate=yes;")
conn.autocommit = True
cur = conn.cursor()

cur.execute("CREATE TABLE #t (v numeric(5,2))")   # max value 999.99
cur.execute("INSERT INTO #t VALUES (?)", [Decimal("12.34")])

# Out-of-range value that sits inside the money range (~ +/-214,748):
cur.execute("SELECT COUNT(*) FROM #t WHERE v = ?", [Decimal("12345.6789")])
# mssql_python.exceptions.DataError:
#   Arithmetic overflow error converting varchar to data type numeric.

# A value OUTSIDE the money range works (binds as SQL_NUMERIC):
cur.execute("SELECT COUNT(*) FROM #t WHERE v = ?", [Decimal("300000.00")])
print(cur.fetchone()[0])   # 0, no error

For contrast, pyodbc returns 0 with no error for the same in-range value.

Expected behavior

A Decimal comparison against a numeric/decimal column returns the correct result (no match) without raising, matching pyodbc. Binding should not depend on whether the value happens to fall in the money range.

Further technical details

Python version: 3.13 (reproduces 3.10 through 3.14)
SQL Server version: SQL Server 2022
Operating system: driver-side, OS-independent
mssql-python version: 1.14.0

Additional context

Found while validating mssql-python for mssql-django. Accounts for 2 of the 9 Django integration-suite failures: model_fields.test_decimalfield.test_lookup_decimal_larger_than_max_digits and test_lookup_really_big_value.

Suggested fix: bind Decimal as SQL_NUMERIC with self-derived precision and scale regardless of range (the _get_numeric_data path already computes these). The money-range varchar shortcut trades correctness for a narrow type choice and misfires whenever a value overlaps the money range but targets a different numeric column.

[github-actions]

Hi Gaurav Sharma (@bewithgaurav), thank you for opening this issue!

Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

If you have additional information to share, please feel free to update the issue.

Thank you for your patience!