Check Constraint on nullable BooleanField should not be simplified

[Peter Law]

Given a model like:

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.

[Mariusz Felisiak]

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 ​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).