Use `EXISTS(SELECT 1 ...)` for subqueries

[w0rp]

If you write a QuerySet call like so in Django...

MyModel.objects.all().exists()

the query run will be like so.

SELECT 1 AS "a" FROM "myapp_mymodel" LIMIT 1;

If you use the Exists() function to filter with a subquery like so...

MyModel.objects.filter(Exists(MyOtherModel.objects.all()))

The subquery will be run like so.

... WHERE EXISTS(SELECT "myapp_myothermodel"."id", ... FROM "myapp_myothermodel");

It would be nice if the queries generated for Exists() used SELECT 1 like .exists() does, where possible. In an app I work on, I have one query in particular that is 15KB in size, but only around 8KB if I apply .annotate(_1=Value(1, output_field=IntegerField())).values_list('_1') to all of the subqueries. That change alone is enough to make my queries much easier to debug.

[Simon Charette]

I could swear there was another ticket about that but I cannot find it.

The logic to clear the column is a bit more complex than what you've described here but it's all detailed in Query.has_results ​(link) and SQLCompiler.has_results ​(link) so it could be added to Exists.as_sql.

Ideally the logic would be encapsulated in a Query method so it doesn't have to be duplicated in Query.has_results and Exists.as_sql so both could path could benefit from optimizations such as #24296.

[Simon Charette]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In 51297a9:

Fixed #31792 -- Made Exists() reuse QuerySet.exists() optimizations.

The latter is already optimized to limit the number of results, avoid
selecting unnecessary fields, and drop ordering if possible without
altering the semantic of the query.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 45f4282:

Refs #31792 -- Updated SQL example in Exists() docs.

Follow up to 51297a92324976a704279b567ec4f80bb92d7b60.