tickets: 25946

This data as json

id	created	changetime	last_pulled_from_trac	stage	status	component	type	severity	version	resolution	summary	description	owner	reporter	keywords	easy	has_patch	needs_better_patch	needs_tests	needs_docs	ui_ux
25946	2015-12-16 14:04:10	2020-01-28 13:30:42	2022-03-06 04:42:38.239997	Accepted	assigned	Database layer (models, ORM)	Bug	Normal	dev		Negated clauses' "isnull" added term does not take field transforms into account	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.	cansarigol	ris	transform negated null	0	1	1	0	0	0