mssql dialect not anticipating existing_type + type sent at the same time

[ALMP-SallaH]

Hello,

I have the following table in my MSSQL database:

Table('NewClients', metadata,
    Column('id', Integer, nullable=False, primary_key=True),
    Column('name', String(255), nullable=False, primary_key=True),
    Column('email', String(255), nullable=True),
    Column('phone', String(255), nullable=True),
    Column('address', String(255), nullable=True),
    Column('zip_code', String(255), nullable=True),
    Column('country', String(255), nullable=True),
    Column('date_of_birth', Date, nullable=True),
    schema='store')

I wish to alter the data type of column id to VARCHAR(15) NOT NULL. I expected the following commands to produce the same result:

op.alter_column(table_name='NewClients', column_name='id', nullable=False, type_=sa.String(15), schema='store')
op.alter_column(table_name='NewClients', column_name='id', nullable=False, type_=sa.String(15), existing_type=sa.Integer, schema='store')

The first one works perfectly

op.drop_constraint('pk_NewClients', table_name='NewClients', schema='store')
op.alter_column(table_name='NewClients', column_name='id', nullable=False, type_=sa.String(15), schema='store')
op.create_primary_key('pk_NewClients', table_name='NewClients', columns=['id', 'name'], schema='store')

However, if I add the existing_type parameter to alter_column operation, things become confusing...

op.drop_constraint('pk_NewClients', table_name='NewClients', schema='store')
op.alter_column(table_name='NewClients', column_name='id', nullable=False, type_=sa.String(15), existing_type=sa.Integer, schema='store')
op.create_primary_key('pk_NewClients', table_name='NewClients', columns=['id', 'name'], schema='store')

This produces

INFO [sqlalchemy.engine.base.Engine] ()
2021-03-04 08:55:50,868 INFO sqlalchemy.engine.base.Engine ALTER TABLE store.[NewClients] ALTER COLUMN id INTEGER NOT NULL
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE store.[NewClients] ALTER COLUMN id INTEGER NOT NULL
2021-03-04 08:55:50,869 INFO sqlalchemy.engine.base.Engine ()
INFO [sqlalchemy.engine.base.Engine] ()
2021-03-04 08:55:50,873 INFO sqlalchemy.engine.base.Engine ALTER TABLE store.[NewClients] ALTER COLUMN id VARCHAR(15)
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE store.[NewClients] ALTER COLUMN id VARCHAR(15)
2021-03-04 08:55:50,873 INFO sqlalchemy.engine.base.Engine ()

and as a result the column id has now type VARCHAR(15) NULL and the creation of primary key fails (because primary key columns cannot be NULL).

Is this the intended behavior? If it is, would it be possible to add some sort of notice or warning regarding the use of parameter existing_type in alter_column with MSSQL to documentation?

Versions.

• OS: Win 10
• Python: 3.8.3
• Alembic: 1.5.5
• SQLAlchemy: 1.3.23
• Database: SQL Server 14.0.3281.6 (2017, Linux)
• DBAPI: pyodbc

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

Adjust mssql accommodate existing_type and type https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/2615

[zzzeek]

that's just a simple bug, MSSQL can alter the type + nullability in one statement, which is distinct from the default logic that can only set them in separate statements, and the logic it uses to detect this simply didn't anticipate existing_type being present. thanks for the bug report.

[zzzeek]

I can release now unless you want to first try out github master to see if it resolves your issue.

[ALMP-SallaH]

Tested and works great. Thank you very much.