Opened 22 months ago

Closed 22 months ago

Last modified 22 months ago

#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, 22 months ago

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

comment:2 by Mariusz Felisiak, 22 months ago

Cc: Gagaro added
Component: contrib.adminDatabase layer (models, ORM)
Severity: NormalRelease blocker
Summary: UniqueConstraint with expression including "desc()" causes admin to crash on adding an objectValidation of UniqueConstraint with ordered expressions crashes.
Triage Stage: UnreviewedAccepted

in reply to:  2 comment:3 by Dan F, 22 months ago

Thanks for the quick review!

Replying to Mariusz Felisiak:

Thanks for the report!

comment:4 by Mariusz Felisiak, 22 months ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

comment:5 by Mariusz Felisiak, 22 months ago

Has patch: set

comment:6 by Carlton Gibson, 22 months ago

Triage Stage: AcceptedReady for checkin

comment:7 by GitHub <noreply@…>, 22 months ago

Resolution: fixed
Status: assignedclosed

In 2b1242a:

Fixed #34291 -- Fixed Meta.constraints validation crash on UniqueConstraint with ordered expressions.

Thanks Dan F for the report.

Bug in 667105877e6723c6985399803a364848891513cc.

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 22 months ago

In 719a14ba:

[4.2.x] Fixed #34291 -- Fixed Meta.constraints validation crash on UniqueConstraint with ordered expressions.

Thanks Dan F for the report.

Bug in 667105877e6723c6985399803a364848891513cc.
Backport of 2b1242abb3989f5d74e787b09132d01bcbee5b55 from main

comment:9 by Mariusz Felisiak <felisiak.mariusz@…>, 22 months ago

In 26b7a25:

[4.1.x] Fixed #34291 -- Fixed Meta.constraints validation crash on UniqueConstraint with ordered expressions.

Thanks Dan F for the report.

Bug in 667105877e6723c6985399803a364848891513cc.

Backport of 2b1242abb3989f5d74e787b09132d01bcbee5b55 from main.

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