Opened 9 months ago

Closed 6 months ago

#35275 closed Bug (fixed)

Constraints validation fails on UniqueConstraint using OpClass

Reported by: Mathieu Kniewallner Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Gagaro Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Adding a UniqueConstraint using PostgreSQL-specific OpClass on a model breaks constraints validation performed when calling validate_constraints on a model when using PostgreSQL.

Minimal reproducer

from django.contrib.postgres.indexes import OpClass
from django.db import models
from django.db.models.functions import Lower


class Place(models.Model):
    name = models.CharField(max_length=255)

    class Meta:
        app_label = "opclass_issue"
        constraints = [
            models.UniqueConstraint(
                OpClass(Lower("name"), name="text_pattern_ops"),
                name="lower_name_uniq",
            )
        ]


place = Place(name="Narnia")
    place.validate_constraints()

This leads to the following error:

django.db.utils.ProgrammingError: syntax error at or near "text_pattern_ops"
LINE 1: ...place" WHERE LOWER("opclass_issue_place"."name") text_patte...

Full SQL query that is generated:

SELECT 1 AS "a" FROM "opclass_issue_place" WHERE LOWER("opclass_issue_place"."name") text_pattern_ops = (LOWER('narnia') text_pattern_ops) LIMIT 1

Just in case, this happens even though django.contrib.postgres is correctly installed in INSTALLED_APPS, so the issue is not related to that.

I've also created a test and adapted the CI to run it in https://github.com/backmarket-oss/django/pull/2/files, which leads to https://github.com/backmarket-oss/django/actions/runs/8171237603/job/22339033423?pr=2 showing the failure.

Potential root cause

OpClass wrapper should only make sense when creating the constraint, and should not be used when validating the constraint, as this leads to an invalid SQL query.

Looking at the code for the PostgreSQL specific ExclusionConstraint, a similar conclusion was reached, and OpClass is explicitly removed in validate method: https://github.com/django/django/blob/4426b1a72dc289643e2ae8c190b8dc4b3a39daf7/django/contrib/postgres/constraints.py#L211-L216.

Potential fix

Applying a similar fix as the one above in UniqueConstraint, showcased in https://github.com/backmarket-oss/django/pull/1/files applied on top of the other PR above, leads to https://github.com/backmarket-oss/django/actions/runs/8171242801/job/22339050847?pr=1 where the added test is now passing.

If you think that this is the correct fix to apply, or have a lead for another fix, I'd be happy to make a proper pull request targeting Django repository.

Change History (6)

comment:1 by Mariusz Felisiak, 9 months ago

Cc: Gagaro added
Triage Stage: UnreviewedAccepted

Good catch, thanks for the report. Please prepare a patch via GitHub PR (a regression test is required).

comment:2 by Mathieu Kniewallner, 9 months ago

Has patch: set
Owner: changed from nobody to Mathieu Kniewallner
Status: newassigned

comment:3 by Mariusz Felisiak, 9 months ago

Patch needs improvement: set

comment:4 by Mariusz Felisiak, 6 months ago

Owner: changed from Mathieu Kniewallner to Mariusz Felisiak
Patch needs improvement: unset

comment:5 by Sarah Boyce, 6 months ago

Triage Stage: AcceptedReady for checkin

comment:6 by Sarah Boyce <42296566+sarahboyce@…>, 6 months ago

Resolution: fixed
Status: assignedclosed

In f030236a:

Fixed #35275 -- Fixed Meta.constraints validation crash on UniqueConstraint with OpClass().

This also introduces Expression.constraint_validation_compatible that
allows specifying that expression should be ignored during a constraint
validation.

Note: See TracTickets for help on using tickets.
Back to Top