#25946: Negated clauses' "isnull" added term does not take field transforms into
account
----------------------------------------------+----------------------------
Reporter: ris | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: master
Severity: Normal | Keywords: transform
| negated null
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25946>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/046.8465d2eaff3a65437e53688c4ee3317e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
