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

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.