#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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018e773a92fe-6c273710-dc61-490b-84d9-56f5025d0a9a-000000%40eu-central-1.amazonses.com.
