[Django] #35559: Calling list() on an empty sliced union still causes a database query

[Django]

#35559: Calling list() on an empty sliced union still causes a database query
-------------------------------------+-------------------------------------
Reporter: Lucidiot | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 5.0
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When upgrading from Django 4.1 to 4.2, some of our unit tests failed
because of unexpected SQL queries, and those queries are still present in
Django 5.0.

We have a Django REST Framework API view, which relies on Django's
`Paginator` to do its pagination. It uses a `QuerySet` which involves a
`UNION` of two other queries that both have some filters. Those filters
can cause the whole union to be empty. The `Paginator` knows that the
result count is 0, so it returns a page of results by slicing with
`[0:0]`.

In Django 4.1, calling `list()` on that sliced union would cause no query
and an empty list is returned, but starting with 4.2, a query runs:

{{{#!python
qs = Thing.objects.filter(id__in=Thing.objects.none())
list(qs) # 0 queries
list(qs[0:0]) # 0 queries
list(qs.union(qs)) # 0 queries
list(qs.union(qs)[0:0]) # 1 query
}}}

{{{#!sql
(
SELECT "app_thing"."id" AS "col1"
FROM "app_thing"
WHERE 0 = 1
) UNION (
SELECT "app_thing"."id" AS "col1"
FROM "app_thing"
WHERE 0 = 1
)
}}}

I think this extra query was added in the fix for #34125.
--
Ticket URL: <https://code.djangoproject.com/ticket/35559>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35559: Calling list() on an empty sliced union still causes a database query
-------------------------------------+-------------------------------------

Reporter: Lucidiot | Owner: Simon
Type: | Charette
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 5.0
(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 Simon Charette):

* owner: nobody => Simon Charette
* stage: Unreviewed => Accepted
* status: new => assigned
* type: Bug => Cleanup/optimization

Comment:

I confirm that c2cc80756b8949cdd87b88bbfdfee698ced441e0 focused on the
correctness aspect of the problem (avoiding the crash) without focusing on
some edge cases that might still allow the query eliding to take place.
--
Ticket URL: <https://code.djangoproject.com/ticket/35559#comment:1>

[Django]

#35559: Calling list() on an empty sliced union still causes a database query
-------------------------------------+-------------------------------------
Reporter: Lucidiot | Owner: Simon
Type: | Charette
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

* has_patch: 0 => 1

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