[Django] #33595: Check Constraint on nullable BooleanField should not be simplified

10 views

Skip to first unread message

Django

unread,

Mar 22, 2022, 1:10:12 PM3/22/22

to django-...@googlegroups.com

#33595: Check Constraint on nullable BooleanField should not be simplified
-------------------------------------+-------------------------------------
Reporter: Peter Law | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Given a model like:

{{{#!python
class Foo(models.Model):
it_exists = models.BooleanField(null=True)
class Meta:
constraints = [
models.constraints.CheckConstraint(
check=models.Q(it_exists=True),
name="exists_constraint",
),
]
}}}

The constraint will allow both True and None values, yet that isn't at all
obvious from the declaration.

I'm using Postgres 11 and the resulting check is simplified (in terms of
the SQL from the migration) down to something like `CHECK (("it_exists"))`
(I've got a few other things in my actual constraint, but this what I see
for the nullable boolean field).

This appears to end up allowing database NULL values to act as truthy
values, which is undesirable (even if something which SQL allows).

I would have expected that the constraint would be more like `"it_exists"
= TRUE`, which I don't think would allow NULL values to pass the check.

It's possible to work around this by adding an explicit check (i.e: not-
null-and-equals-true), though it would be great if the default behaviour
was more in line with expectations.

--
Ticket URL: <https://code.djangoproject.com/ticket/33595>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Mar 23, 2022, 4:19:43 AM3/23/22

to django-...@googlegroups.com

#33595: Check Constraint on nullable BooleanField should not be simplified
-------------------------------------+-------------------------------------
Reporter: Peter Law | Owner: nobody

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => invalid

Comment:

Thanks for this ticket, however that's how a `CHECK` constraints works in
all databases and that's why not-null constraints exist, see for example
PostgreSQL [https://www.postgresql.org/docs/current/ddl-constraints.html
docs]:
> ''"It should be noted that a check constraint is satisfied if the check
expression evaluates to true or the null value. Since most expressions
will evaluate to the null value if any operand is null, they will not
prevent null values in the constrained columns. To ensure that a column
does not contain null values, the not-null constraint described in the
next section can be used."''

Switching to `"it_exists" = TRUE` wouldn't change this behavior. You
should use `models.BooleanField(null=False)` or explicitly add `__isnull`
to `check=models.Q(it_exists=True, is_exists__isnull=False)`.