[Django] #34195: Duplicate Records created when specifying None as a target in a custom ManyToManyField with sqlite3

[Django]

#34195: Duplicate Records created when specifying None as a target in a custom
ManyToManyField with sqlite3
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
Credentive |
Type: Bug | Status: new
Component: Database | Version: 4.1
layer (models, ORM) | Keywords: ManyToManyField,
Severity: Normal | null
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Reading through the documents, the only reference to null in
ManyToManyFields is the following:

**null has no effect since there is no way to require a relationship at
the database level.**

When allowing null values as a target in an M2M field, you are allowed to
assign "None" as the target of a model. However, if you assign "None"
multiple times, you will get multiple DB records.

Understanding that M2M fields are implemented as join tables, I can see
why this may be happening, but I think this behavior should be documented
at least. Note from the example that adding non-null targets multiple
times produces the expected result (it works, but no extra rows are
created)

**Model code (<ProjectRoot>/policypublisher/models.py):**

{{{
class Section(models.Model):
uuid = models.UUIDField(
primary_key=True,
unique=True,
editable=False,
default=uuid.uuid4,
help_text="A unique identifier for the Section",
)
<...>
version = models.ManyToManyField(Version,
related_name="sections_in_version")
<...>
under = models.ManyToManyField("self", through="SectionHierarchy",
symmetrical=False, related_name="over")

class SectionHierarchy(models.Model):
under_id = models.ForeignKey(Section, on_delete=models.CASCADE,
related_name="+")
over_id = models.ForeignKey(Section, null=True,
on_delete=models.CASCADE, related_name="+")
version = models.ForeignKey(Version, on_delete=models.CASCADE)

class Meta:
constraints = [
models.UniqueConstraint(fields=["under_id", "over_id",
"version"], name="unique_sec_under_per_version")
]

}}}

**$ python manage.py shell**

{{{
Python 3.9.15 (main, Nov 15 2022, 09:54:34)
[GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from policypublisher.models import *
>>> section = Section.objects.first()
>>> version = section.version.first()
>>> SectionHierarchy.objects.count()
0
>>> section.under.add(None, through_defaults={"version": version})
>>> section.under.add(None, through_defaults={"version": version})
>>> SectionHierarchy.objects.count()
2
>>> section2 = Section.objects.last()
>>> section.under.add(section2, through_defaults={"version":version})
>>> SectionHierarchy.objects.count()
3
>>> section.under.add(section2, through_defaults={"version":version})
>>> SectionHierarchy.objects.count()
3
>>>

}}}

**$ python manage.py dbshell**

{{{
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite> select * from policypublisher_sectionhierarchy;
55||055744fc79e24f89a9384d79894eee7a|be8225c85fab4a0ba8ea879a3d992abe
56||055744fc79e24f89a9384d79894eee7a|be8225c85fab4a0ba8ea879a3d992abe

}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34195>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.