id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34502	Union giving wrong column ordering even when using .values/.values_list	Thiago Bellini Ribeiro	nobody	"This is a simplified example of a union I have in a project:

{{{
Product.objects.all().annotate(
    kind=Value(""base_price"", output_field=models.CharField()),
    priority=Value(0),
    product_id=F(""id""),
    pos_id=Value(None, output_field=models.BigAutoField()),
).values(
    ""kind"",
    ""priority"",
    ""price"",
    ""product_id"",
    ""pos_id"",
).order_by().union(
    PriceTableProduct.objects.all().annotate(
        priority=Value(1),
        kind=Value(""price-table"", output_field=models.CharField()),
        pos_id=F(""price_table__pos__id""),
    )
    .values(
        ""kind"",
        ""priority"",
        ""price"",
        ""product_id"",
        ""pos_id"",
    ),
)
}}}

On both sqlite and postgresql this produces wrong sql. On sqlite:

{{{
SELECT ""product_product"".""price"" AS ""col1"",
       'base_price' AS ""kind"",
       0 AS ""priority"",
       ""product_product"".""id"" AS ""product_id"",
       NULL AS ""pos_id""
  FROM ""product_product""
 UNION SELECT ""product_pricetableproduct"".""price"" AS ""col1"",
       ""product_pricetableproduct"".""product_id"" AS ""col2"",
       1 AS ""priority"",
       'price-table' AS ""kind"",
       ""pos_pos"".""id"" AS ""pos_id""
  FROM ""product_pricetableproduct""
 INNER JOIN ""product_pricetable""
    ON (""product_pricetableproduct"".""price_table_id"" = ""product_pricetable"".""id"")
  LEFT OUTER JOIN ""pos_pos""
    ON (""product_pricetable"".""id"" = ""pos_pos"".""price_table_id"")
 LIMIT 21
}}}

On postgreql:

{{{
(
        SELECT ""product_product"".""price"" AS ""col1"",
               'base_price' AS ""kind"",
               0 AS ""priority"",
               ""product_product"".""id"" AS ""product_id"",
               NULL AS ""pos_id""
          FROM ""product_product""
       )
 UNION (
        SELECT ""product_pricetableproduct"".""price"" AS ""col1"",
               ""product_pricetableproduct"".""product_id"" AS ""col2"",
               1 AS ""priority"",
               'price-table' AS ""kind"",
               ""pos_pos"".""id"" AS ""pos_id""
          FROM ""product_pricetableproduct""
         INNER JOIN ""product_pricetable""
            ON (""product_pricetableproduct"".""price_table_id"" = ""product_pricetable"".""id"")
          LEFT OUTER JOIN ""pos_pos""
            ON (""product_pricetable"".""id"" = ""pos_pos"".""price_table_id"")
       )
 LIMIT 21
}}}

You can see that the orders given to `values` are not preserved, and thus the returned value is wrong (e.g. ""kind"" is the second column in the base query, but ""product_id"" is the second column on the union query)

I'm using django 4.2, so not sure if this is a 4.2 regression or some old issue.

obs. the `.order_by` in the first query is also another issue. Since that model has an `ordering` defined in its meta, I get `DatabaseError: ORDER BY not allowed in subqueries of compound statements.` when running that query on sqlite (on postgresql it is fine and works without the empty order_by, but the main issue in this ticket still exists)"	Bug	closed	Database layer (models, ORM)	4.2	Normal	duplicate		Simon Charette	Unreviewed	0	0	0	0	0	0