QuerySet.order_by() crashes on union() queryset with a single non-empty query.

[Hannes Ljungberg]

I noticed this error while implementing a dynamic union like this:

union = reduce(
    lambda qs1, qs2: qs1.union(qs2),
    querysets,
    queryset.none(),
)

If len(querysets) == 1 it will result in a pretty weird query throwing a database error when both querysets are ordered, another example recreating this in the test suite:

qs1 = Number.objects.all().order_by(‘pk’)
qs2 = Number.objects.none().union(qs1).order_by(‘pk’)

Executing qs2 result in the following query:

(SELECT "queries_number"."id", "queries_number"."num", "queries_number"."other_num", "queries_number"."another_num" FROM "queries_number" ORDER BY "queries_number"."id" ASC) ORDER BY (1) ASC

Result in the following error on PostgresSQL:

psycopg2.errors.SyntaxError: multiple ORDER BY clauses not allowed
LINE 1: ...umber" ORDER BY "queries_number"."id" DESC) ORDER BY (1) ASC

And sqlite:

django.db.utils.DatabaseError: ORDER BY not allowed in subqueries of compound statements.

MySQL seems to accept this query but it's most likely not the query the user expects the be executed.

My proposal is to simply return the non-empty query and make the union a no-op set in this case.

[Hannes Ljungberg]

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

[Mariusz Felisiak]

Raising django.db.utils.DatabaseError: ORDER BY not allowed in subqueries of compound statements. is not a big issue IMO, I would say that is a cleanup. However it generates and incorrect SQL after 2cbd3967e0a51eab993df89679046d25ec78baec which should be fixed as a bug.

[Mariusz Felisiak <felisiak.mariusz@…>]

In c7c7615d:

Fixed #32116 -- Fixed QuerySet.order_by() crash on EmptyQuerySet with union() on a single non-empty ordered queryset.