Validation of UniqueConstraint with Case() crashes.

[Andrew Roberts]

Consider the following model where we want to guarantee that there is only one active profile per email address:

class Profile(models.Model):
    active = models.BooleanField()
    email = models.CharField(max_length=255)

    class Meta:
        constraints = [
            models.UniqueConstraint(
                Case(When(active=True, then=F("email"))),
                name="unique_active_email",
            )
        ]
        app_label = "test"

Model validation indirectly calls constraint.validate(), but to simulate this we can execute:

constraint = Profile._meta.constraints[0]
constraint.validate(Profile, Profile(active=True, email="test@example.com"))

This fails with:

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[21], line 1
----> 1 constraint.validate(Profile, Profile(active=True, email="test@example.com"))

File /usr/local/lib/python3.11/site-packages/django/db/models/constraints.py:346, in UniqueConstraint.validate(self, model, instance, exclude, using)
    344         if isinstance(expr, OrderBy):
    345             expr = expr.expression
--> 346         expressions.append(Exact(expr, expr.replace_expressions(replacements)))
    347     queryset = queryset.filter(*expressions)
    348 model_class_pk = instance._get_pk_val(model._meta)

File /usr/local/lib/python3.11/site-packages/django/db/models/expressions.py:401, in BaseExpression.replace_expressions(self, replacements)
    398 clone = self.copy()
    399 source_expressions = clone.get_source_expressions()
    400 clone.set_source_expressions(
--> 401     [
    402         expr.replace_expressions(replacements) if expr else None
    403         for expr in source_expressions
    404     ]
    405 )
    406 return clone

File /usr/local/lib/python3.11/site-packages/django/db/models/expressions.py:402, in <listcomp>(.0)
    398 clone = self.copy()
    399 source_expressions = clone.get_source_expressions()
    400 clone.set_source_expressions(
    401     [
--> 402         expr.replace_expressions(replacements) if expr else None
    403         for expr in source_expressions
    404     ]
    405 )
    406 return clone

File /usr/local/lib/python3.11/site-packages/django/db/models/expressions.py:401, in BaseExpression.replace_expressions(self, replacements)
    398 clone = self.copy()
    399 source_expressions = clone.get_source_expressions()
    400 clone.set_source_expressions(
--> 401     [
    402         expr.replace_expressions(replacements) if expr else None
    403         for expr in source_expressions
    404     ]
    405 )
    406 return clone

File /usr/local/lib/python3.11/site-packages/django/db/models/expressions.py:402, in <listcomp>(.0)
    398 clone = self.copy()
    399 source_expressions = clone.get_source_expressions()
    400 clone.set_source_expressions(
    401     [
--> 402         expr.replace_expressions(replacements) if expr else None
    403         for expr in source_expressions
    404     ]
    405 )
    406 return clone

AttributeError: 'Q' object has no attribute 'replace_expressions'

[Mariusz Felisiak]

Thanks for the report.

[David Sanders]

Hi Francesco, please note that while you're free to assign yourself to the ticket, the design has not yet been discussed on what the best approach is.

[David Sanders]

fyi for the uninitiated: #34805 is related to this

[Mariusz Felisiak]

Replying to David Sanders:

fyi for the uninitiated: #34805 is related to this

This situation is slightly different, as we have a Case() expression that returns a field, not a check for condition uniqueness.

[Nick]

Hey all, I'm running into this issue too. Is there any idea on how to work-a-round this issue?

[Simon Charette]

I would suggest using a Func instead of Case / When or Q if you really need to achieve the exact SQL that CASE WHEN would generated

Something like

models.UniqueConstraint(
    Func(F("email")), template="CASE WHEN active = true THEN %(expressions)s END"),
    name="unique_active_email",
)

Or to simply use UniqueConstraint.condition as ​documented if the backend you use support it which will also incur less storage use.

models.UniqueConstraint(
    fields=["email"],
    name="unique_active_email",
    condition=Q(active=True),
)

I think we should spend the time to implement Q.replace_expressions though as it's pretty trivial to do so and would cover for the cases that cannot be expressed using .condition such as CASE WHEN last_name IS NULL THEN first_name ELSE first_name || last_name END.

[Simon Charette]

Playing a bit with trying to implement Q.replace_expressions I have to retract myself; it's not trivial.

The only solution I can think of is have the validate logic pass a special type of dict as replacement to replace_expression that would default missing key lookups for tuples of the form ('active', True) to query.resolve_lookup_value so it gets turned into Exact(F("active"), True).replace_expression(replacements).

It's doable but certainly not trivial ​as this branch demonstrate.

I think this has some ties into #24267 where the idea would be that we'd introduce an UnresolvedLookup(BaseExpression) class initialized from __init__(self, field, **lookup) and that would store F(field) in its source expressions and thus allow replace_expressions to take place normally as Q would be storing UnresolvedLookup(key_parts[0], key_parts[1:]) instead of tuple of the form tuple[str, Any] as children.

All UnresolvedLookup would do in its resolve_expressions is ​what can be found in the trailing part of Query.build_filter today that calls solve_lookup_type, resolve_lookup_value, and build_lookup but it would behave like a normal expression and avoids all the hacks we have in place today that special case Q objects.

Until work towards can be made the best solution here is to use lookups directly to palliate the lack of support for unresolved lookups introspection

from django.db.models.lookups import Exact

models.UniqueConstraint(
    Case(When(Exact(F("active", True), then=F("email"))),
        name="unique_active_email",
    )
)

[David Sanders]

I was almost going to ask whether turning Q object into expressions might be a path here, because it seems to borrow a lot from Expression already being "combinable" and implementing a few expression-like attributes. Then I saw your comment about implementing replace_expressions() being non-trivial.

[Simon Charette]

Making Q more Expression-like is definitely the way to approach this problem so you're right. The way to make it more like so proposed above would be to have it store Expression-like objects (UnresolvedLookup instances) instead of tuple[str, Any] it its children AKA source expressions.

In other words, having Q(name__lower__exact="david") turned into Q(UnresolvedLookup(F("name"), "lower__exact", "david")) internally and having UnresolvedLookup.resolve_expressions do what Query.build_filter does when dealing with ("name__lower__exact", "david") would make Q more Expression-like and allow methods such as replace_expressions.

[Simon Charette]

Working on a solution for #36198 paved the way for a less involved than expected solution here.

Both patches can be found ​in this PR.

[Simon Charette]

Marking as needs improvement as I'd like to find a way to avoid duplicating sql.Query.build_lookup logic.

[Sarah Boyce <42296566+sarahboyce@…>]

In 079d31e6:

Fixed #34871, #36518 -- Implemented unresolved lookups expression replacement.

This allows the proper resolving of lookups when performing constraint
validation involving Q and Case objects.

Thanks Andrew Roberts for the report and Sarah for the tests and review.

[Sarah Boyce <42296566+sarahboyce@…>]

In b3bb723:

[5.2.x] Fixed #34871, #36518 -- Implemented unresolved lookups expression replacement.

This allows the proper resolving of lookups when performing constraint
validation involving Q and Case objects.

Thanks Andrew Roberts for the report and Sarah for the tests and review.

Backport of 079d31e698fa08dd92e2bc4f3fe9b4817a214419 from main.