[Sqlalchemy-tickets] Issue #4092: Type problem with mssql.TIMESTAMP (zzzeek/sqlalchemy)

[Dave H. <iss...@bi...>]

New issue 4092: Type problem with mssql.TIMESTAMP
https://bitbucket.org/zzzeek/sqlalchemy/issues/4092/type-problem-with-mssqltimestamp

Dave Hirschfeld:

As detailed in [GH#382](https://github.com/zzzeek/sqlalchemy/pull/382) the `dialects.mssql.TIMESTAMP` class is a direct import of the `sa.sql.sqltypes.TIMESTAMP` class into the `mssql` namespace.

The fact that the `mssql.TIMESTAMP` class is the same as the `sqltypes.TIMESTAMP` class causes problems in a 3rd party library, [odo](https://github.com/blaze/odo) which operates by mapping sqlalchemy types to the corresponding python/numpy types.

The reason this causes problem is that unlike the ANSI SQL `TIMESTAMP` the `mssql.TIMESTAMP` type *doesn't* represent a datetime object but is instead just a binary type which cannot be interpreted or converted to a datetime object.

This problem doesn't affect sqlalchemy itself because sqlalchemy  emits the correct `TIMESTAMP` DDL and then simply passes through the results from the underlying driver.

With `type(sqltypes.TIMESTAMP) == type(mssql.TIMESTAMP)` being true there is no way for odo to distinguish the two types. Because one represents a Python datetime object and the other a byte string they need to be able to be distinguished and handled differently in odo.

A sufficient condition for odo to correctly handle MSSQL TIMESTAMP types is that the `mssql.TIMESTAMP` class does not inherit from the `sqltypes.TIMESTAMP` class - e.g.

```python
def test_mssql_timestamp_is_not_timestamp():
    """The MSSQL TIMESTAMP type does *not* represent a datetime value
    so should not inherit from the `sqltypes.TIMESTAMP` class

    :ref: https://msdn.microsoft.com/en-us/library/ms182776%28v=SQL.90%29.aspx
    """
    from sqlalchemy.sql import sqltypes
    from sqlalchemy.dialects import mssql
    assert not issubclass(mssql.TIMESTAMP, sqltypes.TIMESTAMP)
```