Q(a=b) and Exact(a, b) handle NULLs differently

[Roman Odaisky]

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!

[Simon Charette]

It seems like the exact same bug as #24267 that has ​a proposed implementation that allows expressions to denote how they handle nulls.

[Natalia Bidart]

Thank you Simon, after reading the bugs I agree this looks like a duplicate. Closing as such!
Duplicate of #32398