#34959 closed Bug (duplicate)

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

Reported by: Roman Odaisky Owned by: nobody
Component: Database layer (models, ORM) Version: 5.0
Severity: Normal Keywords:
Cc: David Sanders Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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!

Change History (3)

comment:1 by Simon Charette, 12 months ago

Last edited 12 months ago by Simon Charette (previous) (diff)

comment:2 by David Sanders, 12 months ago

Cc: David Sanders added

comment:3 by Natalia Bidart, 12 months ago

Resolution: duplicate
Status: newclosed

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

Note: See TracTickets for help on using tickets.
Back to Top