bigint identity column with large negative seed fails to reflect

[gordthompson]

Describe the bug

An mssql table with a bigint identity column that has a large negative seed will cause reflection to fail.

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

2.0.23.dev0

DBAPI (i.e. the database driver)

pyodbc

Database Vendor and Major Version

SQL Server 2019

Python Version

3.10

Operating system

(any)

To Reproduce

import sqlalchemy as sa

engine = sa.create_engine(
    sa.URL.create(
        "mssql+pyodbc",
        username="scott",
        password="tiger^5HHH",
        host="192.168.0.199",
        database="test",
        query={"driver": "ODBC Driver 17 for SQL Server"},
    )
)
table_name = "so77319809"
with engine.begin() as conn:
    conn.exec_driver_sql(f"DROP TABLE IF EXISTS {table_name}")
    conn.exec_driver_sql(
        f"CREATE TABLE {table_name} ("
        "id bigint identity(-9223372036854775808, 1) primary key, "
        "txt nvarchar(5)"
        ")"
    )

tbl = sa.Table(table_name, sa.MetaData(), autoload_with=engine)

Error

Traceback (most recent call last):
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
pyodbc.DataError: ('22003', '[22003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Arithmetic overflow error converting bigint to data type numeric. (8115) (SQLExecDirectW)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Gord\git\sqla-gerrit\gord_test\so77319809.py", line 24, in <module>
    tbl = sa.Table(table_name, sa.MetaData(), autoload_with=engine)
  File "<string>", line 2, in __new__
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\util\deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\sql\schema.py", line 436, in __new__
    return cls._new(*args, **kw)
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\sql\schema.py", line 490, in _new
    with util.safe_reraise():
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\util\langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\sql\schema.py", line 486, in _new
    table.__init__(name, metadata, *args, _no_init=False, **kw)
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\sql\schema.py", line 866, in __init__
    self._autoload(
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\sql\schema.py", line 898, in _autoload
    conn_insp.reflect_table(
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\reflection.py", line 1526, in reflect_table
    _reflect_info = self._get_reflection_info(
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\reflection.py", line 2006, in _get_reflection_info
    columns=run(
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\reflection.py", line 1992, in run
    res = meth(filter_names=_fn, **kw)
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\reflection.py", line 921, in get_multi_columns
    table_col_defs = dict(
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\default.py", line 1099, in _default_multi_reflect
    single_tbl_method(
  File "<string>", line 2, in get_columns
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\reflection.py", line 97, in cache
    ret = fn(self, con, *args, **kw)
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\dialects\mssql\base.py", line 2898, in wrap
    return _switch_db(
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\dialects\mssql\base.py", line 2922, in _switch_db
    return fn(*arg, **kw)
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\dialects\mssql\base.py", line 3711, in get_columns
    c = connection.execution_options(future_result=True).execute(s)
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\base.py", line 1416, in execute
    return meth(
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\sql\elements.py", line 516, in _execute_on_connection
    return connection._execute_clauseelement(
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\base.py", line 1639, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\base.py", line 1848, in _execute_context
    return self._exec_single_context(
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\base.py", line 1988, in _exec_single_context
    self._handle_dbapi_exception(
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\base.py", line 2343, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "C:\Users\Gord\git\sqla-gerrit\lib\sqlalchemy\engine\default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (pyodbc.DataError) ('22003', '[22003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Arithmetic overflow error converting bigint to data type numeric. (8115) (SQLExecDirectW)')
[SQL: SELECT [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_NAME], [INFORMATION_SCHEMA].[COLUMNS].[DATA_TYPE], [INFORMATION_SCHEMA].[COLUMNS].[IS_NULLABLE], [INFORMATION_SCHEMA].[COLUMNS].[CHARACTER_MAXIMUM_LENGTH], [INFORMATION_SCHEMA].[COLUMNS].[NUMERIC_PRECISION], [INFORMATION_SCHEMA].[COLUMNS].[NUMERIC_SCALE], [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_DEFAULT], [INFORMATION_SCHEMA].[COLUMNS].[COLLATION_NAME], sys.computed_columns.definition, sys.computed_columns.is_persisted, sys.identity_columns.is_identity, CAST(sys.identity_columns.seed_value AS NUMERIC) AS seed_value, CAST(sys.identity_columns.increment_value AS NUMERIC) AS increment_value, CAST(sys.extended_properties.value AS NVARCHAR(max)) AS comment 
FROM [INFORMATION_SCHEMA].[COLUMNS] LEFT OUTER JOIN sys.computed_columns ON sys.computed_columns.object_id = object_id([INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA] + CAST(? AS NVARCHAR(max)) + [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME]) AND sys.computed_columns.name = ([INFORMATION_SCHEMA].[COLUMNS].[COLUMN_NAME] COLLATE DATABASE_DEFAULT) LEFT OUTER JOIN sys.identity_columns ON sys.identity_columns.object_id = object_id([INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA] + CAST(? AS NVARCHAR(max)) + [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME]) AND sys.identity_columns.name = ([INFORMATION_SCHEMA].[COLUMNS].[COLUMN_NAME] COLLATE DATABASE_DEFAULT) LEFT OUTER JOIN sys.extended_properties ON sys.extended_properties.class = ? AND sys.extended_properties.major_id = object_id([INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA] + CAST(? AS NVARCHAR(max)) + [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME]) AND sys.extended_properties.minor_id = [INFORMATION_SCHEMA].[COLUMNS].[ORDINAL_POSITION] AND sys.extended_properties.name = CAST(? AS NVARCHAR(max)) 
WHERE [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) ORDER BY [INFORMATION_SCHEMA].[COLUMNS].[ORDINAL_POSITION]]
[parameters: ('.', '.', 1, '.', 'MS_Description', 'so77319809', 'dbo')]
(Background on this error at: https://sqlalche.me/e/20/9h9h)

Additional context

No response

[CaselIT]

how strange. the value has a cast to numeric CAST(sys.identity_columns.seed_value AS NUMERIC).
Is numeric not unlimited precision by default in mssql?

[gordthompson]

Is numeric not unlimited precision by default in mssql?

No, it isn't. The default precision is 18, whereas the maximum precision is 38.

[CaselIT]

ok, then I guess the solution is to just change the precision/type we cast to

[soha-namnabat]

I believe a numeric(19,0) will easily cast to bigint in mssql

[CaselIT]

the issue I think is that identity can be applied on a numeric column that may be larger than a bigint. Using the max precision available on numeric should be greater than any other integer column available in mssql, so it should solve the issue.

[sqla-tester]

Gord Thompson has proposed a fix for this issue in the main branch:

Fix identity column reflection failure https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4909

[sqla-tester]

Gord Thompson has proposed a fix for this issue in the rel_1_4 branch:

Fix identity column reflection failure https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4911