search

microsoft / mssql-django

The Microsoft Django backend for SQL Server provides a connectivity layer for Django on SQL Server or Azure SQL DB.
Other
324 stars 108 forks source link

SQL Server 2005 compatibility issue- migration failure with filtered index queries #380

Open pstumps opened 2 months ago

pstumps commented 2 months ago

Migration fails when running migrate on SQL Server 2005 with error code pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'WHERE'. (156) (SQLExecDirectW)") It seems to be a direct result of when queries that attempt to filter by index are executed similar to this: CREATE UNIQUE INDEX ... ON [table] (col1, col2, ...) WHERE col1 IS NOT NULL AND col2 IS NOT NULL

I believe this error occurs because the ability to filter by index was not introduced until SQL Server 2008. Removing the 'where' statement allows migrate to progress, however, I'm not sure what the consequences of doing so are.

Any suggestions on how to handle this would be appreciated.

absci commented 2 months ago

Is it possible to upgrade to a newer version of SQL Server?

bobince commented 1 month ago

The WHERE is a workaround for a non-ANSI-compliant behaviour in mssql, where NULL is counted as a distinct value for UNIQUE constraints. Consequently if you had a null=True, unique=True field, you could only have a single row with a NULL in that column.

This is redundant if the fields are non-nullable, or if you don't expect there to be more than one NULL. In that case you could get away with removing the WHERE, although it's possible this might confuse introspection for migrations that change the indexes or PKs in the future. (But this is already a bit broken, so I don't know how much worse that'd actually be.)

The workaround was introduced back under the previous fork of the backend IIRC, so you'd have to go quite a long way back to get a version that just works on 2005.