QuerySet.values()/values_list() crashes on a combined queryset ordered by "extra" select.

[Mariusz Felisiak]

QuerySet.values()/values_list() crashed on a combined queryset ordered by "extra" select, e.g.

    def test_union_multiple_models_with_values_list_and_order_by_extra_select_different_fields(self):
        from .models import Celebrity
        reserved_name = ReservedName.objects.create(name='rn1', order=0)
        celebrity = Celebrity.objects.create(name='John Doe')
        qs1 = Celebrity.objects.extra(select={'extra_name': 'greatest_fan_id'})
        qs2 = ReservedName.objects.extra(select={'extra_name': 'name'})
        self.assertSequenceEqual(
            qs1.union(qs2).order_by('extra_name').values_list('pk', flat=True),
            [reserved_name.pk, celebrity.pk],
        )   

tries to execute:

SELECT "queries_celebrity"."id", (greatest_fan_id) AS "__orderbycol2" FROM "queries_celebrity"
UNION
SELECT "queries_reservedname"."id", (greatest_fan_id) AS "__orderbycol2" FROM "queries_reservedname"
ORDER BY (2)

and crashes with:

======================================================================
ERROR: test_union_multiple_models_with_values_list_and_order_by_extra_select_different_fields (queries.test_qs_combinators.QuerySetSetOperationTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "django/django/db/backends/sqlite3/base.py", line 416, in execute
    return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: no such column: greatest_fan_id

It's related with #32627 but it's not a regression in 464a4c0c59277056b5d3c1132ac1b4c6085aee08. It crashes in Django 3.1.X with:

  File "django/django/db/models/sql/query.py", line 1912, in add_fields
    join_info = self.setup_joins(name.split(LOOKUP_SEP), opts, alias, allow_many=allow_m2m)
AttributeError: 'NoneType' object has no attribute 'split'

[Carlton Gibson]

OK. The idea being that the extra should come from the respective queryset, not the first one.

Do it the other way around and the test passes qs2.union(qs1)... (Because Celebrity has a name, I suppose).

[David Wobrock]

​PR

[David Wobrock]

It would seem that postgres is smarter than others in this case.
Even with correctly generated SQL, the suggest test method in the ticket ​description fails with:

UNION types integer and character varying cannot be matched

since 'name' and 'greatest_fan_id' do not have a matching type.

[Tim Graham]

Is there a special reason for fixing this rather than adhering to the ​documented policy for QuerySet.extra()?

This is an old API that we aim to deprecate at some point in the future. Use it only if you cannot express your query using other queryset methods. If you do need to use it, please file a ticket using the QuerySet.extra keyword with your use case (please check the list of existing tickets first) so that we can enhance the QuerySet API to allow removing extra(). We are no longer improving or fixing bugs for this method.

[Mariusz Felisiak]

Replying to Tim Graham:

Is there a special reason for fixing this rather than adhering to the ​documented policy for QuerySet.extra()?

This is an old API that we aim to deprecate at some point in the future. Use it only if you cannot express your query using other queryset methods. If you do need to use it, please file a ticket using the QuerySet.extra keyword with your use case (please check the list of existing tickets first) so that we can enhance the QuerySet API to allow removing extra(). We are no longer improving or fixing bugs for this method.

Agreed, let's not create a precedent.