Combining values() querysets on a model with Meta.ordering crashes when the order fields are not included

[Adam Johnson]

Combining querysets restricted with values()/values_list() (e.g. via union()) on a model with Meta.ordering crashes at SQL compilation on Django 6.1 when the default ordering columns are not part of the selected columns:

django.db.utils.DatabaseError: ORDER BY term does not match any column in the result set.

Minimal reproduction (crashes on PostgreSQL, MySQL, and Oracle — any backend with supports_slicing_ordering_in_compound=True; the same code works on Django 6.0):

class Book(models.Model):
    name = models.CharField(max_length=10)
    rank = models.IntegerField()

    class Meta:
        ordering = ["rank"]

Book.objects.values("name").union(Book.objects.values("name"))  # crashes on iteration

Same for values_list(), and for intersection()/difference(). qs1.union(qs2).values("name") doesn't crash but silently adds a spurious __orderbycol2 column to the compiled query and result set.

Regression in 087bb9e8f3478d53f12b1737af865992af17c5f2 (Refs #36644 -- Applied default ordering after union()), which sets default_ordering = True on the combined query so that Meta.ordering is applied after the combination. Root cause: for combined queries with a limited list of selected fields, SQLCompiler.get_order_by() cannot inject the implicit ordering columns into the values() parts (has_select_fields guard in django/db/models/sql/compiler.py), so it raises DatabaseError.

[Jacob Walls]

I'd like some input on whether the use is invalid, or perhaps should become invalid after a deprecation.

This query always crashed (user error):

Book.objects.values("name").union(Book.objects.values("name")).order_by("rank")

The question is whether the default ordering should imply the same query (and thus imply user error):

Book.objects.values("name").union(Book.objects.values("name"))

The correct version would be:

Book.objects.values("name").union(Book.objects.values("name")).order_by()

I don't think that's so much to ask, as in some cases we expect default ordering to be cleared via order_by() before a union...

SQLite doesn't support ORDER BY in the single leg of a composed query while Oracle allows it only if slicing takes place (other engines allow it but ignore the clause).

​https://github.com/django/django/pull/21277#issuecomment-4453655100

... and now it might be necessary after a union in order to make the effect of Meta.ordering easier to reason about.

Currently, the only time Meta.ordering is ignored is in group by queries. Making the query from this bug report continue to work would be introducing a second, partial exception depending on the specific columns involved. I'm not sure that's good for the long-term.

A python exception would be nice instead of a DatabaseError. We could detect the mismatch, and issue a deprecation warning.

---

How we ended up here:

• #36644 asked to make first() no longer inject order_by("pk") if ordering had ever been force-cleared by order_by().
• While implementing that, we noticed that an implementation detail of union() caused the resulting combined query to appear as if the order had been force-cleared, making it impossible to distinguish whether the user eventually wanted an ordering via first() or not.
• We decided (er, no one stopped me from deciding) to apply default ordering after unions, so that we could distinguish the two states. We did this without a deprecation, as it appeared to agree with the docs for Meta.ordering. In retrospect, we could have added a minor incompatible change as we did for the first() change (7cf1c22d4dfdd46f2082cfc55b714b68c4fd2de3).

[Jacob Walls]

Left a question about whether we can avoid mocking the feature flag in the test.

[Sarah Boyce]

I agree with Jacob that this query be invalid and that it worked before feels accidental.
If it is feasible to catch this and have a FieldError raised, and add a note in the breaking changes release notes - I think I would prefer that over a deprecation

[Sarah Boyce]

Alternative PR: ​https://github.com/django/django/pull/21798
Concluded we don't need to change the error in a back port

[Sarah Boyce <42296566+sarahboyce@…>]

In 1d50f129:

Fixed #37258, Refs #36644 -- Documented error when default ordering fields are missing in combined queries.

Thank you Adam Johnson for the report.

[Sarah Boyce <42296566+sarahboyce@…>]

In 620c50d:

[6.1.x] Fixed #37258, Refs #36644 -- Documented error when default ordering fields are missing in combined queries.

Thank you Adam Johnson for the report.

Backport of 1d50f12929f8f2d424f0e7cc6b1f693f590f71a2 from main.

[Jacob Walls <jacobtylerwalls@…>]

In 0b40210:

Refs #37258, #36644 -- Fixed release note for clearing default ordering after combination.

[Jacob Walls <jacobtylerwalls@…>]

In 8c26e25:

[6.1.x] Refs #37258, #36644 -- Fixed release note for clearing default ordering after combination.

Backport of 0b40210e4808937a7c0922e8b7502bff4752faa3 from main.