MSSQL dialect generates wrong syntax for CAST

[prudnikov]

I generate CTE based on the Table metadata and data in JSON.

engine = create_engine(
        f"mssql+pyodbc://{username}:{password}@{hostname}/{database_name}?"
        "driver=ODBC+Driver+17+for+SQL+Server",
        echo=False,
    )
table_metadata = sa.MetaData(schema="dbo")
destination_table = sa.Table(table_name, table_metadata, autoload_with=engine)

def make_cols(table: sa.Table, row: dict[str, typing.Any]):
    columns = []
    for col in table.columns:
        assert isinstance(col, sa.Column)

        value = sa.literal(row.get(col.key.lower()))
        columns.append(sa.cast(value, col.type).label(str(col.key)))
        # Not sure if next line will produce the same results as previous one
        # columns.append(col.cast(value).label(str(col.key)))

    return columns

stmts = [
    sa.select(
        *make_cols(destination_table, obj)
    )
    for obj in json_objects
]
data_cte = sa.union_all(*stmts).cte(name="MyTable_CTE")

This produces the following query

SELECT Cast(:param_1 AS INTEGER)                                          AS [SrcCModuleId], 
       CAST(:param_2 AS VARCHAR(9) COLLATE SQL_Latin1_General_CP1_CI_AS)  AS [ModuleNumber], 
       CAST(:param_3 AS VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS) AS [ModuleName], 
       CAST(:param_4 AS DATETIME)                                         AS [ModifiedDateTime] 

which is invalid MS SQL Server query. It should be

SELECT Cast(:param_1 AS INTEGER)                                          AS [Id],
       CAST(:param_2 AS VARCHAR(9)) COLLATE SQL_Latin1_General_CP1_CI_AS  AS [ModuleNumber],
       CAST(:param_3 AS VARCHAR(50)) COLLATE SQL_Latin1_General_CP1_CI_AS AS [ModuleName],
       CAST(:param_4 AS DATETIME)                                         AS [ModifiedDateTime]

Am I doing something wrong or it can be considered as a bug?

[prudnikov]

May be there is a workaround that tells SA not to add COLLATE at all? I need to make this work ASAP, any help appreciated. Thanks.

[zzzeek]

yes make a function that fixes the cast:

def fix_cast(value, type):
    if isinstance(type, String) and type.collation:
        return cast(value, String(type.length)).collate(type.collation)
    else:
        return cast(value, type)

[prudnikov]

It worked, thanks a lot. You saved my day :)

[CaselIT]

@zzzeek is this a bug in the mssql that should be tracked by an issue?

[zzzeek]

probably and I'd like to confirm other dbs dont have similar issues, for some reason we thought COLLATE goes inside the CAST expression so we should see what other DBs expect

[gordthompson]

My repro code:

tbl = sa.Table("disc_9914", sa.MetaData(), autoload_with=engine)
qry = sa.select(sa.cast(tbl.c.txt, tbl.c.txt.type).label("txt_cast"))
engine.echo = True
with engine.begin() as conn:
    results = conn.execute(qry)

mssql

SELECT CAST(disc_9914.txt AS VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS) AS txt_cast
FROM disc_9914

Incorrect syntax near the keyword 'COLLATE'. (156)

mysql

SELECT CAST(disc_9914.txt AS CHAR(50) COLLATE utf8mb4_unicode_ci) AS txt_cast
FROM disc_9914

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE utf8mb4_unicode_ci) AS txt_cast \nFROM disc_9914' at line 1)

oracle

SELECT CAST(disc_9914.txt AS VARCHAR(50 CHAR)) AS txt_cast

no error

postgresql

SELECT CAST(disc_9914.txt AS VARCHAR(50)) AS txt_cast
FROM disc_9914

no error

sqlite

SELECT CAST(disc_9914.txt AS VARCHAR(50)) AS txt_cast
FROM disc_9914

no error