Opened 2 months ago

Closed 2 months ago

Last modified 2 months ago

#35329 closed Bug (fixed)

Bug UniqueConstraint with condition and nulls-distinct

Reported by: Lucas Lemke Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 5.0
Severity: Release blocker Keywords: nulls-distinct, condition, UniqueConstraint
Cc: Lucas Lemke, Simon Charette Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Simon Charette)

Hi, I`m Lucas (https://github.com/lsaunitti)

I found a bug when set a UniqueConstrain using condition using nulls_distinct using like that:

Screenshot 2024-03-25 at 10.47.59.png

When django generate SQL to create a check constraint the result is ... WHERE <condition> NULLS NOT DISTINCT.
It raise an exception on Postgresql.

To fix it, I suggest change the file django/db/backends/base/schema.py on line 132:

Today:

    sql_create_unique_index = (
        "CREATE UNIQUE INDEX %(name)s ON %(table)s "
        "(%(columns)s)%(include)s%(condition)s%(nulls_distinct)s"
    )

To:

    sql_create_unique_index = (
        "CREATE UNIQUE INDEX %(name)s ON %(table)s "
        "(%(columns)s)%(include)s%(nulls_distinct)s%(condition)s"
    )

Regards,
Lucas Lemke Saunitti
Software Engineer

Change History (7)

comment:1 by Simon Charette, 2 months ago

Cc: Simon Charette added
Component: Error reportingDatabase layer (models, ORM)
Description: modified (diff)
Keywords: UniqueConstraint added; UniqueConstrain removed
Owner: set to nobody
Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted

Thank you for your report Lucas!

Marking as a release blocker since nulls_distinct is a new feature introduced in Django 5.0.

The Postgres docs clearly point that NULLS DISTINCT should come before WHERE, sorry for missing that.

Would you be interested in submitting a PR with the proposed changes? Adding a test should be as simple as taking inspiration from the ones introduced when the feature was added.

in reply to:  1 comment:2 by Mariusz Felisiak, 2 months ago

Replying to Simon Charette:

Adding a test should be as simple as taking inspiration from the ones introduced when the feature was added.

A regression test:

  • tests/schema/tests.py

    diff --git a/tests/schema/tests.py b/tests/schema/tests.py
    index b912d353eb..f8e314d270 100644
    a b class SchemaTests(TransactionTestCase):  
    36293629        constraints = self.get_constraints(Author._meta.db_table)
    36303630        self.assertNotIn(constraint.name, constraints)
    36313631
     3632    @skipUnlessDBFeature(
     3633        "supports_nulls_distinct_unique_constraints",
     3634        "supports_partial_indexes",
     3635    )
     3636    def test_unique_constraint_nulls_distinct_condition(self):
     3637        with connection.schema_editor() as editor:
     3638            editor.create_model(Author)
     3639        constraint = UniqueConstraint(
     3640            fields=["height", "weight"],
     3641            name="un_height_weight_start_A",
     3642            condition=Q(name__startswith="A"),
     3643            nulls_distinct=False,
     3644        )
     3645        with connection.schema_editor() as editor:
     3646            editor.add_constraint(Author, constraint)
     3647        Author.objects.create(name="Adam", height=None, weight=None)
     3648        Author.objects.create(name="Avocado", height=1, weight=None)
     3649        Author.objects.create(name="Adrian", height=None, weight=1)
     3650        with self.assertRaises(IntegrityError):
     3651            Author.objects.create(name="Alex", height=None, weight=None)
     3652        Author.objects.create(name="Bob", height=None, weight=None)
     3653        with self.assertRaises(IntegrityError):
     3654            Author.objects.create(name="Alex", height=1, weight=None)
     3655        Author.objects.create(name="Bill", height=None, weight=None)
     3656        with self.assertRaises(IntegrityError):
     3657            Author.objects.create(name="Alex", height=None, weight=1)
     3658        Author.objects.create(name="Celine", height=None, weight=1)
     3659        with connection.schema_editor() as editor:
     3660            editor.remove_constraint(Author, constraint)
     3661        constraints = self.get_constraints(Author._meta.db_table)
     3662        self.assertNotIn(constraint.name, constraints)
     3663
    36323664    @skipIfDBFeature("supports_nulls_distinct_unique_constraints")
    36333665    def test_unique_constraint_nulls_distinct_unsupported(self):
    36343666        # UniqueConstraint is ignored on databases that don't support

comment:3 by Mariusz Felisiak, 2 months ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

comment:4 by Mariusz Felisiak, 2 months ago

Has patch: set

comment:5 by Natalia Bidart, 2 months ago

Triage Stage: AcceptedReady for checkin

comment:6 by GitHub <noreply@…>, 2 months ago

Resolution: fixed
Status: assignedclosed

In b98271a:

Fixed #35329 -- Fixed migrations crash when adding partial unique constraints with nulls_distinct.

Bug in 595a2abb58e04caa4d55fb2589bb80fb2a8fdfa1.

Thanks Lucas Lemke Saunitti for the report.

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 2 months ago

In 345e3cf:

[5.0.x] Fixed #35329 -- Fixed migrations crash when adding partial unique constraints with nulls_distinct.

Bug in 595a2abb58e04caa4d55fb2589bb80fb2a8fdfa1.

Thanks Lucas Lemke Saunitti for the report.
Backport of b98271a6e42107233311d17f5d7bc74fbb47f22c from main

Note: See TracTickets for help on using tickets.
Back to Top