Opened 12 months ago
Closed 12 months ago
#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:2 by , 12 months ago
Cc: | added |
---|
comment:3 by , 12 months ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Thank you Simon, after reading the bugs I agree this looks like a duplicate. Closing as such!
Duplicate of #32398
It seems like the exact same bug as #32398 that has a proposed implementation that allows expressions to denote how they handle nulls.