[Django] #35329: Bug UniqueConstraint with condition and nulls-distinct

[Django]

#35329: Bug UniqueConstraint with condition and nulls-distinct
-------------------------------------+-------------------------------------
Reporter: lsaunitti | Owner: (none)
Type: Bug | Status: new
Component: Error | Version: 5.0
reporting | Keywords: nulls-distinct,
Severity: Normal | condition, UniqueConstrain
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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
--
Ticket URL: <https://code.djangoproject.com/ticket/35329>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35329: Bug UniqueConstraint with condition and nulls-distinct
-------------------------------------+-------------------------------------

Reporter: lsaunitti | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: nulls-distinct, | Triage Stage: Accepted
condition, UniqueConstraint |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* cc: Simon Charette (added)
* component: Error reporting => Database layer (models, ORM)
* keywords: nulls-distinct, condition, UniqueConstrain => nulls-distinct,
condition, UniqueConstraint
* owner: (none) => nobody
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted


Old description:

> 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

New description:

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:

{{{#!python

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

To:

{{{#!python

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

--

Comment:

Thank you for your report Lucas!

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

[https://www.postgresql.org/docs/current/sql-createindex.html 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
[https://github.com/django/django/pull/17058/files#diff-
40b7bac727110526783c1fd39a2b6026c9e01862b7e50b21fa89e9a3591bd2d7R3321-R3343
introduced when the feature was added].
--
Ticket URL: <https://code.djangoproject.com/ticket/35329#comment:1>

[Django]

#35329: Bug UniqueConstraint with condition and nulls-distinct
-------------------------------------+-------------------------------------

Reporter: Lucas Lemke | Owner: nobody

Type: Bug | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: nulls-distinct, | Triage Stage: Accepted
condition, UniqueConstraint |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak):

Replying to [comment:1 Simon Charette]:

> Adding a test should be as simple as taking inspiration from the ones
[https://github.com/django/django/pull/17058/files#diff-
40b7bac727110526783c1fd39a2b6026c9e01862b7e50b21fa89e9a3591bd2d7R3321-R3343
introduced when the feature was added].

A regression test:
{{{#!diff
diff --git a/tests/schema/tests.py b/tests/schema/tests.py
index b912d353eb..f8e314d270 100644
--- a/tests/schema/tests.py
+++ b/tests/schema/tests.py
@@ -3629,6 +3629,38 @@ class SchemaTests(TransactionTestCase):
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

}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35329#comment:2>

[Django]

#35329: Bug UniqueConstraint with condition and nulls-distinct
-------------------------------------+-------------------------------------

Reporter: Lucas Lemke | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned

Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: nulls-distinct, | Triage Stage: Accepted
condition, UniqueConstraint |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* owner: nobody => Mariusz Felisiak
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/35329#comment:3>

[Django]

#35329: Bug UniqueConstraint with condition and nulls-distinct
-------------------------------------+-------------------------------------
Reporter: Lucas Lemke | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: nulls-distinct, | Triage Stage: Accepted
condition, UniqueConstraint |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/18022 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/35329#comment:4>

[Django]

#35329: Bug UniqueConstraint with condition and nulls-distinct
-------------------------------------+-------------------------------------
Reporter: Lucas Lemke | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: nulls-distinct, | Triage Stage: Ready for
condition, UniqueConstraint | checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Natalia Bidart):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/35329#comment:5>

[Django]

#35329: Bug UniqueConstraint with condition and nulls-distinct
-------------------------------------+-------------------------------------
Reporter: Lucas Lemke | Owner: Mariusz
| Felisiak

Type: Bug | Status: closed

Component: Database layer | Version: 5.0
(models, ORM) |

Severity: Release blocker | Resolution: fixed

Keywords: nulls-distinct, | Triage Stage: Ready for
condition, UniqueConstraint | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by GitHub <noreply@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"b98271a6e42107233311d17f5d7bc74fbb47f22c" b98271a]:
{{{#!CommitTicketReference repository=""
revision="b98271a6e42107233311d17f5d7bc74fbb47f22c"
Fixed #35329 -- Fixed migrations crash when adding partial unique
constraints with nulls_distinct.

Bug in 595a2abb58e04caa4d55fb2589bb80fb2a8fdfa1.

Thanks Lucas Lemke Saunitti for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35329#comment:6>

[Django]

#35329: Bug UniqueConstraint with condition and nulls-distinct
-------------------------------------+-------------------------------------
Reporter: Lucas Lemke | Owner: Mariusz
| Felisiak
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: nulls-distinct, | Triage Stage: Ready for
condition, UniqueConstraint | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"345e3cf57f1e9bf870482cebf36b169d79ae63d2" 345e3cf]:
{{{#!CommitTicketReference repository=""
revision="345e3cf57f1e9bf870482cebf36b169d79ae63d2"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35329#comment:7>