[Django] #27627: OneToOneField doesn't allow multiple records with NULL value on Microsoft SQL Server
Django
SQL Server
-----------------------------------------+------------------------
Reporter: Ryan Schave | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version:
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
A OneToOneField with null=True should allow multiple records to be saved
with a NULL value in the field. Consider the following models:
{{{
class Company(models.Model):
name = models.CharField(max_length=30)
class UserProfile(models.Model):
case_manager = models.BooleanField(default=False)
company = models.OneToOneField(Company, models.SET_NULL, blank=True,
null=True)
}}}
With a SQLite or PostgreSQL backend I'm able to save multiple records with
a NULL value:
{{{
up1 = UserProfile.objects.create(case_manager=True)
up2 = UserProfile.objects.create(case_manager=False)
}}}
With Microsoft SQL Server, the 2nd attempt to create a record with a NULL
value results in an integrity error:
django.db.utils.IntegrityError: ('23000', "[23000] [Microsoft][SQL Server
Native Client 11.0][SQL Server]Violation of UNIQUE KEY constraint
'UQ__contacts__3E2672343AF86B58'. Cannot insert duplicate key in object
'dbo.contacts_userprofile'. The duplicate key value is (<NULL>). (2627)
(SQLExecDirectW)")
This post - http://stackoverflow.com/a/767702 describes the problem and
suggests creating a "unique filtered index based on a predicate that
excludes NULLs".
I have confirmed that if I manually remove the constraint created by the
migration in the table schema and create a unique filtered index that
excludes NULLs on the company field I can create multiple records with a
NULL value in the company field. The migration should be adding a filter
to the index that looks something like:
{{{
WHERE ([company_id] IS NOT NULL)
}}}
I am able to duplicate the problem with Django 1.9.10 and 1.10.4.
--
Ticket URL: <https://code.djangoproject.com/ticket/27627>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
SQL Server
Reporter: Ryan Schave | Owner: nobody
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* component: Uncategorized => Database layer (models, ORM)
* type: Uncategorized => Bug
* stage: Unreviewed => Accepted
Comment:
Is it certain that Django must be fixed rather than the MSSQL database
backend that you're using? (which one is it?) In any case, adding a test
to Django's test suite to confirm that all built-in backends behave
properly seems like a good idea. Hopefully database backend that you're
using will also run that test.
--
Ticket URL: <https://code.djangoproject.com/ticket/27627#comment:1>
Django
SQL Server
Reporter: Ryan Schave | Owner: nobody
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Simon Charette):
I'm pretty sure that's a bug in the MSSQL server backend used by the
reporter here.
MSSQL server diverges from the SQL standard by disallowing multiple `NULL`
values in column with a `UNIQUE` index. The backend should make sure to
create conditional indexes excluding `NULL` columns for fields marked
`null=True`.
--
Ticket URL: <https://code.djangoproject.com/ticket/27627#comment:2>
Django
SQL Server
Reporter: Ryan Schave | Owner: nobody
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Ryan Schave):
I can produce a test that passes w/ SQLite and PostgreSQL, but fails with
MS SQL. I assume since MS SQL is not a built-in backend, there is little
that can be done easily on the Django side and my experience w/ MS
suggests they aren't going to address this. I created a custom migration
that removes the constraint added by the initial Django migration and add
a unique index that allows multiple NULLs.
I assume that the code in django/db/migrations works together with the
appropriate backend in django/db/backends. Does Django use the base
backend when a 3rd party database is used?
--
Ticket URL: <https://code.djangoproject.com/ticket/27627#comment:3>
Django
SQL Server
Reporter: Ryan Schave | Owner: nobody
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham):
Most likely -- look at the code of the backend you're using and see if it
subclasses Django's base classes.
--
Ticket URL: <https://code.djangoproject.com/ticket/27627#comment:4>
Django
SQL Server
Reporter: Ryan Schave | Owner: nobody
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Ryan Schave):
It makes sense that the 3rd party backend should handle this. There is
already an open pull request (https://github.com/michiya/django-pyodbc-
azure/pull/43) for django-pyodbc-azure.
Would it be beneficial to submit the test that passes with the supported
backends and fails with the the django-pyodbc-azure backend? This would
at least give visuality into the problem when Django tests are run against
that backend.
--
Ticket URL: <https://code.djangoproject.com/ticket/27627#comment:5>
Django
SQL Server
Reporter: Ryan Schave | Owner: nobody
Component: Database layer | Version:
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* cc: Michael Manfre (added)
* status: new => closed
* resolution: => invalid
Comment:
It looks like removing `supports_nullable_unique_constraints = False` may
already yield a regression test in `tests/one_to_one/tests.py` due to the
usage of the feature flag there. Maybe other backends like `django-mssql`
would want to fix the issue so we could remove that feature?
--
Ticket URL: <https://code.djangoproject.com/ticket/27627#comment:6>