[Django] #32398: Excluding on annotations doesn't apply null handling.

Django

unread,

Jan 30, 2021, 7:26:10 AM1/30/21

to django-...@googlegroups.com

#32398: Excluding on annotations doesn't apply null handling.
------------------------------------------+------------------------
Reporter: Gordon Wrigley | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 3.1
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
------------------------------------------+------------------------
I would generally expect `my_timestamp__year` to behave the same as
`.annotate(my_timestamp_year=ExtractYear("my_timestamp"))` however I have
noticed that when using exclude the former will account for null's but the
later won't.

{{{
print(M.objects.exclude(closed_at__year=t.year).values("closed_at__year").query)

SELECT
EXTRACT('year' FROM "r_m"."closed_at" AT TIME ZONE 'Europe/London')
FROM "r_m"
WHERE NOT (
"r_m"."closed_at" BETWEEN 2021-01-01 00:00:00+00:00 AND 2021-12-31
23:59:59.999999+00:00
AND "r_m"."closed_at" IS NOT NULL
)
}}}

{{{
print(M.objects.annotate(year=ExtractYear("closed_at")).exclude(year=t.year).values("year").query)

SELECT
EXTRACT('year' FROM "r_m"."closed_at" AT TIME ZONE 'Europe/London')
AS "year"
FROM "r_m"
WHERE NOT (
"r_m"."closed_at" BETWEEN 2021-01-01 00:00:00+00:00 AND 2021-12-31
23:59:59.999999+00:00
)
}}}

Annotated aggregates like min and max also don't account for nulls which
leads me to suspect this is a problem with annotations.

I also tried wrapping the ExtractYear in an ExpressionWrapper with
`output_field=IntegerField(null=True)` which didn't change anything.

--
Ticket URL: <https://code.djangoproject.com/ticket/32398>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Jan 30, 2021, 1:48:15 PM1/30/21

to django-...@googlegroups.com

#32398: Excluding on annotations doesn't apply null handling.

-------------------------------------+-------------------------------------

Reporter: Gordon Wrigley | Owner: nobody

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* cc: Jacob Walls (added)
* type: Uncategorized => Bug
* component: Uncategorized => Database layer (models, ORM)
* stage: Unreviewed => Accepted

Comment:

Thanks for the report. In this case a `WHERE` clause is
[https://github.com/django/django/blob/6822aa5c6c3fbec7c5393a05e990865ba59fe167/django/db/models/sql/query.py#L1302-L1305
returned] before
[https://github.com/django/django/blob/6822aa5c6c3fbec7c5393a05e990865ba59fe167/django/db/models/sql/query.py#L1354-L1375
handling nullable columns].

--
Ticket URL: <https://code.djangoproject.com/ticket/32398#comment:1>

Django

unread,

Jan 30, 2021, 2:02:25 PM1/30/21

to django-...@googlegroups.com

#32398: Excluding on annotations doesn't apply null handling.

-------------------------------------+-------------------------------------

Reporter: Gordon Wrigley | Owner: nobody

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------

Comment (by Gordon Wrigley):

Whoever looks at it might want to take a quick look at the other early
returns in the same function.

--
Ticket URL: <https://code.djangoproject.com/ticket/32398#comment:2>

Django

unread,

Jan 31, 2021, 4:01:59 AM1/31/21

to django-...@googlegroups.com

#32398: Excluding on annotations doesn't apply null handling.

-------------------------------------+-------------------------------------

Reporter: Gordon Wrigley | Owner: nobody

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Simon Charette):

* cc: Simon Charette (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/32398#comment:3>

Django

unread,

Feb 21, 2021, 9:53:12 PM2/21/21

to django-...@googlegroups.com

#32398: Excluding on annotations doesn't apply null handling.

-------------------------------------+-------------------------------------
Reporter: Gordon Wrigley | Owner: Jordan
| Bae
Type: Bug | Status: assigned

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Jordan Bae):

* owner: nobody => Jordan Bae
* status: new => assigned

Comment:

Could i look into this and fix?

--
Ticket URL: <https://code.djangoproject.com/ticket/32398#comment:4>

Django

unread,

Feb 22, 2021, 8:55:23 AM2/22/21

to django-...@googlegroups.com

#32398: Excluding on annotations doesn't apply null handling.

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------

Comment (by Jacob Walls):

No problem (no need to ask).

--
Ticket URL: <https://code.djangoproject.com/ticket/32398#comment:5>

Django

unread,

Mar 2, 2021, 10:40:08 AM3/2/21

to django-...@googlegroups.com

#32398: Excluding on annotations doesn't apply null handling.

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* needs_better_patch: 0 => 1
* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/14065 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/32398#comment:6>

Django

unread,

Mar 3, 2021, 1:25:01 PM3/3/21

to django-...@googlegroups.com

#32398: Excluding on annotations doesn't apply null handling.

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/32398#comment:7>

unread,

May 19, 2021, 4:21:33 AM5/19/21

to django-...@googlegroups.com

#32398: Excluding on annotations doesn't apply null handling.

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/32398#comment:13>

Django

unread,

May 25, 2021, 3:47:12 PM5/25/21

to django-...@googlegroups.com

#32398: Excluding on annotations doesn't apply null handling.

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------

Comment (by Gerben Morsink):

I think I have found another case that fails and is related to handling
nullable columns:

{{{
value = ['1', None]

status_model =
StatusModel.objects.filter(field=OuterRef('pk')).order_by('-timestamp')
qs =
RelatedModel.annotate(current_status_db=Subquery(status_model.values('status')[:1])).filter(current_status_db__in=value)
}}}
qs always returns an empty queryset, while if the filter equals
{{{.filter(current_status_db=None)}}} it works correctly.

--
Ticket URL: <https://code.djangoproject.com/ticket/32398#comment:14>

unread,

Jun 21, 2023, 2:13:44 PM6/21/23

to django-...@googlegroups.com

#32398: Excluding on annotations doesn't apply null handling.

-------------------------------------+-------------------------------------
Reporter: Gordon Wrigley | Owner: Simon
| Charette

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Natalia Bidart):

* owner: Jacob Walls => Simon Charette

Comment:

Resetting assignee as mentioned
[https://github.com/django/django/pull/16748#issuecomment-1564001712
here].

--
Ticket URL: <https://code.djangoproject.com/ticket/32398#comment:18>

Django

unread,

Nov 10, 2023, 8:01:29 AM11/10/23

to django-...@googlegroups.com

#32398: Excluding on annotations doesn't apply null handling.

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------

Comment (by Natalia Bidart):

#34959 was also closed as duplicate of this one.

--
Ticket URL: <https://code.djangoproject.com/ticket/32398#comment:19>

Django

unread,

Nov 10, 2023, 10:10:34 AM11/10/23

to django-...@googlegroups.com

#32398: Excluding on annotations doesn't apply null handling.

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------

Comment (by Roman Odaisky):

Or sometimes the null handling is applied in places where it should not be
applied:

{{{
def isblank(value, true_or_false):
"""Basically the equivalent of `(not value) == true_or_false`"""
return Exact(Exact(value, 0) | IsNull(value, True), true_or_false)

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(isblank), "isblank")

SomeModel.objects.exclude(isblank(F("field"), True))
# correctly excludes zeroes and nulls

SomeModel.objects.exclude(field__isblank=True)
# only excludes zeroes but not nulls!
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32398#comment:20>

Reply all

Reply to author

Forward