#37520 assigned Bug

~F() on a nullable BooleanField maps NULL to False on SQLite

Reported by: Mariano Ignacio Baragiola Owned by: Mariano Ignacio Baragiola
Component: Database layer (models, ORM) Version: 6.1
Severity: Release blocker Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

On SQLite, negating a nullable BooleanField with ~F() turns NULL into False. So update(flag=~F("flag")), the documented way to swap a boolean, overwrites NULL values with False, and exclude(~F("flag")) returns rows where flag is NULL.

Django 6.0 on SQLite and every version on PostgreSQL keep NULL, as SQL NOT NULL does.

class Item(models.Model):
    flag = models.BooleanField(null=True)
Item.objects.create(flag=True)
Item.objects.create(flag=False)
Item.objects.create(flag=None)

Item.objects.annotate(n=~F("flag")).order_by("pk").values_list("n", flat=True)
# Actual: [False, True, False]    Expected: [False, True, None]

Item.objects.exclude(~F("flag")).values_list("flag", flat=True)
# Actual: [True, None]            Expected: [True]

Item.objects.update(flag=~F("flag"))
Item.objects.order_by("pk").values_list("flag", flat=True)
# Actual: [False, True, False]    Expected: [False, True, None]

Cause: since 4bbc27c8686f10f9556cef02dbfa9f5157fbcf56 (#36492, backported to 6.1), conditional_expression_supported_in_where_clause() returns False on SQLite for a plain column, so NegatedExpression.as_sql() emits CASE WHEN flag = 0 THEN 1 ELSE 0 END instead of NOT flag. The ELSE 0 branch catches NULL.

Versions: 6.1 and main on SQLite (regression in 4bbc27c8686f10f9556cef02dbfa9f5157fbcf56). MySQL and Oracle have used the same CASE since ~F() was added in 4.2 (a320aab5129f4019b3c1d28b7a3b509582bc56f9), so they have the same bug; I read those code paths but did not run them.

Suggested fix: keep NULL in the CASE: CASE WHEN flag = 0 THEN 1 WHEN flag <> 0 THEN 0 END. This matches NOT for all three values, keeps the #36492 change to exact lookups against literals, and fixes MySQL and Oracle too. The cost is that the negated SQL (and its params) appears twice.

AI disclosure: I found and reproduced this with Claude Code (Anthropic).

Change History (0)

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