﻿id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
37520	~F() on a nullable BooleanField maps NULL to False on SQLite	Mariano Ignacio Baragiola	Mariano Ignacio Baragiola	"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.

{{{#!python
class Item(models.Model):
    flag = models.BooleanField(null=True)
}}}

{{{#!python
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)."	Bug	assigned	Database layer (models, ORM)	6.1	Release blocker				Unreviewed	0	0	0	0	0	0
