bigint identity column with large negative seed fails to reflect #10504
Description
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