coercion of UNIQUEIDENTIFIER / uuid values from binary to string only works with fetchone(), not fetchall() or fetchmany()

[zzzeek]

There seems to be some built in coercion occurring when fetching UNIQUEIDENTIFIER results, which is fine, however it seems to take place inconsistently, only when the rows were fetched using fetchone(). When using fetchall() or fetchmany(), the values seem to be bytes using little-endian.

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("""DROP TABLE IF EXISTS t1""")

cursor.execute(
    """
    CREATE TABLE t1 (
        id UNIQUEIDENTIFIER NULL,
        data VARCHAR(50) NULL
    )
    """
)

for i, uuid_value in enumerate(
    [
        uuid.UUID("110e2700-9d34-44e9-ba0e-bd74401a54a4"),
        uuid.UUID("68be2224-543e-4868-914a-a65f06047593"),
    ]
):

    cursor.execute(
        "INSERT INTO t1 (id, data) VALUES (?, ?)",
        (str(uuid_value), f"value {i}"),
    )

    cursor.execute("SELECT id FROM t1 WHERE data = ?", (f"value {i}",))
    row = cursor.fetchone()
    print(
        f"For fetchone() of UUID {uuid_value}, "
        f"we get from the driver: {row[0]}"
    )

    cursor.execute("SELECT id FROM t1 WHERE data = ?", (f"value {i}",))
    row = cursor.fetchall()
    print(
        f"For fetchall() of UUID {uuid_value}, "
        f"we get from the driver: {row[0][0]}; "
        f"this translates to {uuid.UUID(bytes_le=row[0][0])}"
    )

    cursor.execute("SELECT id FROM t1 WHERE data = ?", (f"value {i}",))
    row = cursor.fetchmany()
    print(
        f"For fetchmany() of UUID {uuid_value}, "
        f"we get from the driver: {row[0][0]}; "
        f"this translates to {uuid.UUID(bytes_le=row[0][0])}"
    )

output we get shows that we get a binary value for fetchall(), fetchmany(), which apparently converts back using UUID(bytes_le):

For fetchone() of UUID 110e2700-9d34-44e9-ba0e-bd74401a54a4, we get from the driver: 110e2700-9d34-44e9-ba0e-bd74401a54a4
For fetchall() of UUID 110e2700-9d34-44e9-ba0e-bd74401a54a4, we get from the driver: b"\x00'\x0e\x114\x9d\xe9D\xba\x0e\xbdt@\x1aT\xa4"; this translates to 110e2700-9d34-44e9-ba0e-bd74401a54a4
For fetchmany() of UUID 110e2700-9d34-44e9-ba0e-bd74401a54a4, we get from the driver: b"\x00'\x0e\x114\x9d\xe9D\xba\x0e\xbdt@\x1aT\xa4"; this translates to 110e2700-9d34-44e9-ba0e-bd74401a54a4

For fetchone() of UUID 68be2224-543e-4868-914a-a65f06047593, we get from the driver: 68be2224-543e-4868-914a-a65f06047593
For fetchall() of UUID 68be2224-543e-4868-914a-a65f06047593, we get from the driver: b'$"\xbeh>ThH\x91J\xa6_\x06\x04u\x93'; this translates to 68be2224-543e-4868-914a-a65f06047593
For fetchmany() of UUID 68be2224-543e-4868-914a-a65f06047593, we get from the driver: b'$"\xbeh>ThH\x91J\xa6_\x06\x04u\x93'; this translates to 68be2224-543e-4868-914a-a65f06047593

Expected behavior

fetchone(), fetchmany(), fetchall() should each return rows in the identical format for a particular value. string is probably best. (many drivers also deliver native Python UUID objects, or offer an option to do so. I dont need that but just something to keep in mind).

Further technical details

Python version: 3.13.5
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 running a Fedora 40 container

Additional context

Note this is for development of a SQLAlchemy driver for mssql-python. I'm impressed that at least several hundred tests are running without issue so far. New drivers typically have at least half a dozen bugs though I am getting a lot of passing so far, so that's good.

[gargsaumya]

Hi @zzzeek
Thanks a lot for trying out our driver and for reporting this issue. The detailed context is really helpful.

This is not the intended behavior, and we’ll work on making the return type consistent across all fetch methods.
We’ll update this issue once a fix is in. Thanks again for testing this so thoroughly!

[gargsaumya]

Hi @zzzeek,
Wanted to let you know that the fix for this issue has been added and the related pull request (#236 ) has been merged. You can expect this to be included in the next release, which is planned for this Friday.

Once the release is out, please give it a try and let us know if it works as expected. Your feedback is really valuable.
Thanks again for contributing to improving mssql-python, and please keep raising any issues or suggestions you encounter. :)

[gargsaumya]

@zzzeek - We’ve included this fix in our 0.12.0 release. Please give it a try and let us know if it works as expected. Thank you for your support!

[zzzeek]

Hi -

I guess that part works, it looks like the driver is producing UUID() objects now, so that's fine, but now there is a much more strange issue I can illustrate below but it's so out there it probably warrants a new issue.

I can't insert a string value that happens to be a 32 character hexadecimal string into a CHAR(32) column. No UNIQUEIDENTIFIER present.

It looks very much like the driver is scanning literally all character strings and guessing if they intend to be UUID values? That's not something the driver should ever do.

See test below which uses only CHAR(32) and VARCHAR(50), yet the error message is talking about UNIQUEIDENTIFIER. only if the string is a hex string. Change one letter in the hex string to be not hex, then the error goes away. If the driver is scanning all values that's going to be a big performance issue at the very least.

from mssql_python import connect

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

cursor = conn.cursor()

cursor.execute("""DROP TABLE IF EXISTS data""")

cursor.execute(
    """
CREATE TABLE data (
	id1 CHAR(32) NOT NULL,
	data VARCHAR(50) NULL,
	PRIMARY KEY (id1)
)
    """
)

# works
cursor.execute(
    """INSERT INTO data (id1, data) VALUES (?, ?)""",
    ("some string", "d0"),
)

# works
cursor.execute(
    """INSERT INTO data (id1, data) VALUES (?, ?)""",
    ("hellothisisastringthatsexactly32", "d0"),
)

# suddenly fails, as though every bound value is attempting to be detected
# as a uniqueidentifier, very strange
# "Insufficient result space to convert uniqueidentifier value to char."
# if I convert just one character to something outside of hex, then it works.
cursor.execute(
    """INSERT INTO data (id1, data) VALUES (?, ?)""",
    ("f9dddb7bfca2432a859781876b061fbd", "d0"),
)

output:

Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/test3.py", line 36, in <module>
    cursor.execute(
    ~~~~~~~~~~~~~~^
        """INSERT INTO data (id1, data) VALUES (?, ?)""",
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        ("f9dddb7bfca2432a859781876b061fbd", "d0"),
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/classic/.venv3/lib64/python3.13/site-packages/mssql_python/cursor.py", line 995, in execute
    check_error(ddbc_sql_const.SQL_HANDLE_STMT.value, self.hstmt, ret)
    ~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/classic/.venv3/lib64/python3.13/site-packages/mssql_python/helpers.py", line 78, in check_error
    raise_exception(error_info.sqlState, error_info.ddbcErrorMsg)
    ~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/classic/.venv3/lib64/python3.13/site-packages/mssql_python/exceptions.py", line 651, in raise_exception
    raise exception_class
mssql_python.exceptions.ProgrammingError: Driver Error: Syntax error or access violation; DDBC Error: [Microsoft][SQL Server]Insufficient result space to convert uniqueidentifier value to char.
terminate called after throwing an instance of 'pybind11::error_already_set'
  what():  ImportError: sys.meta_path is None, Python is likely shutting down
Aborted (core dumped)

the driver also core dumps when the program ends without closing the connection, that should probably be fixed also

[gargsaumya]

Thanks for following up.
The earlier UUID issue has been addressed, and we’ll review the behavior you’ve highlighted, as the driver should not be scanning general string parameters in that way.

Regarding the core dump when the connection isn’t explicitly closed, we have a work item tracking it and a fix is in progress.
Really appreciate you continuing to test and report these edge cases, they help us make the driver more robust for everyone.
I’ll update here once the fix for the UUID behavior is in.