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
25946	"Negated clauses' ""isnull"" added term does not take field transforms into account"	ris	Can Sarıgöl	"In https://github.com/django/django/blob/32ef48aa562e6aaee9983f5d0f1c60f02fd555fb/django/db/models/sql/query.py#L1209:

{{{
            if (lookup_type != 'isnull' and (
                    self.is_nullable(targets[0]) or
                    self.alias_map[join_list[-1]].join_type == LOUTER)):
                # The condition added here will be SQL like this:
                # NOT (col IS NOT NULL), where the first NOT is added in
                # upper layers of code. The reason for addition is that if col
                # is null, then col != someval will result in SQL ""unknown""
                # which isn't the same as in Python. The Python None handling
                # is wanted, and it can be gotten by
                # (col IS NULL OR col != someval)
                #   <=>
                # NOT (col IS NOT NULL AND col = someval).
                lookup_class = targets[0].get_lookup('isnull')
                clause.add(lookup_class(targets[0].get_col(alias, sources[0]), False), AND)
}}}

The problem with this is that it always performs the `isnull` on the base field, ignoring any transforms that may apply.

Consider a simple custom transform on a field, `base_field`, which transforms it into `some_function ( base_field )`. Performing a negated `exact` lookup on this will generate SQL along the lines of:

{{{
NOT (some_function ( ""some_table"".""base_field"" ) = 1 AND ""some_table"".""base_field"" IS NOT NULL)
}}}

This will not produce the correct results in cases where `some_function` doesn't pass through nulls as nulls or generates nulls of its own (though it's true that many functions null behaviour is to mirror the source expression null-ness exactly, which is probably why this hasn't been noticed before).

The suggested ""correct"" SQL is probably

{{{
NOT (some_function ( ""some_table"".""base_field"" ) = 1 AND some_function ( ""some_table"".""base_field"" ) IS NOT NULL)
}}}

Though I note that this is itself not problem free if we consider cases where `some_function` may have side effects or may not be `STABLE` or `IMMUTABLE` (as postgres would put it), i.e. won't necessarily return the same result when called twice given the same input. I would consider use of `COALESCE()` instead to remedy this but this may have portability concerns or may be more opaque to the query planner leading to slower queries.

Noticed on 1.8, master's equivalent method doesn't appear to have changed much since then."	Bug	assigned	Database layer (models, ORM)	dev	Normal		transform negated null		Accepted	1	0	0	1	0	0