Excluding on annotations doesn't apply null handling.

[Gordon Wrigley]

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.

[Mariusz Felisiak]

Thanks for the report. In this case a WHERE clause is ​returned before ​handling nullable columns.

[Gordon Wrigley]

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

[Jordan Bae]

Could i look into this and fix?

[Jacob Walls]

No problem (no need to ask).

[Jacob Walls]

​PR

[Jordan Bae]

if anyone has time, please check this PR(​https://github.com/django/django/pull/14065)

[Mariusz Felisiak]

#32684 was a duplicate for a subquery annotation.

[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.

[Jacob Walls]

​preliminary PR to add is_nullable()

[Mariusz Felisiak]

Per Simon's ​comment.

[Natalia Bidart]

Resetting assignee as mentioned ​here.

[Natalia Bidart]

#34959 was also closed as duplicate of this one.

[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!