Opened 92 minutes ago
#37526 assigned Bug
XOR fallback treats NULL operands as false, so filter() and exclude() can return the same row
| Reported by: | Mariano Ignacio Baragiola | Owned by: | Mariano Ignacio Baragiola |
|---|---|---|---|
| Component: | Database layer (models, ORM) | Version: | dev |
| Severity: | Normal | Keywords: | XOR NULL exclude |
| 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 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 thatfilter(q)also returns.filter(q)returns rows that MariaDB and MySQL do not, since their nativeXORgivesNULLwhen either side isNULL.
class Number(models.Model): num = models.IntegerField(null=True) other_num = models.IntegerField()
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 (PR 21642, PR 22007, 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).