bulk_update fails when a column is to be updated to None (NULL)

[danifus]

Django: 2.2.17

The error I get when attempting to bulk update a column to None is:

...
  File "/.../site-packages/django/db/models/query.py", line 525, in bulk_update
    self.filter(pk__in=pks).update(**update_kwargs)
  File "/.../site-packages/django/db/models/query.py", line 741, in update
    rows = query.get_compiler(self.db).execute_sql(CURSOR)
...
django.db.utils.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]At least one of the result expressions in a CASE specification must be an expression other than the NULL constant. (8133) (SQLExecDirectW)')

[saboyutaka]

same problem.

[giovannigenna]

Same problem here. Have you found a workaround ?

[OskarPersson]

Please provide a complete example

[danifus]

objs = [
   Entry.objects.create(headline='Entry 1'),
]
objs[0].headline = None
Entry.objects.bulk_update(objs, ['headline'])

Results in the error from the first comment:

...
  File "/.../site-packages/django/db/models/query.py", line 525, in bulk_update
    self.filter(pk__in=pks).update(**update_kwargs)
  File "/.../site-packages/django/db/models/query.py", line 741, in update
    rows = query.get_compiler(self.db).execute_sql(CURSOR)
...
django.db.utils.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]At least one of the result expressions in a CASE specification must be an expression other than the NULL constant. (8133) (SQLExecDirectW)')

[OskarPersson]

This seems to be a limitation of SQL Server. I've added a draft PR with a failing test if anyone wants to attempt a reasonable workaround

[yigor]

I don't know if it helps anyone, but bulk_update functionality from https://github.com/aykut/django-bulk-update seems to work fine with django-mssql-backend

[techknowfile]

Same problem
Using the old django-bulk-update library as suggested by @yigor does seem to work as a workaround, except it's slower, and seems to silently fail/hang/crash if batch_size is around 500+

[JGoepelEmn]

At least with Microsoft SQL Server, it looks like the statement cannot be prepared, because a data type cannot be inferred from NULL.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d7fc7b5d-ff34-48de-a5a1-490b101f91f4/none-of-the-result-expressions-in-a-case-specification-can-be-null?forum=transactsql