UniqueTogetherValidator doesn't enforce uniqueness of NULL values on databases that DO enforce them #8409
Description
Checklist
- Raised initially as discussion #... (No. Thought an issue would help other people in the future)
- This cannot be dealt with as a third party library. (We prefer new functionality to be in the form of third party libraries where possible.)
- I have reduced the issue to the simplest possible case.
With #2452 code was introduced that changed the behavior of UniqueTogetherValidator to ignore NULL values.
The reasoning being that Postgresql (and supposedly the SQL standard) considers each instance of NULL unique from every other instance (last paragraph of https://www.postgresql.org/docs/13/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS). Many RDBMs follow this but Oracle DB doesn't.
Oracle DB will enforce multi column unique constraints properly even when some of the values are NULL. But DRF will skip the uniqueness check and a constraint violation will be returned by Oracle. Traceback at the end of the ticket.
I'm not too sure how such variation between DB backends should be treated. Skipping the None check in UniqueTogetherValidator seems to work as expected. I guess a setting that controls how NULL values are treated could work but it seems a bit out of place.
Thoughts?
Traceback with Oracle DB:
File "/.../lib/python3.9/site-packages/rest_framework/mixins.py", line 19, in create
self.perform_create(serializer)
File "...api.py", line 585, in perform_create
serializer.save()
File "/.../lib/python3.9/site-packages/rest_framework/serializers.py", line 205, in save
self.instance = self.create(validated_data)
File "/.../lib/python3.9/site-packages/rest_framework/serializers.py", line 939, in create
instance = ModelClass._default_manager.create(**validated_data)
File "/.../lib/python3.9/site-packages/django/db/models/manager.py", line 85, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "/.../lib/python3.9/site-packages/django/db/models/query.py", line 453, in create
obj.save(force_insert=True, using=self.db)
File "/.../lib/python3.9/site-packages/django/db/models/base.py", line 739, in save
self.save_base(using=using, force_insert=force_insert,
File "/.../lib/python3.9/site-packages/django/db/models/base.py", line 776, in save_base
updated = self._save_table(
File "/.../lib/python3.9/site-packages/django/db/models/base.py", line 881, in _save_table
results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
File "/.../lib/python3.9/site-packages/django/db/models/base.py", line 919, in _do_insert
return manager._insert(
File "/.../lib/python3.9/site-packages/django/db/models/manager.py", line 85, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "/.../lib/python3.9/site-packages/django/db/models/query.py", line 1270, in _insert
return query.get_compiler(using=using).execute_sql(returning_fields)
File "/.../lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
cursor.execute(sql, params)
File "/.../lib/python3.9/site-packages/django/db/backends/utils.py", line 98, in execute
return super().execute(sql, params)
File "/.../lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/.../lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/.../lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/.../lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/.../lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/.../lib/python3.9/site-packages/django/db/backends/oracle/base.py", line 523, in execute
return self.cursor.execute(query, self._param_generator(params))
django.db.utils.IntegrityError: ORA-00001: unique constraint (SOME.TABLE__F9A18610_U) violated