﻿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
37526	XOR fallback treats NULL operands as false, so filter() and exclude() can return the same row	Mariano Ignacio Baragiola	Mariano Ignacio Baragiola	"On databases without a native `XOR` (`supports_logical_xor = False`: SQLite, PostgreSQL, Oracle), `WhereNode.as_sql()` rewrites `a ^ b ^ ...` as `(a OR b OR ...) AND 1 = MOD(CASE WHEN a THEN 1 ELSE 0 END + ..., 2)`. The `ELSE 0` counts a `NULL` (unknown) operand as false. As a result:

 * `exclude(q)` can return rows that `filter(q)` also returns.
 * `filter(q)` returns rows that MariaDB and MySQL do not, since their native `XOR` gives `NULL` when either side is `NULL`.

{{{#!python
class Number(models.Model):
    num = models.IntegerField(null=True)
    other_num = models.IntegerField()
}}}

{{{#!python
for num, other_num in [(1, 1), (2, 1), (None, 1), (1, 2), (None, 2)]:
    Number.objects.create(num=num, other_num=other_num)  # pks 1-5

q = ~Q(num=1) ^ Q(other_num=1)
Number.objects.filter(q)   # pks [1, 5]
Number.objects.exclude(q)  # Actual: [2, 4, 5]    Expected: [2, 4]

Number.objects.filter(Q(num=1) ^ Q(other_num=1))
# Actual: [2, 3, 4]    MySQL: [2, 4]
}}}

Row 5 is in both `filter(q)` and `exclude(q)`. In `exclude(q)` the operand `~Q(num=1)` compiles to `NOT (num = 1)`, which is `NULL` for row 5. The fallback counts it as 0, so the `XOR` is false and `exclude()` keeps the row. MySQL gives `NULL XOR FALSE = NULL` and drops it.

Versions: every version since `XOR` support landed in 4.1 (c6b4d62fa2, #29865); #34604 (b81e974e9e) changed the sum to a parity check but kept `ELSE 0`. Reproduced on main on SQLite and PostgreSQL 16; stable/6.1.x and stable/6.0.x have the same code. Not a regression.

Suggested fix: map each operand to `CASE WHEN c THEN 1 WHEN NOT c THEN 0 END`, so a `NULL` operand makes the sum, and the `XOR`, `NULL`. For any number of operands this matches native `XOR` and SQL three-valued logic, and `filter(q)` and `exclude(q)` can no longer overlap.

This is a trade-off that needs a decision. Today a plain `XOR` without `~` splits rows cleanly between `filter()` and `exclude()`, because `exclude()` adds `IS NOT NULL` to each operand. With the fix, a `NULL` operand makes the row match neither, as on MySQL. For example, `Q(num__isnull=True) ^ Q(num=1)` (""num is NULL or 1, but not both"") matches rows 3 and 5 today and matches neither of them with the fix, nor on MySQL. The fix also changes annotations (`annotate(x=Q(num=1) ^ Q(other_num=1))` gives `None` for row 3 instead of `True`) and check constraints (`CheckConstraint(condition=Q(num=1) ^ Q(other_num=1))` stops rejecting rows where `num` is `NULL`; constraints already in the database keep the old SQL). The other choice is to keep two-valued operands and fix `exclude()` instead, which keeps the results we have today but leaves these databases out of step with MySQL.

The operand's SQL and params then appear three times (once in the `OR`, twice in the `CASE`) instead of twice, so a subquery operand may run more often.

Related: #37520 is adding a shared NULL-keeping template, `CASE WHEN {sql} THEN 1 WHEN NOT ({sql}) THEN 0 END`, for `NegatedExpression`, `Lookup`/`WhereNode.select_format()` and `GeneratedField`. The `XOR` fallback is a separate case (a sum of operands in `WHERE`), but it could use the same template once that lands.

I have a patch with tests for the suggested fix ready. Django's PR limit stops me from opening a PR while my earlier ones ([https://github.com/django/django/pull/21642 PR 21642], [https://github.com/django/django/pull/22007 PR 22007], [https://github.com/django/django/pull/22247 PR 22247]) are open, so I'll open it as soon as one of them is merged and the approach is agreed.

AI disclosure: I found and reproduced this with Claude Code (Anthropic)."	Bug	assigned	Database layer (models, ORM)	dev	Normal		XOR NULL exclude		Unreviewed	0	0	0	0	0	0
