Support Array subqueries on PostgreSQL

[Hannes Ljungberg]

I think we should support ​constructing arrays in PostgreSQL() without requiring aggregation through array_agg(). This can be done by correlated subqueries by passing a subquery to the ARRAY constructor. In its simplest form it could look something like this:

class ArraySubquery(Subquery):
    template = 'ARRAY(%(subquery)s)'

Currently all subqueries get their ordering cleared if they aren't fulfilling some conditions like being sliced and using distinct fields. This would need to be refactored to enable array subqueries since the ordering matters due to the subquery producing an array.

There've been a couple of tickets related to this:
https://code.djangoproject.com/ticket/31687
https://code.djangoproject.com/ticket/32658

Simons suggestion to move the clearing of ordering from Query.resolve_expression to In(Lookup).process_rhs seems like a good idea to me.

I also think that adding support for array subqueries would help in resolving this ticket: https://code.djangoproject.com/ticket/26056

[Simon Charette]

Another edge case that came to mind is that we also likely want to keep clearing ordering when doing existence checks so it should only be cleared when using

1. filter(field__in=query)
2. Exists
3. Queryset.exists()

[Hannes Ljungberg]

PR: ​https://github.com/django/django/pull/14575

[Mariusz Felisiak <felisiak.mariusz@…>]

In a06b977a:

Fixed #32776 -- Added support for Array subqueries on PostgreSQL.