Bug UniqueConstraint with condition and nulls-distinct

[Lucas Lemke]

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

[Simon Charette]

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.

[Mariusz Felisiak]

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

@@ -3629 +3629 @@
         constraints = self.get_constraints(Author._meta.db_table)
         self.assertNotIn(constraint.name, constraints)
 
+    @skipUnlessDBFeature(
+        "supports_nulls_distinct_unique_constraints",
+        "supports_partial_indexes",
+    )
+    def test_unique_constraint_nulls_distinct_condition(self):
+        with connection.schema_editor() as editor:
+            editor.create_model(Author)
+        constraint = UniqueConstraint(
+            fields=["height", "weight"],
+            name="un_height_weight_start_A",
+            condition=Q(name__startswith="A"),
+            nulls_distinct=False,
+        )
+        with connection.schema_editor() as editor:
+            editor.add_constraint(Author, constraint)
+        Author.objects.create(name="Adam", height=None, weight=None)
+        Author.objects.create(name="Avocado", height=1, weight=None)
+        Author.objects.create(name="Adrian", height=None, weight=1)
+        with self.assertRaises(IntegrityError):
+            Author.objects.create(name="Alex", height=None, weight=None)
+        Author.objects.create(name="Bob", height=None, weight=None)
+        with self.assertRaises(IntegrityError):
+            Author.objects.create(name="Alex", height=1, weight=None)
+        Author.objects.create(name="Bill", height=None, weight=None)
+        with self.assertRaises(IntegrityError):
+            Author.objects.create(name="Alex", height=None, weight=1)
+        Author.objects.create(name="Celine", height=None, weight=1)
+        with connection.schema_editor() as editor:
+            editor.remove_constraint(Author, constraint)
+        constraints = self.get_constraints(Author._meta.db_table)
+        self.assertNotIn(constraint.name, constraints)
+
     @skipIfDBFeature("supports_nulls_distinct_unique_constraints")
     def test_unique_constraint_nulls_distinct_unsupported(self):
         # UniqueConstraint is ignored on databases that don't support

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

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.

[Mariusz Felisiak <felisiak.mariusz@…>]

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