[Django] #35830: Queryset Union doesn't persist column names

[Django]

#35830: Queryset Union doesn't persist column names
-------------------------------------+-------------------------------------
Reporter: Paul Landon Tuckett | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 4.2 | Severity: Normal
Keywords: orm, database, | Triage Stage:
queryset, union, sql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
In Django v3.2.25 doing a queryset.union() would preserve the original
column names. Upgrading to Django 4.2.16, this is no longer the case. It
converts all the columns (minus annotations), to col1, col2, etc. The
column names are the same in each queryset and the data types are the
same. Even if you do a union() on the exact same queryset, the columns are
renamed, ie: queryset.union(queryset). If this is expected behavior,
please disregard. I'm not seeing anything in the change log that
references this change.
--
Ticket URL: <https://code.djangoproject.com/ticket/35830>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35830: Queryset Union doesn't persist column names
-------------------------------------+-------------------------------------

Reporter: Paul Landon Tuckett | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: orm, database, | Triage Stage:
queryset, union, sql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

Hello Paul.

If you could provide a set of models and a queryset that reproduces it
would be easier to bisect the changeset that caused it and determine if it
was intended or not. I suspect this was a necessary change to address an
issue when combining querysets mixing different field names but it's hard
to tell for sure without a way to reproduce what you are experiencing.
--
Ticket URL: <https://code.djangoproject.com/ticket/35830#comment:1>

[Django]

#35830: Queryset Union doesn't persist column names
-------------------------------------+-------------------------------------
Reporter: Paul Landon Tuckett | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, database, | Triage Stage:
queryset, union, sql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Paul Landon Tuckett):

* Attachment "django_bug.py" added.

--
Ticket URL: <https://code.djangoproject.com/ticket/35830>

[Django]

#35830: Queryset Union doesn't persist column names
-------------------------------------+-------------------------------------
Reporter: Paul Landon Tuckett | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, database, | Triage Stage:
queryset, union, sql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Paul Landon Tuckett):

* Attachment "django_bug.2.py" added.

[Django]

#35830: Queryset Union doesn't persist column names
-------------------------------------+-------------------------------------
Reporter: Paul Landon Tuckett | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, database, | Triage Stage:
queryset, union, sql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Paul Landon Tuckett):

* Attachment "django_bug.2.py" removed.

[Django]

#35830: Queryset Union doesn't persist column names
-------------------------------------+-------------------------------------
Reporter: Paul Landon Tuckett | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: orm, database, | Triage Stage:
queryset, union, sql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Paul Landon Tuckett):

Hello Simon,

Sure, I attached a file that shows a union between 2 querysets of the same
model and the sql output. Let me know if that works or you need more
information. I tested it both with the upgrade and without it.
--
Ticket URL: <https://code.djangoproject.com/ticket/35830#comment:2>

[Django]

#35830: Queryset Union doesn't persist column names
-------------------------------------+-------------------------------------
Reporter: Paul Landon Tuckett | Owner: (none)

Type: Bug | Status: closed

Component: Database layer | Version: 4.2
(models, ORM) |

Severity: Normal | Resolution: invalid

Keywords: orm, database, | Triage Stage:
queryset, union, sql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* resolution: => invalid
* status: new => closed

Comment:

By [https://docs.djangoproject.com/en/5.1/internals/contributing/triaging-
tickets/#bisecting-a-regression bisecting the changes since 3.2] I
identified 70499b25c708557fb9ee2264686cd172f4b2354e as the change that
introduced the aliasing in order to resolve #34123.

The problem it addresses arise when using `select_related` in queryset
that use union as when it's the case and an ambiguous alias is referenced
for ordering purposes. For example if you do

{{{#!python
base = Book.objects.select_related("author")
base.filter(rating__gt=3).union(base.filter(title__contains="Foo")).order_by("id")
}}}

The generated outer `ORDER BY` clause cannot simply reference `"id"` as
it's ambiguous whether it's `book.id` or `author.id` so `book.id` must be
aliased.

It it understood that only ambiguous column names that are referenced by
`ORDER BY` absolutely need to be aliased but it was much easier to
systematically alias all columns instead.

So to answer your question it is expected that the SQL changed and since
it doesn't change the semantic of the query it is not considered a bug. If
you care about the exact name returned from the query you should resort to
`values` instead.
--
Ticket URL: <https://code.djangoproject.com/ticket/35830#comment:3>

[Django]

#35830: Queryset Union doesn't persist column names
-------------------------------------+-------------------------------------
Reporter: Paul Landon Tuckett | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: orm, database, | Triage Stage:
queryset, union, sql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Paul Landon Tuckett):

I totally understand that column references can be ambiguous, but we
convert these Django queries into SQL and inject them into raw sql, so
it's a major overhaul for us to have to convert all the Django queries to
raw sql because of this change. We do use `values` and/or `values_list`,
but it still doesn't preserve the column names. Even with an `order_by`
and `values` combination, same result. If there is another way we can
force the Django ORM to preserver these column names, please advise.
--
Ticket URL: <https://code.djangoproject.com/ticket/35830#comment:4>