Re: [Django] #36592: Incorrect warning when specifying a UniqueConstraint with SQLite and nulls_distinct=False

[Django]

#36592: Incorrect warning when specifying a UniqueConstraint with SQLite and
nulls_distinct=False
-------------------------------------+-------------------------------------
Reporter: r-owen | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: SQLite | Triage Stage:
UniqueConstraint | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by r-owen:

Old description:

> I am trying to create a UniqueConstraint with `nulls_distinct=False`.
> This will be used with sqlite for local use and tests, and postgresql for
> production.
>
> When I migrate my sqlite database I get this warning:
> ```
> SQLite does not support unique constraints with nulls distinct.
> ```
> I suspect this is a bug, since I specified that nulls should not be
> distinct.
>
> Note that I also get the same warning if I specify `nulls_distinct=True`,
> but in that case I expect to see it.
>
> Here is the simplest model I could come up with that shows the issue:
> ```
> class SimpleModel(models.Model):
> field_a = models.IntegerField(null=True)
> field_b = models.IntegerField(null=True)
>
> class Meta:
> constraints = [
> UniqueConstraint(
> name="simple_unique_constraint",
> fields=["field_a", "field_b"],
> nulls_distinct=False,
> ),
> ]
> ```
> The full warning I see on migration is:
> ```
> WARNINGS:
> dances.SimpleModel: (models.W047) SQLite does not support unique
> constraints with nulls distinct.
> HINT: A constraint won't be created. Silence this warning if you
> don't care about it.
> ```
> and, as I said before, I get the same warning if I use
> `nulls_distinct=True` (which is when I expect to see the warning).

New description:

I am trying to create a UniqueConstraint with `nulls_distinct=False`. This
will be used with sqlite for local use and tests, and postgresql for
production.

When I migrate my sqlite database I get this warning:

{{{
SQLite does not support unique constraints with nulls distinct.
}}}


I suspect this is a bug, since I specified that nulls should not be
distinct.

Note that I also get the same warning if I specify `nulls_distinct=True`,
but in that case I expect to see it.

Here is the simplest model I could come up with that shows the issue:
{{{
class SimpleModel(models.Model):
field_a = models.IntegerField(null=True)
field_b = models.IntegerField(null=True)

class Meta:
constraints = [
UniqueConstraint(
name="simple_unique_constraint",
fields=["field_a", "field_b"],
nulls_distinct=False,
),
]
}}}
The full warning I see on migration is:
{{{
WARNINGS:
dances.SimpleModel: (models.W047) SQLite does not support unique
constraints with nulls distinct.
HINT: A constraint won't be created. Silence this warning if you
don't care about it.
}}}
and, as I said before, I get the same warning if I use
`nulls_distinct=True` (which is when I expect to see the warning).

--
--
Ticket URL: <https://code.djangoproject.com/ticket/36592#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36592: Incorrect warning when specifying a UniqueConstraint with SQLite and
nulls_distinct=False
-------------------------------------+-------------------------------------

Reporter: Russell Owen | Owner: (none)
Type: Bug | Status: closed

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

Severity: Normal | Resolution: invalid

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

Changes (by Tim Graham):

* resolution: => invalid
* status: new => closed

Comment:

A problem with your proposal is that the behavior of whether or not nulls
are distinct is database dependent. In fact, the behavior of SQLite is the
opposite of what you stated: null values are considered distinct. The docs
for
[https://docs.djangoproject.com/en/dev/ref/models/constraints/#django.db.models.UniqueConstraint.nulls_distinct
UniqueConstraint.nulls_distinct] says: "The default value is None which
uses the database default which is True on most backends."

I don't think there's a strong argument to make this check aware of the
default treatment of nulls. Using a different database in testing and
production is highly advised against. Indeed, your `UniqueConstraint`
example highlights a feature of PostgreSQL that SQLite doesn't support.
--
Ticket URL: <https://code.djangoproject.com/ticket/36592#comment:3>

[Django]

#36592: Incorrect warning when specifying a UniqueConstraint with SQLite and
nulls_distinct=False
-------------------------------------+-------------------------------------
Reporter: Russell Owen | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: SQLite | Triage Stage:
UniqueConstraint | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Russell Owen):

I feel that you did not understand the point of my bug report (it's
definitely not meant to be a proposal) and have closed it prematurely and
unfairly.

The bug is that one cannot construct a `UniqueConstraint` in sqlite if one
specifies a boolean value for the `nulls_distinct` argument, even if the
boolean value matches the behavior that sqlite supports. The only accepted
value is None, and that is needlessly limiting.

Based on the warning message that is printed, I believe that an explicit
value of False should also be accepted. If I misunderstand and got the
sign wrong, the bug still should be fixed. The *compatible* boolean value
of `nulls_distinct` should be acceptable. It should result in the
constraint being generated, without any warning.

Use cases for specifying an explicit boolean value for `nulls_distinct`
include:

* Clarity: Maintainers can see the intended behavior.
* Predictability. The behavior is known, instead of being a mysterious
database-specific default.
* Portability. The code will behave the same way when used with a
different database (provided that database actually supports the specified
mode).
--
Ticket URL: <https://code.djangoproject.com/ticket/36592#comment:4>

[Django]

#36592: Incorrect warning when specifying a UniqueConstraint with SQLite and
nulls_distinct=False
-------------------------------------+-------------------------------------
Reporter: Russell Owen | Owner: (none)

Type: Bug | Status: new

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

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

Changes (by Russell Owen):

* resolution: invalid =>
* status: closed => new

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