[Django] #35594: Add support for non-distinct NULL expressions to UniqueConstraint.validate()

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Type: New
| feature
Status: new | Component: Database
| layer (models, ORM)
Version: 5.0 | Severity: Normal
Keywords: uniqueconstraint | Triage Stage:
nulls_distinct | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Expressions which evaluate to `NULL` within
`UniqueConstraint(*expressions, nulls_distinct=False)` are still treated
as distinct by `UniqueConstraint.validate()`. This means a
`ValidationError` is not raised when it should be.

Similarly, if the database connection uses
`interprets_empty_strings_as_nulls` this is also ignored by `.validate()`.

Should #35575 be merged, this problem would also extend to any
`GeneratedField` included in `UniqueConstraint(fields=[...],
nulls_distinct=False)`.

E.g.

{{{
class Book(models.Model):
name = CharField(max_length=255, null=True)

class Meta:
constraints = [
UniqueConstraint(F("name"), nulls_distinct=False,
name="book_name_null_unique")
]
}}}
then
{{{
> Book.objects.create(name=None)
> book = Book(name=None)
> book.full_clean() # Should raise a `ValidationError` but doesn't.
> book.save() # The database raises an `IntegrityError`.
}}}

This ticket was raised following discussion in
https://github.com/django/django/pull/18356#pullrequestreview-2166340541
--
Ticket URL: <https://code.djangoproject.com/ticket/35594>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------

Reporter: Mark Gensler | Owner: (none)

Type: New feature | Status: new

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

Keywords: uniqueconstraint | Triage Stage:
nulls_distinct | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Mark Gensler:

Old description:

> Expressions which evaluate to `NULL` within
> `UniqueConstraint(*expressions, nulls_distinct=False)` are still treated
> as distinct by `UniqueConstraint.validate()`. This means a
> `ValidationError` is not raised when it should be.
>
> Similarly, if the database connection uses
> `interprets_empty_strings_as_nulls` this is also ignored by
> `.validate()`.
>
> Should #35575 be merged, this problem would also extend to any
> `GeneratedField` included in `UniqueConstraint(fields=[...],
> nulls_distinct=False)`.
>
> E.g.
>
> {{{
> class Book(models.Model):
> name = CharField(max_length=255, null=True)
>
> class Meta:
> constraints = [
> UniqueConstraint(F("name"), nulls_distinct=False,
> name="book_name_null_unique")
> ]
> }}}
> then
> {{{
> > Book.objects.create(name=None)
> > book = Book(name=None)
> > book.full_clean() # Should raise a `ValidationError` but doesn't.
> > book.save() # The database raises an `IntegrityError`.
> }}}
>
> This ticket was raised following discussion in
> https://github.com/django/django/pull/18356#pullrequestreview-2166340541

New description:

Expressions which evaluate to `NULL` within
`UniqueConstraint(*expressions, nulls_distinct=False)` are still treated
as distinct by `UniqueConstraint.validate()`. This means a
`ValidationError` is not raised when it should be.

Similarly, if the database connection uses
`interprets_empty_strings_as_nulls` this is also ignored by `.validate()`.

Should #35575 be merged, this problem would also extend to any
`GeneratedField` included in `UniqueConstraint(fields=[...],
nulls_distinct=False)`.

E.g.

{{{
class Book(models.Model):

name = CharField(max_length=255, null=True, blank=True)

class Meta:
constraints = [
UniqueConstraint(F("name"), nulls_distinct=False,
name="book_name_null_unique")
]
}}}
then
{{{
> Book.objects.create(name=None)
> book = Book(name=None)
> book.full_clean() # Should raise a `ValidationError` but doesn't.

> book.save() # The database raises a `UniqueViolation`.

}}}

This ticket was raised following discussion in
https://github.com/django/django/pull/18356#pullrequestreview-2166340541

--

--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:1>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |

Severity: Release blocker | Resolution:
Keywords: unique constraint | Triage Stage: Accepted
nulls_distinct validation |
expressions |

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

Changes (by Simon Charette):

* keywords: uniqueconstraint nulls_distinct => unique constraint
nulls_distinct validation expressions
* severity: Normal => Release blocker
* stage: Unreviewed => Accepted

Comment:

Thanks for creating the report!

This was missed in #34701. Elevating to release blocker as this is a bug
in a newly released feature.

I guess solution would be that when `nulls_distinct=True` all expressions
checks should be turned into `OR (rhs IS NULL AND lhs IS NULL)`

{{{#!sql
SELECT 1 FROM book WHERE lower(name) = lower(NULL)
}}}

Is turned into

{{{#!sql
SELECT 1 FROM book WHERE (lower(name) = lower(NULL)) OR (lower(name) IS
NULL AND lower(NULL) IS NULL)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:2>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: unique constraint | Triage Stage: Accepted
nulls_distinct validation |
expressions |
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)

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

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Owner:

| DongwookKim0823
Type: New feature | Status: assigned

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

Changes (by DongwookKim0823):

* owner: (none) => DongwookKim0823
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:4>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Owner:
| DongwookKim0823
Type: New feature | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: unique constraint | Triage Stage: Accepted
nulls_distinct validation |
expressions |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

Dong it would have been appreciate if you could have chimed in with Mark
and myself before jumping on assignment.

Worked on a PoC [https://github.com/django/django/pull/18362 here].
--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:5>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------

Reporter: Mark Gensler | Owner: (none)
Type: New feature | Status: new

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

Changes (by DongwookKim0823):

* owner: DongwookKim0823 => (none)
* status: assigned => new

--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:6>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: unique constraint | Triage Stage: Accepted
nulls_distinct validation |
expressions |

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* has_patch: 0 => 1
* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:7>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: unique constraint | Triage Stage: Accepted
nulls_distinct validation |
expressions |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by DongwookKim0823):

Replying to [comment:5 Simon Charette]:

Sorry for jumping in without prior discussion. I wasn't aware and will be
more cautious in the future.
--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:8>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Owner: (none)
Type: New feature | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: unique constraint | Triage Stage: Accepted
nulls_distinct validation |
expressions |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

No worries and no harm done, I just wanted to make sure you wouldn't waste
time on this problem since both Mark and I had a pretty good idea of where
to take the patch.

I guess a possible rule of thumb would be to at least wait a few days
after the creation of a ticket if there seems to be active discussions and
no call for someone to take the implementation work. In doubt, asking if
help is required is also always welcome.
--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:9>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------

Reporter: Mark Gensler | Owner: Simon
| Charette

Type: New feature | Status: assigned

Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: unique constraint | Triage Stage: Accepted
nulls_distinct validation |
expressions |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* owner: (none) => Simon Charette

* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:10>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Owner: Simon
| Charette
Type: New feature | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: unique constraint | Triage Stage: Accepted
nulls_distinct validation |
expressions |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by DongwookKim0823):

Thanks for the guidance. I appreciate the advice and will follow it moving
forward.
--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:11>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Owner: Simon
| Charette

Type: Bug | Status: assigned

Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: unique constraint | Triage Stage: Accepted
nulls_distinct validation |
expressions |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* needs_better_patch: 1 => 0
* type: New feature => Bug

--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:12>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: unique constraint | Triage Stage: Ready for
nulls_distinct validation | checkin

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

Changes (by Sarah Boyce):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:13>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Owner: Simon
| Charette

Type: Bug | Status: closed

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

Severity: Release blocker | Resolution: fixed

Keywords: unique constraint | Triage Stage: Ready for
nulls_distinct validation | checkin
expressions |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce <42296566+sarahboyce@…>):

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

Comment:

In [changeset:"adc0b6aac3f8a5c96e1ca282bc9f46e28d20281c" adc0b6aa]:
{{{#!CommitTicketReference repository=""
revision="adc0b6aac3f8a5c96e1ca282bc9f46e28d20281c"
Fixed #35594 -- Added unique nulls distinct validation for expressions.

Thanks Mark Gensler for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:14>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: unique constraint | Triage Stage: Ready for
nulls_distinct validation | checkin
expressions |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"4d8e574379720750736434f8f3ca62bd43ecc768" 4d8e5743]:
{{{#!CommitTicketReference repository=""
revision="4d8e574379720750736434f8f3ca62bd43ecc768"
[5.1.x] Fixed #35594 -- Added unique nulls distinct validation for

expressions.

Thanks Mark Gensler for the report.

Backport of adc0b6aac3f8a5c96e1ca282bc9f46e28d20281c from main.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:15>

[Django]

#35594: Add support for non-distinct NULL expressions to
UniqueConstraint.validate()
-------------------------------------+-------------------------------------
Reporter: Mark Gensler | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: unique constraint | Triage Stage: Ready for
nulls_distinct validation | checkin
expressions |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"fe9bf0cef50e8e97e76424df98fd841fdc211e94" fe9bf0c]:
{{{#!CommitTicketReference repository=""
revision="fe9bf0cef50e8e97e76424df98fd841fdc211e94"
[5.0.x] Fixed #35594 -- Added unique nulls distinct validation for

expressions.

Thanks Mark Gensler for the report.

Backport of adc0b6aac3f8a5c96e1ca282bc9f46e28d20281c from main.
}}}
--

Ticket URL: <https://code.djangoproject.com/ticket/35594#comment:16>