#36198: Unique Constraints defined with __date lookups on F expressions fail
validation but create database constraints sucessfully
-------------------------------------+-------------------------------------
Reporter: Tom Hall | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: constraint lookup | Triage Stage:
unique validation | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Tom Hall:
Old description:
> Unique constraints defined with date lookups on F expressions create the
> correct index and work as expected at the database/ORM level but raise
> false positives during validation.
>
> Initially discovered this for date lookups but it would seem to apply to
> the other date/time lookups, at this point I have explicitly confirmed
> the same behaviour for year, iso_year and minute.
>
> Minimal reproducable example:
>
> {{{#!python
> # models.py
> from django.db import models
>
> class DateLookup(models.Model):
> timestamp = models.DateTimeField()
>
> class Meta:
> constraints = [
> # triggering validation errors for objects that can actually
> be saved
> models.UniqueConstraint(
> models.F("timestamp__date"),
> name="%(app_label)s_%(class)s_unique_date_F_expr",
> ),
> ]
>
> # tests.py
> import datetime as dt
>
> from django.core.exceptions import ValidationError
> from django.db import IntegrityError
> from django.utils import timezone
> from django.test import TestCase
>
> from .models import DateLookup
>
> TIMESTAMP = timezone.make_aware(dt.datetime(2025, 2, 18, 8, 18, 6))
>
> class TestDateLookupConstraint(TestCase):
> def test_demonstrate_constraint_fails(self):
> """Demonstrate constraint validation failure at python level when
> db constraint is not triggered"""
> DateLookup.objects.create(timestamp=TIMESTAMP)
> new = DateLookup(timestamp=TIMESTAMP - dt.timedelta(days=1))
> new.save() #success
> self.assertTrue(new.pk)
> try:
> new.full_clean() #ValidationError
> except ValidationError:
> self.fail("False Positive")
>
> def test_constraint_db(self):
> """Demonstrate DB constraint is triggered sucessfully."""
> DateLookup.objects.create(timestamp=TIMESTAMP)
> new = DateLookup(timestamp=TIMESTAMP)
> with self.assertRaises(ValidationError):
> new.full_clean()
> with self.assertRaises(IntegrityError):
> new.save()
> self.assertFalse(new.pk)
> }}}
>
> Output of sqlmigrate command:
>
> {{{#!sql
> BEGIN;
> --
> -- Create model DateLookup
> --
> CREATE TABLE "minimal_example_datelookup" ("id" integer NOT NULL PRIMARY
> KEY AUTOINCREMENT, "timestamp" datetime NOT NULL);
> CREATE UNIQUE INDEX "minimal_example_datelookup_unique_date_F_expr" ON
> "minimal_example_datelookup" ((django_datetime_cast_date("timestamp",
> 'UTC', 'UTC')));
> COMMIT;
> }}}
>
> The workaround is to explicitly use the corresponding database functions
> in the constraint definition; this version works correctly.
>
> {{{#!python
> class TruncDateExpression(models.Model):
> timestamp = models.DateTimeField()
>
> class Meta:
> constraints = [
> models.UniqueConstraint(
> models.functions.TruncDate("timestamp"),
> name="%(app_label)s_%(class)s_unique_date",
> ),
> ]
> }}}
>
> The
> [https://docs.djangoproject.com/en/5.1/ref/models/constraints/#expressions
> docs for constraint expressions] don't show any examples of lookups being
> used in this way but also don't explicitly disallow them either, and
> given that the correct constraint is in fact being generated on the
> database, it implies this ''could'' be fixed to work as expected.
> Otherwise, at a minimum the documentation should be updated to forbid the
> use of F expression lookups, and the constraint should fail to apply
> during migration.
>
> Initially discovered this and investigated on the
> [https://forum.djangoproject.com/t/unique-constraint-on-datetime-date-
> unexpectedly-fails-validation-but-database-allows-saving/38889 django
> forum], see there for additional context and my efforts at debugging the
> validation step.
New description:
Unique constraints defined with date lookups on F expressions create the
correct index and work as expected at the database/ORM level but raise
false positives during validation.
Initially discovered this for date lookups but it would seem to apply to
the other date/time lookups, at this point I have explicitly confirmed the
same behaviour for year, iso_year and minute.
Minimal reproducable example:
{{{#!python
# models.py
from django.db import models
class DateLookup(models.Model):
timestamp = models.DateTimeField()
class Meta:
constraints = [
# triggering validation errors for objects that can actually
be saved
models.UniqueConstraint(
models.F("timestamp__date"),
name="%(app_label)s_%(class)s_unique_date_F_expr",
),
]
# tests.py
import datetime as dt
from django.core.exceptions import ValidationError
from django.db import IntegrityError
from django.utils import timezone
from django.test import TestCase
from .models import DateLookup
TIMESTAMP = timezone.make_aware(dt.datetime(2025, 2, 18, 8, 18, 6))
class TestDateLookupConstraint(TestCase):
def test_demonstrate_constraint_fails(self):
"""Demonstrate constraint validation failure at python level when
db constraint is not triggered"""
DateLookup.objects.create(timestamp=TIMESTAMP)
new = DateLookup(timestamp=TIMESTAMP - dt.timedelta(days=1))
new.save() #success
self.assertTrue(new.pk)
try:
new.full_clean() #ValidationError
except ValidationError:
self.fail("False Positive")
def test_constraint_db(self):
"""Demonstrate DB constraint is triggered sucessfully."""
DateLookup.objects.create(timestamp=TIMESTAMP)
new = DateLookup(timestamp=TIMESTAMP)
with self.assertRaises(ValidationError):
new.full_clean()
with self.assertRaises(IntegrityError):
new.save()
self.assertFalse(new.pk)
}}}
Output of sqlmigrate command for SQLite backend:
{{{#!sql
BEGIN;
--
-- Create model DateLookup
--
CREATE TABLE "minimal_example_datelookup" ("id" integer NOT NULL PRIMARY
KEY AUTOINCREMENT, "timestamp" datetime NOT NULL);
CREATE UNIQUE INDEX "minimal_example_datelookup_unique_date_F_expr" ON
"minimal_example_datelookup" ((django_datetime_cast_date("timestamp",
'UTC', 'UTC')));
COMMIT;
}}}
The workaround is to explicitly use the corresponding database functions
in the constraint definition; this version works correctly.
{{{#!python
class TruncDateExpression(models.Model):
timestamp = models.DateTimeField()
class Meta:
constraints = [
models.UniqueConstraint(
models.functions.TruncDate("timestamp"),
name="%(app_label)s_%(class)s_unique_date",
),
]
}}}
The
[https://docs.djangoproject.com/en/5.1/ref/models/constraints/#expressions
docs for constraint expressions] don't show any examples of lookups being
used in this way but also don't explicitly disallow them either, and given
that the correct constraint is in fact being generated on the database, it
implies this ''could'' be fixed to work as expected. Otherwise, at a
minimum the documentation should be updated to forbid the use of F
expression lookups, and the constraint should fail to apply during
migration.
Initially discovered this and investigated on the
[https://forum.djangoproject.com/t/unique-constraint-on-datetime-date-
unexpectedly-fails-validation-but-database-allows-saving/38889 django
forum], see there for additional context and my efforts at debugging the
validation step.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/36198#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019518911335-bb7eb738-f5ef-4ca3-ac06-0ec2c7ebb14a-000000%40eu-central-1.amazonses.com.
