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
34959	Q(a=b) and Exact(a, b) handle NULLs differently	Roman Odaisky	nobody	"||= Expression =||= SQL =||
|| Q(f=42) || m.f = 42 ||
|| ~Q(f=42) || NOT(m.f = 42 AND m.f IS NOT NULL) ||
|| Exact(F(""f""), 42) || m.f = (42) ||
|| ~Exact(F(""f""), 42) || NOT m.f = (42) ||

~Q tries to treat SQL NULLs like Python None values, ~Exact doesn’t, so Q | ~Q covers the entire table while Exact | ~Exact omits the null values. Given that people who need to use lookups, F objects, functions and so on are most likely doing something complex, I doubt it’s advisable or even possible to extend the null-as-None behavior to every possible expression (the `in` lookup doesn’t try to do that, for example). But in this case the documentation should be very clear that the discrepancy exists and Q objects do extra magic that the lookups do not.

Another option is to deprecate the `~` operator on anything but Q objects, and force people to use `NegatedExpression` (hopefully aliased to `Not`) so it’s explicit it doesn’t do the same thing as ~Q.


By the way, the magic can easily backfire with custom lookups:
{{{
def lookupify(function):
    class LookupifyLookup(Lookup):
        prepare_rhs = False

        def as_sql(self, compiler, connection):
            return compiler.compile(
                function(self.lhs, self.rhs)
                .resolve_expression(compiler.query)
            )

    return LookupifyLookup

Field.register_lookup(
    lookupify(
        lambda x, y: Exact(Exact(x, 0) | IsNull(x, True), y),
    ),
    ""isblank"",
)

SomeModel.objects.exclude(field__isblank=True)
# will exclude zeroes but not nulls!
}}}"	Bug	closed	Database layer (models, ORM)	5.0	Normal	duplicate		David Sanders	Unreviewed	0	0	0	0	0	0