#34207: Incorrect SQL query when adding a ManyToMany related object with a
"through" table prevents adding a new relationship if the new relationship
is identical except for a different value for "through_defaults"
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
Credentive |
Type: Bug | Status: new
Component: Database | Version: 4.1
layer (models, ORM) | Keywords: ManyToManyField
Severity: Normal | through
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I cannot add a related element to an object that is related via a
ManyToMany relationship with a through field. I believe I have traced the
error to a bug in the SQL query generated.
Some Code extracts to set the stage:
models.py:
{{{
class Version(models.Model):
uuid = models.UUIDField(primary_key=True, unique=True, editable=False,
default=uuid.uuid4, )
<...>
class Section(models.Model):
uuid = models.UUIDField(primary_key=True, unique=True, editable=False,
default=uuid.uuid4, )
<...>
class Statement(models.Model):
uuid = models.UUIDField(primary_key=True, unique=True, editable=False,
default=uuid.uuid4, )
<...>
section = models.ManyToManyField(
Section, through="StatementLocation", symmetrical=False,
related_name="statements_in_section"
)
class StatementLocation(models.Model):
section = models.ForeignKey(Section, on_delete=models.CASCADE)
statement = models.ForeignKey(Statement, on_delete=models.CASCADE)
sl_version = models.ForeignKey(Version, on_delete=models.CASCADE)
class Meta:
constraints = [
models.UniqueConstraint(
fields=["section", "statement", "sl_version"],
name="unique_statement_location_per_version"
),
models.UniqueConstraint(fields=["statement", "sl_version"],
name="statement_appears_once_per_version"),
]
}}}
The idea behind the model is that we are representing a document that has
statements in sections. Statements can be moved around in different
versions, so the relationship between a statement and a section, called
"statement location", must be tied to a version.
I have a function under the Version model called "copy". The "copy"
function produces a copy of the version and recreates the existing
relationships.
models.Version (extract):
{{{
def copy(self, name: str, public: bool) -> "Version":
# Make a copy of a published version and return a reference to the
copy
# Create the new version object in the Database
new_version = Version(
name=name,
state="DRAFT",
public=public,
obsoletes=self,
authority=self.authority,
)
new_version.save()
return new_version
<...>
for statement in self.version_data.statements:
<...>
sect = statement.section.filter(version=self).get()
statement.section.add(sect, through_defaults={"sl_version":
new_version})
<...>
}}}
When executing the function, I noticed that the new "StatementLocation"
objects were not being created. For example:
{{{
>>> Version.objects.all()
<QuerySet [<Version: Document - 2.2>]>
>>> version = Version.objects.get()
>>> StatementLocation.objects.count()
89
>>> StatementLocation.objects.filter(sl_version=version).count()
89
>>> new_verson = version.copy(name="2.3", public=True)
>>> new_verson
<Version: Document - 2.3>
>>> StatementLocation.objects.count()
89
>>> StatementLocation.objects.filter(sl_version=new_verson).count()
0
>>>
}}}
If I try a simplified version, with SQL query logging enabled, I get the
following:
{{{
# Create New Version
>>> new_version = Version(name="2.3", state="DRAFT", public=True,
obsoletes=version, authority=version.authority)
# Save It
>>> new_version.save()
(0.002) SELECT "policypublisher_version"."uuid",
"policypublisher_version"."name", "policypublisher_version"."state",
"policypublisher_version"."public",
"policypublisher_version"."published_date",
"policypublisher_version"."effective_date",
"policypublisher_version"."obsoletes_id",
"policypublisher_version"."authority_id" FROM "policypublisher_version"
WHERE "policypublisher_version"."uuid" =
'bcbb218ebdc5435b9e5caf879b55cd24' LIMIT 21;
args=('bcbb218ebdc5435b9e5caf879b55cd24',); alias=default
(0.014) INSERT INTO "policypublisher_version" ("uuid", "name", "state",
"public", "published_date", "effective_date", "obsoletes_id",
"authority_id") VALUES ('bcbb218ebdc5435b9e5caf879b55cd24', '2.3',
'DRAFT', 1, NULL, NULL, 'be8225c85fab4a0ba8ea879a3d992abe',
'833c1b9b30964f3a90b045e2a1254c1b');
args=('bcbb218ebdc5435b9e5caf879b55cd24', '2.3', 'DRAFT', True, None,
None, 'be8225c85fab4a0ba8ea879a3d992abe',
'833c1b9b30964f3a90b045e2a1254c1b'); alias=default
# Get a random statement
>>> statement=Statement.objects.first()
(0.003) SELECT "policypublisher_statement"."uuid",
"policypublisher_statement"."text", "policypublisher_statement"."type",
"policypublisher_statement"."public",
"policypublisher_statement"."obsoletes_id" FROM
"policypublisher_statement" ORDER BY "policypublisher_statement"."uuid"
ASC LIMIT 1; args=(); alias=default
# Make sure we got one
>>> statement
<Statement: Rules for interpreting the pivFASC-N name>
# Add the new version to the statement's version list
>>> statement.version.add(new_version)
(0.000) BEGIN; args=None; alias=default
(0.006) INSERT OR IGNORE INTO "policypublisher_statement_version"
("statement_id", "version_id") VALUES ('0509afa269604028818dc1f53b8f93d2',
'bcbb218ebdc5435b9e5caf879b55cd24');
args=('0509afa269604028818dc1f53b8f93d2',
'bcbb218ebdc5435b9e5caf879b55cd24'); alias=default
# get the section for the statement under the old version
>>> sect=statement.section.filter(version=version).get()
(0.001) SELECT "policypublisher_section"."uuid",
"policypublisher_section"."name", "policypublisher_section"."public" FROM
"policypublisher_section" INNER JOIN "policypublisher_statementlocation"
ON ("policypublisher_section"."uuid" =
"policypublisher_statementlocation"."section_id") INNER JOIN
"policypublisher_section_version" ON ("policypublisher_section"."uuid" =
"policypublisher_section_version"."section_id") WHERE
("policypublisher_statementlocation"."statement_id" =
'0509afa269604028818dc1f53b8f93d2' AND
"policypublisher_section_version"."version_id" =
'be8225c85fab4a0ba8ea879a3d992abe') LIMIT 21;
args=('0509afa269604028818dc1f53b8f93d2',
'be8225c85fab4a0ba8ea879a3d992abe'); alias=default
# Make Sure we got one
>>> sect
<Section: Rules for Interpreting Various Name Forms>
# Add a new section via the through field # NOTE NO INSERT!
>>> statement.section.add(sect,
through_defaults={"sl_version":new_version})
(0.000) BEGIN; args=None; alias=default
(0.002) SELECT "policypublisher_statementlocation"."section_id" FROM
"policypublisher_statementlocation" WHERE
("policypublisher_statementlocation"."section_id" IN
('1cc10c2e5bba4d238d5fabbfdfd72d83') AND
"policypublisher_statementlocation"."statement_id" =
'0509afa269604028818dc1f53b8f93d2');
args=('1cc10c2e5bba4d238d5fabbfdfd72d83',
'0509afa269604028818dc1f53b8f93d2'); alias=default
# No new object was created
>>> StatementLocation.objects.filter(sl_version=new_version).count()
(0.003) SELECT COUNT(*) AS "__count" FROM
"policypublisher_statementlocation" WHERE
"policypublisher_statementlocation"."sl_version_id" =
'bcbb218ebdc5435b9e5caf879b55cd24';
args=('bcbb218ebdc5435b9e5caf879b55cd24',); alias=default
0
}}}
On the second to last statement, I believe Django is checking to see
whether there is an existing M2M relationship, but it is not adding the
version_id to the query, so it thinks the relationship already exists!
Note that I can manually create the relationship, so this is not a
constraints problem:
{{{
>>> new_sl = StatementLocation(section=sect, statement=statement,
sl_version=new_version)
>>> new_sl.save()
(0.027) INSERT INTO "policypublisher_statementlocation" ("section_id",
"statement_id", "sl_version_id") VALUES
('1cc10c2e5bba4d238d5fabbfdfd72d83', '0509afa269604028818dc1f53b8f93d2',
'bcbb218ebdc5435b9e5caf879b55cd24');
args=['1cc10c2e5bba4d238d5fabbfdfd72d83',
'0509afa269604028818dc1f53b8f93d2', 'bcbb218ebdc5435b9e5caf879b55cd24'];
alias=default
>>> StatementLocation.objects.filter(sl_version=new_version).count()
(0.001) SELECT COUNT(*) AS "__count" FROM
"policypublisher_statementlocation" WHERE
"policypublisher_statementlocation"."sl_version_id" =
'bcbb218ebdc5435b9e5caf879b55cd24';
args=('bcbb218ebdc5435b9e5caf879b55cd24',); alias=default
1
>>>
}}}
The FIX:
When a ManyToMany relationship is created with a "through" table, and
through_defaults are provided, that should be part of the search query
that django uses to determine if the relationship already exists.
e.g.
SELECT "policypublisher_statementlocation"."section_id" FROM
"policypublisher_statementlocation" WHERE
("policypublisher_statementlocation"."section_id" IN
('1cc10c2e5bba4d238d5fabbfdfd72d83') AND
"policypublisher_statementlocation"."statement_id" =
'0509afa269604028818dc1f53b8f93d2') AND
"policypublisher_statementlocation"."sl_version_id" =
'bcbb218ebdc5435b9e5caf879b55cd24';
--
Ticket URL: <https://code.djangoproject.com/ticket/34207>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070184fc54eebb-bec4ad73-25d9-484f-8677-434d801e65d5-000000%40eu-central-1.amazonses.com.
