Bug: annotating using When(id__in=[], ...) returns empty queryset

[Kevin Marsh]

qs = MyModel.objects.all().annotate(
    field=models.Case(
        models.When(id__in=[], then=models.Value(True)),
        default=models.Value(False),
        output_field=models.BooleanField()
    )
)

This results in slightly inconsistent behaviour, qs is an empty queryset (irregardless of how many items are in the original queryset before annotating) and calling qs.count will always return 0. However calling str(qs.query) raises EmptyResultSet which is presumably the correct behaviour?

I've attached a failing test better showcasing this.

[Kevin Marsh]

failing tests

[Anssi Kääriäinen]

This is close to the problem mentioned in #26430. Here Case.as_sql() could just catch the EmptyResultSet and instead return sql "1 = 0". But I wonder if we are able to avoid the EmptyResultSet completely by the following approach.

1. Instead of raising EmptyResultSet we generate sql, params = "1 = %s", (EMPTY_PARAM,).
2. EMPTY_PARAM is converted to 0 when the SQL is executed.
3. We check for EMPTY_PARAM in the params inside WhereNode and do necessary conversions for full/empty resultset cases when generating the SQL.
4. When generating the SQL for a queryset which isn't a subquery of another queryset, we check for existence of EMPTY_PARAM in the params generated for the where clause. If one is found, we know we don't need to execute the queryset at all.
5. In all other cases we run the query with the empty param. The condition is always going to fail in the generated SQL, but that is exactly what we want for this ticket.

This should also solve #26430 by the "isn't a subquery" condition in rule 3.

[Simon Charette]

​PR

[Tim Graham]

There's a test failure on PostgreSQL.

[Tim Graham <timograham@…>]

In 1410616:

Fixed #26433 -- Fixed Case expressions with empty When.