Context Navigation

← Previous Ticket
Next Ticket →

#34291 closed Bug (fixed)

Validation of UniqueConstraint with ordered expressions crashes.

Reported by:	Dan F	Owned by:	Mariusz Felisiak
Component:	Database layer (models, ORM)	Version:	4.1
Severity:	Release blocker	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

I added this model:

class Foo(models.Model):
    name = models.CharField(max_length=300, unique=True)

    class Meta:
        constraints = [
            UniqueConstraint(
                Lower("name").desc(), name="unique_lower_name"
            )
        ]

and this admin class:

@admin.register(Foo)
class FooAdmin(admin.ModelAdmin):
    search_fields = ("name",)

I based the UniqueConstraint on the documentation (https://docs.djangoproject.com/en/4.1/ref/models/constraints/#django.db.models.UniqueConstraint.expressions), which shows

UniqueConstraint(Lower('name').desc(), 'category', name='unique_lower_name_category')

When I visit the admin and click "add foo", enter name "foo1", and click Save, I get a stack trace:

Request Method: POST
Request URL: http://localhost:8000/admin/myapp/foo/add/

Django Version: 4.1.5
Python Version: 3.10.9
...

Traceback (most recent call last):
  File ".../lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)

The above exception (syntax error at or near "DESC"
LINE 1: ...werapp_foo" WHERE LOWER("myapp_foo"."name") DESC = (LO...
                                                             ^
) was the direct cause of the following exception:
  File ".../lib/python3.10/site-packages/django/core/handlers/exception.py", line 55, in inner
    response = get_response(request)
  File ".../lib/python3.10/site-packages/django/core/handlers/base.py", line 197, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File ".../lib/python3.10/site-packages/django/contrib/admin/options.py", line 686, in wrapper
    return self.admin_site.admin_view(view)(*args, **kwargs)
  File ".../lib/python3.10/site-packages/django/utils/decorators.py", line 133, in _wrapped_view
    response = view_func(request, *args, **kwargs)
  File ".../lib/python3.10/site-packages/django/views/decorators/cache.py", line 62, in _wrapped_view_func
    response = view_func(request, *args, **kwargs)
  File ".../lib/python3.10/site-packages/django/contrib/admin/sites.py", line 242, in inner
    return view(request, *args, **kwargs)
  File ".../lib/python3.10/site-packages/django/contrib/admin/options.py", line 1890, in add_view
    return self.changeform_view(request, None, form_url, extra_context)
  File ".../lib/python3.10/site-packages/django/utils/decorators.py", line 46, in _wrapper
    return bound_method(*args, **kwargs)
  File ".../lib/python3.10/site-packages/django/utils/decorators.py", line 133, in _wrapped_view
    response = view_func(request, *args, **kwargs)
  File ".../lib/python3.10/site-packages/django/contrib/admin/options.py", line 1750, in changeform_view
    return self._changeform_view(request, object_id, form_url, extra_context)
  File ".../lib/python3.10/site-packages/django/contrib/admin/options.py", line 1796, in _changeform_view
    form_validated = form.is_valid()
  File ".../lib/python3.10/site-packages/django/forms/forms.py", line 205, in is_valid
    return self.is_bound and not self.errors
  File ".../lib/python3.10/site-packages/django/forms/forms.py", line 200, in errors
    self.full_clean()
  File ".../lib/python3.10/site-packages/django/forms/forms.py", line 439, in full_clean
    self._post_clean()
  File ".../lib/python3.10/site-packages/django/forms/models.py", line 492, in _post_clean
    self.instance.full_clean(exclude=exclude, validate_unique=False)
  File ".../lib/python3.10/site-packages/django/db/models/base.py", line 1472, in full_clean
    self.validate_constraints(exclude=exclude)
  File ".../lib/python3.10/site-packages/django/db/models/base.py", line 1423, in validate_constraints
    constraint.validate(model_class, self, exclude=exclude, using=using)
  File ".../lib/python3.10/site-packages/django/db/models/constraints.py", line 347, in validate
    if queryset.exists():
  File ".../lib/python3.10/site-packages/django/db/models/query.py", line 1226, in exists
    return self.query.has_results(using=self.db)
  File ".../lib/python3.10/site-packages/django/db/models/sql/query.py", line 592, in has_results
    return compiler.has_results()
  File ".../lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1366, in has_results
    return bool(self.execute_sql(SINGLE))
  File ".../lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1398, in execute_sql
    cursor.execute(sql, params)
  File ".../lib/python3.10/site-packages/debug_toolbar/panels/sql/tracking.py", line 230, in execute
    return self._record(self.cursor.execute, sql, params)
  File ".../lib/python3.10/site-packages/debug_toolbar/panels/sql/tracking.py", line 154, in _record
    return method(sql, params)
  File ".../lib/python3.10/site-packages/django/db/backends/utils.py", line 103, in execute
    return super().execute(sql, params)
  File ".../lib/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File ".../lib/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File ".../lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File ".../lib/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File ".../lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)

Exception Type: ProgrammingError at /admin/myapp/foo/add/
Exception Value: syntax error at or near "DESC"
LINE 1: ...myapp_foo" WHERE LOWER("myapp_foo"."name") DESC = (LO...
                                                             ^

This happens on the Postgres backend (psycopg2 2.9.5).

I also get this error using the sqlite backend.

Exception Type: OperationalError at /admin/myapp/foo/add/
Exception Value: near "DESC": syntax error

Change History (9)

comment:1 by Dan F, 15 months ago

Component:	Database layer (models, ORM) → contrib.admin

follow-up: 3 comment:2 by Mariusz Felisiak, 15 months ago

Cc:	Gagaro added
Component:	contrib.admin → Database layer (models, ORM)
Severity:	Normal → Release blocker
Summary:	UniqueConstraint with expression including "desc()" causes admin to crash on adding an object → Validation of UniqueConstraint with ordered expressions crashes.
Triage Stage:	Unreviewed → Accepted