NULL value cast as NUMERIC crashes under fetchone()

[zzzeek]

Describe the bug

fetching a NULL value (directly or from a table column) while CASTing to NUMERIC causes the type coercion to fail under fetchone(). It does not fail under fetchall() or fetchmany() but I suspect this is also a bug due to #241.

Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/test3.py", line 23, in <module>
    row = cursor.fetchone()
  File "/home/classic/.venv3/lib64/python3.13/site-packages/mssql_python/cursor.py", line 959, in fetchone
    raise e
  File "/home/classic/.venv3/lib64/python3.13/site-packages/mssql_python/cursor.py", line 939, in fetchone
    ret = ddbc_bindings.DDBCSQLFetchOne(self.hstmt, row_data)
ValueError: basic_string::_M_create

from mssql_python import connect
import uuid

conn_str = "UID=scott;PWD=tiger^5HHH;Server=mssql2022;Database=test;Encrypt=No"
conn = connect(conn_str)

cursor = conn.cursor()

cursor.execute("SELECT schema_name()")
schema_name = cursor.fetchone()[0]

cursor.execute(
    "SELECT CAST(57483735 AS NUMERIC(38,0))"
)
# works
row = cursor.fetchone()


cursor.execute(
    "SELECT CAST(NULL AS NUMERIC(38,0))"
)
# ValueError: basic_string::_M_create
row = cursor.fetchone()

Expected behavior

should return None

Further technical details

Python version: 3.13
SQL Server version: (e.g. SQL Server 2022) SQL Server 2022
Operating system: (e.g. Windows Server 2022, Ubuntu 24.04, macOS 14.7.1, Docker container)
Fedora 40

Additional context
We use this cast in SQLAlchemy in one of our table reflection queries that SELECTS from sys.identity_columns using a LEFT OUTER JOIN where most of the column values come back as NULL as it's an outer join.

[gargsaumya]

Thank you @zzzeek for taking the time to report this issue. The detailed steps and context you’ve provided are very helpful. I’ll review this further and investigate the behavior.
Thanks again for trying our driver, and please keep raising any issues you encounter.

[bewithgaurav]

Hey @zzzeek -
Actually, this issue existed with v0.10.0 of the driver, which was resolved in #188 and released with 0.11.0
The NULL cast is working fine - tested in Win/MacOS/Linux/Dockers, output:

cursor = conn.cursor()
cursor.execute(
    "SELECT CAST(57483735 AS NUMERIC(38,0))"
)
row = cursor.fetchone()
print(row)
# Output: (57483735)

cursor.execute(
    "SELECT CAST(NULL AS NUMERIC(38,0))"
)
row = cursor.fetchone()
print(row)
# Output: (None)

Could you please check if the mssql-python version you've is 0.10.0? if not please let us know.
If yes, then requesting you to upgrade the driver to the latest version using pip install --upgrade mssql-python and let us know if you're facing the same issue. Thanks for raising this, it's really nice to see discussions on mssql-python support in SQLAlchemy!

[zzzeek]

hi -

it looks like release 0.11.0 was released on 9/19 and the change you mention was merged on 9/18, all after I reported the issue on 9/17. so yes that fixes the issue.

I would recommend adding a __version__ or version attribute to the top level of the module like this:

>>> import pyodbc
>>> pyodbc.version
'5.2.0'

then I would strongly recommend putting most of your project metadata into a pep-621 pyproject.toml file and using the "dynamic" scheme to locate this version number (e.g. dont store the same version number in two places). setuptools is fully compatible with pyproject.toml so you can still keep your C extension directives in setup.py.

[bewithgaurav]

Glad it worked with the new release. Have taken note of the version attribute and pyproject.toml to keep project metadata, have created #257 to incorporate the same with the "dynamic" way of versioning. Thanks for the suggestions!
Closing this issue for now, please feel free to raise any other points or issues.