Opened 48 minutes ago
#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).